PHP MySQL Functions

    PHP supports many functions that will provide you with access to the MySQLi database server. PHP version 5.0.0 comes with the MySQLi extension and version 5.3.0 has the native driver for the MySQLi.

    PHP functions for MySQLi

    mysqli affected rows

    This function will return the number of affected rows from the previous SELECT, INSERT, UPDATE, REPLACE, or DELETE query. Syntax

    int mysqli_affected_rows ([ resource $link_identifier = NULL ] )

    Example

    <?php
       $con = mysqli_connect("host", "root", "pwd", "mydb");
       mysqli_query($conn, "SELECT * FROM students");
       $db_rows = mysqli_affected_rows($conn);
       print("affected rows: ".$db_rows);
       mysqli_close($conn);
    ?>

    Output

    affected rows: 7

    mysqli begin transaction

    This function will turn off or turn on the notifications of the auto-committing database. This function will take three parameters- connection (mandatory), flags (MYSQLI_TRANS_START_READ_ONLY MYSQLI_TRANS_START_READ_WRITE MYSQLI_TRANS_START_WITH_CONSISTENT_SNAPSHOT) and name specifies the savepoint for the particular transaction. This function will return true if the execution is successful. Syntax

    mysqli_begin_transaction($con, [$flags, $name]);

    Example

    <?php
       $con = mysqli_connect("localhost", "root", "password", "mydb");
       mysqli_begin_transaction($con, MYSQLI_TRANS_START_READ_ONLY);
       print("start transaction");
       mysqli_query($con, "CREATE TABLE Students(Name VARCHAR(255), AGE INT)");
       print("Table Created\n");
       mysqli_query($con, "INSERT INTO Students  values('Raju', 25),('Rahman', 30),('Sarmista', 27)");
       print("Records Inserted\n");
       mysqli_commit($con);
       print("Transaction Saved\n");
       mysqli_close($con);
    ?>

    Output

    Start transaction
    Table created
    Records inserted
    Transaction saved

    mysqli close

    This function will allow you to close the MySQLi connection. This function will take one mandatory parameter that is connection. Syntax

    mysqli_close($con);

    Example

    <?php
       $db_host = "localhost";
       $u_name  = "root";
       $pwd = "password";
       $db = "mydb";
       //Creating a connection
       $conn = mysqli_connect($db_host, $u_name, $pwd, $db);
       //Close the connection
       $resp = mysqli_close($conn);
       if($resp){
          print("Close connection");
       }else{
          print("Connection error");
       }
    ?>

    Output

    Close connection

    mysqli connect

    This function will allow you to open a connection to a MySQLi Server. This function will take six optional parameters and return the connection object. If the connection fails it will return false. Syntax

    mysqli_connect(host,username,password,dbname,port,socket);

    Example

    <?php
       $db_host = "localhost";
       $u_name  = "root";
       $pwd = "password";
       $db = "mydb";
       //Creating a connection
       $conn = mysqli_connect($db_host, $u_name, $pwd, $db);
      if($conn){
          print("Connection Successful");
       }else{
          print("Connection Invalid");
       }
    ?>

    Output

    Connection Established Successfully

    Mysqli_autocommit

    If you turn on the autocommit feature then all the changes made to the database will be automatically saved else you have to explicitly commit the changes. Syntax

    mysqli_autocommit($con, $mode);

    Example-

    <?php
       $con = mysqli_connect("host", "root", "pwd", "mydb");
      mysqli_autocommit($conn, False);
       mysqli_query($conn, "insert into students values(‘Jacob', 25)");
       $res = mysqli_query($conn, "SELECT * FROM students");
       print_r($res);
       mysqli_close($conn);
    ?>

    Output-

    mysqli_result Object
    (
        [current_field] => 0
        [field_count] => 2
        [lengths] =>
        [num_rows] => 1
        [type] => 0
    )

    The transaction will get auto-commit, now you can query the database to check the results in the students table.

    mysqli_commit()

    This function will allow you to commit the transaction explicitly. This function will take three parameters- connection is mandatory, flags (optional) whose value can be MYSQLI_TRANS_COR_AND_CHAIN MYSQLI_TRANS_COR_AND_NO_CHAIN MYSQLI_TRANS_COR_RELEASE MYSQLI_TRANS_COR_NO_RELEASE And name (optional) specifies the name for the commit point. Syntax

    mysqli_commit($con, [$flags, $name]);

    Example

    <?php
       $con = mysqli_connect("host", "root", "pwd", "mydb");
      mysqli_autocommit($conn, False);
       mysqli_query($conn, "insert into students values(‘'Sam', 27)");
       $res = mysqli_query($conn, "SELECT * FROM students");
       print_r($res);
    mysqli_commit($conn);
       mysqli_close($conn);
    ?>

    Output

    mysqli_result Object
    (
        [current_field] => 0
        [field_count] => 2
        [lengths] =>
        [num_rows] => 1
        [type] => 0
    )

    mysqli_connect_errno()

    This function will specify the error number for the connection. This function does not take any parameters and return an integer value that represents the code error. Syntax

    mysqli_connect_errno()

    Example

    <?php
       $conn = mysqli_connect("localhost", "root", "wrong_password", "mydb");
       $data = mysqli_connect_errno();
       print("Error Code: ".$data);
       ?>

    Output

    Error Code: 1045

    mysqli_connect_error()

    This function will provide you with the description of the error if occurred during the connection.this function will not take any parameter and will return the error description. Syntax

    mysqli_connect_error()

    Example

    <?php
       $conn = mysqli_connect("localhost", "root", "wrong_password", "mydb");
       $e_des = mysqli_connect_error($conn);
       print("Error: ".$e_des);
       ?>

    Output

    Error: Access denied for user 'root'@'localhost'

    Example for successful connection

    <?php
       $conn = mysqli_connect("localhost", "root", "wrong_password", "mydb");
     $e_des = mysqli_connect_error();
       if(!$conn){
          print("Connection Failed: ".$e_des);
       }else{
          print("Connection Successful");
       }
       ?>

    Output

    Connection successful

    mysqli_debug()

    This function will take one parameter that specifies the debugging operation to be performed using the Fred fish debugging library. This function will return a boolean value. Syntax

    mysqli_debug($message);

    Example

    <?php
       $data = mysqli_debug("T:n:t:m:x:F:L:o,/sample.txt");
       print($data);
    ?>
       ?>

    Output

    1 ?>

    mysqli_dump_debug_info()

    This function will accept the MySQL server connection object and then dump the logging or debugging information in the logs. Syntax

    mysqli_dump_debug_info($con);

    Example

    <?php
       $conn = mysqli_connect("localhost", "root", "password", "mydb");
       $data = mysqli_dump_debug_info($conn);
       if($data){
          print("Debugging successful");
       }else{
          print("Failed");
       }
       ?>

    Output

    Debugging successful

    mysqli_error()

    This function will return the description of the error that occurred during the last transaction. Syntax

    mysqli_error($con)

    Example

    <?php
       $conn = mysqli_connect("localhost", "root", "password", "mydb");
      mysqli_query($conn, "SELECT * FORM students");
       $error = mysqli_error($conn);
       print("Error Occurred: ".$error);
       mysqli_close($conn);
       ?>

    Output

    PHP Warning:  mysqli_connect(): (HY000/2002): Connection refused in /workspace/Main.php

    mysqli_connect_error()

    This function will return the error description during the last connection call. This function will not take any parameter. Syntax

    mysqli_connect_error()

    Example

    <?php
       $conn = mysqli_connect("localhost", "root", "password", "mydb");
    $error = mysqli_connect_error($con);
       print("Error: ".$error);
       ?>

    Output

    Error: Access denied for user 'root'@'localhost'

    mysqli_error_list()

    This function will provide you with the error list that occurred during the last function call. This function will take one mandatory parameter that is connection that specifies the connection for which we want the error lust details. Syntax

    mysqli_error_list($con)

    Example

    <?php
       $conn = mysqli_connect("localhost", "root", "password", "mydb");
       mysqli_query($conn, "CREATE TABLE Students(Name VARCHAR(255), AGE INT)");
       print("Table created\n");
       $query= mysqli_query($conn, "INSERT INTO Students  values('Raju', 25),('Rahman ali nath', 30)”);
      $list = mysqli_error_list($conn);
       print_r($list);
       mysqli_close($conn);
    ?>

    Output

    Array
    (
        [0] => Array
            (
                [errno] => 1406
                [sqlstate] => 22001
                [error] => Data too long for column 'Name' at row 2
            )
    )

    mysqli_field_count()

    This function will provide you with the number of columns for the last executed sql in the result set. This function will take one mandatory parameter that is connection for which we run the query Syntax

    mysqli_field_count($con)

    Example

    <?php
       $conn = mysqli_connect("localhost", "root", "password", "mydb");
    mysqli_query($conn, "Select * from students");
       $db_count = mysqli_field_count($conn);
       print("Field Count- ".$db_count);
       mysqli_close($conn);
       ?>

    Output

    Field count- 5

    mysqli_get_charset()

    This function will provide you with the character set class’s object that will contain below information- charset, collation, dir, min_length, max_length, number and state. This function will be one parameter that is the connection object. Syntax

    mysqli_get_charset($con)

    Example

    <?php
       //$conn = mysqli_connect("localhost", "root", "password", "mydb");
    $test_db = mysqli_init();
      //Creating the connection
      mysqli_real_connect($test_db, "localhost","root","password","test");
      //Character set
      $test_db_res = mysqli_get_charset($test_db);
      print_r($test_db_res);
       ?>

    Output

    stdClass Object
    (
        [charset] => utf8
        [collation] => utf8_general_ci
        [dir] =>
        [min_length] => 1
        [max_length] => 3
        [number] => 33
        [state] => 1
        [comment] => UTF-8 Unicode
    )

    mysqli_get_client_info()

    This function will provide you with the underlying client information. This function will take one parameter that is the connection object. Syntax

    mysqli_get_client_info([$con]);

    Example

    <?php
       //$conn = mysqli_connect("localhost", "root", "password", "mydb");
       $info = mysqli_get_client_info();
       print("Client Library Version: ".$info);
       ?>

    Output

    Client Library Version: mysqlnd 7.4.1

    mysqli_get_client_status()

    This function will provide you with an array of the client’s statistics. This function will take one parameter that is the connection object. Syntax

    mysqli_get_client_stats($con);

    Example

    <?php
       $conn = mysqli_connect("localhost", "root", "password", "mydb");
      $statistics = mysqli_get_client_stats();
       print_r($statistics);
       mysqli_close($conn);
       ?>

    Output

    Array
    (
        [bytes_sent] => 138
        [bytes_received] => 93
        [packets_sent] => 2
        [packets_received] => 2
        [protocol_overhead_in] => 8
        [protocol_overhead_out] => 8
        [bytes_received_ok_packet] => 0
        [bytes_received_eof_packet] => 0
        [bytes_received_rset_header_packet] => 0
        [bytes_received_rset_field_meta_packet] => 0
        [bytes_received_rset_row_packet] => 0
    ….
    ….
    ….
    …

    mysqli_get_client_version()

    This function will provide you with the version of the client. This function will take one parameter that is the connection object for which we want the version number. Syntax

    mysqli_get_client_version($con);

    Example

    <?php
      // $conn = mysqli_connect("localhost", "root", "password", "mydb");
      $version = mysqli_get_client_version();
       print("Client Library Version Number: ".$version);
       ?>

    Output

    Client Library Version Number: 70401

    mysqli_get_connection_stats()

    This function will provide you with the information of the specified connection object in the array form. Syntax

    mysqli_get_connection_stats($con);

    Example

    <?php
      $conn = mysqli_connect("localhost", "root", "password", "mydb");
     $statistics = mysqli_get_connection_stats($conn);
       print_r($statistics);
       //Closing the connection
       mysqli_close($conn);
       ?>

    Output

    Array
    (
        [bytes_sent] => 138
        [bytes_received] => 93
        [packets_sent] => 2
        [packets_received] => 2
        [protocol_overhead_in] => 8
        [protocol_overhead_out] => 8
        [bytes_received_ok_packet] => 0
        [bytes_received_eof_packet] => 0
    ...
    ...
    ...
    ..

    mysqli_get_host_info()

    This function will provide you with the information of the underlying host for the given connection. This function will take one parameter that is the connection object and is optional. Syntax

    mysqli_get_host_info($con);

    Example

    <?php
    
      $conn = mysqli_connect("localhost", "root", "password", "mydb");
     $info = mysqli_get_host_info($conn);
       print("Host Info: ".$info);
       mysqli_close($conn);
       ?>
    
    

    Output

    Client Library Version: localhost via TCP/IP

    mysqli_get_proto_info()

    This function will provide you with the details of the used MySQL protocol version. This function will take one parameter that is the connection object which is optional. Syntax

    mysqli_get_proto_info($con);

    Example

    <?php
      $conn = mysqli_connect("localhost", "root", "password", "mydb");
       $info = mysqli_get_proto_info($conn);
       print("Protocol Version: ".$info);
       mysqli_close($conn);
       ?>

    Output

    Protocol Version: 10

    mysqli_get_server_info()

    This function will provide you with the server information to which the connection is made. This function will take one mandatory parameter that is the connection object. Syntax

    mysqli_get_server_info([$con]);

    Example

    <?php
      $conn = mysqli_connect("localhost", "root", "password", "mydb");
       $version = mysqli_get_server_info($conn);
       print("Version Number: ".$version);
       mysqli_close($conn);
       ?>

    Output

    Version Number: 5.7.12-log

    mysqli_get_server_version()

    This function will provide you with the server version details to whc=ich you have made the connection. This function will take the connection object mandatory parameter, Syntax

    mysqli_get_server_version($con);

    Example

    <?php
      $conn = mysqli_connect("localhost", "root", "password", "mydb");
         $version = mysqli_get_server_version($conn);
       print("Version Number: ".$version);
       mysqli_close($conn);
       ?>

    Output Version Number: 50712

    mysqli_get_warnings()

    This function will provide you with the warning details of your last sql query in the array form. This function will take one mandatory parameter that is the connection object. Syntax

    mysqli_get_warnings($con)

    Example

    <?php
      $conn = mysqli_connect("localhost", "root", "password", "mydb");
             $query = "INSERT IGNORE into sudents values('Sanjay', 66)";
       mysqli_query($conn, $query);
       $warn = mysqli_get_warnings($conn);
       print("Warning(s): "."\n");
       print_r($warn);
       $query = "INSERT IGNORE into students values (null, 86), ('Prayaga',986)";
       mysqli_query($conn, $query);
       $warn = mysqli_get_warnings($conn);
       print("\n"."Warning(s): ");
       print_r($warn);
       mysqli_close($conn);
       ?>

    Output

    Warning(s):
    mysqli_warning Object
    (
        [message] => Column 'Last_Name' cannot be null
        [sqlstate] => HY000
        [errno] => 1048
    )
    Warning(s): mysqli_warning Object
    (
        [message] => Data truncated for column 'Last_Name' at row 2
        [sqlstate] => HY000
        [errno] => 1265
    )

    mysqli_info()

    This function will provide you with the information of the sql query that is executed currently. Syntax

    mysqli_info($con)

    Example

    <?php
      $conn = mysqli_connect("localhost", "root", "password", "mydb");
             mysqli_query($con, "INSERT INTO students VALUES ('Sarmista', 21), ('Sheldon', 22)");
       $error = mysqli_info($conn);
       print("Query Info: ".$error);
       mysqli_close($conn);
       ?>

    Output

    Query Info: Records: 2  Duplicates: 0  Warnings: 0

    mysqli_init()

    This function will allow you to initialize the mysql object and thes result can be used in other functions. This function will take one mandatory parameter that is the connection object. Syntax

    mysqli_init($con);

    Example

    <?php
     // $conn = mysqli_connect("localhost", "root", "password", "mydb");
             $db = mysqli_init();
       print_r($db);
       ?>

    Output

    mysqli Object
    (
        [client_info] => mysqlnd 7.4.5
        [client_version] => 70405
        [connect_errno] => 0
        [connect_error] =>
        [errno] => 0
        [error] =>
    )

    mysqli_insert_id()

    This function will allow you to create an automatic id for the auto increment column whenever you insert or delete the result using a query. This function will take one mandatory parameter that is the connection object. Syntax

    mysqli_insert_id($con)

    Example

    <?php
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
             $query = "insert into students values( 'Shikhar', 28)";
       mysqli_query($conn, $query);
       //Insert ID
       $id_data = mysqli_insert_id($conn);
       print("Insert ID: ".$id_data ."\n");
       $query = "insert into students values('Jonathan', 29)";
       mysqli_query($conn, $query);
       $id_data = mysqli_insert_id($conn);
       print("Insert ID: ".$id_data);
       ?>

    Output

    Insert ID: 1
    Insert ID: 2

    mysqli_kill()

    This function will allow you to kill the specific thread as per the given process id. This function will take two mandatory parameters- connection object and the process id. Syntax

     mysqli_kill($con, $processid);

    Example

    <?php
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
        $id_data = mysqli_thread_id($conn);
       mysqli_kill($conn, $id_data);
       $result = mysqli_query($conn, "CREATE TABLE Sample (name VARCHAR(255))");
       if($result){
          print("Success");
       }else{
          print("Fail");
       }
       ?>

    Output

    Fail

    mysqli_more_results()

    This function will provide you with the more information about the last executed query. This function will take one mandatory parameter that is the connection object. Syntax

    mysqli_more_results($con)

    Example

    <?php
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
       $sql = "SELECT * FROM players;SELECT * FROM students";
       mysqli_multi_query($conn, $sql);
       do{
          $res = mysqli_use_result($conn);
          while($row = mysqli_fetch_row($res)){
             print("Name: ".$row[0]."\n");
             print("Age: ".$row[1]."\n");
             print("\n");
          }
          if(mysqli_more_results($conn)){
          }
       }while(mysqli_next_result($conn));
       mysqli_close($conn);
       ?>

    Output

    Name: Jonathan
    Age: 29
    
    Name: Shikhar
    Age: 28
    
    Name: Raju
    Age: 25
    
    Name: Rahman
    Age: 30

    mysqli_multi_query()

    This function will be given with a query as one of its parameters that executes and provides the result. You can even pass more than one query for the particular connection object. Syntax

    mysqli_multi_query($con, query)

    Example

    <?php
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     $query = "CREATE TABLE Data(Name VARCHAR(255), AGE INT); insert into Data values('Ram', 25),('Sam', 30),('Jacob', 27)";
       mysqli_multi_query($conn, $query);
       print("Data is added");
       mysqli_close($conn);
       ?>

    Output

    Data is added

    mysqli_next_result()

    This function allows you to prepare the result set from the previous executed query. This function will take one mandatory parameter that is the connection object. Syntax

    mysqli_next_result($con)

    Example

    <?php
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
      $sql = "SELECT * FROM players;SELECT * FROM students";
       $result = mysqli_multi_query($conn, $sql);
       $num = 0;
       if ($result) {
          do {
             $num = $num+1;
         mysqli_use_result($conn);
          } while (mysqli_next_result($conn));
       }
       print("Number of result sets: ".$num);
       mysqli_close($conn);
       ?>

    Output

    Number of result sets: 2

    mysqli_ping()

    This function will check for the connection if in case the connection is down this function will try to reconnect to the server. This function will take optional parameters. Syntax

    mysqli_ping($con,[$host, $username, $passwd, $dname, $port, $socket, $flags] )

    Example

    <?php
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
       $result = mysqli_ping($conn);
       if($result){
          print("Success");
       }else{
          print("Fail");
       }
       ?>

    Output

    Success

    mysqli_prepare()

    This functional allows you to execute the query. This function will use (?) for the query to specify its value later on. His function will take two mandatory parameters- connection object and the string, Syntax

    mysqli_prepare($con, $str);

    Example

    <?php
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
       $sql = "CREATE TABLE Test(Name VARCHAR(255), AGE INT)"; 
       mysqli_query($conn, $sql);
       print("Table created\n");
       $statement = mysqli_prepare($conn, "INSERT INTO Test values(?, ?)");
       mysqli_stmt_bind_param($statement, "si", $Name, $Age);
       $Name = 'Sam';
       $Age = 40;
       print("Record inserted");
       mysqli_stmt_execute($statement);
       mysqli_stmt_close($statement);
       mysqli_close($conn);
       ?>

    Output

    Table created
    Record inserted

    mysqli_query()

    This function will take a query of its parameters that will get executed on the database. Syntax

    mysqli_query($con, query)

    Example

    <?php
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
       mysqli_query($conn, "CREATE TABLE IF NOT EXISTS data(ID INT, First_Name VARCHAR(255))");
       print("Table created "."\n");`
       mysqli_query($conn, "insert into my_team values(1, 'Shikhar')");
       mysqli_query($conn, "insert into my_team values(2, 'Jonathan')");
       print("Records added"."\n");
       mysqli_close($conn);
       ?>

    Output

    Table created
    Record added

    mysqli_real_connect()

    This function will connect to the MySQL database and will provide the connection object. This function will take all optional parameters. Syntax

    mysqli_real_connect($con,[$host, $username, $passwd, $dname, $port, $socket, $flags] )

    Example

    <?php
    $db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
      if($conn){
          print("Connection successful");
       }else{
          print("Connection Fail ");
       }
       ?>

    Output

    Connection successful

    mysqli_real_escape_string()

    This function will allow you to escape the characters in the given string and make it useful to be used in any SQL statement. This function will take two mandatory parameters. Syntax

    mysqli_real_escape_string($con, $str)

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE my_team(Name VARCHAR(255), Country VARCHAR(255))");
    $player = "Ram";
    $country = "India";
    $result = mysqli_query($conn, "INSERT into my_team VALUES ('$player', '$country')");
    if(!$result){
       print("Error");
    }else{
       print("Record added");
    }
    print("\n");
    $player = mysqli_real_escape_string($conn, $player);
    $country = mysqli_real_escape_string($conn, $country);
    $result = mysqli_query($conn, "INSERT into my_team VALUES ('$player', '$country')");
    if(!$result){
       print("Error");
    }else{
       print("Record added");
    }
    mysqli_close($conn);
       ?>

    Output

    Error
    Record added

    mysqli_real_query()

    This function will allow you to pass the query as one of its parameters and execute it on the database. This function will take two mandatory parameters- connection object and the query string. Syntax

    mysqli_real_query($con, $query)

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($con, "CREATE TABLE IF NOT EXISTS my_team(ID INT, First_Name VARCHAR(255))");
       print("Table created"."\n");
       mysqli_real_query($con, "insert into my_team values(1, 'Shikhar')");
       mysqli_real_query($con, "insert into my_team values(2, 'Jonathan')");
       print("Records added"."\n");
    mysqli_close($conn);
       ?>

    Output

    Table created
    Record added

    mysqli_refresh()

    This function will allow you to refresh the tables, flush logs and the caches. This function will take two mandatory parameters that are- connection object and options (MYSQLI_REFRESH_GRANT MYSQLI_REFRESH_LOG MYSQLI_REFRESH_TABLES MYSQLI_REFRESH_HOSTS MYSQLI_REFRESH_STATUS MYSQLI_REFRESH_THREADS MYSQLI_REFRESH_SLAVE MYSQLI_REFRESH_MASTER) Syntax

    mysqli_refresh($con, options);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
       mysqli_autocommit($con, False);
       mysqli_real_query($conn, "insert into my_team values(1, 'Shikhar')");
       mysqli_real_query($conn, "insert into my_team values(2, 'Jonathan')");
       mysqli_refresh($conn, MYSQLI_REFRESH_TABLES);
    mysqli_close($conn);
       ?>

    After this you can verify the table data by running a select query.

    mysqli_rollback()

    This function will allow you to rollback the current transaction to the last save point. This function will take the mandatory connection object parameter. Syntax

    mysqli_rollback($con, [$flags, $name]);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
       mysqli_autocommit($connn, False);
      $result = mysqli_query($conn, "SELECT * FROM my_team");
       print("No.of rows: ".mysqli_affected_rows($conn)."\n");
       mysqli_commit($conn);
       mysqli_query($conn, "DELETE FROM my_team where id in(3,4)"); 
       $result = mysqli_query($conn, "SELECT * FROM my_team");
       print("No.of rows (before roll back): ".mysqli_affected_rows($conn)."\n");
       mysqli_rollback($conn);
       $result = mysqli_query($conn, "SELECT * FROM my_team");
       print("No.of rows (after roll back): ".mysqli_affected_rows($conn));
    mysqli_close($connn);
       ?>

    Output

    No.of rows : 6
    No.of rows (before roll back): 2
    No.of rows (after roll back): 6

    mysqli_select_db()

    This function will accept the database name and make it as the default database to run queries. Syntax

    mysqli_select_db($con, name)

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
        mysqli_query($conn, "CREATE DATABASE data");
       mysqli_select_db($conn, "data");
       $result = mysqli_query($conn, "SELECT DATABASE()");
       while ($row = mysqli_fetch_row($result)) 
          print("Current Database: ".$row[0]);
    mysqli_close($conn);
       ?>

    Output

    Current Database: data

    mysqli_set_charset()

    This function will allow you to specify the character set that will send the data to the server from the client. Syntax

    mysqli_set_charset($con, charset)

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
        $result = mysqli_set_charset($conn, "utf8");
       print_r($result);
    mysqli_close($conn);
       ?>

    Output

    1

    mysqli_sqlstate()

    This function will allow you to return the error for the last run executed query. This function will take one mandatory parameter- connection object. Syntax

    mysqli_sqlstate($con)

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
        mysqli_query($conn, "Select * from WrongTable");
       $sql_state = mysqli_sqlstate($conn);
       print("SQL State Error: ".$sql_state);
    mysqli_close($conn);
       ?>

    Output

    SQL State Error: 42S02

    mysqli_ssl_set()

    This function will allow you to create a secure connection to the MySQL server. This function will take all the mandatory parameters. Conn is the connection object, key is the path to the key file, cert is the name for the certification file, capath is path to the SSL certificate directory and cipher specifies the encryption Syntax

    mysqli_ssl_set($con, $key, $cert, $ca, $capath, $cipher);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
          $conn->ssl_set("key.pem", "cert.pem", "cacert.pem", NULL, NULL);
       $conn = $conn->real_connect("localhost","root","password","test");
       if($conn){
          print("Connection Successful");
       }else{
          print("Connection Fail ". mysqli_connect_error());
       }
       ?>

    Output

    Connection successful

    mysqli_stat()

    This function will take and provide the status of the current server which may include the thread number, open tables, up time etc. this function will take the connection object as the mandatory parameter. Syntax

    mysqli_stat($con)

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
          $statistics = mysqli_stat($conn);
       print("Status: ".$statistics);
    mysqli_close($conn)
       ?>

    Output

    Status: Uptime: 130131  Threads: 2  Questions: 350  Slow queries: 0  Opens: 172  Flush tables: 1  Open tables: 145  Queries per second avg: 0.002

    mysqli_stmt_init()

    This function will allow you to initialize the statement object. This function will take the connection object as the mandatory parameter. Syntax

    mysqli_stmt_init($con);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
         $sql = "CREATE TABLE Test(Name VARCHAR(255), AGE INT)"; 
       mysqli_query($conn, $sql);
       $statement =  mysqli_stmt_init($conn);
       $result = mysqli_stmt_prepare($statement, "INSERT INTO Test values(?, ?)");
       mysqli_stmt_bind_param($statement, "si", $Name, $Age);
       $Name = 'Raju';
       $Age = 25;
       print("Record added");
       mysqli_stmt_execute($statement);
       mysqli_stmt_close($statement);
    mysqli_close($conn)
       ?>

    Output

    Record added

    mysqli_thread_id()

    This function will allow you to provide the thread id for the given connection object. Syntax

    mysqli_thread_id($con);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
         $id = mysqli_thread_id($conn);
       print("Current thread id: ".$id);
    //mysqli_close($conn)
       ?>

    Output

    Current thread id: 70

    mysqli_thread_safe()

    This function will allow you to check if the thread safety is ensured by the underlying library. This function will not take any parameter. Syntax

    mysqli_thread_safe(void);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
       $result = mysqli_thread_safe();
       if($result){
          print("thread is safe");
       }else{
          print("not safe");
       }
    //mysqli_close($conn)
       ?>

    Output

    thread is safe

    mysqli_use_result()

    This function will allow you to provide the result set from the last executed query. This function will take the mandatory connection object parameter. Syntax

    mysqli_use_result($con)

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
      $sql = "SELECT * FROM players;SELECT * FROM emp;SELECT * FROM tutorials";
       $result = mysqli_multi_query($conn, $sql);
       $num = 0;
       if ($result) {
          do {
             $num = $num+1;
          mysqli_use_result($conn);
         } while (mysqli_next_result($conn));
       }
       print("Number: ".$num);
    mysqli_close($conn)
       ?>

    Output

    Number: 3

    mysqli_warning_count()

    This function will provide you with the count of the error occurred during the last query execution. Connection object is the mandatory parameter. Syntax

    mysqli_warning_count($con)

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
       $query = "INSERT IGNORE into emp values (15, 'sam', 'yam', DATE('1990-11-25'), 9986), (15, NULL, 'sita', DATE('1990-11-25'), 9986)";
       mysqli_query($conn, $query);
       $num = mysqli_warning_count($conn);
       print("Number\: ".$num);
    mysqli_close($conn)
       ?>

    Output

    Number: 5

    mysqli_fetch_all()

    This function will provide you with the result set for the given result object. This function will take two mandatory parameters- connection object and result object. Syntax

    mysqli_fetch_all($result, [$type]);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
      mysqli_query($conn, "CREATE TABLE myplayers(ID INT, First_Name VARCHAR(255))");
       print("Table Created\n");
       mysqli_query($conn, "INSERT INTO myplayers values(1, 'Sikhar')");
       print("Record added\n");
       $result = mysqli_query($conn, "SELECT * FROM myplayers");
       $rows = mysqli_fetch_all($result);
       print_r($rows);
       mysqli_free_result($result);
    mysqli_close($conn)
       ?>

    Output

    Table Created
    Record added
    Array
    (
        [0] => Array
            (
                [0] => 1
                [1] => Sikhar        
            )

    mysqli_fetch_assoc()

    This function will accept the result set as the parameter and the details get retrieved in the result set which will be provided in an array form. Syntax

    mysqli_fetch_assoc($result);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $result = mysqli_query($conn, "SELECT * FROM data");
       while($ob = mysqli_fetch_assoc($result)){
          print("ID: ".$ob["ID"]."\n");
          print("First_Name: ".$ob["First_Name"]."\n");
       }
      mysqli_free_result($result);
    mysqli_close($conn)
      ?>

    Output

    Table added
    Record added
    ID: 1
    First_Name: Sikhar

    mysqli_fetch_fields()

    This function will take the result object as parameter and provide you with an array of objects that represents the fields. Syntax

    mysqli_fetch_fields($result);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $result = mysqli_query($conn, "SELECT * FROM data");
       $detaill = mysqli_fetch_fields($result);
       foreach ($detail as $val) {
          print("ID: ".$val->name."\n");
          print("First_Name: ".$val->table."\n");
          print("\n");
       }
       mysqli_free_result($result);
    mysqli_close($conn)
      ?>

    Output

    Table added
    Record added
    ID: ID
    First_Name: data

    mysqli_fetch_lengths()

    This function will be passed with the result object and returns the length of the column present in the current row and displays the result in an array form. Syntax

    mysqli_fetch_lengths($result);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $result = mysqli_query($conn, "SELECT * FROM data");
       $row = mysqli_fetch_row($result);
       $len = mysqli_fetch_lengths($result);
       print_r($len);
       mysqli_free_result($result);
       mysqli_free_result($result);
       mysqli_close($conn)
       ?>

    Output

    Table added
    Record added
    Array
    (
        [0] => 1
    )

    mysqli_fetch_object()

    This function will pass a result object and provide the column contents for a given row in the array form. This function will have one mandatory parameter and two optional parameters. Syntax

    mysqli_fetch_object($result, [$class_name, $params]);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $result = mysqli_query($conn, "SELECT * FROM data");
       while($ob = mysqli_fetch_object($result)){
          print("ID: ".$ob->ID."\n");
          print("First_Name: ".$ob->First_Name."\n");  
       }
       mysqli_free_result($result);
       mysqli_close($conn)
       ?>

    Output

    Table added
    Record added
    ID: 1
    First_Name: Sikhar

    mysqli_fetch_row()

    This function will accept a result object and provide you with the row contents which will be displayed as the string array. Syntax

    mysqli_fetch_row($result);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $result = mysqli_query($conn, "SELECT * FROM data");
    while ($row_data = mysqli_fetch_row($result)) {
          print("ID: ".$row_data[0]."\n");
          print("First_Name: ".$row_data[1]."\n");
      }
       mysqli_free_result($result);
    mysqli_close($conn)
       ?>

    Output

    Table added
    Record added
    ID: 1
    First_Name: Sikhar

    mysqli_field_seek()

    This function will take parameters as the result object and an integer value which specifies the field number and moves the field seek to the provided field. Syntax

    mysqli_field_seek($result, $field);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $result = mysqli_query($conn, "SELECT * FROM data");
    mysqli_field_seek($result, 2);
      $data = mysqli_fetch_field($result);
       $currentfield = mysqli_field_tell($result);
       print("Current Field: ".$currentfield."\n");
       print("Name: ".$data->name."\n");
       print("Table: ".$data->table."\n");
       mysqli_free_result($result);
    mysqli_close($conn)
       ?>

    Output

    Table added
    Record added
    Current Field: 1
    Name: Last_Name
    Table: data

    mysqli_free_result()

    This function will allow you to free the result object for the specified result object. This function takes one mandatory parameter that is the result object. Syntax

    mysqli_free_result($result);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $result = mysqli_query($conn, "SELECT * FROM data");
    mysqli_field_seek($result, 2);
       $data = mysqli_fetch_field($result);
       $currentfield = mysqli_field_tell($result);
       print("Current Field: ".$currentfield."\n");
       print("Name: ".$data->name."\n");
       print("Table: ".$data->table."\n");
       mysqli_free_result($result);
    mysqli_close($conn)
       ?>

    Output

    Table added
    Record added
    Current Field: 1
    Name: Last_Name
    Table: data

    mysqli_num_fields()

    This function will be provided with the result object as a parameter and it provides the number of fields present for the given result set. Syntax

    mysqli_num_fields($result);

    Example

    <?php
    //$db = mysqli_init();
    $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $result = mysqli_query($conn, "SELECT * FROM data");
     $num = mysqli_num_fields($result);
       print("Number of fields: ".$num);
       mysqli_free_result($result);
    mysqli_close($conn)
       ?>

    Output

    Table added
    Record added
    Number of fields: 3

    mysqli_num_rows()

    This function will be provided with the result object as a parameter and it provides the number of rows present for the given result set. Syntax

    mysqli_num_rows($result);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $result = mysqli_query($conn, "SELECT * FROM data");
     $num = mysqli_num_fields($result);
       print("Number of rows: ".$num);
       mysqli_free_result($result);
    mysqli_close($conn)
       ?>

    Output

    Table added
    Record added
    Number of fields: 3

    mysqli_stmt_affected_rows()

    This function will provide you with the number of rows to be affected by the executed query. Syntax

    mysqli_stmt_affected_rows($stmt)

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
      $statement = mysqli_prepare($conn, "UPDATE students set AGE=AGE-? where AGE>=?");
       mysqli_stmt_bind_param($statement, "si", $reduct, $limit);
       $limit = 10;
       $reduct = 20;
       mysqli_stmt_execute($statement);
       print("Records updated\n");
       $num = mysqli_stmt_affected_rows($statement);
       mysqli_stmt_close($statement);
    mysqli_close($conn);
    print("Rows affected ".$num);
       ?>

    Output

    Record updated
    Rows affected 2

    mysqli_stmt_bind_parameter()

    This function will allow you to bind the variable to the parameters of the prepared statements. All the three parameters are mandatory. Syntax

    mysqli_stmt_bind_param($stmt, $types, $var1, $var2...);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     $conn -> query("CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       $statement = $conn -> prepare( "INSERT INTO data values(?, ?)");
       $statement -> bind_param("issss", $id, $fname);
       $id = 1;
       $fname = 'Shikhar';
       $statement->execute();
       $statement->close();
       $conn->close();
       ?>

    Output

    Table added

    mysqli_stmt_bind_results()

    This function will allow you to bind the result set columns to the variables then you can fetch the details using the fetch function. Syntax

    mysqli_stmt_bind_result($stmt, $var1, $var2...);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
    $statement = mysqli_prepare($conn, "SELECT * FROM data");
       mysqli_stmt_execute($statement);
       mysqli_stmt_bind_result($statement, $id, $fname);
       while (mysqli_stmt_fetch($statement)) {
          print("Id: ".$id."\n");
          print("fname: ".$fname."\n");
       }
       mysqli_stmt_close($statement);
       mysqli_close($conn);
       ?>

    Output

    Table added
    Record added
    Id: 1
    fname: Sikhar

    mysqli_stmt_close()

    This function will allow you to close the statement using the statement object. Syntax

    mysqli_stmt_close($stmt);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
      mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       mysqli_stmt_close($statement);
       mysqli_close($conn);
       ?>

    Output

    Table added
    Record added

    mysqli_stmt_errno()

    This function will provide you with the occurred error details during the last statement execution. Syntax

    mysqli_stmt_errno($stmt)

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
      mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
    $statement = mysqli_prepare($conn, "SELECT * FROM data");
      mysqli_query($conn, "DROP TABLE data");
       mysqli_stmt_execute($statement);
       $num = mysqli_stmt_errno($statement);
       print("Error Code: ".$num);
       mysqli_stmt_close($statement);
       mysqli_close($conn);
       ?>

    Output

    Table added
    Record added
    Error Code: 1139

    mysqli_stmt_error()

    This function will provide you with the occurred error description during the last statement execution. Syntax

    mysqli_stmt_error($stmt)

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
      mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
    $statement = mysqli_prepare($conn, "SELECT * FROM data");
      mysqli_query($conn, "DROP TABLE data");
       mysqli_stmt_execute($statement);
       $num = mysqli_stmt_error($statement);
       print("Error Code: ".$num);
       mysqli_stmt_close($statement);
       mysqli_close($conn);
       ?>

    Output

    Table added
    Record added
    Error: connection failed

    mysqli_stmt_execute()

    This function will accept the statement object as the parameter and will execute the provided statement. Syntax

    mysqli_stmt_execute($stmt);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     $conn -> query("CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       $statement = $conn -> prepare( "INSERT INTO data values(?, ?)");
       $statement -> bind_param("issss", $id, $fname);
       $id = 1;
       $fname = 'Shikhar';
       $statement->execute();
       $statement->close();
       $conn->close();
       ?>

    Output

    Table added

    mysqli_stmt_fetch()

    This function will allow you to fetch the result set columns in the specified variables. Syntax

    mysqli_stmt_fetch($stmt);

    Example-

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $statement = mysqli_query($conn, "SELECT * FROM data");
    mysqli_stmt_execute($statement);
    mysqli_stmt_bind_result($stmt, $id);
    while ($row_data = mysqli_stmt_fetch($result)) {
          print("ID: ".$row_data[0]."\n");
          print("First_Name: ".$row_data[1]."\n");
      }
       mysqli_stmt_close($statement);
    mysqli_close($conn)
       ?>

    Output

    Table added
    Record added
    ID: 1
    First_Name: Sikhar

    mysqli_stmt_field_count()

    This function will allow you to provide the number of columns for the given statement and accept only the statement object. Syntax

    mysqli_stmt_field_count($stmt)

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $statement = mysqli_query($conn, "SELECT * FROM data");
    mysqli_stmt_execute($statement);
    $count = mysqli_stmt_field_count($statement);
       print("Field Count: ".$count);
       mysqli_stmt_close($statement);
    mysqli_close($conn)
    
       ?>

    Output

    Table added
    Record added
    Field Count: 1

    mysqli_stmt_free_result()

    This function will take the statement object as the parameter and free the memory where the statement result is stored. Syntax

    mysqli_stmt_free_result($stmt);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $statement = mysqli_query($conn, "SELECT * FROM data");
    mysqli_stmt_execute($statement);
    mysqli_stmt_store_result($statement);
       $num = mysqli_stmt_num_rows($statement);
       print("Total rows: ".$num."\n");
       mysqli_stmt_free_result($statement);
       $num = mysqli_stmt_num_rows($statement);
       print("Number of rows: ".$num."\n");
       mysqli_stmt_close($statement);
    mysqli_close($conn)
       ?>

    Output

    Table added
    Record added
    Total rows: 2
    Number of rows: 0

    mysqli_stmt_get_result()

    This function will accept the statement object as the parameter and provides you with the result set using the given statement. You cannot provide a close statement for this function. Syntax

    mysqli_stmt_get_result($stmt);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $statement = mysqli_query($conn, "SELECT * FROM data");
    mysqli_stmt_execute($statement);
    $result = mysqli_stmt_get_result($statement);
       while ($db_rpw = mysqli_fetch_array($result, MYSQLI_NUM)){
          foreach($db_rpw as $r){
             print("$r ");
          }
          print("\n");
       }
       mysqli_stmt_close($statement);
    mysqli_close($conn)
       ?>

    Output

    Table added
    
    Record added
    
    1 Sikhar
    
    

    mysqli_stmt_num_rows()

    this function will take a statement object as the parameter and will provide you with the number of the rows for the given statement. Syntax

    mysqli_stmt_num_rows($stmt)

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $statement = mysqli_query($conn, "SELECT * FROM data");
    mysqli_stmt_execute($statement);
    mysqli_stmt_store_result($statement);
       $num = mysqli_stmt_num_rows($statement);
       print("Number of rows: ".$num."\n");
       mysqli_stmt_close($statement);
    mysqli_close($conn)
       ?>

    Output

    Table added
    Record added
    Number of rows: 1

    mysqli_stmt_prepare()

    This function will take the statement object as the parameter and allows you to use the parameter markers (?) to specify the values to be used in the statement. Syntax

    mysqli_stmt_prepare($stmt, $str);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE students(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       $statement = mysqli_stmt_init($conn);
       mysqli_stmt_prepare($statement, "INSERT INTO students values(?, ?)");
       mysqli_stmt_bind_param($statement, "si", $Name, $Age);
       $Name = 'Veer';
       $Age = 25;
       print("Record added");
    mysqli_stmt_execute($statement);
       mysqli_stmt_close($statement);
    mysqli_close($conn)
       ?>
    
    

    Output

    Table added
    Record added

    mysqli_stmt_reset()

    This function will take the statement object as the parameter and reset it. Syntax

    mysqli_stmt_reset($stmt);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       mysqli_query($conn, "INSERT INTO data values(1, 'Sikhar')");
       print("Record added\n");
       $statement = mysqli_query($conn, "SELECT * FROM data");
    mysqli_stmt_execute($statement);
    $result = mysqli_stmt_reset($statement);
       if($result){
          print("Reset Done");
       }
       $result = mysqli_stmt_bind_result($statement, $id, $fname);
       while (mysqli_stmt_fetch($statement)) {
          print("Id: ".$id."\n");
          print("fname: ".$fname."\n");
       }
       mysqli_stmt_close($statement);
    mysqli_close($conn)
       ?>

    Output

    Table added
    Record added
    Reset Done

    mysqli_stmt_result_metadata()

    This function will take the statement object as parameter and run the select query to provide you with a metadata object that has information about the result of the statement. Syntax

    mysqli_stmt_result_metadata($stmt);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
     mysqli_query($conn, "CREATE TABLE data(ID INT, First_Name VARCHAR(255))");
       print("Table added\n");
       $statement = mysqli_query($conn, "SELECT * FROM data");
    mysqli_stmt_execute($statement);
      $m_data = mysqli_stmt_result_metadata($statement);
       print_r(mysqli_fetch_fields($m_data));
       mysqli_stmt_close($statement
    mysqli_close($conn)
       ?>

    Output

    Table added
    Array
    (
        [0] => stdClass Object
            (
                [name] => Name
                [orgname] => Name
                [table] => test
                [org table] => test
                [def] =>
                [db] => mydb
                [catalog] => def
                [max_length] => 0
                [length] => 765
                [charsetnr] => 33
                [flags] => 0
                [type] => 253
                [decimals] => 0
            )

    mysqli_stmt_send_long_data()

    If you use long data then this function will allow you to send the data to the column in small parts. Syntax

    mysqli_stmt_send_long_data($stmt);

    Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
      mysqli_query($conn, "CREATE TABLE mask(message BLOB)");
       print("Table added \n");
       $statement = mysqli_prepare($conn, "INSERT INTO mask values(?)");
       mysqli_stmt_bind_param($statement, "b", $txt);
       $txt = NULL;
       $data = "we have sample text";
       mysqli_stmt_send_long_data($statement, 0, $data);
       print("Data added");
       mysqli_stmt_execute($statement);
       mysqli_stmt_close($statement);
       mysqli_close($conn)
       ?>

    Output

    Table added
    Data added

    mysqli_stmt_store_result()

    This function will take the statement object as the parameter and store the result set locally for the given statement. Syntax-mysqli_stmt_store_result($stmt); Example

    <?php
    //$db = mysqli_init();
     $conn = mysqli_connect("localhost", "root", "password", "mydb");
      mysqli_query($conn, "CREATE TABLE mask(message BLOB)");
       print("Table added \n");
       $statement = mysqli_prepare($conn, "SELECT * FROM Test");
       mysqli_stmt_execute($statement);
       mysqli_stmt_store_result($statement);
       $num = mysqli_stmt_num_rows($statement);
       print("Number of rows: ".$num."\n");
       mysqli_stmt_execute($statement);
       mysqli_stmt_close($statement);
       mysqli_close($conn)
       ?>

    Output

    Table added 
    Number of rows: 3

    People are also reading: