Hello friends,

My previous article MySQLi functions to fetch records from resultset demonstrates about mysqli_fetch_row() and mysqli_fetch_array() functions which are used to retrieve data from result sets. 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 :

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 :

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.