Jump to content


Photo

using WHERE with a list?


  • Please log in to reply
4 replies to this topic

#1 Glyph

Glyph
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 11 February 2006 - 09:45 PM

I am trying to tweak the sample code presented on [a href=\"http://www.tizag.com/mysqlTutorial/mysqlwhere.php\" target=\"_blank\"]this page[/a].

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Get a specific result from the "example" table
$result = mysql_query("SELECT * FROM example
WHERE name='Sandy Smith'") or die(mysql_error());

// get the first (and hopefully only) entry from the result
$row = mysql_fetch_array( $result ); // Print out the contents of each row into a table
echo $row['name']." - ".$row['age'];
?>


In particular, I want to specify a list of records to obtain from the database, rather than just one. The contents of the list needs to be explicitly specified, as does the order, since it is based on something that simply can't be programmed to order them automatically.

Using the above example, instead of just displaying the record for Sandy Smith, I would like to display the records for Bobby Wallace, Tim Mellowman, and Sandy Smith, in that order. How would I rewrite the above in order to produce this?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 February 2006 - 10:03 PM

Well, you can definitely use a WHERE name IN ('name1','name2','name3') to get all three; to get them in a particular order is trickier. You could use ORDER BY FIELD(), but in this case, that's not ideal -- I would suggest dealing with the ordering in PHP with a lookup hash.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Glyph

Glyph
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 11 February 2006 - 10:35 PM

Thanks, but what's a lookup hash?

#4 Glyph

Glyph
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 11 February 2006 - 10:48 PM

Also, how do I generate the output. I've tried your suggestion but it only prints the first item in the list. I presume it uses implode, but what would the precise syntax be?

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 February 2006 - 07:25 AM

Basically, just associate the name with the order, and then you can sort them yourself in PHP. And to get more than one result, you'll need a while loop.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users