Greywacke

updated and resolved this - the records are bring returned now - thanks

ah - the answer was:

as i was using a compiled regular expression, it was better to pass the modifiers in the second argument of the object

document.getElementById("table_results").tHead.children[1].title = title.replace(re = new RegExp(quote("`Prim_Att_Val` = "" + where["Prim_Att_Val"]+"""),"g"),((val!="")?"`Prim_Att_Val` = ""+val+""":val));

hope this helps many others with similar endeavours!

hi everyone,

i am currently having an issue with the following excerpt of javascript - particularly the "regular expressions not wanting to replace".

		case 19:	// set where Prim_Att_Val			document.getElementById("ajaxbg").style.visibility = "visible";			var val = document.form_services.where_primattval.options[form_services.where_primattval.selectedIndex].value;		// new constraint			var title = document.getElementById("table_results").tHead.children[1].title;										// current constraints			//salert("re = ^/[ AND]*" + quote(" `Prim_Att_Val` = ""+where["Prim_Att_Val"]+""") + "/g");			//salert("val = "+val);			//salert("title = "+title);			if (whereo["Prim_Att_Val"]==-1) {				document.getElementById("table_results").tHead.children[1].title += ((wherecnt>0)?" AND":"")+" `Prim_Att_Val` = ""+val+""";				where["Prim_Att_Val"] = val;				whereo["Prim_Att_Val"] = wherecnt;				wherecnt++;			} else {				//salert(whereo["Prim_Att_Val"]);				//salert(val);				document.getElementById("table_results").tHead.children[1].title = title.replace(re = new RegExp("/"+quote("`Prim_Att_Val` = "" + where["Prim_Att_Val"]+""")+"/g"),(val!="")?"`Prim_Att_Val` = ""+val+""":val);				if (!where["Prim_Att_Val"]) whereo["Prim_Att_Val"] = -1;				salert(title);				salert(re.toString());				salert("`Prim_Att_Val` = ""+val+""");				salert(whereo["Prim_Att_Val"]);				where["Prim_Att_Val"] = val;			}			document.getElementById("ajaxbg").style.visibility = "hidden";			break; 

salert is a custom function which prints to a div at the top of the screen. same results could be achieved by calling alert, it's just neater with all the data being kept and new lines being written.

what is the issue with line 680 - it is not replacing the values as expected? in case 13, it does however - this does not have the string inserted into the regular expression to search for... anybody, please help!

ok the function has been updated as follows:

