Connecting PHP to MySQL Using PDO and MySQLi: The Ultimate Guide 🚀

zeroexp.dev ZeroonLeave a Comment on Connecting PHP to MySQL Using PDO and MySQLi: The Ultimate Guide 🚀
Connecting PHP to MySQL Using PDO and MySQLi: The Ultimate Guide 🚀

If you're working with a PHP website that needs to store and retrieve data, you'll need to connect it to a MySQL database. But which method should you use? PDO or MySQLi? 🤔

In this step-by-step guide, we’ll cover:
✅ Differences between PDO and MySQLi
✅ How to connect PHP to MySQL using both methods
✅ How to prevent SQL injection
✅ Building a mini-project

Let’s dive in! 🚀

🎯 PDO vs. MySQLi: Which One Should You Use?

Both PDO and MySQLi allow PHP to interact with MySQL, but they have key differences.

Feature PDO MySQLi
Supports MySQL? ✅ Yes ✅ Yes
Supports Other Databases? ✅ Yes (PostgreSQL, SQLite, etc.) ❌ No (Only MySQL)
Object-Oriented? ✅ Yes ✅ Yes
Prepared Statements? ✅ Yes ✅ Yes
More Secure? ✅ Yes ✅ Yes
Fetching Multiple Data Types? ✅ Yes ✅ Limited

🚀 Recommendation: Use PDO if you need flexibility and want the ability to switch databases in the future. Use MySQLi if you're only working with MySQL and want a slightly faster performance.

1️⃣ Connecting to MySQL Using PDO

db_pdo.php (Database Connection with PDO)

<?php
$host = "localhost";
$dbname = "zeroexp_dev";
$username = "root";
$password = "";

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully (PDO)";
} catch (PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}
?>

🔥 What’s happening?

  • new PDO(...) creates a connection to MySQL.
  • setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) enables error handling.
  • If something goes wrong, the script will catch the error and display a message.

2️⃣ Connecting to MySQL Using MySQLi

db_mysqli.php (Database Connection with MySQLi)

<?php
$host = "localhost";
$dbname = "zeroexp_dev";
$username = "root";
$password = "";

$conn = new mysqli($host, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully (MySQLi)";
?>

🔥 What’s happening?

  • new mysqli(...) connects to MySQL.
  • connect_error checks if the connection failed.
  • If something goes wrong, an error message is displayed.

3️⃣ Performing CRUD Operations (PDO vs. MySQLi)

1️⃣ Insert Data

Using PDO

<?php
require "db_pdo.php";

$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(["Zero Dev", "contact@zeroexp.dev"]);

echo "User added!";
?>

Using MySQLi

<?php
require "db_mysqli.php";

$name = "Zero Dev";
$email = "contact@zeroexp.dev";

$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
$stmt->execute();

echo "User added!";
?>

🔥 Why Use Prepared Statements?
✅ Prevents SQL Injection
✅ Ensures data safety
✅ Allows parameter binding

2️⃣ Fetch Data

Using PDO

<?php
require "db_pdo.php";

$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($users as $user) {
    echo "{$user['id']}: {$user['name']} ({$user['email']}) <br>";
}
?>

Using MySQLi

<?php
require "db_mysqli.php";

$result = $conn->query("SELECT * FROM users");

while ($user = $result->fetch_assoc()) {
    echo "{$user['id']}: {$user['name']} ({$user['email']}) <br>";
}
?>

🔥 What’s Happening?

  • PDO: Uses fetchAll(PDO::FETCH_ASSOC) to get all rows as an array.
  • MySQLi: Uses fetch_assoc() inside a loop to retrieve one row at a time.

3️⃣ Update Data

Using PDO

<?php
require "db_pdo.php";

$stmt = $pdo->prepare("UPDATE users SET email = ? WHERE name = ?");
$stmt->execute(["newemail@zeroexp.dev", "Zero Dev"]);

echo "User updated!";
?>

Using MySQLi

<?php
require "db_mysqli.php";

$new_email = "newemail@zeroexp.dev";
$name = "Zero Dev";

$stmt = $conn->prepare("UPDATE users SET email = ? WHERE name = ?");
$stmt->bind_param("ss", $new_email, $name);
$stmt->execute();

echo "User updated!";
?>

🔥 Why Use Prepared Statements?

  • Prevents SQL injection
  • Ensures safe database updates

4️⃣ Delete Data

Using PDO

<?php
require "db_pdo.php";

$stmt = $pdo->prepare("DELETE FROM users WHERE name = ?");
$stmt->execute(["Zero Dev"]);

echo "User deleted!";
?>

Using MySQLi

<?php
require "db_mysqli.php";

$name = "Zero Dev";

$stmt = $conn->prepare("DELETE FROM users WHERE name = ?");
$stmt->bind_param("s", $name);
$stmt->execute();

echo "User deleted!";
?>

🔥 What’s Happening?

  • Deletes a user based on name.
  • Uses prepared statements to prevent SQL injection.

🎯 Mini Project: Simple User Management System

Let’s put everything together into a mini CRUD app.

1️⃣ user_list.php (Display Users)

<?php
require "db_pdo.php";
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($users as $user) {
    echo "{$user['id']}: {$user['name']} ({$user['email']}) 
          <a href='edit_user.php?id={$user['id']}'>Edit</a> | 
          <a href='delete_user.php?id={$user['id']}'>Delete</a><br>";
}
echo "<a href='add_user.php'>Add New User</a>";
?>

2️⃣ add_user.php (Add User Form)

<form method="post" action="insert.php">
    Name: <input type="text" name="name"><br>
    Email: <input type="email" name="email"><br>
    <input type="submit" value="Add User">
</form>

3️⃣ insert.php (Insert New User)

<?php
require "db_pdo.php";
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute([$_POST['name'], $_POST['email']]);
header("Location: user_list.php");
?>

🔥 Boom! You now have a fully functional user management system! 🚀

🚀 Final Thoughts

Now you know:
✅ How to connect PHP to MySQL using PDO and MySQLi
✅ How to perform CRUD operations
✅ How to prevent SQL injection
✅ How to build a mini CRUD app

👉 Next: Preventing SQL Injection with Prepared Statement in PHP

Happy coding! 🎉🚀

Leave a Reply