What is the best way to get MySQL query results as an array in PHP?

If you are going to learn PHP in 2011, let's do it right.

First off, mysql_query or mysql_ anything code is deprecated. Don't use it anymore.

Don't worry - what I am suggesting works great with mysql databases, but it will also work great with any database:

• PDO is what the PHP community continues to add features to, so I would use that.
• PDO is also way more powerful, and makes it easier to switch databases later.
• MYSQLi (the i stands for improved) replaces deprecated mysql_ based queries, but I would definitely go straight to using PDO.
• You could also easily create an array of objects later with one line change!

Secondly, Phil mentioned fetchAll(). This is the end goal. The other ways simply move thru it one row at a time. This uses a bulldozer instead of a shovel. Note: not the best way of selecting really large amounts of data, as it will use up memory. Otherwise, it is fine.

To get there, use prepared procedures to protect your code from SQL injection attacks.

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();

/* Fetch all of the rows into an array */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);

?>

If you're trying to store all the database rows into an array, yes, that code should do it. A few comments, though:

1. As curiou57 suggested, use mysql_fetch_assoc() to be able to refer to columns in an individual row by their names. (ex: foreach ($data as $row) { echo $row['columnname']; })
2. Make sure you run $id through mysql_real_escape_string() if you have to continue using the mysql extension. This prevents SQL injection attacks.
3. If you don't have to continue using the mysql extension, consider using PDO or mysqli.

Switch to mysql_fetch_row() if you want to reference each column by a numeric index (note, zero-based). Otherwise, that looks correct.

If you decide to switch to PDO, you can use the handy PDOStatement::fetchAll() method, using the PDO::FETCH_NUM fetch style to fetch all rows as numeric arrays into an array.

This is the correct way:

while($rows[] = mysqli_fetch_assoc($result));
array_pop($rows);  // pop the last row off, which is an empty row