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.

Ways to Fetch MySQL Query Result with PHP
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. :)

By Mike Dalisay

I'm Mike Dalisay, a pro web developer since 2010. I love web development. Improving our tutorials and source codes makes me happy. Do you want to suggest an edit to our tutorial? Got something to ask about our source codes? You may use our comments section below or email our team at [email protected]

6 comments

  1. Thanks for this I had a query.
    PHP Help needed. I have two tables city and state in my mysql database. I’ve used this query… Select c.cityid,c.name,s.name from City c inner join state s on s.stateid=c.stateid. While fetching the array there’s a problem the city name is not being displayed as the array can’t have two indexes with same name. What do i do to avoid this and set different name for array indexes of city and state.
    This page solved it. THanks a ton

  2. Oh my god, did you wrote this in 1990? myslq_* functions are almost deprecated, you should learn how to use PDO instead, for the god sake of your projects!

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to top