Build Your Own Generic CRUD API in PHP

A generic CRUD API in PHP is the ultimate tool for developers who value simplicity, reusability, and scalability. Imagine having a single API that can handle Create, Read, Update, Delete operations across multiple database tables without duplicating logic. Sounds perfect, right? In this blog, we’ll guide you step-by-step to create such an API using modern PHP practices, including PDO for secure database interactions, prepared statements to prevent SQL injection, and modularized architecture for better maintainability.

By the end of this tutorial, you’ll have a ready-to-use PHP CRUD API that’s secure, scalable, and packed with best practices. Let’s dive in!

Why Build a Generic CRUD API in PHP?

A generic CRUD API isn’t just a time-saver, it’s a game-changer. Here’s why every developer should have one in their toolkit:

1. Reusability: Use the same API for different database tables with minimal changes.
2. Scalability: Seamlessly extend functionality as your application grows.
3. Modularity: Keep each CRUD operation isolated for easy debugging and maintenance.
4. Security: With prepared statements, you’re protected against SQL injection out of the box.

Whether you’re building a blog, an e-commerce platform, or a full-blown SaaS application, a generic CRUD API is your best friend.

Step 1: Setting Up the Project Structure

To keep things organized, we’ll use a clean folder structure:

/project-folder
    ├── includes/
    │     └── config.php       # Database connection logic
    ├── actions/
    │     ├── create.php       # Handles Create operations
    │     ├── read.php         # Handles Read operations
    │     ├── update.php       # Handles Update operations
    │     └── delete.php       # Handles Delete operations
    └── crud.php               # Central API endpoint

Why This Structure?

• Centralized Entry Point: The crud.php file acts as the main gateway for all CRUD requests.
• Modular Logic: Each CRUD operation (Create, Read, Update, Delete) has its own script for better separation of concerns.
• Reusable Configuration: The config.php file manages the database connection, keeping credentials in one secure location.

Step 2: Central API Endpoint (crud.php)

The crud.php file is the heart of the API. It handles incoming requests, validates them, and routes them to the appropriate script in the actions/ folder.

How It Works:

1. Dynamic Routing: Routes requests based on the action parameter (e.g., create, read, update, delete).
2. Unified API: Exposes a single endpoint for all CRUD operations.
3. Error Handling: Ensures meaningful error messages are returned for invalid requests.

<?php
header('Content-Type: application/json');

// Capture request data
$action = $_GET['action'] ?? $_POST['action'] ?? null;
$table = $_GET['table'] ?? $_POST['table'] ?? null;
$id = $_GET['id'] ?? $_POST['id'] ?? null;
$data = $_POST['data'] ?? [];

// Validate input
if (!$action || !$table) {
    echo json_encode(["success" => false, "message" => "Action and table are required."]);
    exit;
}

// Route request to appropriate script
switch (strtolower($action)) {
    case 'create':
        include 'actions/create.php';
        createRecord($table, $data);
        break;
    case 'read':
        include 'actions/read.php';
        readRecords($table, $id);
        break;
    case 'update':
        include 'actions/update.php';
        updateRecord($table, $data, $id);
        break;
    case 'delete':
        include 'actions/delete.php';
        deleteRecord($table, $id);
        break;
    default:
        echo json_encode(["success" => false, "message" => "Invalid action specified."]);
}
?>

Step 3: Database Configuration (includes/config.php)

The config.php file handles the database connection securely using PDO (PHP Data Objects). It provides a flexible and secure way to interact with your database.

<?php
define('DB_HOST', 'PLACEHOLDERDBHOST');
define('DB_USER', 'PLACEHOLDERDBUSER');
define('DB_PASS', 'PLACEHOLDERDBPASSWORD');
define('DB_NAME', 'PLACEHOLDERDBNAME');

function getDbConnection() {
    try {
        $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4";
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ];
        return new PDO($dsn, DB_USER, DB_PASS, $options);
    } catch (PDOException $e) {
        die(json_encode(["success" => false, "message" => "Database connection failed: " . $e->getMessage()]));
    }
}
?>

Why Use PDO?

