Ways to Fetch MySQL Query Result with PHP
Note: This post only covers mysql_fetch functions and not PDO codes which are more updated.
Hi guys, today we're gonna take a look at the ways to fetch MySQL query results with PHP. I found 6 ways of doing it. I'm going to enumerate each one with its corresponding name, description, sample code, and output.
I also provided the links to php.net if you want to learn more about these functions. You can also use this sample table and data if you want.
 |
| PHP/MySQL: Ways to Fetch MySQL Query Result |
We'll use the following example SQL query statement:
$sql="select id, firstname, lastname, username from users where id=28";
1. mysql_fetch_array() - Fetch a result row as an associative array, a numeric array, or both. It returns an array that corresponds to the fetched row and moves the internal data pointer ahead.
Code:
$rs=mysql_query($sql) or die($sql.">>".mysql_error());
$row=mysql_fetch_array($rs);
echo "<pre>";
print_r($row);
echo "</pre>";
Output:
Array
(
[0] => 28
[id] => 28
[1] => John Michael
[firstname] => John Michael
[2] => Dalisay
[lastname] => Dalisay
[3] => john
[username] => john
)
Use:
$id=$row['id'];
$firstname=$row['firstname'];
$lastname=$row['lastname'];
$username=$row['username'];
2. mysql_fetch_assoc() - Fetch a result row as an associative array
Code:
$rs=mysql_query($sql) or die($sql.">>".mysql_error());
$row=mysql_fetch_assoc($rs);
echo "<pre>";
print_r($row);
echo "</pre>";
Output:
Array
(
[id] => 28
[firstname] => John Michael
[lastname] => Dalisay
[username] => john
)
3. mysql_fetch_field() - Returns an object containing field information. This function can be used to obtain information about fields in the provided query result.
Code:
$rs=mysql_query($sql) or die($sql.">>".mysql_error());
$row=mysql_fetch_field($rs);
echo "<pre>";
print_r($row);
echo "</pre>";
Output:
stdClass Object
(
[name] => id
[table] => users
[def] =>
[max_length] => 2
[not_null] => 1
[primary_key] => 1
[multiple_key] => 0
[unique_key] => 0
[numeric] => 1
[blob] => 0
[type] => int
[unsigned] => 0
[zerofill] => 0
)
4. mysql_fetch_lenghts() - Returns an array that corresponds to the lengths of each field in the last row fetched by MySQL.
Code:
$rs=mysql_query($sql) or die($sql.">>".mysql_error());
$row = mysql_fetch_assoc($rs);
$lenghts=mysql_fetch_lengths($rs);
echo "<pre>";
print_r($lenghts);
echo "</pre>";
Output:
Array
(
[0] => 2
[1] => 12
[2] => 7
[3] => 4
)
5. mysql_fetch_object() - Returns an object with string properties that correspond to the fetched row, or FALSE if there are no more rows.
Code:
$rs=mysql_query($sql) or die($sql.">>".mysql_error());
$row=mysql_fetch_object($rs);
echo "<pre>";
print_r($row);
echo "</pre>";
Output:
stdClass Object
(
[id] => 28
[firstname] => John Michael
[lastname] => Dalisay
[username] => john
)
6. mysql_fetch_row() - Get a result row as an enumerated array
Code:
$rs=mysql_query($sql) or die($sql.">>".mysql_error());
$row=mysql_fetch_row($rs);
echo "<pre>";
print_r($row);
echo "</pre>";
Output:
Array
(
[0] => 28
[1] => John Michael
[2] => Dalisay
[3] => john
)
I usually use mysql_fetch_row and mysql_fetch_object. :)
