Query seems to be crashing phpmyadmin/mysql

Expand Post »

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

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.

Similar Threads

mysql UPDATE not working! Why? in PHP
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in PHP
mysql_fetch_array(): supplied argument is not a valid MySQL in PHP
What's wrong with my query? in MySQL
Error message in MySQL

ray_broome

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

25 posts
since Aug 2004

Permalink

Nov 14th, 2007

Re: Query seems to be crashing phpmyadmin/mysql

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

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:

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?

ray_broome

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

25 posts
since Aug 2004

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: MySQL License

Next Thread in MySQL Forum Timeline: what lecense

DaniWeb Message

Cancel Changes

User Name
Password