PHP MySQL Functions

By | September 2, 2020
PHP MySQL Functions
Table of Contents show

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.

Vamware

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

Leave a Reply

Your email address will not be published. Required fields are marked *