Tuesday, April 23, 2013


$rows = mysql_fetch_array( "select name, address from people");

Means you then get each of the row results as a straight array, meaning you dont necessarily need to know in advance the order elements arrive via the select.

foreach( $rows as $row )
echo $row[0] . ' lives at ' . $row[1] ;

$rows = mysql_fetch_assoc( "select name, address from people" );

Means you then get each of the row results as a named array elements, an associative array.

foreach( $rows as $row )
echo $row['name'] . ' lives at ' . $row['address'];

Which is now dependent on you knowing what the result columns are named.

$rows = mysql_fetch_object( "select name, address from people" );

Means you then get each of the row results using the object notation.

foreach( $rows as $row )
echo $row->name . ' lives at ' . $row->address ;

Which again relies upon your knowing what is coming out of the database, and in my opinion is better because a) its easier to read, and b) if you don't use OOP, it at least gets you thinking about and using OO notation.
-----------------------------------------------------------------------------------------------------

There are 3
mysql_fetch_row();
mysql_fetch_assoc();
mysql_fetch_array();
mysql_fetch_row() returns an array with integer indexing. i.e if you get
a result $rec=mysql_fetch_row(SOME RESULT) then you can only get the
result by specifying an integer index like $rec[0], $rec[1] e.t.c
mysql_fetch_assoc() returns an associative array that can only be
accessed by field name e.g. $rec[firstname], $rec[lastname] e.t.c
mysql_fetch_array() returns an associative array that can be accessed by
both of the above methods.
So mysql_fetch_row() will be best for speed but in more recent versions
of php associative arrays are more or less as fast.
Personally I use mysql_fetch_array(); as I like to use both associations
and integers.
-----------------------------------------------------------------------------------------------------

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.

0 comments:

Post a Comment