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);
}
?>

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();
?>

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);

?>

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();

?>

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);
?>

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();
?>

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);
?>

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();

?>