MySQLi functions

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