attempting to differentiate between substrings to return

[Greywacke]

hi there,as you have probably seen by now, the records are entered from two different sources. therefore, the sets of values saved as text, are not always the same. therefore i have tried to combine the two selects for each source, using the following SELECT IF statement.

SELECT 	IF(		(			LOCATE(				text_LeadAttributes, 				"products_description"			) = 0		), 		(			SUBSTRING_INDEX(				text_Consumer, 				";", 				1			) AS text_ConsumerName, 			CONCAT(				SUBSTRING_INDEX(					SUBSTRING_INDEX(						SUBSTRING_INDEX(							text_LeadAttributes, 							'<br />', 							6						), 						';', 						-1					), 					' = ', 					-1				), 				" - ", 				SUBSTRING_INDEX(					SUBSTRING_INDEX(						SUBSTRING_INDEX(							text_LeadAttributes, 							'<br />', 							2						), 						';', 						-1					), 					' = ', 					-1				)			) AS text_ProductDescription		), 		(			SUBSTRING_INDEX(				text_Consumer, 				";", 				1			) AS text_ConsumerName, 			SUBSTRING_INDEX(				SUBSTRING_INDEX(					text_LeadAttributes, 					'<br />', 					1				), 				' = ', 				-1			) AS text_ProductDescription		)	) FROM 	6_serviceleads WHERE 	text_Consumer LIKE "%hayward@telkomsa.net%" OR 	text_Consumer LIKE "%gcm.nationsu@gmail.com%" LIMIT 1;

however, when i run this query in PMA - it returns with the following:

#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 'AS text_ConsumerName, CONCAT( SUBSTRING_INDEX( SUBSTRING_INDEX( ' at line 14

i will attempt figuring this out myself, but please feel welcome to give it a shot

[Greywacke]

seperately, they work however:

SELECT 	SUBSTRING_INDEX(		text_Consumer, 		";", 		1	) AS text_ConsumerName, 	CONCAT(		SUBSTRING_INDEX(			SUBSTRING_INDEX(				SUBSTRING_INDEX(					text_LeadAttributes, 					'<br />', 					6				), 				';', 				-1			), 			' = ', 			-1		), 		" - ", 		SUBSTRING_INDEX(			SUBSTRING_INDEX(				SUBSTRING_INDEX(					text_LeadAttributes, 					'<br />', 					2				), 				';', 				-1			), 			' = ', 			-1		)	) AS text_ProductDescription FROM 	6_serviceleads WHERE 	text_Consumer LIKE "%gcm.nationsu@gmail.com%" LIMIT 1;

for records from the first source, and

SELECT 	SUBSTRING_INDEX(		text_Consumer, 		";", 		1	) AS text_ConsumerName, 	SUBSTRING_INDEX(		SUBSTRING_INDEX(			text_LeadAttributes, 			'<br />', 			1		), 		' = ', 		-1	) AS text_ProductDescription FROM 	6_serviceleads WHERE 	text_Consumer LIKE "%hayward@telkomsa.net%" LIMIT 1;

for records from the second source, each return one consumer name and product description.

[Greywacke]

unfortunately, i cannot seem to see the forest for the trees here...

[Greywacke]

the matching records for these two e-mails in the table 6_serviceleads are:

