Selecting records based on ID

[eluser]RS71[/eluser]
Hey,

I'm trying to select some records from the database based on their ID. Normally I'd just do

Code:

SELECT * FROM table WHERE table.ID = 1

I'm gonna be storing all the possible IDs in a session variable; would I just append a

Code:

...OR table.ID = 2 OR table.ID = 3 (etc)

or is there a better way to do this? Would I have some problems if I need to retrieve some 50 different rows from the table?

Thanks in advance.

[eluser]TheFuzzy0ne[/eluser]
If you're wanting to get sequential rows, this would work.

Code:

SELECT * FROM table WHERE id >= 1 AND id <= 50;

I doubt you will have any problems doing it the way you've described either, and it's the only way to do it I know of if the rows you want to grab are not sequential.

[eluser]kgill[/eluser]
A bunch of OR's would work but yes there are better ways - and they depend on how the ID's you're looking for are related. If you just want a bunch of sequential ID's e.g. 10 through 50 then the easiest would be: where ID >= 10 and ID <= 50, if you have a bunch of ID's that are all over the place, e.g. you want 1, 3, 8, 10 & 17 then use an IN clause: where ID in (1, 3, 8, 10, 17)

[eluser]TheFuzzy0ne[/eluser]
Dammit! I always forget the WHERE IN condition...

[eluser]RS71[/eluser]
Thank you mates!

Worked like a charm

Might I ask another question?

Lets say I want to delete all rows associated to a certain ID and replace them with a new set of rows. Is there a better way to do this other than a DELETE then a INSERT VALUES (...)?

Also, can I SELECT multiple rows from multiple tables in one query? I don't necessarily want to concatenate the rows returned from the different tables.

For example, lets say I have a table listing the user's favorite 'Books' and another for 'TV Shows'. How can I select all rows from 'Books' and all rows from 'TV Shows' belonging to a certain user in one query?

Thanks once again!

[eluser]TheFuzzy0ne[/eluser]
[quote author="RS71" date="1236716798"]Lets say I want to delete all rows associated to a certain ID and replace them with a new set of rows. Is there a better way to do this other than a DELETE then a INSERT VALUES (...)?[/quote]

No. Not without using multiple queries and a PHP loop. Update only allows you to update a single row, whereas delete and insert can handle multiple rows.

[quote author="RS71" date="1236716798"]Also, can I SELECT multiple rows from multiple tables in one query? I don't necessarily want to concatenate the rows returned from the different tables.[/quote]

Yes, you'll need to use a JOIN.

[eluser]RS71[/eluser]
Thank you for the reply Fuzzy

How can I use JOINs? Wouldn't I get lots of redundant data? (it would multiply the rows in Books by the rows in TV Shows, no?)

[eluser]TheFuzzy0ne[/eluser]
[url="http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php"]Here's a decent tutorial on JOINs[/url].

You may get some redundant data, but that should be expected with JOINs. It all depends on your query, and the fields you've selected.

[eluser]kgill[/eluser]
[quote author="TheFuzzy0ne" date="1236717670"]
No. Not without using multiple queries and a PHP loop. Update only allows you to update a single row, whereas delete and insert can handle multiple rows.
[/quote]

I think you meant that the other way around, update and delete work on multiple rows and insert generally only inserts a single row.

[quote author="TheFuzzy0ne" date="1236717670"]
[quote author="RS71" date="1236716798"]Also, can I SELECT multiple rows from multiple tables in one query? I don't necessarily want to concatenate the rows returned from the different tables.[/quote]

Yes, you'll need to use a JOIN.[/quote]

Actually, in this case he wants to use UNION, a users favourite books & favourite tv shows are each going to be in a one to many relationship with the user and they're unrelated data. A join might give you what you want it's going to be a mess to deal with and it's going to be a cartesian product which is exceptionally bad when dealing with large sets - DBA's do not look kindly on queries with cartesian joins. Whereas a union will combine the two into one giant result set:

Code:

select 'favbook' source, books item from users_books where userid = 1
union
select 'favtv' source, shows item from users_tv where userid = 1

which gives something like:
source  item
------  ----
favbook foo
favbook bar
favbook baz
favtv   blah
favtv   argle
favtv   bargle

[eluser]TheFuzzy0ne[/eluser]
[quote author="kgill" date="1236811205"]I think you meant that the other way around, update and delete work on multiple rows and insert generally only inserts a single row.[/quote]

I beg to differ with you there. I insert multiple rows on a regular basis, especially when importing my database.

However, you can update multiple rows, but the statement will update all the fields matching the WHERE condition (if specified), to only one value. You can't update multiple rows in a single query, with and use multiple values to do it with.

EDIT: Or of course, you could do something like add 1 to all fields that match a particular condition.