Retrieve list of rows by primary ID

Capt_n_Hector

Hi,

Here's the problem:

I've got an array with a list of numbers. These numbers are primary IDs in a table. Ex,

00034
00175
00004
etc...

they don't have to be sequential, or ordered in any way. The list is always less than 50 numbers long.

Can I run a MySQL query to retrieve those rows and just those rows?

Thanks!

-h

eoghain

Yes, you just use the IN clause:

SELECT *
  FROM MyTable
 WHERE primaryID IN (00034, 00175, 00004)

In queries aren't as efficient as other types of queries but sometimes they are the only way to get something done.

Capt_n_Hector

Cool, thanks. It worked! See the results at,

http://harry.blogdns.com/images/

Capt_n_Hector

hmmm.... actually, it's not working. MySQL reorders the results by id... instead of by the order I specify in the IN command. Any way to get around this?

Capt_n_Hector

Here's my setup, in case there's a much easier solution to my rather simple problem:

I've got two tables. One is for images, has 3 columns of interest (id, filename, description) and the other is for galleries (id, list-of-images, description).

The user loads the page, PHP gets the list of galleries available in the MySQL table, the user selects one, and the script is supposed to return an array of image file names with their descriptions... in the right order. Also, when I try to use "IN" with anything other than the primary ID, MySQL has a fit. I'm running v4.0.18.

Roger_Ramjet

Databases store and retrieve records in any random order: just as they find space on the disk and then as they come across them when reading the table. Some rdbms will return the result set in random order unless one is specified, others will default to the primary key when no order is specified. Use an ORDER BY clause after the end bracket of your IN list to specify your required order.

eoghain

To expand on Rogers post, you can order by any column that you are returning, but you won't be able to order on your IN clause.

So if you have a table with a single column that holds the numbers 1 - 10 you can order by that column and get your results in ascending or descending order but not a mixed order.

select num
  from myTable
 where num in (6,3,8)
 order by num;

num
-----
3
6
8

select num
  from myTable
 where num in (6,3,8)
 order by num desc;

num
-----
8
6
3

And if you didn't use an order by clause the results would be returned (by mysql at least) in the order that they were added to the table.

Capt_n_Hector

Thanks for the help, guys. I figured out a way to get around this: I'll have MySQL return an associative array for the images, so it's like: image_filename --> description.

Then, I'll use my original list, ordered how I like, and use that to construct my page.