CODEWITHSUNDEEP
phpsqlmysql
Misha Moroshko
Misha Moroshko

Reputation: 171369

How to get all selected ids at once ? PHP/MySQL question

I have MySQL table called Files which has file_id column and file_type column (and also other columns that are not relevant to the question).

I would like to get all file_ids of all picture files (i.e. file_type='picture').

I do the query like this:

$pictures_query = mysql_query("SELECT file_id FROM Files WHERE file_type='picture')

and now the question is: how would I put all picture file_ids into an array ?

I thought to do this like that:

$pictures_ids = array();

while ($row = mysql_fetch_row($pictures_query)) {
    $pictures_ids[] = $row[0];
}

Does the loop really necessary, or there is a better way to do this ?

Upvotes: 0

Views: 702

Answers (3)

Pekka
Pekka

Reputation: 449525

The loop is in fact necessary. (Update: except if you use GROUP_CONCAT as shown by Tatu).

PDO has a fetch_all() method, but the mysql_* functions don't.

Upvotes: 1

Tatu Ulmanen
Tatu Ulmanen

Reputation: 124788

You can use GROUP_CONCAT with explode to avoid looping:

$pictures_query = mysql_query("SELECT GROUP_CONCAT(file_id) FROM Files WHERE file_type='picture'");

$file_ids = explode(',', mysql_result($pictures_query, 0));

Upvotes: 4

ITroubs
ITroubs

Reputation: 11215

The loop is necessary as long as you don't use any other functionality that hides that loop from you like Doctrine does by hydration or MYSQLI does with it's iterator like next_result() function or its fetch_all function().

Upvotes: 2

Related Questions