mysql: if instr(column,'canopy_req') then select fields A and B, else A and B and C

[Greywacke]

hi again.the reason i am posting the following two questions here, is that i cannot seem to find any related solutions on the web.i need to modify a query i previously ran to select the leads, but this time output the attributes to columns. now the problem lies with the two types of leads, having different attributes - as can be seen by the following examples:

canopy_req = pre-owned_colour_coded<br />canopy_style = lowline_luxury<br />budget = R7,500 to R10,000<br />fitment = ASAP<br />vehicle_status = possession_yes<br />vehicle_make_model = Isuzu - DCAB<br />year_model = 2005

and

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

sofar i have compiled the following query, but i need to know how would something in this line be possible?obviously they need to follow the same column naming convention, but one of the two has a few attributes split and vice versa.one can't seem to select multiple columns in an if else clause.i guess i would have to identify the fields and compile them as such.also, been wondering wether one can create a column name dynamically as i have tried previously.

SELECT 	IF 		INSTR(6_serviceleads.text_LeadAttributes,'canopy_req = ') > 0	THEN 		(		SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_LeadAttributes,'<br />',1),' = ',-1) AS `Canopy Requirement`, 		SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_LeadAttributes,'<br />',2),';',-1),' = ',-1) AS `Canopy Style`, 		SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_LeadAttributes,'<br />',3),';',-1),' = ',-1) AS `Budget`, 		SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_LeadAttributes,'<br />',4),';',-1),' = ',-1) AS `Fitment`, 		SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_LeadAttributes,'<br />',5),';',-1),' = ',-1) AS `Vehicle Status`, 		SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_LeadAttributes,'<br />',6),';',-1),' = ',-1) AS `Vehicle Make Model`, 		SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_LeadAttributes,'<br />',7),';',-1),' = ',-1) AS `Year Model` 		)	ELSE 		(		SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_LeadAttributes,'<br />',1),' = ',-1) AS `Products Description`, 		SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_LeadAttributes,'<br />',2),';',-1),' = ',-1) AS `Bakkie Model`, 		SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_LeadAttributes,'<br />',3),';',-1),' = ',-1) AS `Requirement`, 		SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_LeadAttributes,'<br />',4),';',-1),' = ',-1) AS `Colour Code`, 		SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_LeadAttributes,'<br />',5),';',-1),' = ',-1) AS `Budget`, 		SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_LeadAttributes,'<br />',6),';',-1),' = ',-1) AS `Fitment` 		)	END IF 	SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',1) AS `Consumer Name`, 	SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',2),';',-1) AS `Consumer E-mail`, 	SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',3),';',-1) AS `Consumer Telephone`, 	1_regions.text_RegionDescription AS `Consumer Region`, 	SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',-1) AS `Consumer City/Town`, 	6_serviceleads.timestamp_LeadCreated AS `Lead Created`, 	IF(6_serviceleads.tinyint_LeadSent = 1,'TRUE','FALSE') AS `Lead Sent`, 	5_suppliers.text_SupplierName AS `Supplier`, 	2_servicescatalogue.text_ServiceDescription AS `Service` FROM 6_serviceleads LEFT JOIN 1_regions ON (	6_serviceleads.bigint_RegionID = 1_regions.bigint_RegionID) LEFT JOIN 5_suppliers ON (	6_serviceleads.bigint_SupplierID = 5_suppliers.bigint_SupplierID) LEFT JOIN 2_servicescatalogue ON (	6_serviceleads.bigint_ServiceID = 2_servicescatalogue.bigint_ServiceID) ORDER BY `Lead Created` DESC;

can't seem to find answers to either of these questions, tried two search engines sofar - and it's getting late...

[justsomeguy]

You always need the same set of columns, the entire result set has one set of columns for all rows. So at a minimum you need to give blank or null values for the non-existent columns in each set. I don't think you can specify several columns in the if statement though, the entire if statement is an expression which will go into a single column. You would probably have to do that processing in PHP, or have a really long SQL statement with one if statement per column. Incidentally, this is the reason why it's better to have one column per piece of data instead of storing several pieces of data in one column.