Returning full record details from an array of IDs

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Returning full record details from an array of IDs

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Use IN() that's what it's for.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply