MySQL statistical select fails with

[Greywacke]

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...

Edited October 15, 2013 by Pierre 'Greywacke' du Toit

[Greywacke]

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!

Edited October 15, 2013 by Pierre 'Greywacke' du Toit

[justsomeguy]

I would write it like this:

 

 

SELECT COUNT(*) + 1 AS `l_Cnt` FROM (  SELECT SL.timestamp_LeadCreated FROM...) AS tmp

[Greywacke]

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!

Edited October 16, 2013 by Pierre 'Greywacke' du Toit

[justsomeguy]

That's what I was suggesting in my last post. You're not telling it what to count. The argument to the count function is not a query, it should be a column. Instead of this:

COUNT(SELECTCOUNT(DISTINCT SA1.text_AttributeDescription)

it needs to be this:

 

 

COUNT(*) FROM (  SELECT COUNT(DISTINCT SA1.text_AttributeDescription) FROM ...) as tmp_count_table

[Greywacke]

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.

Edited October 27, 2013 by Pierre 'Greywacke' du Toit

[Greywacke]

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!!!

Edited October 28, 2013 by Pierre 'Greywacke' du Toit

[Greywacke]

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?

Edited October 28, 2013 by Pierre 'Greywacke' du Toit

[justsomeguy]

It sounds like the queries aren't returning any records.

[Greywacke]

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