getting results from a dynamic comma delimited list

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
boybles
Forum Newbie
Posts: 4
Joined: Thu May 26, 2005 12:36 pm

getting results from a dynamic comma delimited list

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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.
boybles
Forum Newbie
Posts: 4
Joined: Thu May 26, 2005 12:36 pm

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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'];
boybles
Forum Newbie
Posts: 4
Joined: Thu May 26, 2005 12:36 pm

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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'])."')";
Post Reply