CRUD Operations Using MySQLi

Let’s first look at a simple CRUD (Create, Read, Update, Delete) example using MySQLi.

MySQLi Example

<?php
// Database connection
$conn = new mysqli("localhost", "root", "", "example_db");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// CREATE
$sql = "INSERT INTO users (name, age) VALUES ('John Doe', 25)";
$conn->query($sql);

// READ
$sql = "SELECT * FROM users";
$result = $conn->query($sql);
while ($row = $result->fetch_assoc()) {
    echo $row['name'] . " - " . $row['age'] . "<br>";
}

// UPDATE
$sql = "UPDATE users SET age = 30 WHERE name = 'John Doe'";
$conn->query($sql);

// DELETE
$sql = "DELETE FROM users WHERE name = 'John Doe'";
$conn->query($sql);

$conn->close();
?>

 

Drawbacks of MySQLi

Vulnerability to SQL Injection
If user inputs are directly included in SQL statements without sanitization, attackers can inject malicious SQL. For example:

$sql = "SELECT * FROM users WHERE name = '" . $_GET['name'] . "'";

Without proper escaping, a user might input John'; DROP TABLE users; -- to delete your table.

Limited Flexibility
MySQLi only supports MySQL databases, making your code less portable if you want to switch databases in the future.

Lack of Prepared Statements
While MySQLi supports prepared statements, its implementation isn't as robust or straightforward as PDO.

CRUD Operations Using PDO

Why PDO?

PDO (PHP Data Objects) offers several advantages:

  1. Supports multiple databases (MySQL, SQLite, PostgreSQL, etc.).
  2. Provides better protection against SQL injection through prepared statements.
  3. Cleaner, more readable syntax for complex queries.

PDO CRUD Example

<?php
try {
    // Database connection
    $conn = new PDO("mysql:host=localhost;dbname=example_db", "root", "");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // CREATE
    $sql = "INSERT INTO users (name, age) VALUES (:name, :age)";
    $stmt = $conn->prepare($sql);
    $stmt->bindValue(':name', 'John Doe', PDO::PARAM_STR);
    $stmt->bindValue(':age', 25, PDO::PARAM_INT);
    $stmt->execute();

    // READ
    $sql = "SELECT * FROM users";
    $stmt = $conn->query($sql);
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['name'] . " - " . $row['age'] . "<br>";
    }

    // UPDATE
    $sql = "UPDATE users SET age = :age WHERE name = :name";
    $stmt = $conn->prepare($sql);
    $stmt->bindValue(':age', 30, PDO::PARAM_INT);
    $stmt->bindValue(':name', 'John Doe', PDO::PARAM_STR);
    $stmt->execute();

    // DELETE
    $sql = "DELETE FROM users WHERE name = :name";
    $stmt = $conn->prepare($sql);
    $stmt->bindValue(':name', 'John Doe', PDO::PARAM_STR);
    $stmt->execute();
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;
?>

 

Key PDO Features and Best Practices

1. Error Handling with ATTR_ERRMODE

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Ensures that PDO throws exceptions when errors occur, making debugging easier.
Prevents silent failures that can occur with default error handling.

2. Preventing SQL Injection with bindValue

Using bindValue or bindParam ensures that inputs are treated as specific data types, not executable SQL.

$stmt->bindValue(':name', 'John Doe', PDO::PARAM_STR);
$stmt->bindValue(':age', 25, PDO::PARAM_INT);
PDO::PARAM_STR: For strings.
PDO::PARAM_INT: For integers.
PDO::PARAM_BOOL: For boolean values.
PDO::PARAM_NULL: For NULL values.

3. Fetch Modes

You can customize how data is retrieved:

$stmt->fetch(PDO::FETCH_ASSOC); // Returns associative arrays
$stmt->fetch(PDO::FETCH_OBJ);   // Returns objects

4. Transactions

Transactions ensure that multiple queries either all succeed or fail as a unit.

try {
    $conn->beginTransaction();
    $conn->exec("INSERT INTO users (name, age) VALUES ('User1', 20)");
    $conn->exec("INSERT INTO users (name, age) VALUES ('User2', 25)");
    $conn->commit();
} catch (PDOException $e) {
    $conn->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}

Why PDO Is the Better Choice

Security
PDO's prepared statements and data type bindings offer superior protection against SQL injection compared to manual escaping in MySQLi.

Flexibility
With support for multiple databases, PDO ensures that your codebase is future-proof.

Scalability
PDO's advanced features, like transactions and custom fetch modes, make it ideal for large-scale applications.

Readable Syntax
Cleaner, more modular code simplifies debugging and maintenance.

Conclusion

While MySQLi may suffice for simple projects, PDO is the smarter choice for production-level applications. It combines security, flexibility, and advanced functionality, making it the gold standard for PHP database operations.

If you're starting a new project, skip MySQLi and go straight to PDO. It might take a little more effort to set up initially, but the long-term benefits far outweigh the cost. Secure, scalable, and efficient code is always worth it!