What is MYSQLi and why you should use that instead of MYSQL?
In every site there is some sort of storage for saving data. Programs like MYSQL and PDO, just let you store and retrieve data in database. These programs can be known as software. Most of the sites use MYSQL database when using PHP. But many people till now didn’t interact with database properly in PHP. To use database accurately in PHP, MYSQLi database is the perfect one for web-applications, it protects website from harmful SQL injection. MYSQLi is the improved version of MYSQL and it’s “i” stand for improved version of MYSQL.
MYSQL supports only PHP5 but on the other side its improved version MYSQLi supports each version of PHP till the PHP7. MYSQLi is the improved, advanced and polished version of MYSQL. PDO is also one of the most efficient database. PDO and MYSQLi both have their own advantages which makes websites more secure.
Basic usage of MYSQLi:
- Connect data
MYSQLI CONNECTION – PROCEDURAL WAY
$connection = mysqli_connect(“hostname”, “username”, “password”, “database”);
// Check connection
if($connection === false){
die(“ERROR: Could not connect. ” . mysqli_connect_error());
}
- Select data
<?php
// To establish a simple MYSQLI connection
$link = mysqli_connect(“localhost”, “root”, “”, “demo”);
// Check connection
if($link === false){
die(“ERROR: Could not connect. ” . mysqli_connect_error());
}
// SELECT Query using MYSQLI
$sql = “SELECT * FROM users”;
if($result = mysqli_query($link, $sql)){
if(mysqli_num_rows($result) > 0){
echo “<table>”;
echo “<tr>”;
echo “<th>id</th>”;
echo “<th>first_name</th>”;
echo “<th>last_name</th>”;
echo “<th>email</th>”;
echo “</tr>”;
while($row = mysqli_fetch_array($result)){
echo “<tr>”;
echo “<td>” . $row[‘id’] . “</td>”;
echo “<td>” . $row[‘first_name’] . “</td>”;
echo “<td>” . $row[‘last_name’] . “</td>”;
echo “<td>” . $row[’email’] . “</td>”;
echo “</tr>”;
}
echo “</table>”;
// mysqli free result set
mysqli_free_result($result);
} else{
echo “No records matching your query were found.”;
}
} else{
echo “ERROR: Could not able to execute $sql. ” . mysqli_error($link);
}
// Close connection
mysqli_close($link);
?>
- Insert data
<?php
// To establish a simple MYSQLI connection
$connection = mysqli_connect(“localhost”, “root”, “”, “demo”);
// Check connection
if($connection === false){
die(“ERROR: Could not connect. ” . mysqli_connect_error());
}
// INSERT Query using MYSQLI
$sql = “INSERT INTO persons (first_name, last_name, email) VALUES (‘John’, ‘Doe’, ‘johndoe@mail.com’)”;
if(mysqli_query($connection, $sql)){
echo “Records inserted successfully.”;
} else{
echo “ERROR: Could not able to execute $sql. ” . mysqli_error($connection);
}
// Close connection
mysqli_close($connection);
?>
- Update data
<?php
// To establish a simple MYSQLI connection
$link = mysqli_connect(“localhost”, “root”, “”, “demo”);
// Check connection
if($link === false){
die(“ERROR: Could not connect. ” . mysqli_connect_error());
}
// Attempt update query execution
$sql = “UPDATE users SET email=’johndoe@mail.com’ WHERE id=’1′”;
if(mysqli_query($link, $sql)){
echo “Records were updated successfully.”;
} else {
echo “ERROR: Could not able to execute $sql. ” . mysqli_error($link);
}
// Close connection
mysqli_close($link);
?>
- Delete records
<?php
// To establish a simple MYSQLI connection
$link = mysqli_connect(“localhost”, “root”, “”, “demo”);
// Check connection
if($link === false){
die(“ERROR: Could not connect. ” . mysqli_connect_error());
}
// DELETE Query for MYSQLI
$sql = “DELETE FROM users WHERE first_name=’John'”;
if(mysqli_query($link, $sql)){
echo “Records were deleted successfully.”;
} else{
echo “ERROR: Could not able to execute $sql. ” . mysqli_error($link);
}
// Close connection
mysqli_close($link);
?>
Benefits of MYSQLi:
1. Object oriented
MYSQLi supports object oriented programming, as well as procedural. With its help, method and property names are more readable than procedural syntax. Importance of Object oriented method in MYSQLi is that you can create your own classes as extensions which makes it easy and extensible.
- More efficient than MYSQL
– MYSQLi is faster, efficient and totally secure against SQL injection.
– MYSQLi has more interfacing capabilities
– It supports prepared and multiple statements
– It enhanced debugging support
- Easy transactions
MYSQLi brings the ability to use transactions with a group of queries which execute efficiently but don’t save their effects in the database. - MYSQLi is a new form of database interaction
MYSQL is now an old method, but some people are still using it because it is easier and quicker method. But MYSQLi, the upgraded version of MYSQL has the ability to process Multiple statements. MYSQLi has good support and maintenance, that makes it safe and secure.5. Create better code
A better and improved code can be created with its help. - Winner over MYSQL
MYSQLi is clearly a winner over the regular MYSQL extension in PHP, and implementation is also not that different. - Migrate & build good projects
MYSQLi helps in migrate and build good projects in present and future also.
About the Author
