PHP offers powerful extensions for connecting to and interacting with databases. Two commonly used extensions are MySQLi (MySQL Improved) and PDO (PHP Data Objects). In this guide, we'll explore how to connect to databases using both MySQLi and PDO extensions in PHP, along with examples of CRUD (Create, Read, Update, Delete) operations.
MySQLi Extension
MySQLi is a PHP extension that provides an improved interface for interacting with MySQL databases. It offers both procedural and object-oriented approaches for database operations.
Connecting to MySQL Database
First, let's establish a connection to a MySQL database using MySQLi extension. We'll create a PHP script (mysqli_connect.php) to connect to the database server and select a specific database.
// Database configuration
$host = "localhost";
$username = "username";
$password = "password";
$database = "dbname";
// Create connection
$conn = new mysqli($host, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} else {
echo "Connected successfully";
}
?>
Performing CRUD Operations
Next, let's demonstrate CRUD operations (Create, Read, Update, Delete) using MySQLi extension.
Create Operation (Insert Data)
$sql = "INSERT INTO users (username, email) VALUES ('JohnDoe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
Read Operation (Select Data)
$sql = "SELECT id, username, email FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Username: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 results";
}
Update Operation (Modify Data)
$sql = "UPDATE users SET email='john.doe@example.com' WHERE username='JohnDoe'";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
Delete Operation (Remove Data)
$sql = "DELETE FROM users WHERE username='JohnDoe'";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
PDO Extension
PDO (PHP Data Objects) is a database abstraction layer that provides a consistent interface for accessing different databases. It supports various database drivers, including MySQL, PostgreSQL, SQLite, and more.
Connecting to MySQL Database
Let's establish a connection to a MySQL database using PDO extension. We'll create a PHP script (pdo_connect.php) to connect to the database server and select a specific database.
// Database configuration
$host = "localhost";
$username = "username";
$password = "password";
$database = "dbname";
try {
// Create connection
$conn = new PDO("mysql:host=$host;dbname=$database", $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();
}
?>
Performing CRUD Operations
Let's demonstrate CRUD operations using PDO extension.
Create Operation (Insert Data)
$username = "JohnDoe";
$email = "john@example.com";
$sql = "INSERT INTO users (username, email) VALUES (:username, :email)";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':username', $username);
$stmt->bindParam(':email', $email);
if ($stmt->execute()) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $stmt->errorInfo();
}
Read Operation (Select Data)
$sql = "SELECT id, username, email FROM users";
$stmt = $conn->prepare($sql);
$stmt->execute();
if ($stmt->rowCount() > 0) {
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "id: " . $row["id"]. " - Username: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 results";
}
Update Operation (Modify Data)
$newEmail = "john.doe@example.com";
$username = "JohnDoe";
$sql = "UPDATE users SET email=:email WHERE username=:username";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':email', $newEmail);
$stmt->bindParam(':username', $username);
if ($stmt->execute()) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $stmt->errorInfo();
}
Delete Operation (Remove Data)
$username = "JohnDoe";
$sql = "DELETE FROM users WHERE username=:username";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':username', $username);
if ($stmt->execute()) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $stmt->errorInfo();
}
Practice Excercise Practice now