MySQL

Query seems to be crashing phpmyadmin/mysql

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2004

Posts: 24

Reputation: ray_broome is an unknown quantity at this point

Solved Threads: 0

ray_broome ray_broome is offline

Offline

Newbie Poster

Query seems to be crashing phpmyadmin/mysql

Nov 13th, 2007

Hi, I have the following query i'm trying to execute:

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
SELECT CONCAT_WS(' ', g.genus_name, s.species_name, i.rank, i.infraspecies_name, conf.auth1, conf.auth2 ) AS scientific_name, 
cn.common_name, GROUP_CONCAT(cn.common_name ORDER BY cn.common_name SEPARATOR ', ' ) AS common_names, ui.unvouch_image_src, ui.unvouch_image_alt, gh.growth_habit, gd.gen_distrib_name 
FROM plants AS p 
LEFT JOIN (genera AS g, species AS s) 
ON (p.genus_num = g.genus_id AND p.species_num = s.species_id)
LEFT JOIN (confirmations AS conf) 
ON (p.plant_id = conf.plant_num)
LEFT JOIN (plant_growth_habits AS pgh, growth_habits AS gh)
ON (p.plant_id = pgh.plant_num)
LEFT JOIN (plant_distributions AS pd, gen_distribs AS gd)
ON (p.plant_id = pd.plant_num)
LEFT JOIN (plant_common_names AS pcn, common_names AS cn) 
ON (p.plant_id = pcn.plant_num)
LEFT JOIN (unvouchered_images AS ui) 
ON (p.plant_id = ui.plant_num)
LEFT JOIN (infraspecies AS i) 
ON (p.infraspecies_num = i.infraspecies_id)
WHERE scientific_name LIKE '%sesuvium%'
GROUP BY scientific_name ORDER BY scientific_name
SELECT CONCAT_WS(' ', g.genus_name, s.species_name, i.rank, i.infraspecies_name, conf.auth1, conf.auth2 ) AS scientific_name, 
cn.common_name, GROUP_CONCAT(cn.common_name ORDER BY cn.common_name SEPARATOR ', ' ) AS common_names, ui.unvouch_image_src, ui.unvouch_image_alt, gh.growth_habit, gd.gen_distrib_name 
FROM plants AS p 
LEFT JOIN (genera AS g, species AS s) 
ON (p.genus_num = g.genus_id AND p.species_num = s.species_id)
LEFT JOIN (confirmations AS conf) 
ON (p.plant_id = conf.plant_num)
LEFT JOIN (plant_growth_habits AS pgh, growth_habits AS gh)
ON (p.plant_id = pgh.plant_num)
LEFT JOIN (plant_distributions AS pd, gen_distribs AS gd)
ON (p.plant_id = pd.plant_num)
LEFT JOIN (plant_common_names AS pcn, common_names AS cn) 
ON (p.plant_id = pcn.plant_num)
LEFT JOIN (unvouchered_images AS ui) 
ON (p.plant_id = ui.plant_num)
LEFT JOIN (infraspecies AS i) 
ON (p.infraspecies_num = i.infraspecies_id)
WHERE scientific_name LIKE '%sesuvium%'
GROUP BY scientific_name ORDER BY scientific_name

I only recently added the last 3/4 join statements but before those it was working, i'm not sure if there's a problem with my joins/join syntax any help would be greatly appreciated.

•

Join Date: Aug 2004

Posts: 24

Reputation: ray_broome is an unknown quantity at this point

Solved Threads: 0

ray_broome ray_broome is offline

Offline

Newbie Poster

Re: Query seems to be crashing phpmyadmin/mysql

Nov 14th, 2007