function popwhereclauses() {	//global $conn;	// set the mysql connection string details	$server = "*****";	$username = "*****";	$password = "*****";	$query = array(		"SELECT DISTINCT UPPER(SC.text_ServiceDescription) AS `service` 		FROM 2_servicescatalogue SS 		INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) 		ORDER BY service ASC;",		"SELECT DISTINCT IF(R.bigint_ParentRegionID =0, R.text_RegionDescription, R1.text_RegionDescription) AS `country` 		FROM 4_servicesuppliers SS 		INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) 		LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 		AND R.bigint_ParentRegionID = R1.bigint_RegionID) 		ORDER BY country ASC;",		"SELECT DISTINCT UPPER(IF(R.bigint_ParentRegionID !=0, R.text_RegionDescription, R0.text_RegionDescription)) AS `region` 		FROM 4_servicesuppliers SS 		INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) 		LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 		AND R.bigint_RegionID = R0.bigint_ParentRegionID) 		ORDER BY region ASC;",		"SELECT DISTINCT UPPER(S.text_SupplierName) AS `supplier` 		FROM 4_servicesuppliers SS 		INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) 		ORDER BY supplier ASC;",		"SELECT DISTINCT UPPER(CASE S.smallint_SupplierStatus WHEN 0 THEN 'Premium' WHEN 1 THEN 'Paused' WHEN 2 THEN 'Trial' WHEN 3 THEN 'Inactive' WHEN 4 THEN 'Freemium' END) AS `status` 		FROM 4_servicesuppliers SS 		INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) 		ORDER BY status ASC;",		"SELECT DISTINCT UPPER(IF(SC.bigint_PrimaryAttributeKey = 0 OR SA1.text_AttributeValue = NULL, 'DEFAULT', SA1.text_AttributeValue)) AS `prim_att_val` 		FROM 4_servicesuppliers SS 		INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) 		LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) 		LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) 		ORDER BY prim_att_val ASC;"	);	$conn = array(		mysql_connect($server, $username, $password, true), 		mysql_connect($server, $username, $password, true), 		mysql_connect($server, $username, $password, true), 		mysql_connect($server, $username, $password, true), 		mysql_connect($server, $username, $password, true), 		mysql_connect($server, $username, $password, true)	);	$result = array(6);	echo "	<whereclauses ";	$groups = array(		'service',		'country',		'region',		'supplier',		'status',		'prim_att_val'	);	for ($i = 0; $i < count($groups); $i++) {		mysql_select_db("performatix", $conn[$i]);		$result[$i] = mysql_query_errors($query[$i], $conn[$i], __FILE__, __LINE__, true);		if ($result[$i]) {			$field = $groups[$i];			echo $field."="";			$counter = 0;			while ($row = mysql_fetch_row($result[$i])) {				$counter++;				echo $row[0].($counter == mysql_num_rows($result))?"":",";			}			echo "" ";			mysql_free_result($result[$i]);		}		mysql_close($conn[$i]);	}	echo "/>rn";} 

resulting in:

<whereclauses service="" country="" region="" supplier="" status="" prim_att_val="" />

the results, however still do not want to print dynamically... how could i get this right?

ok, the function has been modified to be as follows:

function popwhereclauses() {	global $conn;	// set the mysql connection string details	$server = "*****";	$username = "*****";	$password = "*****";	$query = array(		"SELECT UPPER(SC.text_ServiceDescription) AS service 		FROM 2_servicescatalogue SS 		INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) 		ORDER BY service ASC;",		"SELECT DISTINCT IF(R.bigint_ParentRegionID =0, R.text_RegionDescription, R1.text_RegionDescription) AS country 		FROM 4_servicesuppliers SS 		INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) 		LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 		AND R.bigint_ParentRegionID = R1.bigint_RegionID) 		ORDER BY country ASC;",		"SELECT DISTINCT UPPER(IF(R.bigint_ParentRegionID !=0, R.text_RegionDescription, R0.text_RegionDescription)) AS region 		FROM 4_servicesuppliers SS 		INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) 		LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 		AND R.bigint_RegionID = R0.bigint_ParentRegionID) 		ORDER BY region ASC;",		"SELECT DISTINCT UPPER(S.text_SupplierName) AS supplier		FROM 4_servicesuppliers SS 		INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) 		ORDER BY supplier ASC;",		"SELECT DISTINCT UPPER(CASE S.smallint_SupplierStatus WHEN 0 THEN 'Premium' WHEN 1 THEN 'Paused' WHEN 2 THEN 'Trial' WHEN 3 THEN 'Inactive' WHEN 4 THEN 'Freemium' END) AS status 		FROM 4_servicesuppliers SS 		INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) 		ORDER BY status ASC;",		"SELECT DISTINCT UPPER(IF(SC.bigint_PrimaryAttributeKey = 0 OR SA1.text_AttributeValue = NULL, 'DEFAULT', SA1.text_AttributeValue)) AS prim_att_val 		FROM 4_servicesuppliers SS 		INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) 		LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) 		LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) 		ORDER BY prim_att_val ASC;"	);	$conn = array(		mysql_connect($server, $username, $password, true), 		mysql_connect($server, $username, $password, true), 		mysql_connect($server, $username, $password, true), 		mysql_connect($server, $username, $password, true), 		mysql_connect($server, $username, $password, true), 		mysql_connect($server, $username, $password, true)	);	$result = array(6);	echo "	<whereclauses ";	$groups = array(		'service',		'country',		'region',		'supplier',		'status',		'prim_att_val'	);	for ($i = 0; $i < count($groups); $i++) {		$result[$i] = mysql_query_errors($query[$i], $conn[$i], __FILE__, __LINE__, true);		if ($result[$i]) {			$field = $groups[$i];			echo $field."="";			$counter = 0;			while ($row = mysql_fetch_assoc($result[$i])) {				$counter++;				echo $row[$field].($counter == mysql_num_rows($result))?"":",";			}			echo "" ";		}		mysql_free_result($result[$i]);	}	echo "/>rn";}

the results i get for this function, in the xml output however - are as follows:

<whereclauses />

why would this be? where are service, country, region, supplier, status & prim_att_val?? somebody please help asap!!!

this query has been modified in order to use 4_servicesuppliers as the central table...

in my effort to try and get the results i need, i have split the query into 6 - each taking around 30 seconds to complete - i understand it is due to the multiple, complex joins. below are the queries:

$query = array(	"SELECT 		GROUP_CONCAT(DISTINCT UPPER(SC.text_ServiceDescription) SEPARATOR ',') AS service 	FROM 		4_servicesuppliers SS 	INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) 	INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) 	INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) 	LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) 	LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) 	LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) 	LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) 	LEFT JOIN 9_supplierattributes SA3 ON (S.bigint_SupplierID = SA3.bigint_SupplierID AND SC.bigint_ServiceID = SA3.bigint_ServiceID AND ((R.bigint_RegionID = SA3.bigint_RegionID OR R.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R0.bigint_RegionID = SA3.bigint_RegionID OR R0.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R1.bigint_RegionID = SA3.bigint_RegionID OR R1.bigint_ParentRegionID = SA3.bigint_RegionID)) AND SA1.bigint_AttributeID = SA3.bigint_AttributeID) 	LEFT JOIN 3_serviceattributes SA2 ON (SA3.bigint_AttributeID = SA2.bigint_AttributeID AND SC.bigint_ServiceID = SA2.bigint_AttributeServiceID) 	WHERE 		SA2.text_AttributeValue IS NULL 	GROUP BY 		UPPER(SC.text_ServiceDescription);",	"SELECT 		GROUP_CONCAT(DISTINCT IF(			 R.bigint_ParentRegionID=0, 			 R.text_RegionDescription, 			 R1.text_RegionDescription		) SEPARATOR ',') AS country 	FROM 		4_servicesuppliers SS 	INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) 	INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) 	INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) 	LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) 	LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) 	LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) 	LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) 	LEFT JOIN 9_supplierattributes SA3 ON (S.bigint_SupplierID = SA3.bigint_SupplierID AND SC.bigint_ServiceID = SA3.bigint_ServiceID AND ((R.bigint_RegionID = SA3.bigint_RegionID OR R.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R0.bigint_RegionID = SA3.bigint_RegionID OR R0.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R1.bigint_RegionID = SA3.bigint_RegionID OR R1.bigint_ParentRegionID = SA3.bigint_RegionID)) AND SA1.bigint_AttributeID = SA3.bigint_AttributeID) 	LEFT JOIN 3_serviceattributes SA2 ON (SA3.bigint_AttributeID = SA2.bigint_AttributeID AND SC.bigint_ServiceID = SA2.bigint_AttributeServiceID) 	WHERE 		SA2.text_AttributeValue IS NULL 	GROUP BY 		UPPER(IF(			 R.bigint_ParentRegionID=0, 			 R.text_RegionDescription, 			 R1.text_RegionDescription		));",	"SELECT 		GROUP_CONCAT(DISTINCT UPPER(IF(			 R.bigint_ParentRegionID!=0, 			 R.text_RegionDescription, 			 R0.text_RegionDescription		)) SEPARATOR ',') AS region 	FROM 		4_servicesuppliers SS 	INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) 	INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) 	INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) 	LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) 	LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) 	LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) 	LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) 	LEFT JOIN 9_supplierattributes SA3 ON (S.bigint_SupplierID = SA3.bigint_SupplierID AND SC.bigint_ServiceID = SA3.bigint_ServiceID AND ((R.bigint_RegionID = SA3.bigint_RegionID OR R.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R0.bigint_RegionID = SA3.bigint_RegionID OR R0.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R1.bigint_RegionID = SA3.bigint_RegionID OR R1.bigint_ParentRegionID = SA3.bigint_RegionID)) AND SA1.bigint_AttributeID = SA3.bigint_AttributeID) 	LEFT JOIN 3_serviceattributes SA2 ON (SA3.bigint_AttributeID = SA2.bigint_AttributeID AND SC.bigint_ServiceID = SA2.bigint_AttributeServiceID) 	WHERE 		SA2.text_AttributeValue IS NULL 	GROUP BY 		UPPER(IF(			 R.bigint_ParentRegionID!=0, 			 R.text_RegionDescription, 			 R0.text_RegionDescription		));",	"SELECT 		GROUP_CONCAT(DISTINCT UPPER(S.text_SupplierName)) AS supplier 	FROM 		4_servicesuppliers SS 	INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) 	INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) 	INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) 	LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) 	LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) 	LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) 	LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) 	LEFT JOIN 9_supplierattributes SA3 ON (S.bigint_SupplierID = SA3.bigint_SupplierID AND SC.bigint_ServiceID = SA3.bigint_ServiceID AND ((R.bigint_RegionID = SA3.bigint_RegionID OR R.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R0.bigint_RegionID = SA3.bigint_RegionID OR R0.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R1.bigint_RegionID = SA3.bigint_RegionID OR R1.bigint_ParentRegionID = SA3.bigint_RegionID)) AND SA1.bigint_AttributeID = SA3.bigint_AttributeID) 	LEFT JOIN 3_serviceattributes SA2 ON (SA3.bigint_AttributeID = SA2.bigint_AttributeID AND SC.bigint_ServiceID = SA2.bigint_AttributeServiceID) 	WHERE 		SA2.text_AttributeValue IS NULL 	GROUP BY 		UPPER(S.text_SupplierName);",	"SELECT 		GROUP_CONCAT(DISTINCT UPPER(CASE S.smallint_SupplierStatus 			 WHEN 0 THEN 'Premium' 			 WHEN 1 THEN 'Paused' 			 WHEN 2 THEN 'Trial' 			 WHEN 3 THEN 'Inactive' 			 WHEN 4 THEN 'Freemium' 		END) SEPARATOR ',') AS status 	FROM 		4_servicesuppliers SS 	INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) 	INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) 	INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) 	LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) 	LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) 	LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) 	LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) 	LEFT JOIN 9_supplierattributes SA3 ON (S.bigint_SupplierID = SA3.bigint_SupplierID AND SC.bigint_ServiceID = SA3.bigint_ServiceID AND ((R.bigint_RegionID = SA3.bigint_RegionID OR R.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R0.bigint_RegionID = SA3.bigint_RegionID OR R0.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R1.bigint_RegionID = SA3.bigint_RegionID OR R1.bigint_ParentRegionID = SA3.bigint_RegionID)) AND SA1.bigint_AttributeID = SA3.bigint_AttributeID) 	LEFT JOIN 3_serviceattributes SA2 ON (SA3.bigint_AttributeID = SA2.bigint_AttributeID AND SC.bigint_ServiceID = SA2.bigint_AttributeServiceID) 	WHERE 		SA2.text_AttributeValue IS NULL 	GROUP BY 		UPPER(CASE S.smallint_SupplierStatus 			 WHEN 0 THEN 'Premium' 			 WHEN 1 THEN 'Paused' 			 WHEN 2 THEN 'Trial' 			 WHEN 3 THEN 'Inactive' 			 WHEN 4 THEN 'Freemium' 		END);",	"SELECT 		GROUP_CONCAT(DISTINCT UPPER(IF (			 SC.bigint_PrimaryAttributeKey = 0 OR SA1.text_AttributeValue = NULL, 			 'DEFAULT', 			 SA1.text_AttributeValue		)) SEPARATOR ',') AS prim_att_val 	FROM 		4_servicesuppliers SS 	INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) 	INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) 	INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) 	LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) 	LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) 	LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) 	LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) 	LEFT JOIN 9_supplierattributes SA3 ON (S.bigint_SupplierID = SA3.bigint_SupplierID AND SC.bigint_ServiceID = SA3.bigint_ServiceID AND ((R.bigint_RegionID = SA3.bigint_RegionID OR R.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R0.bigint_RegionID = SA3.bigint_RegionID OR R0.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R1.bigint_RegionID = SA3.bigint_RegionID OR R1.bigint_ParentRegionID = SA3.bigint_RegionID)) AND SA1.bigint_AttributeID = SA3.bigint_AttributeID) 	LEFT JOIN 3_serviceattributes SA2 ON (SA3.bigint_AttributeID = SA2.bigint_AttributeID AND SC.bigint_ServiceID = SA2.bigint_AttributeServiceID) 	WHERE 		SA2.text_AttributeValue IS NULL 	GROUP BY 		UPPER(IF (			 SC.bigint_PrimaryAttributeKey = 0 OR SA1.text_AttributeValue = NULL, 			 'DEFAULT', 			 SA1.text_AttributeValue		));"); 

however, the GROUP_CONCAT does not concatenate these fields into one query... the results i am getting, in 6 individual recordsets is from 5 records, to 223 records as follows per individual query executed in pma:

Showing rows 0 - 13 ( 14 total, Query took 24.6017 sec)serviceACCOUNTING BOOKKEEPINGBAKKIE CANOPYBROADBAND INTERNETBUSINESS PHONE SYSTEMSCOMPANY REGISTRATIONSCONFERENCE VENUESDEBT COLLECTION AGENCIESDIGITAL OFFICE COPIERSOFFICE COFFEEOFFICE COLOUR PRINTERSOFFICE FURNITUREOFFICE MOVERSOFFICE NETWORK CABLINGOFFICE WATER

Showing rows 0 - 4 ( 5 total, Query took 24.5863 sec)

country

BOTSWANALESOTHONAMIBIASOUTH AFRICAZIMBABWE

Showing rows 0 - 29 ( 41 total, Query took 24.3603 sec)

region

CENTRAL - HARARECENTRAL - WINDHOEKCOASTAL - WALVIS BAYEASTERN - FRANCISTOWNEASTERN CAPE - BUFFALO CITYEASTERN CAPE - MTHATHAEASTERN CAPE - NELSON MANDELAFREE STATE - BLOEMFONTEINFREE STATE - GOLDFIELDSGAUTENG - EKURHULENI METROGAUTENG - JOBURG METROGAUTENG - TSHWANE METROGAUTENG - VAAL AREAGAUTENG - WEST RANDKWAZULU NATAL - DURBANKWAZULU NATAL - EMPANGENIKWAZULU NATAL - MARITZBURGKWAZULU NATAL - NEWCASTLELIMPOPO - LEPHALALELIMPOPO - MAKHADOLIMPOPO - POLOKWANELIMPOPO - TZANEENMPUMALANGA - NELSPRUITMPUMALANGA - SECUNDA/ERMELOMPUMALANGA - WITBANK/MIDDELBURGNORTHERN - OTJIWARONGONORTHERN CAPE - KIMBERLEYNORTHERN CAPE - SPRINGBOKNORTHERN CAPE - UPINGTONNORTHWEST - KLERKSDORP

Showing rows 0 - 29 ( 223 total, Query took 0.0003 sec)supplier

3CX@LANTICACE CANOPIES (RANDBURG)ADVANCED COMMUNICATIONSAERO CANOPIES (ISANDO)AIR-CANOPY (JOHANNESBURG)AIRITE CANOPIES (WINDHOEK)ALRIC CANOPIES (NORTH END, PORT ELIZABETH)ALRIC CANOPIES (PORT ELIZABETH)ANDY CAB PLATINUM CANOPIES (CAPE TOWN)ANDY CAB PLATINUM CANOPIES (GAUTENG)ANDY CAB ROADRUNNER CANOPIES (CAPE TOWN)ANDY CAB ROADRUNNER CANOPIES (GAUTENG)ATLANTIC GROUP AUDIOMAX (EAST LONDON)B SMART COMMUNICATIONSBAKKIE BOUTIQUE (WELKOM)BARNEY CANOPIES (PINETOWN)BASELINE NGN (NEXT GENERATION NETWORKS)BAY CANOPIES (PORT ELIZABETH)BEEKMAN CANOPIESBEEKMAN CANOPIES (BENONI)BEEKMAN CANOPIES (BETHAL)BEEKMAN CANOPIES (CAPE TOWN)BEEKMAN CANOPIES (GEORGE)BEEKMAN CANOPIES (KRUGERSDORP)BEEKMAN CANOPIES (PINETOWN)BEEKMAN CANOPIES (RANDBURG)BEEKMAN FITMENT CENTRE (PRETORIA)BEKKERS CANOPY CENTRE (WITBANK)

Showing rows 0 - 4 ( 5 total, Query took 23.9434 sec)

status

FREEMIUMINACTIVEPAUSEDPREMIUMTRIAL

Showing rows 0 - 4 ( 5 total, Query took 24.5149 sec)

prim_att_val

DEFAULTFORSALENEWREPAIRSUSED

we no longer require counting these records, but instead retrieving them in a dynamic selection, which is the combination of the above queries - to be updated and modified via ajax calls. why can't i get these to display one record, concatenating the results? :/ i have a feeling it is similar to the previous issue...

i have had a look into pcntl multithreading to execute these all at once but have just realised, that if i could do them in one query, retrieving these values concatenated - that the script could continue to work in linear fashion...

only how would i do that - not being as familiar to MySQL GROUP_CONCAT's, GROUP_BY's and JOIN's as i would like to be? what say you, justsomeguy - or anybody else about a single query vs. splitting the processing and keeping it to seperate queries as above?

sincerely - Pierre du Toit.

sorry justsomeguy - do not understand/see your perspective...

the subquery was edited as follows:

SELECT 	COUNT(DISTINCT S.bigint_SupplierID)FROM 5_suppliers SLEFT JOIN 9_supplierattributes SA0 ON (S.bigint_SupplierID = SA0.bigint_SupplierID) LEFT JOIN 4_servicesuppliers SS ON (SA0.bigint_ServiceID = SS.bigint_ServiceID AND S.bigint_SupplierID = SS.bigint_SupplierID) LEFT JOIN 2_servicescatalogue SC ON (SA0.bigint_ServiceID = SC.bigint_ServiceID) LEFT JOIN 3_serviceattributes SA1 ON (SC.bigint_PrimaryAttributeKey = SA1.bigint_AttributeID) LEFT JOIN 3_serviceattributes SA2 ON (SA0.bigint_AttributeID = SA2.bigint_AttributeID) WHERE SA0.bigint_ServiceID = 1       AND SS.bigint_RegionID = 1       AND SA1.text_AttributeDescription NOT IN (SELECT DISTINCT SA2.text_AttributeDescription)GROUP BY S.bigint_SupplierID;

however, when included within the main query as follows - it returns 0 in PMA! yet we have around 2-4 suppliers per region in our database (i just have to replace the id 1 for region with the region id i need to test - there are about 17 in total)...

SELECT 	SW1.bigint_RegionID, 	R.text_RegionDescription, 	SW1.smallint_SuppliersWanted, 	SW1.bigint_AttributeValue, 	COUNT(		SELECT                         COUNT(DISTINCT SA1.text_AttributeDescription) 		FROM 5_suppliers S 		LEFT JOIN 9_supplierattributes SA0 ON (S.bigint_SupplierID = SA0.bigint_SupplierID) 		LEFT JOIN 4_servicesuppliers SS ON (SA0.bigint_ServiceID = SS.bigint_ServiceID AND S.bigint_SupplierID = SS.bigint_SupplierID) 		LEFT JOIN 2_servicescatalogue SC ON (SA0.bigint_ServiceID = SC.bigint_ServiceID) 		LEFT JOIN 3_serviceattributes SA1 ON (SC.bigint_PrimaryAttributeKey = SA1.bigint_AttributeID) 		LEFT JOIN 3_serviceattributes SA2 ON (SA0.bigint_AttributeID = SA2.bigint_AttributeID) 		WHERE SA0.bigint_ServiceID = 1 		      AND SS.bigint_RegionID = 1 		      AND SA1.text_AttributeDescription NOT IN (SELECT DISTINCT SA2.text_AttributeDescription) 		GROUP BY S.bigint_SupplierID 	) AS bigint_SuppliersActualAmount, 	C.tinyint_GetMethod FROM 	11_supplierswanted SW1 JOIN 1_regions R ON SW1.bigint_RegionID = R.bigint_RegionID JOIN 32_webformconfigs C ON C.bigint_FormService = SW1.bigint_ServiceID AND C.tinyint_FormDefault WHERE SW1.bigint_ServiceID = 1 ORDER BY 	R.text_RegionDescription ASC;

however - when i include this in the php document and run it, i get the following error from the site console:

 

 

===>SELECT SW1.bigint_RegionID, R.text_RegionDescription, SW1.smallint_SuppliersWanted, SW1.bigint_AttributeValue, COUNT( SELECT COUNT(DISTINCT SA1.text_AttributeDescription) FROM 5_suppliers S LEFT JOIN 9_supplierattributes SA0 ON (S.bigint_SupplierID = SA0.bigint_SupplierID) LEFT JOIN 4_servicesuppliers SS ON (SA0.bigint_ServiceID = SS.bigint_ServiceID AND S.bigint_SupplierID = SS.bigint_SupplierID) LEFT JOIN 2_servicescatalogue SC ON (SA0.bigint_ServiceID = SC.bigint_ServiceID) LEFT JOIN 3_serviceattributes SA1 ON (SC.bigint_PrimaryAttributeKey = SA1.bigint_AttributeID) LEFT JOIN 3_serviceattributes SA2 ON (SA0.bigint_AttributeID = SA2.bigint_AttributeID) WHERE SA0.bigint_ServiceID = SW1.bigint_ServiceID AND SS.bigint_RegionID = SW1.bigint_RegionID AND SA1.text_AttributeDescription NOT IN (SELECT DISTINCT SA2.text_AttributeDescription) GROUP BY S.bigint_SupplierID ) AS bigint_SuppliersActualAmount, C.tinyint_GetMethod FROM 11_supplierswanted SW1 JOIN 1_regions R ON SW1.bigint_RegionID = R.bigint_RegionID JOIN 32_webformconfigs C ON C.bigint_FormService = SW1.bigint_ServiceID AND C.tinyint_FormDefault WHERE SW1.bigint_ServiceID = 10 ORDER BY R.text_RegionDescription ASC;ERROR #1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(DISTINCT SA1.text_AttributeDescription) FROM 5_su' at line 7 in /var/www/performatix.co/production/scripts/ajax_services.php on Line 90

what does this mean? what needs to change?? and where??? once again, i battle to see the forest for the flowers or even trees within it!

ok, have updated query as follows (the subqueries need to be within brackets within the count function):

SELECT (		SELECT COUNT(			(				SELECT SL.timestamp_LeadCreated FROM 27_leadssent LS 				LEFT JOIN 25_serviceleads SL ON (SL.bigint_LeadID = LS.bigint_LeadID) 				WHERE 					LS.bigint_SupplierID = 192 					AND SL.timestamp_LeadCreated > DATE_SUB(CURDATE(), INTERVAL 30 DAY)			)		) + 1 AS `l_Cnt`	) / (		SELECT COUNT(			(				SELECT LM.timestamp_LastUpdated FROM 43_leadsmatched LM 				WHERE CONCAT('%,',192,',%') LIKE CONCAT(',',LM.text_PremiumSupplierIDs,',') 				AND LM.timestamp_LastUpdated > DATE_SUB(CURDATE(), INTERVAL 30 DAY)			)		) AS `t_Cnt`) * 100 AS `lead_share`;

but it gives following error instead of counting the records from the subqueries.

#1242 - Subquery returns more than 1 row

somebody please help!

hi again...

am having an issue with the following error - the query follows the error, and the table layouts are attached if you wish to recreate this issue to try and assist discovering what the heck the problem is here...

 

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM 27_leadssent LS LEFT JOIN 25_serviceleads SL ON (SL.bigint_Lea' at line 4

SELECT (	(		COUNT(			SELECT * FROM 27_leadssent LS 			LEFT JOIN 25_serviceleads SL ON (SL.bigint_LeadID = LS.bigint_LeadID) 			WHERE 				SL.bigint_SupplierID = 192 				AND SL.timestamp_LeadCreated > DATE_SUB(CURDATE(), INTERVAL 30 DAY)		)	+ 1	) / (		COUNT(			SELECT * FROM 43_leadsmatched LM 			WHERE CONCAT(',',192,',') LIKE CONCAT(',',LM.text_PremiumSupplierIDs,',') 			AND LM.timestamp_LastUpdated > DATE_SUB(CURDATE(), INTERVAL 30 DAY)		)	* 100	)) AS `lead_share`

there is also a version of this query for freemium suppliers (which uses 10_serviceprospects and 13_prospectleadsent instead of 25_serviceleads and 27_leadssent), all 5 tables' creation sql was exported in:

performatix.sql.zip

from phpMyAdmin on our MySQL 5.5 server.

what need to change so that the above sql code would work and not throw the error mentioned above???

afraid i do not see the forest for the trees here once again...

What exactly is the issue? The first spreadsheet is a summary of the other 2. Kango, for example, is listed with 34 on the first spreadsheet (it's only listed once), and if you add up all of the entries for Kango on the other sheets (listed multiple times) then it adds up to 34. What errors are you seeing with the data?

afraid i need to try a different approach to resolving this, i'm going to be re-writing the queries from scratch today - according to my client not all of the records tally up... especially when a different working service is used! will reply back here with the queries once working 100%. after discussing with a friend (unfortunately he's terribly busy), i realised that in the second and third selects - that the JOIN types need to change as the perspective changes. will update once this work is complete.

hi there, the following query was developed and works as it should.

SELECT 	(		SELECT 			COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP1 		LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID 		WHERE PLS1.timestamp_Sent 			BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" 			AND SP1.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS1.bigint_SupplierID = S.bigint_SupplierID 	) AS `Previous Month`, 	(		SELECT 			COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP2 		LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID 		WHERE PLS2.timestamp_Sent 			BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" 			AND SP2.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS2.bigint_SupplierID = S.bigint_SupplierID 	) AS `Last Month`, 	(		SELECT 			COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) 		FROM 10_serviceprospects SP3 		LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID 		WHERE PLS3.timestamp_Sent 			BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" 			AND SP3.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS3.bigint_SupplierID = S.bigint_SupplierID 	) AS `Current Month`, 	(		SELECT 			COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP0 		LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID 		WHERE PLS0.timestamp_Sent 			BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" 			AND SP0.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS0.bigint_SupplierID = S.bigint_SupplierID 	) AS `Total`, 	S.text_SupplierName AS `Supplier Name` FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) WHERE SS.bigint_ServiceID = 1 												# Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 											# Freemium Suppliers OnlyGROUP BY S.bigint_SupplierID ORDER BY `Total` DESC, `Supplier Name` ASC;										# Order by Total, then Supplier Name

took about a day to develop this inbetween other work. it returns the correct data.

the output is available at https://performatix.co/csv/Bakkie_Canopies_Freemium_Supplier_Opportunities_2013-10-09.csv

i was then requested to change the ordering by `Total` and primarily by `Region Description`; as well as grouping by Region ID and primarily by Supplier ID, creating a secondary output.

SELECT 	R.text_RegionDescription AS `Region Description`, 	(		SELECT 			COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP1 		LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID 		WHERE PLS1.timestamp_Sent 			BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" 			AND SP1.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS1.bigint_SupplierID = S.bigint_SupplierID 			AND SP1.bigint_RegionID = R.bigint_RegionID	) AS `Previous Month`, 	(		SELECT 			COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP2 		LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID 		WHERE PLS2.timestamp_Sent 			BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" 			AND SP2.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS2.bigint_SupplierID = S.bigint_SupplierID 			AND SP2.bigint_RegionID = R.bigint_RegionID	) AS `Last Month`, 	(		SELECT 			COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) 		FROM 10_serviceprospects SP3 		LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID 		WHERE PLS3.timestamp_Sent 			BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" 			AND SP3.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS3.bigint_SupplierID = S.bigint_SupplierID 			AND SP3.bigint_RegionID = R.bigint_RegionID	) AS `Current Month`, 	(		SELECT 			COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP0 		LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID 		WHERE PLS0.timestamp_Sent 			BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" 			AND SP0.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS0.bigint_SupplierID = S.bigint_SupplierID 			AND SP0.bigint_RegionID = R.bigint_RegionID	) AS `Total`, 	S.text_SupplierName AS `Supplier Name` FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID)WHERE SS.bigint_ServiceID = 1 											# Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 										# Freemium Suppliers OnlyGROUP BY SS.bigint_RegionID, SS.bigint_SupplierID ORDER BY `Total` DESC, `Region Description` ASC;								# Order by Total, then Region Description 

