Page 1 of 1

getting results from a dynamic comma delimited list

Posted: Thu May 26, 2005 12:51 pm
by boybles
I get a comma delimited list (i.e. "1,3,4") in "services" stored in a string from the following query:


$query = "SELECT services FROM businesses WHERE id = '".mysql_escape_string($_GET['id'])."'";
$result = mysql_query($query);
$business = mysql_fetch_array($result);
mysql_free_result($result);


Now from this result, I would like to pick up all the service names from a different table where the id is an integer



$query = "SELECT name AS servicename FROM service WHERE id IN (".mysql_escape_string($business[ 'servicenames' ]).")";
$result = mysql_query($query);
$services = mysql_fetch_array($result);
mysql_free_result($result);

This is not working in mysql :cry: . Can anybody help?

Thanks,
Anthony

Posted: Thu May 26, 2005 12:56 pm
by Burrito
put your php code in

Code: Select all

tags...makes it much easier to read.


the field name in your IN clause isn't the same name as you're selecting:

servicenames vs services.

Posted: Thu May 26, 2005 1:00 pm
by boybles
Actually, I had used services....still my SQL grammar is off....and the string/integer character types are also plaguing me...
Please advise...Thx,
Anthony

Posted: Thu May 26, 2005 1:08 pm
by Burrito
what version of MySQL is it?

if you've got 4.1 you could do a subquery for your IN and do it all with one query.

in fact you might be able to do it all with one query anyway:

try this:

Code: Select all

$query = "select n.servicename from service n, businesses b where n.id = b.services and b.id = ".$_GET['id'];

Posted: Thu May 26, 2005 2:39 pm
by boybles
You are correct with the MySQL version. As for the query, it is a one to many relationship, so the query you gave me would only give one result from the services table (if any). If I had multiple ids in a string like "1,2,5" from the first query and wanted to fetch those ids from the services table, how would I do it?

Anthony

Posted: Thu May 26, 2005 2:59 pm
by Burrito

Code: Select all

$query = "SELECT name AS servicename FROM service WHERE id IN (SELECT services FROM businesses WHERE id = '".mysql_escape_string($_GET['id'])."')";