MySQLi functions to fetch records from resultset.

In previous articles we already learned how to connect MySQL database and how to perform different queries using MySQLi. This article demonstrates some MySQLi functions which are used to retrieve row by row data from resultset. The complete article is divided into two parts , this part provides the explanation and implementation details of mysqli_fetch_row() and mysqli_fetch_array() functions in both Procedural and Object oriented style. 

1. mysqli_fetch_row()

mysqli_fetch_row() fetches a row from the resultset and returns row data as an enumerated array.

Syntax :

a. Procedural Style : mysqli_fetch_row( result )

    result : Specifies mysqli result set.(Required)

b. Object Oriented Style : mysqli_result::fetch_row()

Description : mysqli_fetch_row() fetches a row from the resultset and returns the row data as an array with numeric indexes starting from 0. Next subsequent call fetch the next row from result set. The function return NULL if there are no more rows in the resultset.

Result : This function returns an array of strings which corresponds to row data fetched from resultset and it return NULL if there are no more rows in the resultset.

Example : Procedural style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 2";

if ($result = mysqli_query($con, $query))
{
    echo "<pre>";
    while($res = mysqli_fetch_row($result)){
    print_r($res);
    }    
    echo "</pre>";
    mysqli_free_result($result);
}
?>

Example : Object Oriented style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 2";
if ($result = $con->query($query)) 
{
   echo "<pre>";
   //----fetch rows from resultset----
   while ($res = $result->fetch_row()) {
        print_r($res);
    }
   echo "</pre>";
   $result->free();
}
?>

Output :

Array
(
    [0] => 1
    [1] => Amit
    [2] => Kumar
    [3] => amit.kumar21@gmail.com
    [4] => Dehradun
)
Array
(
    [0] => 2
    [1] => Ravi
    [2] => Singh
    [3] => ravi22singh@yahoo.com
    [4] => Mumbai
)

2. mysqli_fetch_array()

mysqli_fetch_array() function also used to fetch row from the resultset. It fetches a row from the resultset and return row data as an array of numeric, associative or combination of both types.

Syntax :

a. Procedural Style : mysqli_fetch_array( result, resulttype );

result : Specifies mysqli result set.(Required)

resulttype :  Constant value which defines the type of the returned array.(Optional)

resulttype can have one of the following values :

MYSQLI_NUM : Return a numeric array.

MYSQLI_ASSOC : Return an associative array.

MYSQLI_BOTH : Return an array with both numeric and associative keys.

b. Object Oriented Style : mysqli_result::fetch_array ( resulttype )

resulttype :  Constant value which defines the type of the returned array.

Description : mysqli_fetch_array() fetches a row from the specified resultset result and returns the row data as an array of type specified by passing second optional parameter resulttype. The function returns an array with both numeric and associative keys as the default, if resulttype parameter is not passed. The column names of resultset used as keys for associative array.

The name of fields returned by function are case sensitive. If in a resultset two or more columns have the same name, then the value of the last column overwrites the value of an earlier column with the same name. To get values of multiple columns with the same name it is better to use MYSQLI_NUM type.

Result : This function returns an array of row data fetched from the resultset. It returns NULL, if there are no more rows in resultset.

Example : Procedural Style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 3";

if ($result = mysqli_query($con, $query))
{
    echo "<pre>";
    echo "Using MYSQLI_NUM : <br>";
    $res = mysqli_fetch_array($result,MYSQLI_NUM);
    print_r($res);
    echo "Using MYSQLI_ASSOC : <br>";    
    $res1 = mysqli_fetch_array($result,MYSQLI_ASSOC);
    print_r($res1);
    echo "Using MYSQLI_BOTH : <br>";    
    $res = mysqli_fetch_array($result,MYSQLI_BOTH);
    print_r($res);        
    echo "</pre>";
    mysqli_free_result($result);
}
?>

Example : Object Oriented Style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 3";
if ($result = $con->query($query)) 
{
   echo "<pre>";
   echo "Using MYSQLI_NUM : <br>";
   $res = $result->fetch_array(MYSQLI_NUM);
   print_r($res);
   echo "Using MYSQLI_ASSOC : <br>";    
   $res1 = $result->fetch_array(MYSQLI_ASSOC);
   print_r($res1);
   echo "Using MYSQLI_BOTH : <br>";    
   $res = $result->fetch_array(MYSQLI_BOTH);
   print_r($res);        
   echo "</pre>";
   $result->free();
}
?>

Output :

Using MYSQLI_NUM :
Array
(
    [0] => 1
    [1] => Amit
    [2] => Kumar
    [3] => amit.kumar21@gmail.com
    [4] => Dehradun
)
Using MYSQLI_ASSOC :
Array
(
    [id] => 2
    [first_name] => Ravi
    [last_name] => Singh
    [email] => ravi22singh@yahoo.com
    [city] => Mumbai
)
Using MYSQLI_BOTH :
Array
(
    [0] => 3
    [id] => 3
    [1] => Deepak
    [first_name] => Deepak
    [2] => Rawat
    [last_name] => Rawat
    [3] => deepak.rawat@gmail.com
    [email] => deepak.rawat@gmail.com
    [4] => New Delhi
    [city] => New Delhi
)