• Prepared Statements: Prevent SQL injection by separating query logic from input data.
• Exception Handling: Provides detailed error messages for easier debugging.
• Cross-Database Compatibility: PDO supports multiple database types, making it future-proof.

Step 4: CRUD Functions

Create Function (actions/create.php)

Securely inserts data into the specified table.

<?php
include '../includes/config.php';

function createRecord($table, $data) {
    $conn = getDbConnection();

    $columns = implode(", ", array_keys($data));
    $placeholders = implode(", ", array_fill(0, count($data), '?'));
    $values = array_values($data);

    $sql = "INSERT INTO $table ($columns) VALUES ($placeholders)";
    $stmt = $conn->prepare($sql);

    if ($stmt->execute($values)) {
        echo json_encode(["success" => true, "message" => "Record created successfully."]);
    } else {
        echo json_encode(["success" => false, "error" => $stmt->errorInfo()]);
    }
}
?>

Read Function (actions/read.php)

Reads all records or a specific record if an ID is provided.

<?php
include '../includes/config.php';

function readRecords($table, $id = null) {
    $conn = getDbConnection();

    $sql = $id ? "SELECT * FROM $table WHERE id = ?" : "SELECT * FROM $table";
    $stmt = $conn->prepare($sql);
    $id ? $stmt->execute([$id]) : $stmt->execute();

    $result = $stmt->fetchAll();
    if ($result) {
        echo json_encode(["success" => true, "data" => $result]);
    } else {
        echo json_encode(["success" => false, "message" => "No records found."]);
    }
}
?>

Update Function (actions/update.php)

Updates a record based on the given ID.

<?php
include '../includes/config.php';

function updateRecord($table, $data, $id) {
    if (!$id) {
        echo json_encode(["success" => false, "message" => "ID is required for update."]);
        return;
    }

    $conn = getDbConnection();

    $setClause = implode(", ", array_map(fn($key) => "$key = ?", array_keys($data)));
    $values = array_values($data);
    $values[] = $id;

    $sql = "UPDATE $table SET $setClause WHERE id = ?";
    $stmt = $conn->prepare($sql);

    if ($stmt->execute($values)) {
        echo json_encode(["success" => true, "message" => "Record updated successfully."]);
    } else {
        echo json_encode(["success" => false, "error" => $stmt->errorInfo()]);
    }
}
?>

Delete Function (actions/delete.php)

Deletes a record based on the given ID.

<?php
include '../includes/config.php';

function deleteRecord($table, $id) {
    if (!$id) {
        echo json_encode(["success" => false, "message" => "ID is required for delete."]);
        return;
    }

    $conn = getDbConnection();

    $sql = "DELETE FROM $table WHERE id = ?";
    $stmt = $conn->prepare($sql);

    if ($stmt->execute([$id])) {
        echo json_encode(["success" => true, "message" => "Record deleted successfully."]);
    } else {
        echo json_encode(["success" => false, "error" => $stmt->errorInfo()]);
    }
}
?>

Best Practices and Disclaimers

1. Password Handling: If the API is used for handling sensitive data like passwords, always hash passwords using PHP’s password_hash() function before storing them in the database.
   $hashedPassword = password_hash($plainPassword, PASSWORD_BCRYPT);
2. Hardcode Table Names: Avoid passing table names directly from user input in production. Use a predefined list or mapping to validate table names.
   $allowedTables = ['users', 'products'];
if (!in_array($table, $allowedTables)) {
die(json_encode(["success" => false, "message" => "Invalid table name."]));
}
3. Sanitize Inputs: Even with prepared statements, validate and sanitize user inputs to ensure data integrity.
4. Authentication: Protect your API with authentication (e.g., API keys, OAuth) to prevent unauthorized access.

Final Words

This generic CRUD API in PHP is not just a functional solution, it’s a scalable foundation for your web applications. By following best practices like modularization, SQL injection prevention, and proper input validation, you ensure the reliability and security of your system.

Start building your generic CRUD API today and experience the power of clean, maintainable PHP development!

If you have any suggestions or questions do comment down below. If you want to learn about how to develop a Webserver in PHP, you can check that out here