What is How To Create REST API Using Php And Mysql? Everything You Need to Know

Introduction

A REST API (Representational State Transfer Application Programming Interface) is a set of stateless HTTP endpoints that enable clients to create, read, update, and delete resources. In the PHP ecosystem, pairing REST with MySQL gives developers a lightweight, scalable way to expose database‑driven data to web, mobile, or IoT applications. Mastering this stack is essential for building modern, decoupled systems that can grow alongside your business needs.

---

Core Concepts

Concept	Description
Resources	Entities such as users, orders, or products that are identified by a unique URI (e.g., /api/v1/users/42).
HTTP Verbs	GET (retrieve), POST (create), PUT/PATCH (update), DELETE (remove). The verb defines the operation on a resource.
Statelessness	Each request contains all information required to process it; the server does not store session state between calls.
Content Negotiation	Clients specify the desired response format (usually JSON) via the Accept header, while servers declare the format with Content-Type.
Authentication	Token‑based schemes (JWT, API keys) protect endpoints without relying on traditional PHP sessions.
Error Handling	Use standardized HTTP status codes (200, 201, 400, 401, 404, 500) and a consistent JSON error payload.
Routing	Maps incoming URLs to controller functions. In plain PHP, this can be handled with .htaccess rewrites or a micro‑framework like Slim.
Data Layer	PDO (PHP Data Objects) provides a secure, prepared‑statement interface to MySQL, preventing SQL injection.

---

Real‑World Use Cases

• Mobile Backend – A native iOS/Android app consumes a PHP‑MySQL API to sync user profiles, push notifications, and in‑app purchases.
• Single‑Page Applications (SPA) – Front‑end frameworks such as React or Vue fetch JSON data from a REST API to render dynamic dashboards.
• Third‑Party Integrations – Expose product catalogs or order data to partner systems, enabling B2B data exchange without sharing database credentials.
• IoT Device Management – Sensors post telemetry to /api/v1/metrics, while a dashboard pulls aggregated data for analytics.

---

Getting Started Guide

1. Prerequisites

• PHP 8.1+ with the pdo_mysql extension enabled.
• MySQL 8.0+ (or MariaDB).
• A web server (Apache or Nginx) configured for URL rewriting.
• Composer (optional, for installing a micro‑framework).

2. Project Structure

my-rest-api/
├── public/
│   └── index.php          # Front controller
├── src/
│   ├── Controller/
│   │   └── UserController.php
│   ├── Model/
│   │   └── User.php
│   └── Router.php
├── config/
│   └── database.php
├── vendor/                # Composer packages
└── .htaccess              # Apache rewrite rules

3. Database Setup

CREATE DATABASE rest_api CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE rest_api;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4. Configure PDO

// config/database.php
<?php
$dsn = 'mysql:host=localhost;dbname=rest_api;charset=utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
try {
    $pdo = new PDO($dsn, 'db_user', 'db_pass', $options);
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database connection failed']);
    exit;
}

5. Simple Router (plain PHP)

// public/index.php
<?php
require __DIR__ . '/../vendor/autoload.php';
require __DIR__ . '/../config/database.php';
require __DIR__ . '/../src/Router.php';

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

$router = new Router($pdo);
$router->dispatch($_SERVER['REQUEST_METHOD'], $_SERVER['REQUEST_URI']);

// src/Router.php
<?php
class Router {
    private $pdo;
    public function __construct(PDO $pdo) { $this->pdo = $pdo; }

    public function dispatch(string $method, string $uri) {
        $uri = parse_url($uri, PHP_URL_PATH);
        $segments = explode('/', trim($uri, '/'));

        // Example: /api/v1/users/42
        if ($segments[0] === 'api' && $segments[1] === 'v1' && $segments[2] === 'users') {
            require_once __DIR__ . '/Controller/UserController.php';
            $controller = new UserController($this->pdo);
            $id = $segments[3] ?? null;

            switch ($method) {
                case 'GET':
                    $id ? $controller->show($id) : $controller->index();
                    break;
                case 'POST':
                    $controller->store();
                    break;
                case 'PUT':
                case 'PATCH':
                    $controller->update($id);
                    break;
                case 'DELETE':
                    $controller->destroy($id);
                    break;
                default:
                    http_response_code(405);
                    echo json_encode(['error' => 'Method Not Allowed']);
            }
            return;
        }

        http_response_code(404);
        echo json_encode(['error' => 'Endpoint not found']);
    }
}

6. Controller Example

// src/Controller/UserController.php
<?php
class UserController {
    private $pdo;
    public function __construct(PDO $pdo) { $this->pdo = $pdo; }

    public function index() {
        $stmt = $this->pdo->query('SELECT id, name, email FROM users');
        $users = $stmt->fetchAll();
        echo json_encode($users);
    }

    public function show($id) {
        $stmt = $this->pdo->prepare('SELECT id, name, email FROM users WHERE id = ?');
        $stmt->execute([$id]);
        $user = $stmt->fetch();
        if ($user) {
            echo json_encode($user);
        } else {
            http_response_code(404);
            echo json_encode(['error' => 'User not found']);
        }
    }

    public function store() {
        $data = json_decode(file_get_contents('php://input'), true);
        if (!isset($data['name'], $data['email'], $data['password'])) {
            http_response_code(400);
            echo json_encode(['error' => 'Missing required fields']);
            return;
        }
        $hash = password_hash($data['password'], PASSWORD_BCRYPT);
        $stmt = $this->pdo->prepare('INSERT INTO users (name, email, password_hash) VALUES (?, ?, ?)');
        $stmt->execute([$data['name'], $data['email'], $hash]);
        http_response_code(201);
        echo json_encode(['message' => 'User created', 'id' => $this->pdo->lastInsertId()]);
    }

    public function update($id) {
        $data = json_decode(file_get_contents('php://input'), true);
        $fields = [];
        $params = [];

        if (isset($data['name'])) { $fields[] = 'name = ?'; $params[] = $data['name']; }
        if (