this took about an hour inbetween other work.

the results are in https://performatix.co/csv/Bakkie_Canopies_Regional_Freemium_Supplier_Opportunities_2013-10-09.csv

however you will notice that the values do not corroborate with the first (this was discovered recently after reviewing the csv's! what is wrong with this query to get it to corroborate with the first?). i was then asked to order by `Total` but primarily by `Region Description`, and group by Region ID - creating a third output.

SELECT 	R.text_RegionDescription AS `Region Description`, 	(		SELECT 			COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP1 		LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID 		WHERE PLS1.timestamp_Sent 			BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" 			AND SP1.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS1.bigint_SupplierID = S.bigint_SupplierID 			AND SP1.bigint_RegionID = R.bigint_RegionID	) AS `Previous Month`, 	(		SELECT 			COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP2 		LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID 		WHERE PLS2.timestamp_Sent 			BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" 			AND SP2.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS2.bigint_SupplierID = S.bigint_SupplierID 			AND SP2.bigint_RegionID = R.bigint_RegionID	) AS `Last Month`, 	(		SELECT 			COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) 		FROM 10_serviceprospects SP3 		LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID 		WHERE PLS3.timestamp_Sent 			BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" 			AND SP3.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS3.bigint_SupplierID = S.bigint_SupplierID 			AND SP3.bigint_RegionID = R.bigint_RegionID	) AS `Current Month`, 	(		SELECT 			COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP0 		LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID 		WHERE PLS0.timestamp_Sent 			BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" 			AND SP0.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS0.bigint_SupplierID = S.bigint_SupplierID 			AND SP0.bigint_RegionID = R.bigint_RegionID	) AS `Total`FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID)WHERE SS.bigint_ServiceID = 1 							# Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 						# Freemium Suppliers OnlyGROUP BY SS.bigint_RegionID ORDER BY `Total` DESC, `Region Description` ASC;	# Order by Total, then Region Description

