Process Returned MySQL Query Results In PHP

4 Cool Ways To Process Returned MySQL Query Results In PHP

In the prior episode of working with MySQL and PHP, we took a good look at the entire process of getting data to work with. This involved setting up a database connection, testing the database connection, defining a query, running said query, processing any results we got back, releasing memory, and closing the database connection. Bing, Bang, Boom, we’re talking to the database successfully. In this PHP and MySQL Tutorial we’ll take a closer look at the processing portion of this cycle. So far we have only looked at mysqli_fetch_row, but now we’ll take a look at mysqli_fetch_assoc, mysqli_fetch_array, and mysqli_fetch_object as well. There are some similarities and differences between the various approaches so we’ll examine those as well. Let’s jump in!

Processing Returned Query Data

There are four ways to process the data we get back from a query. The first approach is simply using the mysqli_fetch_row option. This retrieves a row of data from the database and assigns it to a standard array. There is nothing fancy about this array, and the keys of this array are simply 0 based integers. For example this query will return three rows of data from the wp_posts table of a WordPress database.

1. mysqli_fetch_row


<?php

// Step 1
// open a connection
$dhost = 'localhost';
$duser = 'root';
$dpw = '';
$dname = 'wordpress';
$connection = mysqli_connect($dhost, $duser, $dpw, $dname);

// Step 2
// test the connection
if(mysqli_connect_errno()){
    die('Something went wrong with the database<br><br> '
    . mysqli_connect_error() . ':' 
    . mysqli_connect_errno());
}

// Step 3
// define the query
$query1 = "select * from wp_posts";

// build up the query style
$query2 = "SELECT * ";
$query2 .= "FROM `wp_posts` ";
$query2 .= "WHERE `post_title` LIKE '%laravel%' ";
$query2 .= "LIMIT 0 , 3 ";

// Step 4
// run the query to get a resource placed in $result
$result = mysqli_query($connection, $query2);

// Step 5
// test to see if the query was successful
if(!$result) {
    die('The query was not successful.');
} else {
    // Step 6
    // the query was successful so let's get the data if there is some
    // as long as there is another row in the $result, assign it to $row
    while($row = mysqli_fetch_row($result)) {
        // output each row
        var_dump($row);
    }
    
    // Step 7
    // Release the returned data
    mysqli_free_result($result);
}

// Step 8
// close the connection
if(mysqli_close($connection) == true) {
    echo 'Database connection closed.';
}

?>

Here is the output of your three posts. Notice that each key in the array is just an integer which is not exactly the most helpful way of identifying what you are dealing with. A pro is that this approach is the fastest of all options.

array (size=23)
  0 => string '3518' (length=4)
  1 => string '1' (length=1)
  2 => string '2014-01-07 21:51:27' (length=19)
  3 => string '2014-01-07 21:51:27' (length=19)
  4 => string '' (length=9243)
  5 => string 'Install Laravel on Windows' (length=26)
  6 => string '' (length=0)
  7 => string 'publish' (length=7)
  8 => string 'open' (length=4)
  9 => string 'open' (length=4)
  10 => string '' (length=0)
  11 => string 'install-laravel-on-windows' (length=26)
  12 => string '' (length=0)
  13 => string '' (length=0)
  14 => string '2014-01-07 21:51:27' (length=19)
  15 => string '2014-01-07 21:51:27' (length=19)
  16 => string '' (length=0)
  17 => string '0' (length=1)
  18 => string 'https://vegibit.com/?p=3518' (length=26)
  19 => string '0' (length=1)
  20 => string 'post' (length=4)
  21 => string '' (length=0)
  22 => string '9' (length=1)