This article demonstrates two more functions mysqli_fetch_assoc() and mysqli_fetch_object() which are also used to fetch rows from the result set. The details of each function are given below :

1. mysqli_fetch_assoc()

mysqli_fetch_assoc() function retrieves a row from a result set and returns the row data as an associative array.

Syntax :

a. Procedural Style : mysqli_fetch_assoc(result)

result : Specifies mysqli result set.(Required)

b. Object Oriented Style : mysqli_result::fetch_assoc()

Description : mysqli_fetch_assoc() is used to retrieve the data of result row as an associative array where each key represents a column name of the result set. The function returns NULL if there are no more rows in resultset.

Result : This function returns the fetched row as an associative array where each key of array represents a column name in the result set. If there are no more rows to retrieve in the result set, then function returns NULL.

Note : If two or more columns have the same name, the value of the last column overwrites the values of earlier columns.

Example : Procedural Style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 2";

if ($result = mysqli_query($con, $query))
{
    echo "<pre>";
    while($res = mysqli_fetch_assoc($result)){
  print_r($res);
    } 
    echo "</pre>";
    mysqli_free_result($result);
}
?>

Example : Object Oriented Style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 2";
if ($result = $con->query($query)) 
{
   echo "<pre>";
   while($res = $result->fetch_assoc()){
      print_r($res);    
   }
   echo "</pre>";
   $result->free();
}
?>

Output :

Output :
Array
(
    [id] => 1
    [first_name] => Amit
    [last_name] => Kumar
    [email] => amit.kumar21@gmail.com
    [city] => Dehradun
)
Array
(
    [id] => 2
    [first_name] => Ravi
    [last_name] => Singh
    [email] => ravi22singh@yahoo.com
    [city] => Mumbai
)

2. mysqli_fetch_object()

mysqli_fetch_object() function fetches the current row of a result set as an object.

Syntax :

a. Procedural Style : mysqli_fetch_object(result, classname, params)

result : Specifies mysqli result set.(Required)

classname : Specifies the name of the class to instantiate. Default an object of stdClass is returned.(Optional)

params : Specifies an array of parameters to pass to the constructor of classname object.(Optional)

b. Object Oriented Style :  mysqli_result::fetch_object (class_name, params)

classname : Specifies the name of the class to instantiate.(Optional)

params : Specifies an array of parameters to pass to the constructor of classname object.(Optional)

Description : mysqli_fetch_object() function retrieves the current row from a result set and return it as an object. The columns of the result row are represent by object properties.

Result : This function returns an object which represents the current row of the result set. It returns NULL if there are no more rows in the result set.

Example : Procedural Style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 2";

if ($result = mysqli_query($con, $query))
{
    echo "<pre>";
    while($object = mysqli_fetch_object($result)){
  echo $object->id.". ".$object->first_name." ".$object->last_name." from ".$object->city;
  echo "<br>";
    } 
    echo "</pre>";
    mysqli_free_result($result);
}
?>

Example : Object Oriented Style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 2";
if ($result = $con->query($query)) 
{
   echo "<pre>";
   while($object = $result->fetch_object()){
      echo $object->id.". ".$object->first_name." ".$object->last_name." from ".$object->city;
      echo "<br>";    
   }
   echo "</pre>";
   $result->free();
}
?>

Output :

1. Amit Kumar from Dehradun
2. Ravi Singh from Mumbai

There is one other function mysqli_fetch_all() which fetch all rows from the result set, but it needs the mysqlnd driver installed before using it as mentioned in the given link : http://www.php.net/manual/en/mysqli-result.fetch-all.php#88031

mysqli_fetch_all()

mysqli_fetch_all() fetch all rows from the result set and returns rows as an array of associative, numeric or both types.

Syntax :

a. Procedural style : mysqli_fetch_all(result, resulttype)

result : Specifies mysqli result set.(Required)

resulttype :  An optional constant value which defines the type of the returned array.(Optional)

One of the following constant values can be used for the resulttype : 

MYSQLI_NUM : Returns a numeric array.

MYSQLI_ASSOC : Returns an associative array.

MYSQLI_BOTH : Returns an array with both numeric and associative keys.

b. Object Oriented Style : mysqli_result::fetch_all (resulttype)

resulttype : An optional constant value which defines the type of the returned array.(Optional)

Description : mysqli_fetch_all() looks similar to mysqli_fetch_array() function as it also returns fetched rows as an array of  numeric, associative and combination of both types. The main difference between these two functions is mysqli_fetch_all() fetches all rows at once while mysqli_fetch_array() fetches one row at a time.

Result : This function fetches all rows from the result set and returns a multidimensional array containing numeric or associative arrays of rows data fetched from the result set.