Jump to content

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


Greywacke
 Share

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...