MySQL Fetch using PHP

PHP provides huge list of MySQL functions to access database from front end. Among those functions, we are going to discuss about some of them that are used to fetch data from database. Such functions are differed with the type of results they are expected to return.

In PHP, MySQL fetch results can be obtained by the following functions. All of these functions will fetch only one row per function call. If required, we should call such functions with a loop for getting subsequent MySQL fetch results row by row.

  • mysql_fetch_row()
  • mysql_fetch_assoc()
  • mysql_fetch_array()
  • mysql_fetch_object()
  • mysql_fetch_lengths()
  • mysql_fetch_field()

mysql_fetch_row()

This function will fetch data about single row with which the row pointer is currently exist. After fetching the entire row details, it will be returned as an array with number indices corresponding to the MySQL field offset.

For getting such resultant array, mysql_fetch_row() requires a resource data that is returned by executing query appropriate with MySQL fetch operations. If no results found for the query, then mysql_fetch_row() will return nothing as expected.

Let us consider the Users table, which we had taken for example while seeing about MySQL Left JOIN and MySQL Right JOIN. And the table data and structure is as follows.

mysql_user_table

The following PHP program is for fetching MySQL data using mysql_fetch_row(). Before that, the database connections are made with first two lines as usual.

$conn = mysql_connect("localhost","root","");
mysql_select_db("phppot_examples",$conn);
$query = "SELECT * from Users"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_row($result);
print "<pre>";
print_r($row);
print "<pre>";

This program will return values of the entire first row, since the row pointer is at the beginning. So the output will be as shown below that can be cross checked with the table data shown already.

Array
(
[0] => 1
[1] => admin
[2] => admin123
[3] => student
)

If we want to get all the row information, we should do the same process recursively by iterating through a loop until end of record. For example,

while($row = mysql_fetch_row($result)) {
print "<pre>";
print_r($row);
print "<pre>";
}

mysql_fetch_assoc()

This functions is similar to the mysql_fetch_row(), except that, it will return array of row information containing column values are indexed with column name. So the result type is of an associative array where each column name and values of a single row is associated together as name,value pairs.

Let us replace mysql_fetch_row() with mysql_fetch_assoc() in the above program which will return the following array.

Array
(
[user_id] => 1
[user_name] => admin
[password] => admin123
[user_type] => student
)

By using this type of MySQL fetch, we can access the data by its name instead of its offset. Since, remembering the order of fields is too tough for huge list of columns, accessing by field name is easy and there by this function could be preferred in such scenario.

mysql_fetch_array()

This MySQL fetch method returns resultant array with both indices, that is, field offset and field name. So, it would be used most probably by having both option of indexing.

mysql_fetch

Unlike above two functions, mysql_fetch_array() accepts an optional argument for specifying resultant array index type and its possible values are,

  • MYSQL_BOTH – It is the default value that would be taken if no second argument is provided for this function. It will provide resultant array with both indices.
  • MYSQL_NUM – With this option, mysql_fetch_array() will return array with offset indices as same as mysql_fetch_row().
  • MYSQL_ASSOC – With this option, mysql_fetch_array() will return array with name indices as same as mysql_fetch_assoc().

By replacing mysql_fetch_row() with mysql_fetch_array(), the output array will be,

Array
(
    [0] => 1
    [user_id] => 1
    [1] => admin
    [user_name] => admin
    [2] => admin123
    [password] => admin123
    [3] => student
    [user_type] => student
)

mysql_fetch_object()

mysql_fetch_object() function will return MySQL data with same structure as returned by mysql_fetch_assoc(), but its type is different. mysql_fetch_object() returns object where as mysql_fetch_assoc() returns array. So, the way of accessing these data will also be differed. For example, if we are required to access user_name, after array fetch, it will be done by,

echo $row["user_name"];

Or else after object fetch, it would be,

echo $row->user_name;

mysql_fetch_lengths()

This PHP function is used to return the string length each column value of the recently fetched row. So, before calculating the string length, any one of the above MySQL fetch functions need to be invoked.

For example, the following program is to fetch single row data using mysql_fetch_object() and to print the corresponding length array to the browser.

$conn = mysql_connect("localhost","root","");
mysql_select_db("phppot_examples",$conn);
$query = "SELECT * from Users"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_object($result);
$student_length = mysql_fetch_lengths($result);
print "<pre>";
print_r($row);
print_r($student_length);
print "<pre>";

And the output is,

stdClass Object
(
[user_id] => 1
[user_name] => admin
[password] => admin123
[user_type] => student
)
Array
(
[0] => 1
[1] => 5
[2] => 8
[3] => 7
)

Obviously, the object properties array and the length array are returned as shown above that could be cross checked manually to ensure the property value length is correct as expected.

mysql_fetch_field()

Unlike above functions, mysql_fetch_field() is for getting MySQL Database table’s field information instead of record data. And, this function also fetch one field per call and need loop implementation for getting more fields. These information array will be returned as an object which includes properties like, table name, field name, field maximum length, primary key flag offset and etc. For example, the user_id field details of Users table is returned as follows.

stdClass Object
(
 [name] => user_id
 [table] => Users
 [def] => 
 [max_length] => 1
 [not_null] => 1
 [primary_key] => 1
 [multiple_key] => 0
 [unique_key] => 0
 [numeric] => 1
 [blob] => 0
 [type] => int
 [unsigned] => 0
 [zerofill] => 0
)

Note:mysql extenstion we have seen here are deprecated as of PHP version 5.5. So the alternative extension mysqli is used instead for later version of PHP. So for later version, the above functions should be changed by replacing its prefix mysql as mysqli

This MySQL code tutorial was published on June 14, 2013.

↑ Back to Top