In any dynamic web application, database interaction is a critical component. PHP, being a versatile and widely used server-side scripting language, offers multiple ways to connect and interact with MySQL databases. In this blog post, we will cover all types of MySQL database connections in PHP using the latest PHP syntax, ensuring security, performance, and maintainability.
We’ll focus on three primary methods of connecting PHP to MySQL:
- MySQLi (MySQL Improved) – Object-oriented and Procedural
- PDO (PHP Data Objects)
- Legacy MySQL (Deprecated) - (for historical context, but not recommended)
Let’s dive into each connection type, explaining its strengths, use cases, and best practices.
MySQLi (MySQL Improved)
MySQLi (MySQL Improved) is an enhanced version of the deprecated MySQL extension. It provides both procedural and object-oriented APIs. MySQLi is the go-to choice for developers who want better functionality while connecting PHP with MySQL databases.
Key Benefits:
- Supports prepared statements, preventing SQL injection attacks.
- Offers multi-query support.
- Provides both object-oriented and procedural APIs for flexibility.
Object-Oriented MySQLi Connection Example:
<?php
class Database
{
private mysqli $connection;
public function __construct(string $host = 'localhost', string $user = 'root', string $password = '', string $database = 'test')
{
$this->connection = new mysqli($host, $user, $password, $database);
if ($this->connection->connect_error) {
die('Connection failed: ' . $this->connection->connect_error);
}
}
public function query(string $sql): mysqli_result|bool
{
return $this->connection->query($sql);
}
public function prepare(string $sql): mysqli_stmt|false
{
return $this->connection->prepare($sql);
}
public function close(): void
{
$this->connection->close();
}
}
Explanation:
- Connection: The constructor connects to the MySQL database using object-oriented MySQLi.
- Query Execution: The
query()method is used for simple SQL queries likeSELECT,INSERT,UPDATE, etc. - Prepared Statements: The
prepare()method is available for SQL statements requiring parameter binding, which is safer. - Close Connection: The
close()method ensures the connection is properly closed.
Using the MySQLi Class:
<?php
require_once 'Database.php';
try {
$db = new Database();
$result = $db->query('SELECT * FROM users');
while ($row = $result->fetch_assoc()) {
echo 'User: ' . $row['username'] . '<br>';
}
$db->close();
} catch (Exception $e) {
echo 'Error: ' . $e->getMessage();
}
MySQLi Procedural Connection Example:
If you prefer a procedural approach:
<?php
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'test';
$connection = mysqli_connect($host, $user, $password, $database);
if (!$connection) {
die('Connection failed: ' . mysqli_connect_error());
}
$sql = 'SELECT * FROM users';
$result = mysqli_query($connection, $sql);
while ($row = mysqli_fetch_assoc($result)) {
echo 'User: ' . $row['username'] . '<br>';
}
mysqli_close($connection);
Key Points:
- Prepared Statements: Use
$stmt = $connection->prepare($sql)for queries that require parameters. - Error Handling: Always check for connection and query errors using
$connection->connect_errorormysqli_error().
PDO (PHP Data Objects)
PDO is a powerful, flexible, and secure way to interact with databases in PHP. It offers a consistent interface for multiple databases, making it an ideal choice if you need to switch between databases like MySQL, PostgreSQL, or SQLite without changing much of your code.
Key Benefits:
- Database-agnostic: Supports multiple databases (MySQL, PostgreSQL, SQLite, etc.).
- Prepared Statements: Provides strong protection against SQL injection.
- Flexible Error Handling: Customizable exception and error modes.
PDO Connection Example:
<?php
class PDODatabase
{
private PDO $connection;
public function __construct(string $dsn, string $user = 'root', string $password = '')
{
try {
$this->connection = new PDO($dsn, $user, $password);
$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die('Connection failed: ' . $e->getMessage());
}
}
public function query(string $sql): PDOStatement|false
{
return $this->connection->query($sql);
}
public function prepare(string $sql): PDOStatement|false
{
return $this->connection->prepare($sql);
}
public function close(): void
{
$this->connection = null;
}
}
Explanation:
- Connection: The PDO connection uses a DSN (Data Source Name) string, making it flexible for various databases.
- Error Handling: PDO offers different error modes, and we are using
PDO::ERRMODE_EXCEPTIONto throw exceptions on errors. - Prepared Statements: The
prepare()method allows you to execute queries with bound parameters, which is crucial for security.
Example Usage:
<?php
require_once 'PDODatabase.php';
$dsn = 'mysql:host=localhost;dbname=test';
try {
$db = new PDODatabase($dsn);
$result = $db->query('SELECT * FROM users');
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
echo 'User: ' . $row['username'] . '<br>';
}
$db->close();
} catch (PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
Prepared Statements with PDO:
<?php
$stmt = $db->prepare('SELECT * FROM users WHERE username = :username');
$stmt->execute(['username' => 'john_doe']);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
echo 'User: ' . $user['username'];
Key Points:
- Security: Always use prepared statements when dealing with user input to avoid SQL injection.
- Flexibility: You can easily switch between databases by simply changing the DSN without rewriting your queries.
Legacy MySQL Extension (Deprecated)
The old MySQL extension (mysql_connect(), mysql_query(), etc.) is deprecated as of PHP 5.5 and was removed in PHP 7.0. You should avoid using it in modern applications.
Legacy MySQL Example (Not Recommended):
<?php
$connection = mysql_connect('localhost', 'root', '');
if (!$connection) {
die('Connection failed: ' . mysql_error());
}
mysql_select_db('test', $connection);
$result = mysql_query('SELECT * FROM users');
while ($row = mysql_fetch_assoc($result)) {
echo 'User: ' . $row['username'] . '<br>';
}
mysql_close($connection);
Why Not Use It?
- No Prepared Statements: Lacks modern security features, making it vulnerable to SQL injection.
- Deprecated: Removed in modern versions of PHP, so you cannot use it in PHP 7 or above.
Comparing MySQLi and PDO
Best Practices for PHP MySQL Database Connections
- Use Prepared Statements: Always use prepared statements to prevent SQL injection, especially when dealing with user input.
- Error Handling: Use exception-based error handling to catch connection or query failures early.
- Close Connections: Always close your database connections when they are no longer needed to free up resources.
- Choose PDO for Flexibility: If your application may need to support databases other than MySQL, opt for PDO.
- Avoid Deprecated Extensions: Never use the deprecated MySQL extension in modern PHP applications.
Conclusion
Whether you're using MySQLi or PDO, modern PHP provides you with secure and robust ways to connect and interact with MySQL databases. MySQLi offers flexibility for MySQL-only projects, while PDO is the best choice for database-agnostic applications. Whichever method you choose, always follow best practices like using prepared statements, handling errors properly, and closing connections after use.