array (size=23)
  0 => string '3582' (length=4)
  1 => string '1' (length=1)
  2 => string '2014-01-20 20:33:01' (length=19)
  3 => string '2014-01-20 20:33:01' (length=19)
  4 => string '' (length=7009)
  5 => string 'Crud In Laravel 4' (length=17)
  6 => string '' (length=0)
  7 => string 'publish' (length=7)
  8 => string 'open' (length=4)
  9 => string 'open' (length=4)
  10 => string '' (length=0)
  11 => string 'crud-in-laravel-4' (length=17)
  12 => string '' (length=0)
  13 => string '' (length=0)
  14 => string '2014-01-20 20:33:01' (length=19)
  15 => string '2014-01-20 20:33:01' (length=19)
  16 => string '' (length=0)
  17 => string '0' (length=1)
  18 => string 'https://vegibit.com/?p=3582' (length=26)
  19 => string '0' (length=1)
  20 => string 'post' (length=4)
  21 => string '' (length=0)
  22 => string '5' (length=1)

array (size=23)
  0 => string '3658' (length=4)
  1 => string '1' (length=1)
  2 => string '2014-01-25 03:11:55' (length=19)
  3 => string '2014-01-25 03:11:55' (length=19)
  4 => string '' (length=29)
  6 => string '' (length=0)
  7 => string 'publish' (length=7)
  8 => string 'open' (length=4)
  9 => string 'open' (length=4)
  10 => string '' (length=0)
  11 => string 'laravel-eloquent-orm-tutorial-2' (length=31)
  12 => string '' (length=0)
  13 => string '' (length=0)
  14 => string '2014-01-25 03:11:55' (length=19)
  15 => string '2014-01-25 03:11:55' (length=19)
  16 => string '' (length=0)
  17 => string '0' (length=1)
  18 => string 'https://vegibit.com/?p=3658' (length=26)
  19 => string '0' (length=1)
  20 => string 'post' (length=4)
  21 => string '' (length=0)
  22 => string '6' (length=1)

2. mysqli_fetch_assoc

An easier way to work with the data is to be able to have the database field names be the key names of the array. To do this, you can simply use mysqli_fetch_assoc function. Simply update the while loop to use this function like so.


    while($row = mysqli_fetch_assoc($result)) {
        // output each row
        var_dump($row);
    }

Now here are the three posts and notice that the keys of the array are nice descriptive names. These are the field names from the table you pulled the data from. So the difference between mysqli_fetch_row and mysqli_fetch_assoc is that mysqli_fetch_row places results into a standard array and mysqli_fetch_assoc places results into an associative array.

array (size=23)
  'ID' => string '3518' (length=4)
  'post_author' => string '1' (length=1)
  'post_date' => string '2014-01-07 21:51:27' (length=19)
  'post_date_gmt' => string '2014-01-07 21:51:27' (length=19)
  'post_content' => string ''(length=9243)
  'post_title' => string 'Install Laravel on Windows' (length=26)
  'post_excerpt' => string '' (length=0)
  'post_status' => string 'publish' (length=7)
  'comment_status' => string 'open' (length=4)
  'ping_status' => string 'open' (length=4)
  'post_password' => string '' (length=0)
  'post_name' => string 'install-laravel-on-windows' (length=26)
  'to_ping' => string '' (length=0)
  'pinged' => string '' (length=0)
  'post_modified' => string '2014-01-07 21:51:27' (length=19)
  'post_modified_gmt' => string '2014-01-07 21:51:27' (length=19)
  'post_content_filtered' => string '' (length=0)
  'post_parent' => string '0' (length=1)
  'guid' => string 'https://vegibit.com/?p=3518' (length=26)
  'menu_order' => string '0' (length=1)
  'post_type' => string 'post' (length=4)
  'post_mime_type' => string '' (length=0)
  'comment_count' => string '9' (length=1)

array (size=23)
  'ID' => string '3582' (length=4)
  'post_author' => string '1' (length=1)
  'post_date' => string '2014-01-20 20:33:01' (length=19)
  'post_date_gmt' => string '2014-01-20 20:33:01' (length=19)
  'post_content' => string '' (length=7009)
  'post_title' => string 'Crud In Laravel 4' (length=17)
  'post_excerpt' => string '' (length=0)
  'post_status' => string 'publish' (length=7)
  'comment_status' => string 'open' (length=4)
  'ping_status' => string 'open' (length=4)
  'post_password' => string '' (length=0)
  'post_name' => string 'crud-in-laravel-4' (length=17)
  'to_ping' => string '' (length=0)
  'pinged' => string '' (length=0)
  'post_modified' => string '2014-01-20 20:33:01' (length=19)
  'post_modified_gmt' => string '2014-01-20 20:33:01' (length=19)
  'post_content_filtered' => string '' (length=0)
  'post_parent' => string '0' (length=1)
  'guid' => string 'https://vegibit.com/?p=3582' (length=26)
  'menu_order' => string '0' (length=1)
  'post_type' => string 'post' (length=4)
  'post_mime_type' => string '' (length=0)
  'comment_count' => string '5' (length=1)

