PHP and MySQL Connection: MySQLi vs PDO Explained
June 8, 2024 ⚊ 2 Min read ⚊ PHPConnecting PHP to MySQL is a common task in web development. Below are the steps to establish a connection using the MySQLi (MySQL Improved) extension and the PDO (PHP Data Objects) extension, which are the two most commonly used methods.
Method 1: Using MySQLi
MySQLi stands for MySQL Improved. It provides both procedural and object-oriented interfaces.
Procedural Style
Establish a connection:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
Close the connection:
<?php
mysqli_close($conn);
?>
Object-Oriented Style
Establish a connection:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
Close the connection:
<?php
$conn->close();
?>
Method 2: Using PDO
PDO (PHP Data Objects) is a more flexible way to connect to databases as it supports multiple database types.
Establish a connection:
<?php
$dsn = 'mysql:host=localhost;dbname=database';
$username = 'username';
$password = 'password';
try {
$conn = new PDO($dsn, $username, $password);
// Set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
Close the connection:
<?php
$conn = null;
?>
Summary
Both MySQLi and PDO extensions provide robust ways to connect to a MySQL database. MySQLi is specifically designed for MySQL and offers both procedural and object-oriented ways to connect, whereas PDO provides a more flexible object-oriented approach that can be used with different database types. Your choice between the two depends on your specific requirements and preferences.