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

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 ?

Answer 1

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.

Answer 2

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().

Answer 3

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));