Ways to Fetch MySQL Query Result with PHP

Note: This post only covers mysql_fetch functions and not PDO codes which is more updated.

Hi guys, today we're gonna take a look at the ways to fetch MySQL query result 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. :)

Tweet

For FREE programming tutorials, click the red button below and subscribe! :)

SUBSCRIBE to Receive Ninja Codes