How to Create a CRUD with PHP and PDO

2 min
Beginner

CRUD is the set of operations for creating, reading, updating, and deleting data in an application.

Let’s see how to implement each one using PDO and Prepared Statements to ensure security.

Imagine we manage a products table with the fields: id, name, price.

C - create (insert data)

To save new information. We use the SQL INSERT statement.

function createProduct($pdo, $name, $price) {
    try {
        // 1. Prepare the template with placeholders
        $sql = "INSERT INTO products (name, price) VALUES (:name, :price)";
        $stmt = $pdo->prepare($sql);

        // 2. Execute by passing the values
        $stmt->execute([
            'name' => $name,
            'price' => $price
        ]);

        // lastInsertId() is very useful: it returns the generated auto-increment ID
        return $pdo->lastInsertId();

    } catch (PDOException $e) {
        return "Error creating: " . $e->getMessage();
    }
}

// Usage:
$newId = createProduct($pdo, "Mechanical Keyboard", 59.99);
echo "Product created with ID: " . $newId;

Copied!

R - read (read data)

To display information. This can be reading the entire list or reading a single item.

A. Read all (List):

function getProducts($pdo) {
    // Since there are no external variables, we can use query() directly
    $stmt = $pdo->query("SELECT * FROM products");
    
    // Return all results as an associative array
    return $stmt->fetchAll();
}

Copied!

B. Read one (Detail):

function getProductById($pdo, $id) {
    $sql = "SELECT * FROM products WHERE id = :id";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(['id' => $id]);
    
    // Use fetch() because we only expect one row
    return $stmt->fetch();
}

Copied!

U - update (update data)

To modify existing records. The WHERE clause limits which rows change and prevents accidentally updating the entire table.

function updatePrice($pdo, $id, $newPrice) {
    try {
        $sql = "UPDATE products SET price = :price WHERE id = :id";
        $stmt = $pdo->prepare($sql);
        
        $stmt->execute([
            'price' => $newPrice,
            'id'     => $id
        ]);
        
        // rowCount() tells us how many rows were affected
        // If it returns 0, it means the ID didn't exist or the price was the same
        return $stmt->rowCount();

    } catch (PDOException $e) {
        return "Error updating: " . $e->getMessage();
    }
}

// Usage: Change the price of product 5 to 45.00
$rows = updatePrice($pdo, 5, 45.00);
echo "$rows products were updated.";

Copied!

D - delete (delete data)

To delete records. As with Update, the WHERE clause is mandatory to avoid disasters.

function deleteProduct($pdo, $id) {
    try {
        $sql = "DELETE FROM products WHERE id = :id";
        $stmt = $pdo->prepare($sql);
        
        $stmt->execute(['id' => $id]);
        
        if ($stmt->rowCount() > 0) {
            return true; // Successful deletion
        } else {
            return false; // That ID was not found
        }

    } catch (PDOException $e) {
        return "Error deleting: " . $e->getMessage();
    }
}

Copied!