Hudzilla.org - the homepage of Paul Hudson
Contents > Databases > Using MySQL with PHP Wish List | Report Bug | About Me ]

9.4.4     Reading in data: mysql_fetch_assoc()

This is NOT the latest copy of this book; click here for the latest version.

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() ?





<< 9.4.3 Disconnecting from a MySQL database: mysql_free_result() and mysql_close()   9.4.5 Mixing in PHP variables: mysql_escape_string() >>
Table of Contents
 Want to see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.



My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
Top-right shadow
 
Bottom-left shadow Bottom shadow

Comments from other readers
Hassan Kayani - 29 Aug 2008
@i_am_sharjeel
The first thing I would suggest you to check is your php mysql module.
To test if it is correctly installed and enabled, you can use phpinfo() function

Alec - 29 Aug 2008
The mysql table is being populated by the load average numbers:

cur_date cur_time load1 load5 load15
2008-08-05 09:23:01 0.49 0.55 0.39

The date and time are being stamped by mysql. I need to plot the results on a graph. Once php reads one line from the table, what would be the next step. I understand I have to set up the code for the graph. Would that be within the while loop or before hand. I am stuck! I would appreciate your input.

Thank you

Alec - 29 Aug 2008
The mysql table is being populated by the load average numbers:

cur_date cur_time load1 load5 load15
2008-08-05 09:23:01 0.49 0.55 0.39

The date and time are being stamped by mysql. I need to plot the results on a graph. Once php reads one line from the table, what would be the next step. I understand I have to set up the code for the graph. Would that be within the while loop or before hand. I am stuck! I would appreciate your input.

Thank you

A PHP User - 29 Aug 2008
I was searching for the difference between mysql_fetch_assoc and mysql_fetch_array functions.....and i got the answer right here....it s explained very clearly.......

Thank you :)

i_m_sharjeel@yahoo.com - 29 Aug 2008
<?php
mysql_connect("localhost", "root", "allahm");
mysql_select_db("Books");
$result = mysql_query("SELECT * FROM users");

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;
}
}
?>
I am trying to run above scripts.its always give me error..
ERROr IS;
PHP Fatal error: Call to undefined function mysql_connect() in D:\Inetpub\wwwroot\project\test1.php on line 2

Please help me..how can i solve this problem.

A PHP User - 29 Aug 2008
How can I change the script above so that for a query returning 20 results it would output the results in such a form?

<table><tr>
<td>
result 1<br />
result 2<br />
result 3<br />
result 4<br />
result 5<br />
result 6<br />
result 7<br /></td>
<td>
result 8<br />
result 9<br />
result 10<br />
result 11<br />
result 12<br />
result 13<br />
result 14<br /></td>
<td>
result 15<br />
result 16<br />
result 17<br />
result 18<br />
result 19<br />
result 20<br /></td>
</tr><table>

meaning, I want the result divided/split/outputted in 3 columns.

Thanks for any help.

A PHP User - 29 Aug 2008
it returns bool because it will return false when there are no more rows left.

A PHP User - 29 Aug 2008
mohsen:
1. mysql_query("SELECT * FROM yourtable WHERE ID=(3 OR 7)");
2. mysql_query("SELECT * FROM yourtable LIMIT 0,10");
then LIMIT 10,20

prajeesh/prajikk123@sify.com - 29 Aug 2008
How can i get the code of password problem in php.
waiting your positive reply

mohsen - 29 Aug 2008
i can't understand this array , the kind of it e.g i want to fetch row 3 and 7 what can i do?
or i want to fetch row 1 to 10 and then in the next page 10 to 20 (when there is a long list of rows)

THANX

A PHP User - 29 Aug 2008
I love how useful this book is, how I refer to it religiously, and yet the dates are broken...why do I take advice from someone who can't write a proper comment script? Oh well.

Yogesh - 29 Aug 2008
I came into this link for verifying the difference between mysql_fetch_array and mysql_fetch_assoc. I found the difference and the article is useful.

A PHP User - 29 Aug 2008
Whats a good function to use if I have an array, $myarray, and want to store each of its values in my MySQL databasse with the array keys corresponding to column titles in the database?

A PHP Addict - 29 Aug 2008
An easier way to get an array with numerical indexes is mysql_fetch_row().

A PHP User - 29 Aug 2008
mysql_fetch_assoc returns an array, then why is its return type 'bool' ?

Kyle Wilcox - 29 Aug 2008
A little bit of advice for this section:

If you are finding it hard to digest, (which I found it excruciatingly so), try breaking it up line by line in a text editor, making sure it is tabbed nicely, and comment every line, makeing sure you understand exactly what is going on at every step of the way. Then picture a simple table, and run this through yourself, as if you were a computer. You are guaranteed to understand it if you can do that.

Hudzilla - 29 Aug 2008
Jervous: mysql_fetch_array() and mysql_fetch_assoc() are both very similar - internally they both call the same function, just setting different flags.

The difference is that mysql_fetch_assoc() places fields into the array return value with the field name as the array keys. For example, $foo['bar'], $foo['baz'], etc.

Mysql_fetch_array(), on the other hand, lets you provide a parameter specifying how you want the values returned. If you don't provide the second parameter to mysql_fetch_array(), you'll get $foo['bar'] and $foo['baz'], but also the numerical equivalents, eg $foo[0] and $foo[1].

If you only want the field names as the array keys, use mysql_fetch_assoc(). If you want both - or want to choose - then use mysql_fetch_array().

Hope this helps!

Jervous - 29 Aug 2008
I personally do not use this function, I use mysql_fetch_array. What exactly is the difference? Which one is faster? Are they the same? Thanks.



Add comment
Please note that by posting a comment here you are committing it to the public domain. This is important so that others can make use of your code themselves, and also so that I can incorporate helpful notes directly into the main text. Comments are limited to 2000 characters in length.

If you are reporting an error in the content, please tell me directly.

Your name/email address:
Your comment:
 
Now, in order to verify that you're a real person, please answer this simple question: what is seven plus one?
The answer is:
(please write in
numbers, eg 19)


Top-right shadow
 
Bottom-left shadow Bottom shadow

This website and its content is copyright © Paul Hudson 2005, all rights reserved.