Reading in data

bool mysqli_fetch_assoc ( resource result)

To read data in from a MySQL result index, use the mysqli_fetch_assoc() function. This takes one row from a MySQL result, and converts it to an associative array with each field name as a key and the matching field value as the value. Mysqli_fetch_assoc() increments its position each time it is called - calling it for the first time reads the first row, the second time the second row, etc, until you run out of rows in which case it returns false. In this respect it works like the each() array function we looked at it previously.

To extend our previous script to handle outputting data in a nicely formatted manner, we would need to make it use mysqli_fetch_assoc() to go through each row returned by the query, printing out all fields in there. Take a look at this next example:

    $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
    $result = mysqli_query($db, "SELECT * FROM usertable");

    if ($result && mysqli_num_rows($result)) {
        $numrows = mysqli_num_rows($result);
        $rowcount = 1;
        print "There are $numrows people in usertable:<br /><br />";
        while ($row = mysqli_fetch_assoc($result)) {
            print "Row $rowcount<br />";
            while(list($var, $val) = each($row)) {
                print "<B>$var</B>: $val<br />";
            print "<br />";

It might look like a lot of code at first, but you should already know what a lot of it does. To start of, the script connects to the local MySQL database server and selects the phpdb database for use. It then runs a basic query on our usertable table and stores the result index in $result. The next line checks that $result is true and that there is at least one row in there - if so, it stores the number of rows in $numrows, sets the $rowcount variable to 1, then outputs the number of rows it found.

The next section is the new part - $row is set to the return value of mysqli_fetch_assoc(), which means it will be set to an array containing the data from the next row in the result. If mysqli_fetch_assoc() has no more rows to return, it sends back false and ends the while loop. Each time we have a row to read, $rowcount is outputted, then the script goes through the array stored in $row (sent back from mysqli_fetch_assoc()) outputting each key and its value.

Finally, $rowcount is incremented, and the while loop goes around again. This is a construct you will be using a lot , so I recommend you get a firm grip on how the above code works before you go any further!

Author's Note: As an alternative to mysqli_fetch_assoc(), many programmers use mysqli_fetch_array(). The difference between the two is that, by default, mysqli_fetch_array() returns an array of the row data with numerical field indexes (i.e. 0, 1, 2, 3) as well as string field indexes (i.e. Name, Age, etc). This extra work does take a little extra processing from PHP, so, in its default settings, mysqli_fetch_array() is a little slower than mysqli_fetch_assoc().

Many people override this difficulty by passing the MYSQLI_ASSOC as the second parameter to mysqli_fetch_array(), which makes it behave the same as mysqli_fetch_assoc() and run as fast also. However, in this situation, why not just use mysqli_fetch_assoc() ?


Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Mixing in PHP variables >>

Previous chapter: Disconnecting from a MySQL database

Jump to:


Home: Table of Contents

Copyright ©2015 Paul Hudson. Follow me: @twostraws.