Page 1 of 1

Returning full record details from an array of IDs

Posted: Tue Apr 24, 2007 11:55 am
by batfastad
Hi everyone

As a user is browsing our database, I want to be recording their history in a session.
I've got this aspect up and running in PHP.

But I'd like to be able to generate a a table of the full details of their browse history.

All I store in the session is the company ID, but if a user's been browsing the database all day there can be 300 or so items in the session.

I guess I could do the following query to return the full records...

Code: Select all

SELECT * FROM companies WHERE company_id=$company_id_0 OR company_id=$company_id_1 ... OR company_id=$company_id_480
But it's aweful and I would have thought it's the most inefficient way of doing things.

Or far better use the IN statement...

Code: Select all

SELECT * FROM companies WHERE company_id IN (1,2,3,4,5...890,898)
I can get that to work, even though I am very new to MySQL.

But I was wondering if that's the best way to do what I'm looking for?

Is it appropriate to do this for thousands of values?
Or is there a better way I've not come across yet?

Or is the answer to just not do this type of thing?


Thanks in advance
Ben

Posted: Tue Apr 24, 2007 2:58 pm
by feyd
Use IN() that's what it's for.

Posted: Tue Apr 24, 2007 3:03 pm
by batfastad
Yeah I thought that would be the best way.

Is it acceptable to use it for 1,000 or so values?
Or is that just a bad idea?

Posted: Tue Apr 24, 2007 3:07 pm
by feyd
If they are contiguous, use the BETWEEN clause instead.

At 1000 values, you may exceed the maximum packet size the database may accept, so be careful.