Connecting to MySQL with PHP
For PHP 5 and later versions, it is recommended to connect to MySQL using the following methods:
- MySQLi extension ("i" stands for improved)
- PDO (PHP Data Objects)
In earlier versions of PHP, we used the MySQL extension. However, this extension has been deprecated since 2012.
Should I use MySQLi or PDO?
For a quick answer, it's "use whichever you are comfortable with."
MySQLi and PDO have their own advantages:
PDO works with 12 different database systems, while MySQLi is specific to MySQL.
Therefore, if your project requires switching between multiple databases, PDO is recommended. You only need to modify the connection string and some query statements. With MySQLi, if you switch databases, you need to rewrite all the code, including queries.
Both are object-oriented, but MySQLi also provides an API interface.
Both support prepared statements. Prepared statements help prevent SQL injection, which is crucial for web application security.
MySQLi and PDO Examples to Connect to MySQL
In this and the following sections, we will demonstrate PHP operations on MySQL using the following three methods:
- MySQLi (Object-oriented)
- MySQLi (Procedural)
- PDO
MySQLi Installation
On Linux and Windows, the MySQLi extension is usually installed automatically when the PHP5 MySQL package is installed.
For detailed installation information, see: http://php.net/manual/en/mysqli.installation.php
You can check if the installation was successful using phpinfo():
PDO Installation
For detailed installation information, see: http://php.net/manual/en/pdo.installation.php
You can check if the installation was successful using phpinfo():
Connecting to MySQL
Before we can access the MySQL database, we need to connect to the database server:
Example (MySQLi - Object-oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
Note: In the above object-oriented example, $connect_error
was added in PHP 5.2.9 and 5.3.0. If you need to be compatible with earlier versions, use the following code instead:
// Check connection
if (mysqli_connect_error()) {
die("Database connection failed: " . mysqli_connect_error());
}
Example (MySQLi - Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
try {
$conn = new PDO("mysql:host=$servername;", $username, $password);
echo "Connected successfully";
} catch (PDOException $e) {
echo $e->getMessage();
}
?>
Note: In the above PDO example, we have specified the database (myDB). PDO requires the database name during the connection process. If not specified, it will throw an exception.
Closing the Connection
To close the connection, you can simply unset the connection object or call the close()
method for MySQLi, or set the PDO connection object to null
.
The connection will automatically close after the script execution. You can also use the following code to close the connection:
Example (MySQLi - Object-Oriented)
$conn->close();
Example (MySQLi - Procedural)
mysqli_close($conn);
Example (PDO)
$conn = null;