array (size=23)
  'ID' => string '3658' (length=4)
  'post_author' => string '1' (length=1)
  'post_date' => string '2014-01-25 03:11:55' (length=19)
  'post_date_gmt' => string '2014-01-25 03:11:55' (length=19)
  'post_content' => string ''(length=16826)
  'post_title' => string 'Laravel Eloquent ORM Tutorial' (length=29)
  'post_excerpt' => string '' (length=0)
  'post_status' => string 'publish' (length=7)
  'comment_status' => string 'open' (length=4)
  'ping_status' => string 'open' (length=4)
  'post_password' => string '' (length=0)
  'post_name' => string 'laravel-eloquent-orm-tutorial-2' (length=31)
  'to_ping' => string '' (length=0)
  'pinged' => string '' (length=0)
  'post_modified' => string '2014-01-25 03:11:55' (length=19)
  'post_modified_gmt' => string '2014-01-25 03:11:55' (length=19)
  'post_content_filtered' => string '' (length=0)
  'post_parent' => string '0' (length=1)
  'guid' => string 'https://vegibit.com/?p=3658' (length=26)
  'menu_order' => string '0' (length=1)
  'post_type' => string 'post' (length=4)
  'post_mime_type' => string '' (length=0)
  'comment_count' => string '6' (length=1)

3. mysqli_fetch_array

The third option for you if you’re unable to make up your mind which of the first two are better, is to use the mysqli_fetch_array. Why you say? Because this function returns both index based and associative style in one big array. We’ll update the while loop one more time, then output the results. Note that this approach is probably not ideal since it is more computational and memory intensive for no real gain.


    while($row = mysqli_fetch_array($result)) {
        // output each row
        var_dump($row);
    }

Once again, here are the three posts with the output processed by mysqli_fetch_array

array (size=46)
  0 => string '3518' (length=4)
  'ID' => string '3518' (length=4)
  1 => string '1' (length=1)
  'post_author' => string '1' (length=1)
  2 => string '2014-01-07 21:51:27' (length=19)
  'post_date' => string '2014-01-07 21:51:27' (length=19)
  3 => string '2014-01-07 21:51:27' (length=19)
  'post_date_gmt' => string '2014-01-07 21:51:27' (length=19)
  4 => string ''(length=9243)
  'post_content' => string '' (length=9243)
  5 => string 'Install Laravel on Windows' (length=26)
  'post_title' => string 'Install Laravel on Windows' (length=26)
  6 => string '' (length=0)
  'post_excerpt' => string '' (length=0)
  7 => string 'publish' (length=7)
  'post_status' => string 'publish' (length=7)
  8 => string 'open' (length=4)
  'comment_status' => string 'open' (length=4)
  9 => string 'open' (length=4)
  'ping_status' => string 'open' (length=4)
  10 => string '' (length=0)
  'post_password' => string '' (length=0)
  11 => string 'install-laravel-on-windows' (length=26)
  'post_name' => string 'install-laravel-on-windows' (length=26)
  12 => string '' (length=0)
  'to_ping' => string '' (length=0)
  13 => string '' (length=0)
  'pinged' => string '' (length=0)
  14 => string '2014-01-07 21:51:27' (length=19)
  'post_modified' => string '2014-01-07 21:51:27' (length=19)
  15 => string '2014-01-07 21:51:27' (length=19)
  'post_modified_gmt' => string '2014-01-07 21:51:27' (length=19)
  16 => string '' (length=0)
  'post_content_filtered' => string '' (length=0)
  17 => string '0' (length=1)
  'post_parent' => string '0' (length=1)
  18 => string 'https://vegibit.com/?p=3518' (length=26)
  'guid' => string 'https://vegibit.com/?p=3518' (length=26)
  19 => string '0' (length=1)
  'menu_order' => string '0' (length=1)
  20 => string 'post' (length=4)
  'post_type' => string 'post' (length=4)
  21 => string '' (length=0)
  'post_mime_type' => string '' (length=0)
  22 => string '9' (length=1)
  'comment_count' => string '9' (length=1)

