Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Count number of rows and fields in a result set using MySQLi functions

    • 1
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1.26k
    Comment on it

    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

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: