PHP + MySQL CRUD with PDO

PHP + MySQL CRUD

CRUD stands for Create, Read, Update, and Delete, the four basic things almost every database-backed app does. In PHP, the safe, modern way to talk to MySQL is PDO (PHP Data Objects) with prepared statements. This approach protects you from SQL injection, the most common database attack.

Important: the old mysql_* functions were removed years ago. Use PDO (shown here) or MySQLi. Never build queries by gluing user input into a string.

Connecting with PDO

First, create a connection. PDO works with MySQL and many other databases through one consistent interface.

<?php
$host = "localhost";
$db   = "infoplanet_demo";
$user = "root";
$pass = "";

// Data Source Name describes the connection.
$dsn = "mysql:host=$host;dbname=$db;charset=utf8mb4";

try {
    // Create the PDO connection and tell it to throw exceptions on errors.
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ]);
    echo "Connected.";
} catch (PDOException $e) {
    // Never show raw errors to users in production; log them instead.
    echo "Connection failed.";
}
?>

Assume $pdo from above is available in the examples below, and a table created like this:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL
);

Create: inserting a record

Use placeholders (? or :name) and pass the actual values separately. PDO handles escaping safely.

<?php
// Prepare a query with named placeholders.
$stmt = $pdo->prepare(
    "INSERT INTO students (name, email) VALUES (:name, :email)"
);

// Execute with the user-supplied values bound safely.
$stmt->execute([
    ":name"  => "Aditya",
    ":email" => "[email protected]",
]);

echo "New student id: " . $pdo->lastInsertId();
?>

Read: fetching records

<?php
// Read all students.
$stmt = $pdo->query("SELECT id, name, email FROM students");

// fetchAll returns an array of rows.
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($students as $row) {
    echo htmlspecialchars($row["name"]) . "<br>";
}
?>

To read one record by id, still use a prepared statement:

<?php
$stmt = $pdo->prepare("SELECT * FROM students WHERE id = :id");
$stmt->execute([":id" => 1]);

$student = $stmt->fetch(PDO::FETCH_ASSOC);

if ($student) {
    echo htmlspecialchars($student["email"]);
}
?>

Update: changing a record

<?php
$stmt = $pdo->prepare(
    "UPDATE students SET email = :email WHERE id = :id"
);

$stmt->execute([
    ":email" => "[email protected]",
    ":id"    => 1,
]);

// rowCount tells you how many rows were affected.
echo $stmt->rowCount() . " row updated.";
?>

Delete: removing a record

<?php
$stmt = $pdo->prepare("DELETE FROM students WHERE id = :id");
$stmt->execute([":id" => 1]);

echo $stmt->rowCount() . " row deleted.";
?>

Why prepared statements matter

When you bind values as parameters, the database treats them strictly as data, never as part of the SQL command. This blocks SQL injection, where an attacker tries to sneak commands through an input field. Building queries by concatenating user input, like "... WHERE name = '" . $_POST["name"] . "'", is unsafe and must be avoided.

Putting it together

A typical CRUD page flow:

1. Connect to the database once (often in a shared db.php).
2. Validate and clean any incoming form data.
3. Use a prepared statement for the operation.
4. Escape output with htmlspecialchars() when displaying results.

Common mistakes

• Using removed mysql_* functions. They no longer exist; use PDO or MySQLi.
• Concatenating user input into SQL. Always use prepared statements with placeholders.
• Showing raw database errors to users. Log them privately; show a friendly message.
• Forgetting htmlspecialchars() when displaying database values that originated from user input.

FAQ

Should I use PDO or MySQLi? Both support prepared statements. PDO works across many databases and has a clean interface, which makes it a great default choice.

What is SQL injection? It is an attack where malicious input changes the meaning of your query. Prepared statements prevent it by separating SQL from data.

Do I need a try/catch every time? Set PDO to throw exceptions once, then wrap database operations in try/catch where you want to handle failures gracefully.

Keep learning

Back to the PHP & MySQL hub, revisit Sessions & Cookies in PHP, or continue to PHP Project Ideas.

Build a complete database-driven app with mentor reviews. Join the waitlist for the PHP course at Infoplanet in Jalgaon.