array (size=46)
  0 => string '3582' (length=4)
  'ID' => string '3582' (length=4)
  1 => string '1' (length=1)
  'post_author' => string '1' (length=1)
  2 => string '2014-01-20 20:33:01' (length=19)
  'post_date' => string '2014-01-20 20:33:01' (length=19)
  3 => string '2014-01-20 20:33:01' (length=19)
  'post_date_gmt' => string '2014-01-20 20:33:01' (length=19)
  4 => string ''(length=7009)
  'post_content' => string ''(length=7009)
  5 => string 'Crud In Laravel 4' (length=17)
  'post_title' => string 'Crud In Laravel 4' (length=17)
  6 => string '' (length=0)
  'post_excerpt' => string '' (length=0)
  7 => string 'publish' (length=7)
  'post_status' => string 'publish' (length=7)
  8 => string 'open' (length=4)
  'comment_status' => string 'open' (length=4)
  9 => string 'open' (length=4)
  'ping_status' => string 'open' (length=4)
  10 => string '' (length=0)
  'post_password' => string '' (length=0)
  11 => string 'crud-in-laravel-4' (length=17)
  'post_name' => string 'crud-in-laravel-4' (length=17)
  12 => string '' (length=0)
  'to_ping' => string '' (length=0)
  13 => string '' (length=0)
  'pinged' => string '' (length=0)
  14 => string '2014-01-20 20:33:01' (length=19)
  'post_modified' => string '2014-01-20 20:33:01' (length=19)
  15 => string '2014-01-20 20:33:01' (length=19)
  'post_modified_gmt' => string '2014-01-20 20:33:01' (length=19)
  16 => string '' (length=0)
  'post_content_filtered' => string '' (length=0)
  17 => string '0' (length=1)
  'post_parent' => string '0' (length=1)
  18 => string 'https://vegibit.com/?p=3582' (length=26)
  'guid' => string 'https://vegibit.com/?p=3582' (length=26)
  19 => string '0' (length=1)
  'menu_order' => string '0' (length=1)
  20 => string 'post' (length=4)
  'post_type' => string 'post' (length=4)
  21 => string '' (length=0)
  'post_mime_type' => string '' (length=0)
  22 => string '5' (length=1)
  'comment_count' => string '5' (length=1)

array (size=46)
  0 => string '3658' (length=4)
  'ID' => string '3658' (length=4)
  1 => string '1' (length=1)
  'post_author' => string '1' (length=1)
  2 => string '2014-01-25 03:11:55' (length=19)
  'post_date' => string '2014-01-25 03:11:55' (length=19)
  3 => string '2014-01-25 03:11:55' (length=19)
  'post_date_gmt' => string '2014-01-25 03:11:55' (length=19)
  4 => string ''(length=16826)
  'post_content' => string ''(length=16826)
  5 => string 'Laravel Eloquent ORM Tutorial' (length=29)
  'post_title' => string 'Laravel Eloquent ORM Tutorial' (length=29)
  6 => string '' (length=0)
  'post_excerpt' => string '' (length=0)
  7 => string 'publish' (length=7)
  'post_status' => string 'publish' (length=7)
  8 => string 'open' (length=4)
  'comment_status' => string 'open' (length=4)
  9 => string 'open' (length=4)
  'ping_status' => string 'open' (length=4)
  10 => string '' (length=0)
  'post_password' => string '' (length=0)
  11 => string 'laravel-eloquent-orm-tutorial-2' (length=31)
  'post_name' => string 'laravel-eloquent-orm-tutorial-2' (length=31)
  12 => string '' (length=0)
  'to_ping' => string '' (length=0)
  13 => string '' (length=0)
  'pinged' => string '' (length=0)
  14 => string '2014-01-25 03:11:55' (length=19)
  'post_modified' => string '2014-01-25 03:11:55' (length=19)
  15 => string '2014-01-25 03:11:55' (length=19)
  'post_modified_gmt' => string '2014-01-25 03:11:55' (length=19)
  16 => string '' (length=0)
  'post_content_filtered' => string '' (length=0)
  17 => string '0' (length=1)
  'post_parent' => string '0' (length=1)
  18 => string 'https://vegibit.com/?p=3658' (length=26)
  'guid' => string 'https://vegibit.com/?p=3658' (length=26)
  19 => string '0' (length=1)
  'menu_order' => string '0' (length=1)
  20 => string 'post' (length=4)
  'post_type' => string 'post' (length=4)
  21 => string '' (length=0)
  'post_mime_type' => string '' (length=0)
  22 => string '6' (length=1)
  'comment_count' => string '6' (length=1)

