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

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.

---

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.

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.

---

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.

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

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.

---

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.

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.