this took approximately another hour between other work.

the results are in https://performatix.co/csv/Bakkie_Canopies_Regional_Freemium_Supplier_Opportunities_2013-10-09.csv

once again - the data does not corroborate with the first csv,

the issue i am facing is that i do not understand why the queries are not synchronous to the data... the first query works just as well for a different service, however the second and third queries do not return anything at all. i am assuming that this is because they do not have varying regions (all the same). further than this i just keep hitting my head... anyone comfortable enough to have a look at the queries and suggest changes other than the ones i applied?

the data from the tables are available at https://performatix.co/csv/1_regions, 2_servicescatalogue, 4_servicesuppliers, 5_suppliers, 10_serviceprospects & 13_prospectleadsent.csv.

awesome sight, Foxy Mod at catching the . instead of the : on line 69!

as for the others (lines 77 and 197) i've tested with only the php eol semicolon - the error remains where it is,

and without any semicolon - the error jumps to the following line

as well as with only the query end - the error jumps to following line... - on both queries.

also noticed $wsql was still being used - here $rsql is supposed to be used.

but the errors on lines 77 and 197 remain...

edit: thanks every body for the assistance - i have located the errors a week or two ago and resolved the issue

hi all,

apparently i have errors on these lines within the function below, according to Dreamweaver CS6 (version 12.0 build 5861) - but unable to locate issues with Notepad++ (6.4.5). when this function is processed - it generates one of those forsaken untraceable http 500 issues raised by an invalid mysql query! have looked at error logs - but according to DW CS6 the query does not compile completely.