4. mysqli_fetch_object

Last up is the ability to fetch rows as objects using the mysqli_fetch_object function. Note the updated syntax and associated updated output of our query for three posts in a WordPress database.


    while($row = mysqli_fetch_object($result)) {
        // output each row
        var_dump($row);
    }


object(stdClass)[3]
  public 'ID' => string '3518' (length=4)
  public 'post_author' => string '1' (length=1)
  public 'post_date' => string '2014-01-07 21:51:27' (length=19)
  public 'post_date_gmt' => string '2014-01-07 21:51:27' (length=19)
  public 'post_content' => string ''(length=9243)
  public 'post_title' => string 'Install Laravel on Windows' (length=26)
  public 'post_excerpt' => string '' (length=0)
  public 'post_status' => string 'publish' (length=7)
  public 'comment_status' => string 'open' (length=4)
  public 'ping_status' => string 'open' (length=4)
  public 'post_password' => string '' (length=0)
  public 'post_name' => string 'install-laravel-on-windows' (length=26)
  public 'to_ping' => string '' (length=0)
  public 'pinged' => string '' (length=0)
  public 'post_modified' => string '2014-01-07 21:51:27' (length=19)
  public 'post_modified_gmt' => string '2014-01-07 21:51:27' (length=19)
  public 'post_content_filtered' => string '' (length=0)
  public 'post_parent' => string '0' (length=1)
  public 'guid' => string 'https://vegibit.com/?p=3518' (length=26)
  public 'menu_order' => string '0' (length=1)
  public 'post_type' => string 'post' (length=4)
  public 'post_mime_type' => string '' (length=0)
  public 'comment_count' => string '9' (length=1)

object(stdClass)[4]
  public 'ID' => string '3582' (length=4)
  public 'post_author' => string '1' (length=1)
  public 'post_date' => string '2014-01-20 20:33:01' (length=19)
  public 'post_date_gmt' => string '2014-01-20 20:33:01' (length=19)
  public 'post_content' => string ''(length=7009)
  public 'post_title' => string 'Crud In Laravel 4' (length=17)
  public 'post_excerpt' => string '' (length=0)
  public 'post_status' => string 'publish' (length=7)
  public 'comment_status' => string 'open' (length=4)
  public 'ping_status' => string 'open' (length=4)
  public 'post_password' => string '' (length=0)
  public 'post_name' => string 'crud-in-laravel-4' (length=17)
  public 'to_ping' => string '' (length=0)
  public 'pinged' => string '' (length=0)
  public 'post_modified' => string '2014-01-20 20:33:01' (length=19)
  public 'post_modified_gmt' => string '2014-01-20 20:33:01' (length=19)
  public 'post_content_filtered' => string '' (length=0)
  public 'post_parent' => string '0' (length=1)
  public 'guid' => string 'https://vegibit.com/?p=3582' (length=26)
  public 'menu_order' => string '0' (length=1)
  public 'post_type' => string 'post' (length=4)
  public 'post_mime_type' => string '' (length=0)
  public 'comment_count' => string '5' (length=1)

