Mysqli extension provides us to access the functionality offered by MySQL 4.1 and above.The i in the function name stands for improved (MySQL Improved). The mysqli extension is built using the PHP extension framework.The MySQLi library is much more optimized and quicker than the previous MYSQL library.
The benefits of mysqli extension over mysql extension are:
The following is a simple example of how to use prepared queries
Prepared Queries
mysqli->prepare($query): A prepared statement is use to prepare a query for execution and the method returns a statement handle.
mysqli_stmt->bind_param($types, $param1): Bind variables to the query and returns TRUE on success or FALSE on failure.Here types can be (s)string , (i)integer , (d)double or (b)blob.
mysqli_stmt->execute(): Execute the prepared query with the given variables and
returns TRUE on success or FALSE on failure.
mysqli_stmt->bind_result(&$data1, &$data2,...): Bind the result set to
given set of variables and returns TRUE on success or FALSE on failure.
mysqli_stmt->fetch(): Fetch a row into the bound variables and returns
TRUE on Success.FALSE on Error.NULL No data exists.
Example table : employee
Here's the PHP code using mysqli extension to access the above mentioned example table.
Output:
The benefits of mysqli extension over mysql extension are:
- OOP style interface
- mysqli extension supports prepared statements & multiple statements
- Support for transactions
- Better debugging capabilities
- Embedded server support
Code Example
The following is a simple example of how to use prepared queries
Prepared Queries
mysqli->prepare($query): A prepared statement is use to prepare a query for execution and the method returns a statement handle.
mysqli_stmt->bind_param($types, $param1): Bind variables to the query and returns TRUE on success or FALSE on failure.Here types can be (s)string , (i)integer , (d)double or (b)blob.
mysqli_stmt->execute(): Execute the prepared query with the given variables and
returns TRUE on success or FALSE on failure.
mysqli_stmt->bind_result(&$data1, &$data2,...): Bind the result set to
given set of variables and returns TRUE on success or FALSE on failure.
mysqli_stmt->fetch(): Fetch a row into the bound variables and returns
TRUE on Success.FALSE on Error.NULL No data exists.
Example table : employee
Code:
+------+------------+-----------+------------+
| id | first_name | last_name | DOJ |
+------+------------+-----------+------------+
| 1 | Ramesh | Prasad | 1996-09-17 |
| 2 | Dilip | Das | 1987-07-30 |
| 3 | Prakash | Chandra | 1982-10-24 |
| 4 | Sourav | Roy | 1996-07-25 |
| 5 | Vijay | Kumar | 1982-10-18 |
| 6 | Sourav | Kumar | 1996-09-11 |
+------+------------+-----------+------------+
Here's the PHP code using mysqli extension to access the above mentioned example table.
Code: PHP
<?php
$mysqli = new mysqli('localhost', 'root', '', 'mydb');
if ($mysqli->connect_errno) echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
$stmt = $mysqli->prepare('SELECT id,first_name,doj from employee where first_name=?');
$fname = "Sourav";
// Bind the parameters, s is for string type
$stmt->bind_param("s", $fname);
// Execute the query
$stmt->execute();
// Bind resulting variables following the execute
$stmt->bind_result($id, $name, $doj);
// Loop through each result
while ($stmt->fetch())
{
echo $id . " " . $name . " " . $doj . "<br>";
}
$stmt->close();
$mysqli->close();
?>
Output:
Code:
4 Sourav 1996-07-25
6 Sourav 1996-09-11
