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.
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. :)
Hi! I'm Mike Dalisay, the co-founder of codeofaninja.com, a site that helps you build web applications with PHP and JavaScript. Need support? Comment below or contact [email protected]
I'm also passionate about technology and enjoy sharing my experience and learnings online. Connect with me on LinkedIn, Twitter, Facebook, and Instagram.