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.

Count number of rows and fields in a result set using MySQLi functions

In my previous articles we learned how to perform CRUD operation using MySQLi Procedural and Object Oriented style. On performing CRUD operations, the data of MySQL table are affected. This article provides the details of functions which can be used to get the number of rows affected by the last operation performed and also get the number of fields and rows in a result set.

1. mysqli_affected_rows

The mysqli_affected_rows() function is used to get the number of affected rows by the last MySQL operation performed, for example number of rows affected in a table by SELECT, INSERT, UPDATE and DELETE query.

Result : This function returns an integer value as a result which defined the following cases :

• >0 : If function returns an integer value greater than 0, it represents the number of rows retrieved or affected by last query executed.
• -1 : If function returns an integer less than 0, it indicates that the last performed query is invalid or it returns an error.
• = 0 : If function returns an integer equal to 0, it indicates that no rows affected by the last performed operation.

Note :

1. mysqli_affected_rows() function works only for those queries which modify the table data. For SELECT query, it work same like mysqli_num_rows() function.

2. If the number of affected rows exceeds the maximum integer value (PHP_INT_MAX), then the function returns the number of affected rows as a string.

Syntax :

1.1 Procedural Style : mysqli_affected_rows($con)

Example :

<?php
//create connection
$con = @mysqli_connect($host_name, $username, $password, $database);

// check connection errors
if (!$con) {
die('Connect Error: ' . mysqli_connect_errno());
}

//--------SQL statement---------
$query = "INSERT INTO users (id, first_name, last_name, email, city) VALUES (1, 'Rohit', 'Negi', 'negi.rohit25@gmail.com', 'Chennai')";
if (!$con->query($query)) {
echo "Error in inserting records : ". $con->error;
} else {
echo "Number of Rows Added : ".mysqli_affected_rows($con);
}
?>

Output : Number of Rows Added : 1

1.2 Object oriented Style : $con->affected_rows

Example :

<?php
// Create connection
$con = @new mysqli($host_name, $username, $password, $database);

//check for connection errors
if ($con->connect_errno) {
die('Connect Error: ' . $con->connect_error);
}

$query = "INSERT INTO users (first_name, last_name, email, city) VALUES ('Rohit', 'Negi', 'negi.rohit25@gmail.com', 'Chennai')";

//-----execute query------
if (!$con->query($query)) {
    echo "Error : ". $con->error;
} else {
     echo "Number of Rows Added : ".$con->affected_rows; 
}

// close connection
$con->close();
?>

Output : Number of Rows Added : 1

2. mysqli_num_rows()

mysqli_num_rows() function is used to get the numbers of rows in a result set. The function generates different results with buffered or unbuffered result set, With unbuffered result sets, this function returns incorrect result until all the rows are not retrieved.

Result : mysqli_num_rows() returns an integer value which represents the number of rows in a result set.

Syntax :

2.1 Procedural Style : mysqli_num_rows ( $result )

$result : Specifies a result set identifier returned by mysqli_query(), mysqli_store_result() or mysqli_use_result()

Example :

<?php

//create connection
$con = @mysqli_connect($host_name, $username, $password, $database);

// check connection errors
if (!$con) {
die('Connect Error: ' . mysqli_connect_errno());
}

//----sql statement-----
$query = "SELECT * FROM users";

if ($result = mysqli_query($con, $query)) {

    //get number of rows in result set
    echo "Total Rows : ".mysqli_num_rows($result);

    mysqli_free_result($result);
}

// close connection 
mysqli_close($con);

?>

Output : Total Rows : 4

2.2 Object Oriented Style : $result->num_rows

Example :

<?php
// Create connection
$con = @new mysqli($host_name, $username, $password, $database);

//check for connection errors
if ($con->connect_errno) {
die('Connect Error: ' . $con->connect_error);
}

//--------SQL statement---------
$query = "SELECT * FROM users";
if ($result = $con->query($query)) 
{
   echo "Total Rows : ".$result->num_rows; 
   $result->close();
}

// close connection
$con->close();

?>

Output : Total Rows : 4

3. mysqli_field_count()

mysqli_field_count() function returns the number of fields (columns) in the result set obtained by last executed query on connection defined by link identifier. It can also use with mysqli_store_result() function to check whether the result set produced by the query is empty or not.

Result : mysqli_field_count() returns an integer that represents the number of fields (columns) in the result set.

Syntax :

3.1 Procedural Style : mysqli_field_count($con)

$con : Specifies MySqli connection identifier

Example :

<?php
//create connection
$con = @mysqli_connect($host_name, $username, $password, $database);

// check connection errors 
if (!$con) {
  die('Connect Error: ' . mysqli_connect_errno());
}

//sql statement
$query = "SELECT * FROM users";
if (mysqli_query($con, $query)) {
  echo "Number of columns : ".mysqli_field_count($con);
} else {
  echo "Error: ". mysqli_error($con);
}
mysqli_close($con);
?>

Output : Number of columns : 5

3.2 Object Oriented Style : $con->field_count

Example :

<?php
// Create connection
$con = @new mysqli($host_name, $username, $password, $database);
//check for connection errors
if ($con->connect_errno) {
die('Connect Error: ' . $con->connect_error);
}

//--------SQL statement---------
$query = "SELECT * FROM users";
if (!$con->query($query)) {
echo "Error in inserting records : ". $con->error;
} else {
echo "Number of columns : ".$con->field_count;
}

//close connection 
$mysqli->close();
?>

Output : Number of columns : 5

4. mysqli_num_fields()

Just like mysqli_field_count() function, mysqli_num_fields() also used to get the number of fields (columns) in a result set.

Result : mysqli_num_fields() returns an integer that represents the number of fields (columns) in a result set.

Syntax :

4.1 Procedural Style : mysqli_num_fields ( $result )

$result : Specifies a mysqli result set identifier

Example :

<?php
//create connection
$con = @mysqli_connect($host_name, $username, $password, $database);

// check connection errors 
if (!$con) {
  die('Connect Error: ' . mysqli_connect_errno());
}

//--------SQL statement---------
$query = "SELECT id, email, city FROM users";

if ($result = mysqli_query($con, $query)) {

    //count of fields in result set
    echo "Number of fields : ".mysqli_num_fields($result);
    mysqli_free_result($result);
}

// close connection
mysqli_close($con);
?>

Output :  Number of fields : 3

4.2 Object Oriented Style : $result->field_count

Example :

<?php
// Create connection
$con = @new mysqli($host_name, $username, $password, $database);

//check for connection errors
if ($con->connect_errno) {
die('Connect Error: ' . $con->connect_error);
}

//--------SQL statement---------
$query = "SELECT id, email, city FROM users";
if ($result = $con->query($query)) 
{
   echo "Number of fields : ".$result->field_count; 
   $result->close();
}

// close connection
$con->close();

?>

Output : Number of fields : 3