Wednesday, May 7, 2008

The Difference Between mysql_fetch_array() And mysql_fetch_assoc()

Getting data from MySQL with PHP is very convenient. But I found most of PHP beginners don't know the difference between two MySQL functions: mysql_fetch_array() and mysql_fetch_assoc(). If you don't understand the two functions, you always come up against such problem: why can't I see the result from MySQL?

Suppose we have a database called 'test' and it has a table called 'members' which stores some members' information. The goal is to fetch all the members' information. Consider such code below:

$conn=mysql_connect('localhost','root','');
$mysql_select_db('test');
$rs=mysql_query('SELECT * FROM members');


Then $rs is an array that stores all personal information. You can use mysql_fetch_array() or mysql_fetch_assoc() to fetch each row by calling the function repeatedly. Consider:

while ($row=mysql_fetch_array($rs,MYSQL_NUM)){
// ...
}

Here $row is a normal array (integer-indexed array). You cannot get data with $row['name'], $row['birthday'], etc. You must use integer as the index, e.g. $row[0], $row[1]... How to get an associative array? You need to set the second parameter of mysql_fetch_array(). MYSQL_ASSOC tells mysql_fetch_array() to return an associative array. See below:

while ($row=mysql_fetch_array($rs,MYSQL_ASSOC)){
// ... here you can use $row['name'], $row['birthday']...
}


mysql_fetch_array($rs,MYSQL_BOTH) equals to mysql_fetch_array($rs). There's no need to use MYSQL_BOTH, so we often omit it.

So you can use two methods to fetch data by specifying the second parameter to MYSQL_BOTH or omitting the second parameter. Then both of the styles, $row[0] and $row['name'], are available.

But where's mysql_fetch_assoc()? It seems not useful. Right. mysql_fetch_assoc($rs) is equivalent to mysql_fetch_array($rs,MYSQL_ASSOC). Obviously, when you need to an associated array (string-indexed array) only, mysql_fetch_assoc() is more convenient. By the way, mysql_fetch_assoc doesn't have the second parameter. It can only return associative array so it needs less memory space to store the result array.

15 comments:

Anonymous said...

very helpful

Anonymous said...

Really very useful information

Anonymous said...

thanks for clearing that up. much appreciated

Daniel said...

Useful indeed. Thanks.

Anonymous said...

that was amazingly well-explained. thank you.

Anonymous said...

thanks

amit said...

Nice tutorial....thanks for this post.....
Please visit this link for learn more script.
http://amitmondal.wordpress.com/

Sri said...

Thankx dude

Anonymous said...

Excellent, thank you sir.

Anonymous said...

Thanks a lot for you explanation it is awesome and well detailed. If you can explain some other functions that confuse newbies like met, it would be great.

Suhailshah said...

Thank you very much...

Anonymous said...

was very helpful... thanks a lot.

Anonymous said...

thanks ....very helpful

Anonymous said...

Thanks a lot for the explanation!

Syed Osama said...

Thumbs up !!! Nice oNe