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. 🙂