Menu

MySQLi: Handling MySQL databases in PHP

PHP

MySQLi (MySQL Improved) is the new driver which is used to connect to MySQL databases exclusively in PHP. This improved extension has a lot of advantages over the old mysql_query statements. You can take advantages of the new functionality when you upgrade from mysql to mysqli. One of the major advantage you can get is the ability to use the object oriented style to handle databases. It makes the coding simpler to maintain.

Connecting to a MySql database using MySQLi

You just use the new mysqli statement to make a database connection. Here’s how you do it:

<?php
$db = new mysqli('localhost','username','password','database');
?>

The above code is good for a simple connection. But if you want to display errors when they occur, add some additional lines as:

<?php
$db = new mysqli('localhost','username','password','database');
if($db->connect_errno){
echo 'Error in database connection:'.$db->connect_errno;
echo '<br />';
echo $db->connect_error;
die();
}
?>

Query Database

To perform a simple query on the database just try the following:

$result = $db->query(“SELECT * FROM table”) or die($db->error);

If the queries take in input from users, then it should be secured. We do it my using $db->real_escape_string or the prepare statements. After preparing the statements, you will have to bind it.

Here’s how you use the prepare statement for MySQLi

<?php
$result = $db->prepare("SELECT name FROM table WHERE id = ?");
$result->bind_param('i',$id); //s would be for string
$result->execute();
$result->bind_result($name);
?>

Using the Query Results

You can, of course, add additional lines to the query to make sure to see whether or not you get results and to output the results using a while or a for each loop.

Example:

<?php
$result = $db->query("SELECT * FROM table") or die($db->error);
if($result->num_rows){
while($row = $result->fetch_assoc()){
echo $row['name'];
}
}
?>

Instead of fetch_assoc() you can also use:
fetch_row(): To get the query results in a numbered array.
fetch_object(): To get the results of the query in the form of an object.
fetch_array(): Can get the results in numbered as well as associative arrays.

Freeing Resoures at The End

At the end, you can free the resources by using:

$result->free_result();
$db->close();

Preparing, Binding and Querying Example

Database Table

Database Table
<?php
$db = new mysqli('localhost','root','','database');
if($db->connect_errno){
echo 'Error in database connection:'.$db->connect_errno;
echo '<br />';
echo $db->connect_error;
die();
}
$id=5;
$name="Ashish";
$result = $db->prepare("SELECT name,address FROM `table` WHERE name=? AND id < ?") or die($db->error);
$result->bind_param('si',$name,$id);
$result->execute();
$result->bind_result($name,$address);
while($result->fetch()){
echo $name." lives in ".$address;
echo "<br />";
}
?>