Reading in data

bool mysql_fetch_assoc ( resource result)

To read data in from a MySQL result index, use the mysql_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. Mysql_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 mysql_fetch_assoc() to go through each row returned by the query, printing out all fields in there. Take a look at this next example:

<?php
    mysql_connect
("localhost", "phpuser", "alm65z");
    
mysql_select_db("phpdb");
    
$result = mysql_query("SELECT * FROM usertable");

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

Here is a screenshot of how that script looks when viewed through a web browser:


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 mysql_fetch_assoc(), which means it will be set to an array containing the data from the next row in the result. If mysql_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 mysql_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 mysql_fetch_assoc(), many programmers use mysql_fetch_array(). The difference between the two is that, by default, mysql_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, mysql_fetch_array() is a little slower than mysql_fetch_assoc(). Many people override this difficulty by passing the MYSQL_ASSOC as the second parameter to mysql_fetch_array(), which makes it behave the same as mysql_fetch_assoc() and run as fast also. However, in this situation, why not just use mysql_fetch_assoc() ?

 

Next chapter: Mixing in PHP variables >>

Previous chapter: Disconnecting from a MySQL database

Jump to:

 

Home: Table of Contents

Follow us on Identi.ca or Twitter

Username:   Password:
Create Account | About TuxRadar