MySQLi extension of PHP

In this tutorial we will learn about MySQLi extension of PHP to work with MySQL database.

Prerequisite

In order to follow this tutorial it is assumed that you are familiar with MySQL and database queries and have MySQL installed.

What is MySQLi?

The PHP MySQLi extesion is an improved version of the mysql extension that we use to work with MySQL database.

It is not recommended to use mysql extension to access the database in production enviroment as it is not secured and has been deprecated as of PHP 5.5.0, and has been removed from PHP 7.0.0.

How to create MySQLi connection?

We use the mysqli() constructor to create a MySQLi connection.

In the following example we are creating a connection to connect to "mydb" database.

//constants
define('DB_HOST', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', 'root123');
define('DB_NAME', 'mydb');

//create a connection
$mysqlicon = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);

In the above code DB_HOST holds the host string which in this case is 'localhost'.

DB_USERNAME is holding the username for the database and in the above case it is 'root'.

DB_PASSWORD is for the password of the user of the database and for the above example it is 'root123'.

I generally use 'root123' password for demo purpose only. Always use a strong password.

DB_NAME holds the database name.

There is another way to connect to a database. In the following example we connect to a database and then select the database.

//constants
define('DB_HOST', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', 'root123');
define('DB_NAME', 'mydb');

//create object of mysqli class
$mysqlicon = new mysqli();


//now connect to a database
$mysqlicon->connect(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);

Throughout this course we will use $mysqlicon as an object of the mysqli class.

How to select a database?

To select a database we use the select_db() method.

//connect to myblog database
$mysqlicon->select_db('myblog');

Always close connection

It is a good practice to always close a database connection using the close() method.

//close connection
$mysqlicon->close();

MySQLi error code

We use errno to get the error code if the last executed MySQLi method fails. If no error occurs then 0 is returned.

//check error
if ($mysqlicon->errno !== 0) {
 die("Error occured " . $mysqli->errno);
}

In the above code, if an error occurs for the last executed MySQLi method, then $mysqlicon->errno will not be 0. So, we will stop further execution by using the die() function and print out the error code.

MySQLi error message

We use error to print out the error message.

//check error
if ($mysqlicon->errno !== 0) {
 die("Error Code: " . $mysqli->errno . " Message: " . $mysqli->error);
}

Free memory occupied by retrieved result

It is a good practice to free the memory after using the result by using the free() method.

Retrieve data

To retrieve data from a table we use the SELECT query. To get data using MySQLi we use the query method.

In the following example we will fetch 'studentid', 'name' and 'branch' column from a 'student' table. A total of 10 records will be fetched per execution of the query.

//query
$query = sprintf("SELECT studentid, name, branch FROM student LIMIT 0,10");

//execute query and get result
$result = $mysqlicon->query($query);

//loop through the result and print
foreach ($result as $row) {
 printf("ID: " . $row['studentid'] . " Name: " . $row['name'] . " Branch: " . $row['branch'] . "\n");
}

//free the memory taken by query result
$result->free();

//close connection
$mysqlicon->close();

Alternatively, we can retrieve data as an associative array using the fetch_assoc() method. It will return NULL if there are no more rows in the result set.

//query
$query = sprintf("SELECT studentid, name, branch FROM student LIMIT 0,10");

//execute query and get result
$result = $mysqlicon->query($query);

//get associative array and loop through row-by-row
while ($row = $result->fetch_assoc()) {
 printf("ID: " . $row['studentid'] . " Name: " . $row['name'] . " Branch: " . $row['branch'] . "\n");
}

How many rows returned?

To know about the number of rows returned when a SELECT query is executed we use num_rows.

//query
$query = sprintf("SELECT studentid, name, branch FROM student LIMIT 0,10");

//execute query and get result
$result = $mysqlicon->query($query);

//display total number of rows returned
printf("Rows: %d", $mysqlicon->num_rows);

How many rows affected?

When we perform the INSERT, UPDATE and DELETE query rows are affected and use use affected_rows to know the number of rows affected by the executed query.

In the following code we are printing out the number of rows affected.

//execute query
$result = $mysqlicon->query($query);

printf("Affected Rows: %d", $mysqlicon->affected_rows);

Insert data

To insert data we use the INSERT query and query() method.

In the following example we are inserting a student data in student table.

//query
$query = sprintf("INSERT INTO student (`studentid`, `name`, `branch`) VALUES ('%s', '%s', '%s')", 's123-456', 'Yusuf', 'CSE');

//execute query
$result = $mysqlicon->query($query);

printf("Inserted Rows: %d", $mysqlicon->affected_rows);  //this will print "Inserted Rows: 1"

Update data

To update data we use the UPDATE query and query method.

In the following code we are updating the 'student' table and setting the name to 'Alice' for studentid = '123'.

//query
$query = sprintf("UPDATE student SET name = '%s' WHERE studentid = '%s'", 'Alice', '123');

//execute query
$result = $mysqlicon->query($query);

printf("Updated Rows: %d", $mysqlicon->affected_rows);  //this will print "Updated Rows: 1"

Delete data

To delete data we use the DELETE query and query method.

In the following code we are deleting record of studentid = '123' from the student table.

//query
$query = sprintf("DELETE FROM student WHERE studentid = '%s'", '123');

//execute query
$result = $mysqlicon->query($query);

printf("Deleted Rows: %d", $mysqlicon->affected_rows);  //this will print "Deleted Rows: 1"

Prepared Statement

When we want to execute a query multiple times using same or different parameters, we use prepared statement.

How to create a prepared statment?

To create a prepared statement we use the prepare() method.

In the following example we are creating a statement object and then preparing the statement for execution.

//create a statement object
$stmt = $mysqlicon->stmt_init();

//prepare statement for execution
$stmt->prepare($query);

How to execute a prepared statment?

To execute a prepared statement we use the exeute() method.

//execute a prepared statment
$stmt->execute();

Release prepared statment resource

It is a good practice to release the resources that prepared statement holds after use.

We use the close() method for this purpose.

//release resource
$stmt->close();

Free memory occupied by retrieved data

We use the free() method to free the memory occupied by the retrieved result of prepared statment.

Types of Prepared statement

There are two types of prepared statement available.

• Bound Parameters
• Bound Results

Prepared statement - Bound Parameters

We use bound parameters when we want to save a query to the MySQL server and then later send only the changing data to the server to get integrated with the query and then get executed.

Good example is a multiple INSERT query for student table. The base structure of the query remains the same and only the data to be inserted changes.

Prepared statement - Bound Results

This allows us to bind variables to the retrieved fields to the result set.

Example: The fields returned in a SELECT query can be bind with variables.

How to bind parameters?

To bind parameters to a prepared statement we use the bind_param() method.

The first argument passed to this method is the type. It tells us about the data type of the variables that follows.

Following are the types allowed for this method.

• i for integer types
• d for float and double types
• b for blob types
• s for string and all other types

In the following example we have a bind_param() for integer, float and string type variables. var1 and var2 are of type integer. var3 is of type float and var4 is of string type.

$stmt->bind_param("iids", $var1, $var2, $var3, $var4);

How to store result set?

We use the store_result() method to store the result set after a prepared statement is executed.

$stmt->store_result();

Find the total number of returned rows

After we have stored the result set we can find the total number of returned rows by SELECT query by using num_rows.

$stmt->num_rows;

Find the total number of affected rows

To find the total number of affected rows by INSERT, UPDATE and DELETE query we use affected_rows.

$stmt->affected_rows;

Example of bind parameters using INSERT query

In the following example we will insert data into the student table using bind_param() method.

/**
 * array of data
 */
$arrdata = array(
 array(
  "studentid" => "s101",
  "name" => "Alice",
  "branch" => "CSE"
 ),
 array(
  "studentid" => "s102",
  "name" => "Bob",
  "branch" => "ECE"
 ),
 array(
  "studentid" => "s102",
  "name" => "Eve",
  "branch" => "ME"
 )
);

//query
$query = sprintf("INSERT INTO student (`studentid`, `name`, `branch`) VALUES (?, ?, ?)");

//create statement object
$stmt = $mysqlicon->stmt_init();

//prepare the statement for execution
$stmt->prepare($query);

//bind parameters
$stmt->bind_param("sss", $studentid, $name, $branch);

//loop through array of data
foreach ($arrdata as $row) {
 $studentid = $row['studentid'];
 $name = $row['name'];
 $branch = $row['branch'];
 
 //execute the prepared statment
 $stmt->execute();

 //print affected rows
 printf("Affected Rows by Insert Query: %d", $stmt->affected_rows);  //this will print "Affected Rows by Insert Query: 1" for every insert query execution
}

//release prepared statment resource
$stmt->close();

//close connection
$mysqlicon->close();

How to Bind Result?

After a query is prepared and executed, we use the bind_result() method to attach variables to the returned fields.

In the following code we have two variables var1 and var2 from some retrieved query.

$stmt->bind_result($var1, $var2);

How to fetch data using prepared statement?

We use the fetch() method to retrieve each row from the prepared statement.

$stmt->fetch();

Example of bind result using SELECT query

In the following example we will retrieve data from the student table using bind_result() method.

//query
$query = sprintf("SELECT studentid, name, branch FROM student LIMIT 10");

//create statement object
$stmt = $mysqlicon->stmt_init();

//prepare the statement for execution
$stmt->prepare($query);

//execute the prepared statement
$stmt->execute();

//store result set
$stmt->store_result();

//total fetched rows
printf("Retrieved Rows: %d
", $stmt->num_rows);

//bind results
$stmt->bind_result($studentid, $name, $branch);

//loop through result and assign retrieved fields to the bind variables
while ($stmt->fetch()) {
 //print the data
 printf("StudentId: %s Name: %s Branch: %s", $studentid, $name, $branch);
}

//recuperate the prepared statment
$stmt->close();

//close connection
$mysqlicon->close();

How to enable and disable autocommit?

To enable autocommit option we pass TRUE to autocommit() method and to disable autocommit we pass FALSE.

$mysqlicon->autocommit(TRUE); //this will enable

$mysqlicon->autocommit(FALSE); //this will disable

How to commit a transaction?

We use commit() method to commit a transaction.s

$mysqlicon->commit();

How to rollback a transaction?

To rollback a transaction we use the rollback() method.

$mysqlicon->rollback();