If you’re using PHP 5 or later, you can use:
- MySQLi extension (the “i” stands for improved)
- PDO (PHP Data Objects) (PHP Data Objects)
When PHP was first made, it used the MySQL extension. But this extension stopped being used in 2012.
Should I use PDO or MySQLi?
If you want a short answer, “Whatever you like” would be the right answer.
MySQLi and PDO both have their interesting facts:
MySQLi will only work with MySQL databases, but PDO will work with 12 different database systems.
So, if you need to change your project so that it uses a different database, PDO makes it easy to do so. Only the connection string and a few queries need to be changed. With MySQLi, you will have to write the code all over again, including the queries.
Both MySQL and MySQLi are based on objects, but MySQLi also has a procedural API.
Both of them agree with Prepared Statements. SQL injection can be stopped with Prepared Statements, which are a very important part of web application security.
There are examples of MySQL in both MySQLi and PDO syntax.
In this chapter and the ones that follow, we show you three ways to work with PHP and MySQL:
- MySQLi (object-oriented) (object-oriented)
- MySQLi (procedural)
- PDO
MySQLi Installation
Most of the time, when the php5 mysql package is installed, the MySQLi extension is installed automatically on Linux and Windows.
Go to http://php.net/manual/en/mysqli.installation.php for information on how to set up.
Installation of PDO
Go to http://php.net/manual/en/pdo.installation.php for information on how to set up.
Start a Connection with MySQL
Before we can use the MySQL database, we need to be able to connect to the server:
Example (MySQLi Object-Oriented)
<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;// connection check
$link = new mysqli($servername, $username, $password);// connection check
if ($link->connect_error) {
die(“Connection failed: ” . $link->connect_error);
}
echo “Connected successfully”;
?>
Example (MySQLi Procedural)
<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;
// connection create
$link = mysqli_connect($servername, $username, $password);// connection check
if (!$link) {
die(“Connection failed: ” . mysqli_connect_error());
}
echo “Connected successfully”;
?>
PDO Example
<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;try {
$link = new PDO(“mysql:host=$servername;dbname=myDB”, $username, $password);
// set the PDO error mode to exception
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo “Connected successfully”;
} catch(PDOException $e) {
echo “Connection failed: ” . $e->getMessage();
}
?>
One of the best things about PDO is that it has an exception class to deal with any problems that might come up when we query our database. If an error occurs in the try block, the script stops running and goes straight to the first catch block.
Close the Connection
When the script is done, the connection will be closed on its own. Use the following to end the connection:
Object-oriented MySQLi:
$conn->close();
Procedures for MySQLi:
mysqli close($conn);
PDO:
$conn = null;