<?phpfunction getleads($tcategory, $table, $order, $country="",$state="",$county="",$zip="",$suppliers="",$search="") {	global $conn;	$wsql = array();	if ($country!="") {		$f_country = explode(",",$country);		for ($i = 0; $i < count($f_country); $i++) {			$r_country[$i] = "SS.bigint_RegionID = ".$f_country[$i];			//array_push($f_country, "S.bigint_CountryID = ".$f_country[$i]);		}		//array_push($wsql,"(".implode(" OR ",$f_country).")");		array_push($rsql,"(".implode(" OR ",$r_country).")");	}	if ($state!="") {		$f_state = explode(",",$state);		for ($i = 0; $i < count($f_state); $i++) {;			$r_state[$i] = "SS.bigint_RegionID = ".$f_state[$i];			//$f_state[$i] = "S.bigint_StateID = ".$f_state[$i];		}		//array_push($wsql,"(".implode(" OR ",$f_state).")");		array_push($rsql,"(".implode(" OR ",$r_state).")");	}	if ($county!="") {		$f_county = explode(",",$county);		for ($i = 0; $i < count($f_county); $i++) {			$r_county[$i] = "SS.bigint_RegionID = ".$f_county[$i];			//$f_county[$i] = "S.bigint_CountyID = ".$f_county[$i];		}		//array_push($wsql,"(".implode(" OR ",$f_county).")");		array_push($rsql,"(".implode(" OR ",$r_county).")");	}	if ($zip!="") {		$f_zip = explode(",",$zip);		for ($i = 0; $i < count($f_zip); $i++) {			$r_zip[$i] = "SS.bigint_RegionID = ".$f_zip[$i];			//$f_zip[$i] = "S.bigint_ZipCodeID = ".$f_zip[$i];		}		//array_push($wsql,"(".implode(" OR ",$f_zip).")");		array_push($rsql,"(".implode(" OR ",$r_zip).")");	}	if ($suppliers!="") {		$f_suppliers = "S.bigint_SupplierID = ".$suppliers;		array_push($wsql,$f_suppliers);	}	array_push($wsql,"SL.timestamp_LeadCreated BETWEEN "".$GLOBALS["m"][0]."" AND "".$GLOBALS["m"][1].""");	if ($search!="") {		$f_search = "(".					"C.text_ConsumerName LIKE "%".$search."%" ".					"OR C.text_ConsumerPhone LIKE "%".$search."%" ".					"OR C.`text_ConsumerE-Mail` LIKE "%".$search."%" ".					"OR CONCAT(SL.bigint_LeadID + 11001000,LS.smallint_LeadOrdinal) LIKE "%".$search."%"".					")";	}	$tcat = explode(".",$tcategory);	// 0:	SL.bigint_ServiceID,		SC,	text_ServiceDescription	// 1:	LS.bigint_SupplierID,		S,	text_SupplierName	// 2:	C.bigint_ConsumerRegion,	R,	text_RegionDescription	$sql = "SELECT 				DISTINCTROW 				".$tcategory.", ".$table.".".$order." 			FROM 				25_serviceleads SL 			LEFT JOIN 19_consumers C ON SL.bigint_ConsumerID = C.bigint_ConsumerID 			LEFT JOIN 27_leadssent LS ON SL.bigint_LeadID = LS.bigint_LeadID 			LEFT JOIN 2_servicescatalogue SC ON SL.bigint_ServiceID = SC.bigint_ServiceID 			LEFT JOIN 5_suppliers S ON LS.bigint_SupplierID = S.bigint_SupplierID 			LEFT JOIN 1_regions R ON C.bigint_ConsumerRegion = R.bigint_RegionID 			LEFT JOIN 4_servicesuppliers SS ON S.bigint_SupplierID = SS.bigint_SupplierID 			".((isset($GLOBALS["m"])||count($wsql)||$suppliers!=""||$search!="")?"WHERE "."").				((isset($GLOBALS["m"]))?"SL.timestamp_LeadCreated BETWEEN "".$GLOBALS["m"][0]."" AND "".$GLOBALS["m"][1]."" ":"").			((isset($GLOBALS["m"])&&count($wsql)>0)?"AND ":"").				((count($wsql)>0)?"(".implode(" OR ",$wsql).") ":"").			(((count($wsql)>0||isset($GLOBALS["m"]))&&$suppliers!="")?"AND ":"").				(($suppliers!="")?$f_suppliers." ":"").			(((count($wsql>0||isset($GLOBALS["m"])||$suppliers!="")&&$search!="")?"AND ":"").				(($search!="")?$f_search." ":"").			"ORDER BY ".$table.".".$order." ASC;";	error_log("rn".print_r($sql,true)."rn",3,dirname(__FILE__).DIRECTORY_SEPARATOR."debug.log");	//$result = mysql_query_errors( $sql , $conn , __FILE__ , __LINE__, true);	if ($result) {		while ($row = mysql_fetch_array($result)) {			if ($row[$order] != "") {				echo "	<order name="".xmlentities($row[$order])."">n";				$sql0 = "SELECT 							SL.bigint_LeadID, 							IFNULL(								LS.smallint_LeadOrdinal, 								0							) AS smallint_LeadOrdinal, 							CONCAT_WS(								";", 								C.text_ConsumerName, 								C.`text_ConsumerE-Mail`, 								C.text_ConsumerPhone, 								C.text_ConsumerCity, 								C.text_ConsumerCounty, 								C.text_ConsumerState, 								C.text_ConsumerCountry							) AS text_Consumer, 							IFNULL(								LS.text_Duplicates, 								0							) AS text_Duplicates, 							SL.text_LeadAttributes, 							SL.timestamp_ExpectedBy, 							SL.text_LeadMessage, 							SL.bigint_ServiceID, 							IFNULL(								LS.bigint_SupplierID, 								0							) AS bigint_SupplierID, 							C.bigint_ConsumerRegion AS bigint_RegionID, 							IFNULL(								LS.tinyint_LeadSent, 								0							) AS tinyint_LeadSent, 							SL.timestamp_LeadCreated, 							SL.bigint_ServiceID, 							SC.text_ServiceDescription, 							SC.bigint_CostPerLead, 							LS.double_LeadValueScoring, 							IFNULL(								S.text_SupplierName, 								""							) AS text_SupplierName, 							IFNULL(								S.text_SupplierW3, 								""							) AS text_SupplierW3, 							IFNULL(								S.text_ContactFirstName, 								""							) AS text_ContactFirstName, 							IFNULL(								S.text_ContactSurname, 								""							) AS text_ContactSurname, 							IFNULL(								S.text_ContactPosition, 								""							) AS text_ContactPosition, 							IFNULL(								S.`text_ContactE-mail`, 								""							) AS `text_ContactE-mail`, 							IFNULL(								S.bigint_ContactTel, 								""							) AS bigint_ContactTel, 							IFNULL(								S.bigint_CurrentBalance, 								""							) AS bigint_CurrentBalance, 							IFNULL(								S.tinyint_VariablePricingEnabled, 								""							) AS tinyint_VariablePricingEnabled, 							R.bigint_RegionID, 							R.text_RegionDescription, 							FC.text_FormName, 							IFNULL(								(SELECT text_RegionDescription FROM 1_regions WHERE bigint_RegionID = S.bigint_CountryID),								""							) AS text_Country, 							IFNULL(								(SELECT CONCAT(R4.text_RegionDescription, " (", A4.text_RegionAbbrev, ")") FROM 1_regions R4, 35_regionabbrevs A4 WHERE R4.bigint_RegionID = A4.bigint_RegionID AND A4.bigint_RegionID = S.bigint_StateID),								""							) AS text_State, 							IFNULL(								(SELECT CONCAT(text_RegionDescription, ", ", text_RegionAbbrev) FROM 1_regions R3, 35_regionabbrevs A3 WHERE R3.bigint_ParentRegionID = A3.bigint_RegionID AND R3.bigint_RegionID = S.bigint_CountyID),								""							) AS text_County, 							IFNULL(								(SELECT CONCAT(R1.text_RegionDescription, ", ", A2.text_RegionAbbrev, " ", LPAD(R1.bigint_RegionID,5,"0")) FROM 1_regions R1 LEFT JOIN 1_regions R2 ON R1.bigint_ParentRegionID = R2.bigint_RegionID LEFT JOIN 35_regionabbrevs A2 ON R2.bigint_ParentRegionID = A2.bigint_RegionID WHERE R1.bigint_RegionID = S.bigint_ZipCodeID),								""							) AS text_City 						FROM 							25_serviceleads SL 						LEFT JOIN 19_consumers C ON SL.bigint_ConsumerID = C.bigint_ConsumerID 						LEFT JOIN 27_leadssent LS ON SL.bigint_LeadID = LS.bigint_LeadID 						LEFT JOIN 2_servicescatalogue SC ON SL.bigint_ServiceID = SC.bigint_ServiceID 						LEFT JOIN 5_suppliers S ON LS.bigint_SupplierID = S.bigint_SupplierID 						LEFT JOIN 4_servicesuppliers SS ON S.bigint_SupplierID = SS.bigint_SupplierID 						LEFT JOIN 1_regions R ON C.bigint_ConsumerRegion = R.bigint_RegionID 						LEFT JOIN 32_webformconfigs FC ON SL.bigint_FormID = FC.bigint_FormID 						WHERE 							".$tcategory." = ".$row[$tcat[1]]." ".							((isset($GLOBALS["m"]))?"AND SL.timestamp_LeadCreated BETWEEN "".$GLOBALS["m"][0]."" AND "".$GLOBALS["m"][1]."" ":"").						((count($wsql)>0||$suppliers!=""||$search!="")?"AND ":"").							((count($wsql)>0)?"(".implode(" OR ",$wsql).") ":"").						((count($wsql>0&&$suppliers!="")?"AND ":"").							(($suppliers!="")?$f_suppliers." ":"").						(((count($wsql>0||$suppliers!="")&&$search!="")?"AND ":"").							(($search!="")?$f_search." ":"").						"ORDER BY 							SL.timestamp_LeadCreated 						DESC;";				error_log("rn".print_r($sql0,true)."rn",3,dirname(__FILE__).DIRECTORY_SEPARATOR."debug.log");				//$result0 = mysql_query_errors( $sql0 , $conn , __FILE__ , __LINE__,true);				if ($result0) {					while ($row0 = mysql_fetch_assoc($result0)) {						$sql2 = "SELECT * FROM 2_servicescatalogue WHERE bigint_ServiceID = ".$row0["bigint_ServiceID"].";";						$result2 = mysql_query_errors( $sql2 , $conn , __FILE__ , __LINE__);						$row2 = mysql_fetch_assoc($result2);						echo "		<lead>n";						global $cat, $catid, $servicename, $servicecost, $fixedcost, $dynamiccost, $attribsarr, $leadid, $ordinal, $leadsuccess, $leadcreated, $consumerfullname, $consumeremail, $consumercell, $city_town, $message, $exby, $dtmstyle, $dtlast, $formname, $region, $recipient, $service;						$service = $row0["bigint_ServiceID"];						$attribsarr = formattribs($row0["text_LeadAttributes"]);						$sqlx = "SELECT * FROM 2_servicescatalogue WHERE bigint_ServiceID = ".$service.";";						$resultx = mysql_query_errors($sqlx, $conn, __FILE__, __LINE__);						if ($resultx) {							if ($rowx = mysql_fetch_array($resultx)) {								$servicename = $rowx["text_ServiceDescription"];								$fixedcost = $rowx["bigint_CostPerLead"];							}						}						$catid = getCategory();						$servicecost = getCost($row0["tinyint_VariablePricingEnabled"]);						$leadid = $row0["bigint_LeadID"];						$ordinal = $row0["smallint_LeadOrdinal"];						$leadsuccess = $row0["tinyint_LeadSent"];						$leadcreated = $row0["timestamp_LeadCreated"];						$consumer = explode(";",$row0["text_Consumer"]);						$consumerfullname = $consumer[0];						$consumeremail = $consumer[1];						$consumercell = $consumer[2];						$city_town = $consumer[3];						$message = $row0["text_LeadMessage"];						$exby = $row0["timestamp_ExpectedBy"];						$expected = explode(" ",date($dtmstyle,strtotime($exby)));						$formname = $row0["text_FormName"];						$region = $row0["bigint_RegionID"];						$recipient = $row0["bigint_SupplierID"];						echo "			<id>".$row0["bigint_LeadID"].";".$row0["smallint_LeadOrdinal"]."</id>n";						echo "			<header>".xmlentities(populateflags("%CONFIGTEXT Intellisource | Premium Lead Manager Header | ".$formname."%",false,false))."</header>n";						echo "			<consumer>".xmlentities($row0["text_Consumer"])."</consumer>n";						echo "			<duplicates>".(($row0["text_Duplicates"]=="")?"No Duplicates Found":xmlentities(trim($row0["text_Duplicates"])))."</duplicates>n";						echo "			<attribs>".xmlentities(str_replace("r","",str_replace("n","",$row0["text_LeadAttributes"])))."</attribs>n";						echo "			<expected>" . $expected[$dtlast] . "</expected>n";						echo "			<message>".xmlentities($row0["text_LeadMessage"])."</message>n";						echo "			<sent>".xmlentities(($row0["tinyint_LeadSent"]!="")?$row0["tinyint_LeadSent"]:"0")."</sent>n";						echo "			<created>".xmlentities($row0["timestamp_LeadCreated"])."</created>n";						echo "			<service";						$sql2 = "SELECT * FROM 2_servicescatalogue WHERE bigint_ServiceID = ".$row0["bigint_ServiceID"].";";						$result2 = mysql_query_errors( $sql2 , $conn , __FILE__ , __LINE__);						if ($result2) {							while ($row2 = mysql_fetch_array($result2)) {								$GLOBALS["lvdenabled"] = $row2["tinyint_LeadScoringEnabled"];								$GLOBALS["lvdtot"] = number_format($row0["double_LeadValueScoring"],3);								$fixedcost = $row2["bigint_CostPerLead"];								echo " id="".$row2["bigint_ServiceID"]."" name="".$row2["text_ServiceDescription"]."" cost="".getCost($row0["tinyint_VariablePricingEnabled"])."" lvdtot="".$GLOBALS["lvdtot"]."" svccost="".$row2["bigint_CostPerLead"].""";							}						}						echo " />n";						if ($row0["bigint_SupplierID"] > 0) {							echo "			<supplier id="".$row0["bigint_SupplierID"]."" name="".xmlentities($row0["text_SupplierName"])."" w3a="".xmlentities($row0["text_SupplierW3"]).""".													" contact="".xmlentities($row0["text_ContactFirstName"]." ".$row0["text_ContactSurname"])."" position="".xmlentities($row0["text_ContactPosition"]).""".													" mail="".xmlentities($row0["text_ContactE-mail"])."" tel="".$row0["bigint_ContactTel"]."" bal="".$row0["bigint_CurrentBalance"].""".													" cntr="".$row0["text_Country"]."" stat="".$row0["text_State"]."" cnty="".$row0["text_County"]."" cty="".$row0["text_City"]."" />n";						}						echo "		</lead>n";					}				}				echo "	</order>n";				if ($GLOBALS["q"]!=7) {					$sql1 = "SELECT 								IF(									(SELECT COUNT(*) FROM 4_servicesuppliers) < 1, 									S.bigint_SupplierID, 									SS.bigint_SupplierID								) AS bigint_SupplierID, 								IF(									(SELECT COUNT(*) FROM 4_servicesuppliers) < 1, 									REPLACE(S.text_ServiceID,";",","), 									GROUP_CONCAT(SS.bigint_ServiceID SEPARATOR ',')								) AS bigint_ServiceID, 								S.text_SupplierName, 								IF(									(SELECT COUNT(*) FROM 4_servicesuppliers) < 1, 									CONCAT_WS(										",", 										S.bigint_CountryID, 										S.bigint_StateID, 										S.bigint_CountyID, 										S.bigint_ZipCodeID									), 									GROUP_CONCAT(SS.bigint_RegionID SEPARATOR ',')								) AS bigint_RegionID 							FROM 								5_suppliers S 							LEFT JOIN 4_servicesuppliers SS ON S.bigint_SupplierID = SS.bigint_SupplierID 							WHERE 								S.smallint_SupplierStatus = 0 							GROUP BY 								S.bigint_SupplierID 							ORDER BY 								bigint_ServiceID ASC, 								text_SupplierName ASC;";					$result1 = mysql_query_errors( $sql1 , $conn , __FILE__ , __LINE__);					while ($row1 = mysql_fetch_array($result1)) {						echo "	<forwardpossible serviceid="".$row1["bigint_ServiceID"]."" supplierid="".$row1["bigint_SupplierID"]."" suppliername="".xmlentities($row1["text_SupplierName"])."" regionid="".$row1["bigint_RegionID"]."" />n";					}				}			}		}	}}?> 

what in the world is wrong with the above code??? *futilely tries to slap self awake*

please help!

this issue is still awaiting assistance from the 1pg developers - the apparent post data does not get posted as it should!

(the webhooks i am trying to integrate here - are still in beta...)

Maybe the user that PHP is running under doesn't have permission to read that file. Make sure that all error messages are being reported and that you're using the error log where you can check it for errors like that.

would i have to set permissions within the file function perhaps? the permissions are rw-r--r-- in winscp - and no it successfully gets the key and prints it from the file.

hi all,

what is wrong with the following code that it does not write the following to log file when an item is updated using webhooks?

<?php/*timestamp	int			Sent as unix time (http://en.wikipedia.org/wiki/Unix_time)secretkey	string		If you provided a SecretKey it will be included here, otherwise this field is emptydata		hash		A data hash containing the same data as returned from a API call but as a POST parameter body.type		string		What type of data was changed(Contact, Action, Deal etc..)reason		string		What change triggered the Webhook(Created, Deleted, Updated etc..)*/function microtime_float() {    list($usec, $sec) = explode(" ", microtime());    return ((float)$usec + (float)$sec);}$time_start = microtime_float();$keys = file("/var/equote",FILE_USE_INCLUDE_PATH | FILE_SKIP_EMPTY_LINES | FILE_IGNORE_NEW_LINES);if ($_POST["secretkey"]==$keys[0]) {	$data = json_encode($_POST["data"],true);	$type = $_POST["type"];	$reason = $_POST["reason"];	// contact					{"data"=>"{"id"=>"51a8852aceb9976888000007", "pendingdeal"=>true, "starred"=>true, "owner"=>"51a88516ceb9976888000001", "type"=>"contact", "firstname"=>"Jane", "lastname"=>"Doe", "company"=>"Acme Inc.", "status"=>"customer", "tags"=>[], "photo_url"=>"https://opcrm-fd-us-west-1-51a896c4ceb997741c000006.s3.amazonaws.com/51a88516ceb9976888000001/51a8852cceb9976888000009/jane_doe.png", "next_actions"=>{"51a88516ceb9976888000001"=>[{"id"=>BSON::ObjectId('51c422aaceb997128700026c'), "name"=>"Contact Jane to confirm she has received our deal", "type"=>"date", "date"=>2013-06-26 00:00:00 UTC}]}, "company_id"=>nil}", "type"=>"contact", "reason"=>"updated", "secretkey"=>"mysecretkey", "timestamp"=>"1371810060"}	if ($type == "contact") {		switch ($reason) {			case "Created":		// Created		Object has been created.								break;			case "Updated":		// Updated		Object has been updated.								break;		}	}}$time_end = microtime_float();$time = $time_end - $time_start;print_r($_POST);// if (count($_POST)) error_log("rn".$time." second execution completed at ".date("Y/m/d H:i:s",time())."rn".print_r($_POST,true)."rn",3,dirname(__FILE__).DIRECTORY_SEPARATOR."webhook.log");?> 

the file is located in /var/www/performatix.co/production/public/webhook.php with an md5'd htaccess file as /var/equote containing two username/encoded password strings.

however whenever updating the onepage crm - however the array is always empty! what would be wrong here? this is on the onepage crm using the version 3.0 api...

and its finally working 100%, since Friday 20th at 07:55 am (+2 gmt)

updated line 840 to read

	$manual = (empty($types))?true:false;

but still when printing $manual i get nothing with print_r

should i try it as a string? ok testing following code:

function opconvert($array,$types=NULL,$levels=1) {	$manual = (!empty($types))?"true":"false";	if ($manual) $types = gettypes($array);	switch ($levels) {		case 1:	// pre-formatted array passed			return implode(",",$array);			break;		case 2: // unformatted hash passed			$i = 0;			$out = array();			$type = explode(",",$types);			array_push($GLOBALS["sql"],				"$manual = ".print_r($manual,true),				"$types = ".print_r($types,true),				"$type = ".print_r($type,true)			);			foreach ($array as $key=>$value) {				$value = objectToArray($value);				if ($manual=="true") {					$out[$key] = $value["type"]."|".$value[$type[$i]];				} else {					$out[$key] = $type[$i]."|".$value;				}				array_push($GLOBALS["sql"],					"$key = ".print_r($key,true),					"$value = ".print_r($value,true),					"$out[$key] = ".print_r($out[$key],true)				);				$i++;			}			return implode(",",$out);			break;	}}

why can't i get $manual set to true or false, if NULL is provided as the second parameter in the function opconvert??? this is driving me crazy - it works in other instances just not in this one. the output of $out[$key] is $out[$key] = number|Array

this is happening because it is falling past the exception. if i change the $manual to !$manual then it messes up the other instances where this function is used from.

 

 

why can't i get $manual set to true or false, if NULL is provided as the second parameter in the function opconvert???

ok - am going to try using empty($types) rather, see if this helps.

function opconvert($array,$types=NULL,$levels=1) {	$manual = empty($types);	if ($manual) $types = gettypes($array);	switch ($levels) {		case 1:	// pre-formatted array passed			return implode(",",$array);			break;		case 2: // unformatted hash passed			$i = 0;			$out = array();			$type = explode(",",$types);			array_push($GLOBALS["sql"],				"$manual = ".print_r($manual,true),				"$types = ".print_r($types,true),				"$type = ".print_r($type,true)			);			foreach ($array as $key=>$value) {				$value = objectToArray($value);				if ($manual) {					$out[$key] = $value["type"]."|".$value[$type[$i]];				} else {					$out[$key] = $type[$i]."|".$value;				}				array_push($GLOBALS["sql"],					"$key = ".print_r($key,true),					"$value = ".print_r($value,true),					"$out[$key] = ".print_r($out[$key],true)				);				$i++;			}			return implode(",",$out);			break;	}}

am currently testing setting $manual initially, the changes i have made are above.

i replaced

	$manual = false;	if (is_null($types)) {		$manual = true;		$types = gettypes($array);		//$array = objectToArray($array);	}

with

	$manual = ($types===NULL);	if ($manual) $types = gettypes($array);

 

[14] => $manual =

[15] => $types = number

[16] => $type = Array

(

[0] => number

)

[17] => $key = 0

[18] => $value = Array

(

[type] => mobile

[number] => 0782571038

)

[19] => $out[$key] = number|Array

[20] => $manual =

[21] => $types = address

[22] => $type = Array

(

[0] => address

)

[23] => $key = 0

[24] => $value = Array

(

[type] => other

[address] => krugermc.55@gmail.com

)

[25] => $out[$key] = address|Array

[26] => $manual =

[27] => $types = address

[28] => $type = Array

(

[0] => address

)

and the result from the function, even after clearing memcached - is *STILL* the above! why is $manual never true with the above condition? even when using is_null($types)...

ok have edited following line as such:

$manual = is_null($types);

testing again... ok still 0o am moving away from the inline if's lower in the function (originally lines 857 to 859, but now lines 857 to 861) and executing it as follows:

function opconvert($array,$types=NULL,$levels=1) {	$manual = is_null($types);	if ($manual) $types = gettypes($array);	switch ($levels) {		case 1:	// pre-formatted array passed			return implode(",",$array);			break;		case 2: // unformatted hash passed			$i = 0;			$out = array();			$type = explode(",",$types);			array_push($GLOBALS["sql"],				"$manual = ".print_r($manual,true),				"$types = ".print_r($types,true),				"$type = ".print_r($type,true)			);			foreach ($array as $key=>$value) {				$value = objectToArray($value);				if ($manual) {					$out[$key] = $value["type"]."|".$value[$type[$i]];				} else {					$out[$key] = $type[$i]."|".$value;				}				array_push($GLOBALS["sql"],					"$key = ".print_r($key,true),					"$value = ".print_r($value,true),					"$out[$key] = ".print_r($out[$key],true)				);				$i++;			}			return implode(",",$out);			break;	}}

and testing again...

doesn't show it being false either... and $types gets populated on line 843. why would it not be executing the line before, or at least displaying $manual as true?

$manual needs to be set to true for this function to work as expected... how would i accomplish this? the following code is not working either - and was recommended on the reference url below (the post has been around for some 12 years already! hope this still holds water - although what i am actually referring to is contained within the Editors Note at the bottom of the post).

http://www.php.net/manual/en/function.is-null.php#12120

function opconvert($array,$types=NULL,$levels=1) {	$manual = ($types===NULL);	if ($manual) $types = gettypes($array);	switch ($levels) {		case 1:	// pre-formatted array passed			return implode(",",$array);			break;		case 2: // unformatted hash passed			$i = 0;			$out = array();			$type = explode(",",$types);			array_push($GLOBALS["sql"],				"$manual = ".print_r($manual,true),				"$types = ".print_r($types,true),				"$type = ".print_r($type,true)			);			foreach ($array as $key=>$value) {				$value = objectToArray($value);				$out[$key] = (($manual)?$value["type"]:$type[$i]).							 "|".							 (($manual)?$value[$type[$i]]:$value);				array_push($GLOBALS["sql"],					"$key = ".print_r($key,true),					"$value = ".print_r($value,true),					"$out[$key] = ".print_r($out[$key],true)				);				$i++;			}			return implode(",",$out);			break;	}}

hi - have not been able to test the function untill justnow, and unfortunately $manual still does not get set to true. how is this possible with the following version of opconvert?

function opconvert($array,$types=NULL,$levels=1) {	$manual = false;	if (is_null($types)) {		$manual = true;		$types = gettypes($array);		//$array = objectToArray($array);	}	switch ($levels) {		case 1:	// pre-formatted array passed			return implode(",",$array);			break;		case 2: // unformatted hash passed			$i = 0;			$out = array();			$type = explode(",",$types);			array_push($GLOBALS["sql"],				"$manual = ".print_r($manual,true),				"$types = ".print_r($types,true),				"$type = ".print_r($type,true)			);			foreach ($array as $key=>$value) {				$value = objectToArray($value);				$out[$key] = (($manual)?$value["type"]:$type[$i]).					     "|".					     (($manual)?$value[$type[$i]]:$value);				array_push($GLOBALS["sql"],					"$key = ".print_r($key,true),					"$value = ".print_r($value,true),					"$out[$key] = ".print_r($out[$key],true)				);				$i++;			}			return implode(",",$out);			break;	}}

with the following output from $GLOBALS["sql"] on lines 854 and 864, after clearing memcached.

 

[14] => $manual =

[15] => $types = number

[16] => $type = Array

(

[0] => number

)

[17] => $key = 0

[18] => $value = Array

(

[type] => mobile

[number] => 0724121932

)

[19] => $out[$key] = number|Array

[20] => $manual =

[21] => $types = address

[22] => $type = Array

(

[0] => address

)

[23] => $key = 0

[24] => $value = Array

(

[type] => other

[address] => greywacke@outlook.com

)

[25] => $out[$key] = address|Array

[26] => $manual =

[27] => $types = address

[28] => $type = Array

(

[0] => address

)

for some reason unbekbownst to me - it is still not setting $manual to true...

sincerely,

Pierre du Toit.

thankyou justsomeguy

hi all, i am having a problem regarding the following function - specifically opconvert. it is used in different places, but when $types is set as NULL when calling the function - checking it to be null in the first if statement - does not seem to be executing. so $manual stays false and the first value is not returned but the last. below the code is a data extract which is logged during execution.

function objectToArray($d) {	if (is_object($d)) {		// Gets the properties of the given object		// with get_object_vars function		$d = get_object_vars($d);	}	if (is_array($d)) {		/*		* Return array converted to object		* Using __FUNCTION__ (Magic constant)		* for recursive call		*/		return array_map(__FUNCTION__, $d);	} else {		// Return array		return $d;	}}function arrayToObject($d) {	if (is_array($d)) {		/*		* Return array converted to object		* Using __FUNCTION__ (Magic constant)		* for recursive call		*/		return (object) array_map(__FUNCTION__, $d);	}	else {		// Return object		return $d;	}}function gettypes($array) {	$types = "";	foreach ($array as $id=>$element) {		$types .= (($id>0)?",":"").$element["type"];	}	return $types;}function opconvert($array,$types=NULL,$levels=1) {	$manual = false;	if ($types===NULL) {		$manual = true;		$types = gettypes($array);		//$array = objectToArray($array);	}	switch ($levels) {		case 1:	// pre-formatted array passed			return implode(",",$array);			break;		case 2: // unformatted hash passed			$i = 0;			$out = array();			$type = explode(",",$types);			array_push($GLOBALS["sql"],				"$manual = ".print_r($manual,true),				"$types = ".print_r($types,true),				"$type = ".print_r($type,true)			);			foreach ($array as $key=>$value) {				$value = objectToArray($value);				$out[$key] = (($manual)?$value["type"]:$type[$i]).							 "|".							 (($manual)?$value[$type[$i]]:$value);				array_push($GLOBALS["sql"],					"$key = ".print_r($key,true),					"$value = ".print_r($value,true),					"$out[$key] = ".print_r($out[$key],true)				);				$i++;			}			return implode(",",$out);			break;	}}

 

[14] => $manual =

[15] => $types = number

[16] => $type = Array

(

[0] => number

)

[17] => $key = 0

[18] => $value = Array

(

[type] => mobile

[number] => 0724121932

)

[19] => $out[$key] = number|Array

[20] => $manual =

[21] => $types = address

[22] => $type = Array

(

[0] => address

)

[23] => $key = 0

[24] => $value = Array

(

[type] => other

[address] => greywacke@hotmail.com

)

[25] => $out[$key] = address|Array

[26] => $manual =

[27] => $types = address

[28] => $type = Array

(

[0] => address

)

why does $manual not set to true if parameter $types is passed as NULL? i have converted it from stdClass data type to array data type - which seems to make it more legit. in the end i am looking to have the following types of values returned from the function formatted as DataType|DataValue,DataType|DataValue from the passed json data extract:

POSSIBLE EMAIL TYPES

• work
• home
• other

POSSIBLE PHONE TYPES

• work
• mobile
• home
• direct
• fax
• other

POSSIBLE URL TYPES

• website
• blog
• twitter
• linkedin
• facebook
• other

the main issue though, is when calling via "opconvert($data->data->contact->phones,NULL,2)" for instance, it does not set $manual to true - even though it enters the if statement and parses the variables property (actually array element after converting from stdClass type).

a swift answer (or even kick in the right direction ), would truly be appreciated,

Pierre "Greywacke" du Toit.

ok nevermind got it! started listening to some visualization manifestation music and it came to me

			// load attribute keys & weighting / 1, containing attribute values & weighting / 10			var lvdk = xmldoc.getElementsByTagName("lvdk");			for (var k = 0; k < lvdk.length; k++) {				var i = 0;				var arr = new Array();				for (var v = 0; v < lvdk[k].attributes.length; v++) {					arr[v] = lvdk[k].attributes[v].value;				}				var t = lvdk[k].attributes.length;				for (var v = 0; v < lvdk[k].childNodes.length; v++) {					var lvd = lvdk[k].childNodes[v];					if (lvd.nodeName == "lvdv" && lvd.attributes) {						arr[v+t] = new Array();						for (var i = 0; i < lvd.attributes.length; i++) {							arr[v+t][i] = lvd.attributes[i].value;						}					}				}				salert(arr);				//getlvds(arr);			}

hope this helps somebody else with a similar problem