Build a REST API with PHP and MySQL

Build a full CRUD Products API using pure PHP, PDO, and MySQL.

Introduction

30 Second Summary

Every time you add something to a shopping cart, search for a flight, or check your bank balance, an API is doing the work behind the scenes. APIs are the invisible layer connecting apps to their data.

In this project, you will build a Products REST API from scratch using pure PHP and MySQL. You'll handle creating, reading, updating, and deleting products through HTTP requests that talk directly to a database.

What You'll Build

A fully functional Products REST API that supports full CRUD operations, returns consistent JSON responses with proper HTTP status codes, and uses prepared statements for secure database access.

By the end of this project, you'll have:

  • A PDO database connection class with error handling and security settings.
  • A Product model with secure CRUD methods using prepared statements that prevent SQL injection.
  • API endpoint files that route HTTP requests, validate input, and return consistent JSON responses.
  • Secret Mission: Add API key authentication to protect your endpoints from unauthorized access.

Are there any prerequisites?

This project assumes basic PHP knowledge (variables, arrays, functions, classes) and basic SQL knowledge (SELECT, INSERT, UPDATE, DELETE). You do not need any framework experience.

Before We Start

Every web and mobile app you use relies on a backend API to handle data. When you open a shopping app and browse products, an API is fetching that data from a database and sending it back as a structured response.

In this project, you will build a Products REST API from scratch using pure PHP and MySQL. Before writing any API code, you need to verify your tools are ready and set up a clean project structure.

In this step, get ready to:
  • Verify your PHP installation and required extensions.
  • Create the project folder structure.
  • Test the PHP built-in server with a JSON response.
Verify PHP and extensions

This project requires PHP 8.0 or higher and the pdo_mysql extension. Let's confirm both are present on your machine.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or PowerShell (Windows) and press Enter to open it.
  • Check your PHP version by running this command:
php -v

Look at the first line of output. It will show something like PHP 8.x.x followed by the build date.

✔️ I see version 8.0 or higher

Your PHP version is ready. Move on to the next check below.

ⓧ I see a version lower than 8.0

You need to upgrade PHP to version 8.0 or higher.

  • On macOS, run brew install php to install the latest PHP version via Homebrew. After installation, restart your terminal so the new version takes priority.
  • On Windows, download the latest PHP ZIP from php.net/downloads. Extract it to a folder like C:\php and add that folder to your system PATH environment variable.

After upgrading, run php -v again to confirm the new version.

ⓧ Command not found

PHP is not installed on your machine yet.

  • On macOS, install PHP using Homebrew by running brew install php in your terminal.
  • On Windows, download PHP from php.net/downloads. Extract the ZIP to C:\php and add that folder to your system PATH.

After installing, close and reopen your terminal, then run php -v to verify.

  • Now check that the pdo_mysql extension is available by running:
php -m

This prints a list of all installed PHP extensions. Scroll through and look for pdo_mysql in the list.

✔️ I see pdo_mysql in the list

Your database driver is ready. You have everything you need to continue.

ⓧ I don't see pdo_mysql

The pdo_mysql extension is not enabled.

  • On Windows, find your php.ini file (in your PHP installation folder). Open it in a text editor and find the line ;extension=pdo_mysql. Remove the semicolon at the start to uncomment it, then save the file.
  • On macOS with Homebrew, pdo_mysql is included by default. If it is missing, reinstall PHP with brew reinstall php.

After making changes, run php -m again to confirm pdo_mysql now appears.

Create the project structure

A clean folder structure keeps your API code organized. You will separate database config, models, helpers, and endpoint files into their own directories.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir products-api
cd products-api
  • Create the subdirectories for your project by running:
mkdir config models helpers api
  • Confirm the folders exist by running:
ls

You should see api, config, helpers, and models listed in the output.

What is this structure for?

Each folder has a specific responsibility. config/ holds database connection settings. models/ holds classes that interact with database tables. helpers/ holds reusable utility classes. api/ holds the endpoint files that handle HTTP requests.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type VS Code and press Enter to open it.
  • In VS Code, click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop, select the products-api folder, then click Open.

You should see the api, config, helpers, and models folders in the VS Code Explorer sidebar on the left.

Test the PHP built-in server

PHP includes a built-in web server designed for development. You will create a simple index.php file that returns a JSON welcome message, then start the server to confirm everything works.

  • In VS Code, right-click inside the Explorer sidebar (not inside any folder).
  • Click New File.
  • Type index.php and press Enter.
  • Paste the following code into index.php:
<?php

// Set the response content type to JSON
header('Content-Type: application/json');

// Return a welcome message with available endpoints
echo json_encode([
    'status' => 'success',
    'message' => 'Products API is running',
    'endpoints' => [
        'GET /api/products.php' => 'List all products',
        'GET /api/products.php?id=1' => 'Get a single product',
        'POST /api/products.php' => 'Create a product',
        'PUT /api/products.php?id=1' => 'Update a product',
        'DELETE /api/products.php?id=1' => 'Delete a product',
    ]
]);

What does this code do?

header('Content-Type: application/json') tells the browser (or any client) that this response contains JSON data, not HTML.