object(stdClass)[3]
  public 'ID' => string '3658' (length=4)
  public 'post_author' => string '1' (length=1)
  public 'post_date' => string '2014-01-25 03:11:55' (length=19)
  public 'post_date_gmt' => string '2014-01-25 03:11:55' (length=19)
  public 'post_content' => string ''(length=16826)
  public 'post_title' => string 'Laravel Eloquent ORM Tutorial' (length=29)
  public 'post_excerpt' => string '' (length=0)
  public 'post_status' => string 'publish' (length=7)
  public 'comment_status' => string 'open' (length=4)
  public 'ping_status' => string 'open' (length=4)
  public 'post_password' => string '' (length=0)
  public 'post_name' => string 'laravel-eloquent-orm-tutorial-2' (length=31)
  public 'to_ping' => string '' (length=0)
  public 'pinged' => string '' (length=0)
  public 'post_modified' => string '2014-01-25 03:11:55' (length=19)
  public 'post_modified_gmt' => string '2014-01-25 03:11:55' (length=19)
  public 'post_content_filtered' => string '' (length=0)
  public 'post_parent' => string '0' (length=1)
  public 'guid' => string 'https://vegibit.com/?p=3658' (length=26)
  public 'menu_order' => string '0' (length=1)
  public 'post_type' => string 'post' (length=4)
  public 'post_mime_type' => string '' (length=0)
  public 'comment_count' => string '6' (length=1)

Drilling Down on Results

In the examples above, we simply used a var_dump to dump out the entire contents of the query results to the screen. This is not what you are likely going to do in your applications. Sure it’s great for troubleshooting and debugging, but it makes much more sense to strictly access only the data you want to work with. Let’s try this out with the mysqli_fetch_assoc function.

<?php

// Step 1
// open a connection
$dhost = 'localhost';
$duser = 'root';
$dpw = '';
$dname = 'wordpress';
$connection = mysqli_connect($dhost, $duser, $dpw, $dname);

// Step 2
// test the connection
if(mysqli_connect_errno()){
    die('Something went wrong with the database<br><br> '
    . mysqli_connect_error() . ':' 
    . mysqli_connect_errno());
}

// Step 3
// define the query
$query1 = "select * from wp_posts";

// build up the query style
$query2 = "SELECT * ";
$query2 .= "FROM `wp_posts` ";
$query2 .= "WHERE `post_title` LIKE '%laravel%' ";
$query2 .= "LIMIT 0 , 3 ";

// Step 4
// run the query to get a resource placed in $result
$result = mysqli_query($connection, $query2);

// Step 5
// test to see if the query was successful
if(!$result) {
    die('The query was not successful.');
} else {
    // Step 6
    // the query was successful so let's get the data if there is some
    // as long as there is another row in the $result, assign it to $row
    while($post = mysqli_fetch_assoc($result)) {
        // output each row
        echo 'The post ID is '.$post['ID'].'<br>';
        echo 'The post title is '.$post['post_title'].'<br><br>';
    }
    
    // Step 7
    // Release the returned data
    mysqli_free_result($result);
}

// Step 8
// close the connection
if(mysqli_close($connection) == true) {
    echo 'Database connection closed.';
}

?>

The post ID is 3518
The post title is Install Laravel on Windows

The post ID is 3582
The post title is Crud In Laravel 4

The post ID is 3658
The post title is Laravel Eloquent ORM Tutorial

Look at how much nicer that is to look at! Also note that instead of assigning the results to a $row variable, we assign the results to a $post variable. Since we are dealing with posts in WordPress database, it makes sense to name things this way. If you were querying an orders table, you might assign the results to an $order variable, and so on. This is to give some indication to yourself and anyone else who needs to review the code of what you are actually dealing with.

Conclusion

In this particular adventure in our PHP and MySQL tutorial series, we took a look at the four different ways to process query results. We looked at mysqli_fetch_row, mysqli_fetch_assoc, mysqli_fetch_array, and mysqli_fetch_object as ways to loop through your results and output them or process them as needed.