Ok it looks like i got the crashing problem fixed by reworking my query to:

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
SELECT 
CONCAT_WS(' ',g.genus_name,s.species_name,i.rank,i.infraspecies_name,
conf.auth1,conf.auth2) AS scientific_name, cn.common_name,
GROUP_CONCAT(DISTINCT cn.common_name ORDER BY cn.common_name SEPARATOR ', ' ) AS common_names, 
ui.unvouch_image_src, ui.unvouch_image_alt, gh.growth_habit, GROUP_CONCAT(DISTINCT gh.growth_habit ORDER BY gh.growth_habit SEPARATOR ', ') AS growth_habits,
gd.gen_distrib_name, GROUP_CONCAT(DISTINCT gd.gen_distrib_name ORDER BY gd.gen_distrib_name SEPARATOR ', ') AS general_dists
FROM plants AS p 
 LEFT JOIN genera AS g 
	ON g.genus_id = p.genus_num
 LEFT JOIN species AS s 
	ON s.species_id = p.species_num
 LEFT JOIN confirmations AS conf 
	ON conf.plant_num = p.plant_id
 LEFT JOIN (plant_growth_habits AS pgh INNER JOIN growth_habits AS gh ON gh.growth_habit_id = pgh.growth_habit_num) 
	ON pgh.plant_num = p.plant_id
 LEFT JOIN (plant_distributions AS pd INNER JOIN gen_distribs AS gd ON gd.gen_distrib_id = pd.gen_distrib_num) 
	ON pd.plant_num = p.plant_id
 LEFT JOIN (plant_common_names AS pcn INNER JOIN common_names AS cn ON cn.common_name_id = pcn.common_name_num) 
	ON pcn.plant_num = p.plant_id 
 LEFT JOIN unvouchered_images AS ui
	ON p.plant_id = ui.plant_num
 LEFT JOIN infraspecies AS i 
	ON p.infraspecies_num = i.infraspecies_id
WHERE scientific_name LIKE '%sesuvium%' GROUP BY scientific_name ORDER BY scientific_name
SELECT 
CONCAT_WS(' ',g.genus_name,s.species_name,i.rank,i.infraspecies_name,
conf.auth1,conf.auth2) AS scientific_name, cn.common_name,
GROUP_CONCAT(DISTINCT cn.common_name ORDER BY cn.common_name SEPARATOR ', ' ) AS common_names, 
ui.unvouch_image_src, ui.unvouch_image_alt, gh.growth_habit, GROUP_CONCAT(DISTINCT gh.growth_habit ORDER BY gh.growth_habit SEPARATOR ', ') AS growth_habits,
gd.gen_distrib_name, GROUP_CONCAT(DISTINCT gd.gen_distrib_name ORDER BY gd.gen_distrib_name SEPARATOR ', ') AS general_dists
FROM plants AS p 
 LEFT JOIN genera AS g 
	ON g.genus_id = p.genus_num
 LEFT JOIN species AS s 
	ON s.species_id = p.species_num
 LEFT JOIN confirmations AS conf 
	ON conf.plant_num = p.plant_id
 LEFT JOIN (plant_growth_habits AS pgh INNER JOIN growth_habits AS gh ON gh.growth_habit_id = pgh.growth_habit_num) 
	ON pgh.plant_num = p.plant_id
 LEFT JOIN (plant_distributions AS pd INNER JOIN gen_distribs AS gd ON gd.gen_distrib_id = pd.gen_distrib_num) 
	ON pd.plant_num = p.plant_id
 LEFT JOIN (plant_common_names AS pcn INNER JOIN common_names AS cn ON cn.common_name_id = pcn.common_name_num) 
	ON pcn.plant_num = p.plant_id 
 LEFT JOIN unvouchered_images AS ui
	ON p.plant_id = ui.plant_num
 LEFT JOIN infraspecies AS i 
	ON p.infraspecies_num = i.infraspecies_id
WHERE scientific_name LIKE '%sesuvium%' GROUP BY scientific_name ORDER BY scientific_name

the above query seems to be working fine but i got onnneee not so big issue i was wondering about. This line here:

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
CONCAT_WS(' ',g.genus_name,s.species_name,i.rank,i.infraspecies_name,conf.auth1,conf.auth2) AS scientific_name
CONCAT_WS(' ',g.genus_name,s.species_name,i.rank,i.infraspecies_name,conf.auth1,conf.auth2) AS scientific_name

works and everything but there will not always be a value for conf.auth1 or conf.auth2 and the concatenation sometimes prints say for e.g. "Sesuvium microphyllum Willd. NULL" where there was no value for conf.auth2. Is this the default behaviour of concat_ws, can i get around this or would i just have to strip that out using php?