PHP: Save MySQL Result in Array

When we want to process the result of a MySQL query in PHP, it is often practical, to store the data directly into an array. Impcityant functions in this context are mysqli_fetch_array(), mysqli_fetch_row() and mysqli_fetch_assoc(). In this info, I would like to go over this and explain the difference.

• mysqli_fetch_row - Numerically indexed Arrays
• mysqli_fetch_assoc - Associative Arrays
• mysqli_fetch_array - The all-rounder

Numerically indexed array with mysqli_fetch_row

The function mysqli_fetch_row() returns a numerically indexed array. This means, that we can access the data, for example, with $arr[1] or $arr[3]:

$res = mysqli_query($db, "SELECT name, city, country FROM tab WHERE id=1");
$arr = mysqli_fetch_row($res);
 
echo $arr[0];  // name
echo $arr[1];  // city
echo $arr[2];  // country

As the example shows, the order of the elements in the array corresponds to the order of the fields in the query. So we can use the query to determine how our array should be filled.

Associative array with mysqli_fetch_assoc

The function mysqli_fetch_array() returns an associative array. This means, that the fields can not be addressed with the index number but with the name of the field. And this field name is the name of our column from our MySQL database:

$res = mysqli_query($db, "SELECT name, city, country FROM tab WHERE id=1");
$arr = mysqli_fetch_assoc($res);
 
echo $arr['name'];    // name
echo $arr['city'];    // city
echo $arr['country']; // country

Interesting in this context is the function extract(). This function can automatically create individual variables with the respective name from the array, which are then available to us in the code, as the following example shows:

$res = mysqli_query($db, "SELECT name, city, country FROM tab WHERE id=1");
$arr = mysqli_fetch_assoc($res);
extract($arr);
 
echo $name;    // name
echo $city;    // city
echo $country; // country

Also the function list() can be used to make individual variables out of the array, as the following example shows:

$res = mysqli_query($db, "SELECT name, city, country FROM tab WHERE id=1");
list($name, $city, $country) = mysqli_fetch_row($res);
 
echo $name;    // name
echo $city;    // city
echo $country; // country

In contrast to extract(), however, with list() we have the option of freely defining what our variables should be called. We can, but don't have to, stick to the names of the columns in our table.

The all-rounder mysqli_fetch_array

The function mysqli_fetch_array() masters to output the data set both numerically indexed as well as an associative array. This can be controled with the parameters MYSQLI_NUM or MYSQLI_ASSOC.

If you omit the parameter completely or you are using MYSQLI_BOTH, you can use both types of indexes:

$arr = mysqli_fetch_array($res);  
// equivalent to mysqli_fetch_array($res, MYSQL_BOTH);
 
echo $arr[0];      // name
echo $arr['city']; // city
echo $arr[2];      // country

Calling mysqli_fetch_array($res, MYSQLI_NUM) is equivalent to the function mysqli_fetch_row($res).

Calling mysqli_fetch_array($res, MYSQLI_ASSOC) is equivalent to the function mysqli_fetch_assoc($res).