[b]bigint_LeadID[/b] 	[b]text_Consumer[/b][color="#FF0000"]725		Tony Hayward;tony.hayward@telkomsa.net;		0843720330;Pretoria[/color][color="#008000"]720		Tony Hayward;tony.hayward@telkomsa.net;		0843720330;Pretoria[/color][color="#0000FF"]3064		Lazarus Munetsi;gcm.nationsu@gmail.com;		0760270022;Pretoria[/color][b]text_LeadAttributes[/b][color="#FF0000"]products_description = Opel Corsa Canopy - Lowline<br />Bakkie model = 2004 - current model<br />Requirement = Pre-owned - White<br />Colour code = White Non-coded (standard)<br />Budget = R3,000 to R4,000<br />Fitment = ASAP[/color][color="#008000"]products_description = Opel Corsa Canopy - Lowline<br />Bakkie model = 2004 - current model<br />Requirement = Pre-owned - White<br />Colour code = White Non-coded (standard)<br />Budget = R3,000 to R4,000<br />Fitment = ASAP[/color][color="#0000FF"]canopy_req = pre-owned_colour_coded<br />canopy_style = lowline_luxury<br />budget = R4,000 to R5,000<br />fitment = within_3_months<br />vehicle_status = possession_yes<br />vehicle_make_model = Mitsubishi - DCAB<br />year_model = 2005[/color][b]text_LeadMessage[/b][color="#FF0000"]I need a canopy for a new shape corsa utility bakkie asap.[/color][color="#008000"]I need a canopy for a new shape corsa utility bakkie asap.[/color][color="#0000FF"]NULLSTR[/color][b]bigint_ServiceID[/b] 	[b]bigint_SupplierID[/b] 	[b]bigint_RegionID[/b][color="#FF0000"]1 			17 			24[/color][color="#008000"]1			23			24[/color][color="#0000FF"]1			23			24[/color][b]tinyint_LeadSent[/b] 	[b]timestamp_LeadCreated[/b][color="#FF0000"]1 			2010-02-25 07:49:57[/color][color="#008000"]1 			2010-02-25 07:49:57[/color][color="#0000FF"]1			2010-07-08 03:51:34[/color]

however, i need to select the concatenation of substring vehicle_make_model, " - " and canopy_style if the record is from source a, and the substring products_description if from source b. something is wrong with the query i posted, but i'm afraid i cannot see what.

[Greywacke]

okay, there were a few things wrong with it... i've edited the query as follows (let the IF() function only return one field):

SELECT 	SUBSTRING_INDEX(		text_Consumer, 		";", 		1	) AS text_ConsumerName, 	IF(		LOCATE(			text_LeadAttributes, 			"products_description"		) = 0		, 		CONCAT(				SUBSTRING_INDEX(					SUBSTRING_INDEX(						SUBSTRING_INDEX(							text_LeadAttributes, 							"<br />", 							6						), 						";", 						-1					), 					" = ", 					-1				), 				" - ", 				SUBSTRING_INDEX(					SUBSTRING_INDEX(						SUBSTRING_INDEX(							text_LeadAttributes, 							"<br />", 							2						), 						";", 						-1					), 					" = ", 					-1				)		) AS text_ProductDescription		, 		SUBSTRING_INDEX(			SUBSTRING_INDEX(				text_LeadAttributes, 				"<br />", 				1			), 			" = ", 			-1		) AS text_ProductDescription	) FROM 	6_serviceleads WHERE 	text_Consumer LIKE "%hayward@telkomsa.net%" OR 	text_Consumer LIKE "%gcm.nationsu@gmail.com%"

with the following 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 'AS text_ProductDescription , SUBSTRING_INDEX( SUBSTRING_INDEX( text_' at line 41

[Greywacke]

awesome ^^i had the AS thingy inside the if function, it was supposed to be outside and only once.

SELECT 	SUBSTRING_INDEX(		text_Consumer, 		";", 		1	) AS text_ConsumerName, 	IF(		LOCATE(			"products_description", 			text_LeadAttributes		) = 0		, 		CONCAT_WS(			" - ", 			SUBSTRING_INDEX(				SUBSTRING_INDEX(					SUBSTRING_INDEX(						text_LeadAttributes, 						'<br />', 						6					), 					';', -1				), 				' = ', 				-1			), 			SUBSTRING_INDEX(				SUBSTRING_INDEX(					SUBSTRING_INDEX(						text_LeadAttributes, 						'<br />', 						2					), 					';', 					-1				), 				' = ', 				-1			)		) 		, 		SUBSTRING_INDEX(			SUBSTRING_INDEX(				text_LeadAttributes, 				'<br />', 				1			), 			' = ', 			-1		)	) AS text_ProductDescriptionFROM 	6_serviceleads WHERE 	text_Consumer LIKE "%hayward@telkomsa.net%" OR 	text_Consumer LIKE "%gcm.nationsu@gmail.com%";

this issue is now RESOLVED

[chibineku]

You do some really complex things with MySQL, dude.

[thescientist]

You do some really complex things with MySQL, dude.

no shizzle. his posts always blow my mind.