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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
<?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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
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.
1 2 3 4 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
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.
1 2 3 4 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 |
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.
1 2 3 4 |
while($row = mysqli_fetch_object($result)) { // output each row var_dump($row); } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
<?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.'; } ?> |
1 2 3 4 5 6 7 8 |
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.