json_encode() converts a PHP array into a JSON string. The array lists the API endpoints you will build throughout this project.

  • Save index.php by pressing Cmd+S (macOS) or Ctrl+S (Windows).
  • Open the integrated terminal in VS Code by pressing Ctrl+` (backtick key, same shortcut on both platforms).
  • Start the PHP built-in server by running:
php -S localhost:8000

What does this command do?

php -S localhost:8000 starts a development web server on your machine at port 8000. It serves files from the current working directory (your products-api folder).

This server is single-threaded and meant for development only. Your terminal will stay occupied while the server runs.

  • Open your browser and navigate to http://localhost:8000.

You should see a JSON response with the status field set to success and a list of endpoints displayed in your browser.

Don't see JSON in your browser?

  • Make sure the terminal shows PHP Development Server started with no errors. If it shows an error about the port being in use, try php -S localhost:8001 instead.
  • Confirm you are in the products-api folder. Run ls and check that index.php appears. If not, cd into the correct directory and restart the server.
  • Check for typos in index.php. A missing semicolon or bracket will cause a blank page or an error.

help me debug why my PHP server isn't showing JSON output

  • Once you have confirmed the JSON response works, stop the server by pressing Ctrl+C in the terminal.

✔️ Awesome, I've got everything!

Great. Make sure you have saved index.php before moving on.

ⓧ I'd like to double check the full code

Here is the complete index.php file:

<?php

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

echo json_encode([
    'status' => 'success',
    'message' => 'Products API is running',
    'endpoints' => [
        'GET /api/products.php' => 'List all products',
        'GET /api/products.php?id=1' => 'Get a single product',
        'POST /api/products.php' => 'Create a product',
        'PUT /api/products.php?id=1' => 'Update a product',
        'DELETE /api/products.php?id=1' => 'Delete a product',
    ]
]);

Your environment is ready and your project structure is in place. Next up, you will create the MySQL database and build a connection class to talk to it from PHP.

Set Up the Database and Connection Class

Your project folder is structured and PHP is serving JSON responses. Now the API needs somewhere to store its data.

Every REST API needs a data layer that persists information between requests. In this step, you'll create a MySQL database for your products and build a PHP class that manages the connection using PDO.

In this step, get ready to:
  • Create a MySQL database with a products table.
  • Build a reusable Database connection class with PDO.
  • Test that the connection works.
Create the database and products table

MySQL will store all the product data your API creates, reads, updates, and deletes. You need a database and a table with columns for each piece of product information.

  • Log into the MySQL client by running this command:
mysql -u root

Can't connect to MySQL?

If you see an error about MySQL not being found, make sure MySQL is installed and the mysql executable is in your system PATH.

If you see "Access denied", your MySQL root user may have a password. Try mysql -u root -p instead and enter your password when prompted.

If MySQL is installed but not running, start the MySQL service first. On macOS with Homebrew, run brew services start mysql. On Windows, search for "Services" and start the MySQL service.

help me connect to MySQL on my machine

  • Create the products_api database and products table by running these SQL commands:
CREATE DATABASE products_api;
USE products_api;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT DEFAULT 0,
    category VARCHAR(255) DEFAULT 'general',
    created_at DATETIME
);

What does this SQL do?

  • CREATE DATABASE products_api creates a fresh database to hold all your API data.
  • USE products_api switches to that database so all following commands run inside it.
  • id INT AUTO_INCREMENT PRIMARY KEY gives every product a unique number that increments automatically.
  • price DECIMAL(10, 2) stores prices with exactly two decimal places, avoiding floating-point rounding issues.
  • DEFAULT 'general' on category means products get a category even if the API caller doesn't specify one.
  • Confirm the table was created by running:
DESCRIBE products;

You should see a table with seven rows listing the columns: id, name, description, price, quantity, category, and created_at. Each row shows the column's Field, Type, Null, Key, Default, and Extra values.

  • Exit the MySQL client by running:
exit;

Table not showing up?

Make sure you ran USE products_api; before the DESCRIBE command. Without selecting the database first, MySQL doesn't know which table to look for.

If you see a syntax error, check that every SQL statement ends with a semicolon.

help me fix my MySQL table creation

Build the Database class

PDO (PHP Data Objects) is PHP's standard interface for database access. Wrapping the connection in a class lets you reuse it across your entire API without repeating credentials in every file.

  • In VS Code, create a new file called Database.php inside the config/ folder of your products-api project.
  • Add this code to config/Database.php:
<?php

class Database
{
    private string $host = '127.0.0.1';
    private string $dbName = 'products_api';
    private string $username = 'root';
    private string $password = '';
    private ?PDO $connection = null;

    public function getConnection(): PDO
    {
        if ($this->connection === null) {
            $dsn = "mysql:host={$this->host};dbname={$this->dbName};charset=utf8mb4";

            $this->connection = new PDO($dsn, $this->username, $this->password, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ]);
        }

        return $this->connection;
    }
}

What does this code do?

  • The class stores database credentials as private properties. The ?PDO type hint means the connection starts as null and only gets created when needed.
  • getConnection() checks if a connection already exists before creating one. This avoids opening duplicate connections on repeated calls.
  • ERRMODE_EXCEPTION makes PDO throw exceptions on errors instead of failing silently. This makes bugs surface immediately.
  • FETCH_ASSOC returns query results as associative arrays (key-value pairs) instead of numbered arrays.
  • ATTR_EMULATE_PREPARES => false sends real prepared statements to MySQL instead of simulating them in PHP. This provides stronger SQL injection protection.

Using a MySQL password?

If your MySQL root user has a password, update the $password property in the class to match. For example: private string $password = 'yourpassword';.

  • Save config/Database.php.

You won't see any output yet. The next substep tests this connection.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

<?php

class Database
{
    private string $host = '127.0.0.1';
    private string $dbName = 'products_api';
    private string $username = 'root';
    private string $password = '';
    private ?PDO $connection = null;

    public function getConnection(): PDO
    {
        if ($this->connection === null) {
            $dsn = "mysql:host={$this->host};dbname={$this->dbName};charset=utf8mb4";

            $this->connection = new PDO($dsn, $this->username, $this->password, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ]);
        }

        return $this->connection;
    }
}
Test the connection

Before building anything else, confirm that PHP can actually reach your database. A quick test script will catch any credential or configuration problems now.

  • Create a new file called test_connection.php in the root of your products-api folder.
  • Add this code to test_connection.php:
<?php

// Pull in the Database class
require_once 'config/Database.php';

try {
    // Create a new Database instance and request a connection
    $database = new Database();
    $connection = $database->getConnection();
    echo "Connected to the database successfully!\n";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage() . "\n";
}

What does this test do?

require_once loads your Database class so PHP knows it exists. The try/catch block attempts to connect. If the credentials or database name are wrong, PDO throws a PDOException that gets caught and printed instead of crashing the script.

  • Save test_connection.php.
  • Run the test script by running this command in your terminal (make sure you are inside the products-api folder):
php test_connection.php

You should see: Connected to the database successfully!.

  • Delete the test file now that you've confirmed the connection works:
rm test_connection.php

Seeing "Connection failed"?

If the error mentions "Access denied", the password in config/Database.php doesn't match your MySQL root password. Update the $password property and try again.

If the error mentions "Connection refused", MySQL isn't running. Start the MySQL service and retry.

If the error mentions "Unknown database", double check that you created the products_api database in the previous substep. Log back into MySQL and run SHOW DATABASES; to confirm it exists.

help me fix my PHP database connection error

Your database is live and PHP can talk to it. Next up, you'll build the response helper and routing system that turn HTTP requests into structured JSON output.

Build the Response Helper and Router

Your database is connected and ready to store products. Now you need a way for the outside world to talk to it.

Every API needs two things: a consistent way to format responses, and a router that directs incoming requests to the right handler. In this step, you will build both.

In this step, get ready to:
  • Create a Response helper class for consistent JSON output.
  • Build a router that directs HTTP methods to handlers.
  • Test the API endpoint with cURL.
Create the Response helper

Without a shared response format, every endpoint would build its own JSON structure. A dedicated helper guarantees every response your API sends looks the same.

  • In VS Code, create a new file called Response.php inside the helpers/ folder.
  • Add the following code to helpers/Response.php:
<?php

class Response
{
    // Send a success response with data and HTTP status code
    public static function success(mixed $data, int $statusCode = 200): void
    {
        http_response_code($statusCode);
        header('Content-Type: application/json');

        echo json_encode([
            'status' => 'success',
            'data' => $data,
        ]);

        exit;
    }

    // Send an error response with a message and HTTP status code
    public static function error(string $message, int $statusCode = 400): void
    {
        http_response_code($statusCode);
        header('Content-Type: application/json');

        echo json_encode([
            'status' => 'error',
            'message' => $message,
        ]);

        exit;
    }
}

What does this code do?

The Response class gives your API two static methods that any endpoint can call without creating an instance.

  • success() sets the HTTP status code, adds the JSON content-type header, wraps your data in a {"status": "success", "data": ...} envelope, and exits.
  • error() does the same but wraps a message string in a {"status": "error", "message": ...} envelope.
  • Both methods call exit to stop script execution immediately. This prevents accidental extra output after the response is sent.
  • Save helpers/Response.php.

You won't see a change yet. The next file you create will use this helper to produce visible output.

Seeing a syntax error later?

Check that your Response.php file starts with <?php on the very first line with no blank lines or spaces before it.

Also confirm the class name is Response with a capital R. PHP class names are case-sensitive in autoloading contexts.

help me debug a syntax error in my PHP Response helper class

Create the router entry point

A router reads the incoming HTTP method and decides which code to run. This file will become the single entry point for all product-related API requests.

  • Create a new file called products.php inside the api/ folder.
  • Add the following code to api/products.php:
<?php

// Load the database connection and response helper
require_once __DIR__ . '/../config/Database.php';
require_once __DIR__ . '/../helpers/Response.php';

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

// Read the HTTP method (GET, POST, PUT, DELETE)
$method = $_SERVER['REQUEST_METHOD'];
// Check if a product ID was passed in the URL (e.g., ?id=1)
$id = isset($_GET['id']) ? (int) $_GET['id'] : null;

// Route the request to the correct handler based on HTTP method
switch ($method) {
    case 'GET':
        Response::success(['message' => 'GET handler placeholder']);
        break;
    case 'POST':
        Response::success(['message' => 'POST handler placeholder']);
        break;
    case 'PUT':
        Response::success(['message' => 'PUT handler placeholder']);
        break;
    case 'DELETE':
        Response::success(['message' => 'DELETE handler placeholder']);
        break;
    default:
        Response::error('Method not allowed', 405);
}

What does this code do?

This script is the front door for all product API requests. It reads the request and decides what to do.

  • $_SERVER['REQUEST_METHOD'] tells you which HTTP verb the client used (GET, POST, PUT, or DELETE).
  • $_GET['id'] captures the product ID from the URL query string. The (int) cast ensures it is always a number.
  • The switch statement routes each method to a handler. Right now the handlers are placeholders. You will replace them with real database calls in the next step.
  • The default case catches unsupported methods (like PATCH) and returns a 405 error using your Response helper.
  • Save api/products.php.

You won't see a change yet. You need to start the PHP server to test this file.

Seeing a blank page or HTML instead of JSON?

Make sure require_once paths use __DIR__ followed by the relative path. If the path is wrong, PHP will throw a fatal error and output nothing.

Double check that helpers/Response.php exists and has no typos in the filename.

help me fix my PHP require_once path

Test with cURL

Your router is ready. Time to start the PHP built-in server and send a real HTTP request to confirm everything is wired up.

  • In your terminal, make sure you are inside the products-api folder.
  • Start the PHP built-in server by running this command:
php -S localhost:8000

What is the PHP built-in server?

PHP ships with a lightweight development server. The -S flag starts it on the address and port you specify.

It serves files from your current working directory. Since you are inside products-api, it can reach api/products.php and all your other project files.

This server is for development only. It processes one request at a time.

The terminal will show a message like "Listening on http://localhost:8000" and stay open. This is expected.

  • Open a second terminal window or tab. Press Cmd+T (macOS) or Ctrl+Shift+T (Windows) in most terminal apps to open a new tab.
  • Send a GET request to your products endpoint by running this command:
curl http://localhost:8000/api/products.php
  • Confirm you see a JSON response like this in your terminal:
{"status":"success","data":{"message":"GET handler placeholder"}}

What should I see?

The JSON output confirms three things are working together: the router detected a GET request, the Response helper wrapped your data in the standard envelope, and the Content-Type header was set correctly so curl displayed it as text.

Seeing 'Connection refused' or no output?

Make sure the PHP server is still running in your first terminal tab. If you closed it or pressed Ctrl+C, restart it with php -S localhost:8000 from inside the products-api folder.

If you see a PHP error mentioning Response.php, check the require_once path in api/products.php. The path must go up one directory with /../ before reaching helpers/.

help me troubleshoot my PHP API endpoint returning nothing

✔️ Awesome, I've got everything!

Great. Double check you have saved both files before moving on.

ⓧ I'd like to double check the full code

Here is the complete code for both files at this point.

<?php

class Response
{
    public static function success(mixed $data, int $statusCode = 200): void
    {
        http_response_code($statusCode);
        header('Content-Type: application/json');

        echo json_encode([
            'status' => 'success',
            'data' => $data,
        ]);

        exit;
    }

    public static function error(string $message, int $statusCode = 400): void
    {
        http_response_code($statusCode);
        header('Content-Type: application/json');

        echo json_encode([
            'status' => 'error',
            'message' => $message,
        ]);

        exit;
    }
}
<?php

require_once __DIR__ . '/../config/Database.php';
require_once __DIR__ . '/../helpers/Response.php';

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

$method = $_SERVER['REQUEST_METHOD'];
$id = isset($_GET['id']) ? (int) $_GET['id'] : null;

switch ($method) {
    case 'GET':
        Response::success(['message' => 'GET handler placeholder']);
        break;
    case 'POST':
        Response::success(['message' => 'POST handler placeholder']);
        break;
    case 'PUT':
        Response::success(['message' => 'PUT handler placeholder']);
        break;
    case 'DELETE':
        Response::success(['message' => 'DELETE handler placeholder']);
        break;
    default:
        Response::error('Method not allowed', 405);
}

Your API can now receive requests, route them by HTTP method, and return clean JSON. Next up, you will build the Product model that talks to your database and replaces those placeholder messages with real data.

Build the Product Model with CRUD

Your database connection and response system are ready. Now you need a way to actually talk to the products table in your database.

In this step, you will build a Product model class that handles all the database operations (Create, Read, Update, Delete) using PDO prepared statements. This keeps your SQL queries secure and your code organized in one place.

In this step, get ready to:
  • Create the Product model class with a PDO connection.
  • Implement read and create operations using prepared statements.
  • Implement update and delete operations with existence checks.
Create the Product model class

A model is a class responsible for interacting with a specific database table. Your Product model will accept a PDO connection in its constructor and use it for every database query.

  • Create a new file called Product.php inside your models/ folder.
  • Add the class definition with its constructor by pasting this code:
<?php

class Product
{
    // Store the database connection for all methods to use
    private PDO $db;

    // Accept a PDO connection when creating a Product instance
    public function __construct(PDO $db)
    {
        $this->db = $db;
    }
}

What does this code do?

The Product class stores a PDO database connection as a private property. When you create a new Product instance, you pass in the connection. Every method in the class then uses $this->db to run queries.

This pattern is called dependency injection. Instead of the model creating its own connection, it receives one from outside. This makes the code easier to test and reuse.

  • Save models/Product.php.
  • Confirm the file exists by running:
ls models/
  • You should see Product.php in the output.

Don't see Product.php?

Make sure you created the file inside the models/ directory, not in the project root. The file path should be products-api/models/Product.php.

Also check that ls is running from within your products-api folder.

Why isn't my Product.php file showing up in the models directory?

Implement read and create operations

Your model needs three methods to start: one to fetch all products, one to fetch a single product by ID, and one to create a new product. Each uses prepared statements to prevent SQL injection.

  • In models/Product.php, find the closing } of the constructor method.
  • Add the following three methods inside the class, below the constructor:
    public function getAll(): array
    {
        // Fetch every product, newest first
        $stmt = $this->db->query('SELECT * FROM products ORDER BY created_at DESC');
        return $stmt->fetchAll();
    }

    public function getOne(int $id): array|false
    {
        // Use a prepared statement to safely bind the ID parameter
        $stmt = $this->db->prepare('SELECT * FROM products WHERE id = :id');
        $stmt->execute([':id' => $id]);
        return $stmt->fetch();
    }

    public function create(array $data): array
    {
        // Prepare the INSERT with named placeholders for each column
        $stmt = $this->db->prepare(
            'INSERT INTO products (name, description, price, quantity, category, created_at)
             VALUES (:name, :description, :price, :quantity, :category, NOW())'
        );

        // Bind values from the input data, with defaults for optional fields
        $stmt->execute([
            ':name' => $data['name'],
            ':description' => $data['description'] ?? '',
            ':price' => $data['price'],
            ':quantity' => $data['quantity'] ?? 0,
            ':category' => $data['category'] ?? 'general',
        ]);

        // Get the auto-generated ID and return the full new record
        $newId = (int) $this->db->lastInsertId();
        return $this->getOne($newId);
    }

What does this code do?

  • getAll() runs a plain query with no user input. It fetches every row ordered by created_at DESC so the newest products appear first.
  • getOne() uses a prepared statement with a :id placeholder. PDO binds the actual value at execution time, which makes it impossible for a malicious ID to inject SQL.
  • create() inserts a new row using named parameters. After inserting, it calls lastInsertId() to get the auto-generated ID, then returns the full record by calling getOne().

Why prepared statements instead of string concatenation?

Without prepared statements, you would build SQL by pasting user input directly into the query string. An attacker could submit a product name like '; DROP TABLE products; -- and destroy your data.

Prepared statements separate the SQL structure from the data values. The database treats the placeholders as data slots, never as executable SQL. This eliminates SQL injection entirely.

  • Save models/Product.php.
  • You won't see a visible change yet. The next substep adds the remaining methods, then you will wire everything together in the next step.

Seeing a red underline in your editor?

If VS Code shows a syntax error, check that each method is inside the class braces. The methods should sit between the constructor's closing } and the class's final }.

Also confirm that your create() method's SQL string uses single quotes around the whole query, not double quotes. Mixing quote types causes parse errors.

Help me fix a syntax error in my Product.php model file.

Implement update and delete operations

The update method needs to check if the product exists before modifying it. The delete method also needs to confirm a row was actually removed. Both use rowCount() or existence checks to report success or failure.

  • In models/Product.php, find the closing } of the create() method.
  • Add the following two methods below it, still inside the class:
    public function update(int $id, array $data): array|false
    {
        // Check if the product exists before attempting update
        $existing = $this->getOne($id);
        if (!$existing) {
            return false;
        }

        // Update all fields, falling back to existing values for any missing ones
        $stmt = $this->db->prepare(
            'UPDATE products
             SET name = :name, description = :description, price = :price,
                 quantity = :quantity, category = :category
             WHERE id = :id'
        );

        $stmt->execute([
            ':name' => $data['name'] ?? $existing['name'],
            ':description' => $data['description'] ?? $existing['description'],
            ':price' => $data['price'] ?? $existing['price'],
            ':quantity' => $data['quantity'] ?? $existing['quantity'],
            ':category' => $data['category'] ?? $existing['category'],
            ':id' => $id,
        ]);

        // Return the freshly updated record
        return $this->getOne($id);
    }

    public function delete(int $id): bool
    {
        // Prepare and execute the DELETE with a bound ID
        $stmt = $this->db->prepare('DELETE FROM products WHERE id = :id');
        $stmt->execute([':id' => $id]);
        // rowCount() tells us if a row was actually removed
        return $stmt->rowCount() > 0;
    }

What does this code do?

  • update() first calls getOne() to check the product exists. If it does not, the method returns false immediately. For fields the caller did not provide, it falls back to the existing database values using the ?? null coalescing operator.
  • delete() runs a prepared DELETE statement and uses rowCount() to check if a row was actually removed. If the ID did not exist, rowCount() returns 0 and the method returns false.
  • Save models/Product.php.

Syntax error on the union return type?

The array|false return type requires PHP 8.0 or higher. If your editor flags it, confirm your PHP version by running php -v in your terminal.

Also double check that the update() method has a matching number of opening and closing braces. The if (!$existing) block adds an extra level of nesting that is easy to miss.

Help me debug a syntax error in my update or delete method.

✔️ Awesome, I've got everything!

Great. Double check you have saved models/Product.php.

ⓧ I'd like to double check the full code

Here is the complete models/Product.php file. Compare it against yours:

<?php

class Product
{
    private PDO $db;

    public function __construct(PDO $db)
    {
        $this->db = $db;
    }

    public function getAll(): array
    {
        $stmt = $this->db->query('SELECT * FROM products ORDER BY created_at DESC');
        return $stmt->fetchAll();
    }

    public function getOne(int $id): array|false
    {
        $stmt = $this->db->prepare('SELECT * FROM products WHERE id = :id');
        $stmt->execute([':id' => $id]);
        return $stmt->fetch();
    }

    public function create(array $data): array
    {
        $stmt = $this->db->prepare(
            'INSERT INTO products (name, description, price, quantity, category, created_at)
             VALUES (:name, :description, :price, :quantity, :category, NOW())'
        );

        $stmt->execute([
            ':name' => $data['name'],
            ':description' => $data['description'] ?? '',
            ':price' => $data['price'],
            ':quantity' => $data['quantity'] ?? 0,
            ':category' => $data['category'] ?? 'general',
        ]);

        $newId = (int) $this->db->lastInsertId();
        return $this->getOne($newId);
    }

    public function update(int $id, array $data): array|false
    {
        $existing = $this->getOne($id);
        if (!$existing) {
            return false;
        }

        $stmt = $this->db->prepare(
            'UPDATE products
             SET name = :name, description = :description, price = :price,
                 quantity = :quantity, category = :category
             WHERE id = :id'
        );

        $stmt->execute([
            ':name' => $data['name'] ?? $existing['name'],
            ':description' => $data['description'] ?? $existing['description'],
            ':price' => $data['price'] ?? $existing['price'],
            ':quantity' => $data['quantity'] ?? $existing['quantity'],
            ':category' => $data['category'] ?? $existing['category'],
            ':id' => $id,
        ]);

        return $this->getOne($id);
    }

    public function delete(int $id): bool
    {
        $stmt = $this->db->prepare('DELETE FROM products WHERE id = :id');
        $stmt->execute([':id' => $id]);
        return $stmt->rowCount() > 0;
    }
}

Your Product model is complete with all five CRUD methods. In the next step, you will wire this model into your router and test every operation with real HTTP requests.

Wire Everything Together and Add Validation

Your Product model has all the database operations ready to go. Now it is time to connect that model to the router so that real HTTP requests actually create, read, update, and delete products in your database.

Right now, api/products.php returns placeholder messages for every request. In this step, you will replace those placeholders with real model calls and add input validation so your API rejects bad data before it ever reaches the database.

In this step, get ready to:
  • Wire the Product model into the router so each HTTP method calls the correct database operation.
  • Add input validation that rejects incomplete or invalid product data with clear error messages.
  • Test the full CRUD lifecycle with cURL to confirm everything works end-to-end.
Wire the model into the router

The router currently knows which HTTP method was used, but it does nothing with that information. You need to instantiate the Database and Product classes, then call the right model method inside each case of the switch statement.

  • In VS Code, open api/products.php and replace the entire file content with the following code:
<?php

require_once __DIR__ . '/../config/Database.php';
require_once __DIR__ . '/../helpers/Response.php';
require_once __DIR__ . '/../models/Product.php';

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

try {
    // Instantiate the database and product model
    $database = new Database();
    $db = $database->getConnection();
    $product = new Product($db);

    $method = $_SERVER['REQUEST_METHOD'];
    $id = isset($_GET['id']) ? (int) $_GET['id'] : null;

    switch ($method) {
        case 'GET':
            // Return one product if ID provided, otherwise return all
            if ($id) {
                $result = $product->getOne($id);
                if (!$result) {
                    Response::error('Product not found', 404);
                }
                Response::success($result);
            } else {
                $results = $product->getAll();
                Response::success($results);
            }
            break;

        case 'POST':
            // Read the raw JSON body from the request
            $input = json_decode(file_get_contents('php://input'), true);

            if (!$input) {
                Response::error('Invalid JSON body', 400);
            }

            $result = $product->create($input);
            Response::success($result, 201);
            break;

        case 'PUT':
            if (!$id) {
                Response::error('Product ID is required for update', 400);
            }

            $input = json_decode(file_get_contents('php://input'), true);

            if (!$input) {
                Response::error('Invalid JSON body', 400);
            }

            $result = $product->update($id, $input);
            if (!$result) {
                Response::error('Product not found', 404);
            }
            Response::success($result);
            break;

        case 'DELETE':
            if (!$id) {
                Response::error('Product ID is required for delete', 400);
            }

            $deleted = $product->delete($id);
            if (!$deleted) {
                Response::error('Product not found', 404);
            }
            Response::success(['message' => 'Product deleted successfully']);
            break;

        default:
            Response::error('Method not allowed', 405);
    }
} catch (PDOException $e) {
    // Database-specific errors
    Response::error('Database error: ' . $e->getMessage(), 500);
} catch (Exception $e) {
    // Catch-all for any other errors
    Response::error('Server error: ' . $e->getMessage(), 500);
}

What does this code do?

This replaces the placeholder routing with real database operations. Here is what changed:

  • require_once __DIR__ . '/../models/Product.php' pulls in the Product model so you can use it.
  • The entire switch block is now wrapped in a try/catch so any database error returns a clean JSON error instead of crashing.
  • Each case calls the matching Product model method (getAll(), getOne(), create(), update(), delete()) and passes the result to the Response helper.
  • file_get_contents('php://input') reads the raw JSON body that the client sends with POST and PUT requests.
  • Save api/products.php.
  • In your terminal where the PHP server is running, confirm it is still active. If you stopped it, restart it by running:
php -S localhost:8000
  • Open a new terminal window and test the GET endpoint by running:
curl http://localhost:8000/api/products.php

You should see a JSON response with "status": "success" and an empty data array (since you have no products yet).

Seeing a 500 error or no response?

  • Check that MySQL is still running. The database connection fails silently from the server side but you will see a Database error message in the JSON response.
  • Make sure the file paths in each require_once are correct. A typo in the path causes a fatal error.
  • Confirm the PHP server is running in the products-api folder, not a parent or child directory.

Why is my API returning a 500 error instead of product data?

Add input validation

Your POST and PUT handlers currently accept any data without checking it. If someone sends a request without a product name or with a non-numeric price, the database call will either fail or store garbage data. Validation catches these problems early and returns helpful error messages.

  • In api/products.php, find this line inside the POST case:
            $result = $product->create($input);
  • Add the following validation check directly above that line:
            // Validate required fields before creating
            $errors = validateProductInput($input);
            if (!empty($errors)) {
                Response::error('Validation failed: ' . implode(', ', $errors), 422);
            }
  • Now find this line inside the PUT case:
            $result = $product->update($id, $input);
  • Add the following price check directly above that line:
            // Validate price format if provided
            if (isset($input['price']) && !is_numeric($input['price'])) {
                Response::error('Price must be a number', 422);
            }

Why different validation for POST and PUT?

POST creates a brand new product, so it requires both name and price to be present. PUT updates an existing product, so it only needs to validate the format of fields that are actually provided. A PUT request might only send a new price without changing the name.

  • Finally, scroll to the very bottom of api/products.php (after the closing } of the catch block) and add the validation function:
function validateProductInput(array $input): array
{
    $errors = [];

    // Name must be present and non-empty
    if (empty($input['name'])) {
        $errors[] = 'name is required';
    }

    // Price must be present, numeric, and non-negative
    if (!isset($input['price'])) {
        $errors[] = 'price is required';
    } elseif (!is_numeric($input['price'])) {
        $errors[] = 'price must be a number';
    } elseif ($input['price'] < 0) {
        $errors[] = 'price must be positive';
    }

    return $errors;
}

What does this function do?

The validateProductInput() function collects all validation errors into an array instead of stopping at the first one. This means the client sees every problem at once rather than fixing them one at a time.

The function checks three things about price using elseif chains: is it present, is it a number, is it non-negative. Each check only runs if the previous one passed.

  • Save api/products.php.
  • Test that validation catches bad input by running:
curl -X POST -H "Content-Type: application/json" -d '{"price":9.99}' http://localhost:8000/api/products.php

You should see a 422 response with the message Validation failed: name is required since you sent a price but no name.

Seeing a fatal error about undefined function?

If you see an error mentioning an undefined function, make sure validateProductInput is defined outside the try/catch block, at the very bottom of the file. PHP needs the function to exist at the file level, not nested inside another block.

Why am I getting a fatal error about an undefined function in my PHP API?

Test all endpoints with cURL

Your API is fully wired and validated. Time to test the complete lifecycle: create a product, read it back, update it, and delete it.

  • Create a new product by running:
curl -X POST -H "Content-Type: application/json" -d '{"name":"Widget","price":9.99}' http://localhost:8000/api/products.php

You should see a 201 response with the full product object, including an id and created_at timestamp generated by the database.

  • Read all products back by running:
curl http://localhost:8000/api/products.php

You should see your Widget in the data array.

  • Update the product's price (replace 1 with the actual ID from your create response) by running:
curl -X PUT -H "Content-Type: application/json" -d '{"price":12.99}' http://localhost:8000/api/products.php?id=1

You should see the updated product with the new price of 12.99 while all other fields remain unchanged.

  • Delete the product by running:
curl -X DELETE http://localhost:8000/api/products.php?id=1

You should see a success response with the message Product deleted successfully.

  • Verify the delete worked by trying to fetch that product again:
curl http://localhost:8000/api/products.php?id=1

You should see a 404 error response with Product not found.

What is a full CRUD lifecycle?

CRUD stands for Create, Read, Update, Delete. These four operations map directly to the HTTP methods POST, GET, PUT, and DELETE. Every REST API resource follows this pattern. You just proved your API handles the complete lifecycle correctly.

Getting unexpected results from cURL?

  • On Windows, you may need to use double quotes for the -d flag and escape inner quotes: -d "{\"name\":\"Widget\",\"price\":9.99}".
  • If you get Invalid JSON body, check that your JSON is properly formatted with no trailing commas or missing quotes.
  • If the product ID in your PUT or DELETE does not match, run the GET request first to see which IDs exist in your database.

Why is cURL returning an invalid JSON body error when I send a POST request?

✔️ Awesome, I've got everything!

Great. Double check that you have saved api/products.php and that all four cURL tests (POST, GET, PUT, DELETE) return the expected responses.

ⓧ I'd like to double check the full code

Here is the complete api/products.php file. Compare yours against it:

<?php

require_once __DIR__ . '/../config/Database.php';
require_once __DIR__ . '/../helpers/Response.php';
require_once __DIR__ . '/../models/Product.php';

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

try {
    $database = new Database();
    $db = $database->getConnection();
    $product = new Product($db);

    $method = $_SERVER['REQUEST_METHOD'];
    $id = isset($_GET['id']) ? (int) $_GET['id'] : null;

    switch ($method) {
        case 'GET':
            if ($id) {
                $result = $product->getOne($id);
                if (!$result) {
                    Response::error('Product not found', 404);
                }
                Response::success($result);
            } else {
                $results = $product->getAll();
                Response::success($results);
            }
            break;

        case 'POST':
            $input = json_decode(file_get_contents('php://input'), true);

            if (!$input) {
                Response::error('Invalid JSON body', 400);
            }

            $errors = validateProductInput($input);
            if (!empty($errors)) {
                Response::error('Validation failed: ' . implode(', ', $errors), 422);
            }

            $result = $product->create($input);
            Response::success($result, 201);
            break;

        case 'PUT':
            if (!$id) {
                Response::error('Product ID is required for update', 400);
            }

            $input = json_decode(file_get_contents('php://input'), true);

            if (!$input) {
                Response::error('Invalid JSON body', 400);
            }

            if (isset($input['price']) && !is_numeric($input['price'])) {
                Response::error('Price must be a number', 422);
            }

            $result = $product->update($id, $input);
            if (!$result) {
                Response::error('Product not found', 404);
            }
            Response::success($result);
            break;

        case 'DELETE':
            if (!$id) {
                Response::error('Product ID is required for delete', 400);
            }

            $deleted = $product->delete($id);
            if (!$deleted) {
                Response::error('Product not found', 404);
            }
            Response::success(['message' => 'Product deleted successfully']);
            break;

        default:
            Response::error('Method not allowed', 405);
    }
} catch (PDOException $e) {
    Response::error('Database error: ' . $e->getMessage(), 500);
} catch (Exception $e) {
    Response::error('Server error: ' . $e->getMessage(), 500);
}

function validateProductInput(array $input): array
{
    $errors = [];

    if (empty($input['name'])) {
        $errors[] = 'name is required';
    }

    if (!isset($input['price'])) {
        $errors[] = 'price is required';
    } elseif (!is_numeric($input['price'])) {
        $errors[] = 'price must be a number';
    } elseif ($input['price'] < 0) {
        $errors[] = 'price must be positive';
    }

    return $errors;
}

Your REST API is fully operational. You have a clean separation between routing, validation, data access, and response formatting. Next up, the Secret Mission challenges you to add authentication so only authorized clients can modify your data.

Secret mission

Add API Key Authentication

Your API is wide open right now. Anyone who knows the URL can create, update, or delete products. In this secret mission, you'll add API key authentication to protect all write operations while keeping read access public.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • The products-api project folder on your Desktop.
  • The products_api MySQL database with the products table.
  • The PHP built-in server process running at localhost:8000.

Keep everything running

No action needed. Choose this if you're still actively building or experimenting with your API.

  • Your PHP server will keep running until you close the terminal or press Ctrl+C.
  • Your MySQL database stays available for queries anytime.
  • Your project files remain on your Desktop ready for editing.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your files and database stay intact for next time.

  • Switch to the terminal where the PHP server is running and press Ctrl+C to stop it.
  • When you come back, restart the server by navigating to your project folder and running php -S localhost:8000 again.

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes everything permanently.

  • Stop the PHP server by pressing Ctrl+C in the terminal where it is running.
  • Delete the MySQL database by running this command in your terminal:
mysql -u root -e "DROP DATABASE IF EXISTS products_api;"
  • Delete the project folder. On macOS/Linux, run:
rm -rf ~/Desktop/products-api
  • On Windows, run:
rmdir /s /q %USERPROFILE%\Desktop\products-api

After these steps, your system is back to its original state.

That's a Wrap!

That's a Wrap!

Nice work! You just built a complete REST API from scratch using pure PHP and MySQL.

You've learned how to:

  • Built a fully functional Products REST API with full CRUD operations using PDO prepared statements that prevent SQL injection.
  • Created a clean project structure separating concerns into config, models, helpers, and API endpoints.
  • Added input validation with meaningful error messages and returned consistent JSON response envelopes with proper HTTP status codes.
  • Added API key authentication to protect write operations while keeping read endpoints public.

Ready to quiz yourself?