Selecting large amount of rows from database table with MySQL

Question

I have a table with attributes like this:

likes(id, message, ip, time)

id is the primary key and is auto-incrementing. Whet I want to do is select 30 rows from this table based on an id that I provide.

I can build a random array of all IDs I want to grab from the table, now I just need to go about doing that. My usual SQL query would be:

SELECT message
FROM `likes`
WHERE id=$id
LIMIT 1

But I need to select 30 rows. There are two ways I can do this, I can either use a FOR loop with 30 queries, or combine it all into one query and grab them all at the same time. Obviously I would like to do the latter.

Can anyone help me out on the SQL query to use to grab more than one row from my database table at once? And perhaps an example of how I would parse these into an array for easy retrieval?

Greatly appreciated, thanks.

I'm confused - 30 rows limited by what? It can't be limited by id=$id because then you'd get 30 of the same row. Unless that's what you want? — Jamie Wong
– Jamie Wong, Commented Jun 28, 2010 at 1:42
Nah, I have all the IDs of the rows I want in an array. I want to go through all of these IDs in that array and get the message attribute from that row. The example I used was my example of getting ONE row from the table :) — VIVA LA NWO
– VIVA LA NWO, Commented Jun 28, 2010 at 1:43

Darko Kenda · Accepted Answer · 2010-06-28 01:43:36Z

8

You could use the IN operator, so it would be something like this:

SELECT message FROM likes WHERE id IN (1, 2, 3, 4, 5, ...)

answered Jun 28, 2010 at 1:43

Darko Kenda

5,0001 gold badge30 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zebediah49 · Accepted Answer · 2010-06-28 02:05:11Z

1

$ids = array(1,2,3,4,5,6,7,8,9); //or however many
$query = "SELECT * FROM `likes` WHERE `id` IN (".implode(', ', $ids).")";
$res = mysql_query($query);
$results = array();
while($tmp = mysql_fetch_array($res)) {
     $results[$tmp['id'] = $tmp;
}

Will give you an array, $results, sorted by key ['id'], with each being an array on each answer key-- 'id', 'message', 'ip', 'time'. So for example:

for($results as $key => $val) {
    echo "${val['ip']} posted {$var['message']} on {$val['time']} <br/>";
}

edited Jun 28, 2010 at 2:05

answered Jun 28, 2010 at 1:58

zebediah49

7,6211 gold badge36 silver badges51 bronze badges

1 Comment

too much php Over a year ago

In PHP you create an array with $ids = array(1,2,3,...);.

Collectives™ on Stack Overflow

Selecting large amount of rows from database table with MySQL

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related