MySQLi is a new improved extension for accessing mysql database. It allows procedural and object oriented interface for accessing mysql database. Though you can use the old mysql functions but new mysqli offers security, advanced options, speed and similar syntax. So if you’re trying to write code using old mysql functions, just switch to mysqli. You have nothing to lose even if you don’t know how to use Object oriented way of accessing the database.
Advantages of using Mysqli over Old Mysql Extension
- Security
- Similar Syntax
- Maintainable
- Speed
- Advanced Option
- Support for procedural and OOP interface
In this tutorial, we are going to take a look at old way of accessing the database and then going to write the code for the procedural and object-oriented way of writing code.
Old way of accessing mysql database is something like this :
//connect to database
$con = mysql_connect("localhost", "username", "password");
// select database
//$db= mysql_select_db("database") or die(mysql_error());
//Write query
$query= "SELECT* from tablename";
//Fetch Results
$result=mysql_query($query);
//Display result
while($row=mysql_fetch_array($result))
{
// acccess the table elements and display it
}
//mysql close connection
mysql_close($con);
Now that was the old way for accessing the mysql database. We are going to take a look at how you’re going to access the database using new mysql drive – mysqli in procedural way.
Mysqli Procedural
//Connect to the database
$con=mysqli_connect("localhost", username", "password", "database") or die(mysqli_connect_error());
//Write query
$query="SELECT * from tablename";
//Fetch results
$result=mysqli_query($con, $query);
//Display results
while($row=mysqli_fetch_array($result))
{
//use row to fetch the element of each column
}
//close connection
mysqli_close($con);
That was easy, isn’t it? There is hardly anything you have to learn for accessing the database differently. Now we’ll take a look at how to access the database using object oriented interface.
Object Oriented Interface
//connect to database
$mysqli = new mysqli("localhost", "username", "password", "database");
//check for errors
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli->connect_error;
}
//write query
$res = $mysqli->query("SELECT * from tablename");
//fetch query
$row = $res->fetch_assoc();
//display results
echo $row['table_id'];
If you’re not comfortable with OOP then you can always go with the procedural way of accessing database. That doesn’t mean you should ignore the OOP way because If you’re dealing with postgresql or any other database, using the PDO is much better as it allows you to switch between the mysql or postgre without having to worry about extension or writing any different functions to access the database.
Note: Avoid mixing procedural and Object oriented style to access the database. Mysqli is more powerful and better way to access the database compared to the old way of accessing the database. Also learn about how to write the prepared statements using mysqli.
Where to go from here?
I suggest checking out the Mysqli Manual and learn about OO interface for accessing the database, If you haven’t already. That said, In future articles for PHP I am going to review PDO for accessing the mysql database. Please feel free to let me know If there are any errors in this article and also if you have anything to improve this article.