How to Build a CRUD REST API With PHP and MySQL

Building a CRUD (Create, Read, Update, Delete) REST API with PHP involves setting up a local server environment, establishing a database connection, defining API endpoints, and implementing the Create, Read, Update, and Delete operations. You can achieve this using a framework like Laravel or plain PHP with MySQL

In this tutorial, we will be building a CRUD (Create, Read, Update, Delete) REST API from scratch which is the best way to understand how backend data communication works. We will use cURL to test our endpoints.

Below is a streamlined guide to building a lightweight API using Core PHP (without frameworks like Laravel) and MySQL.

Prerequisites

• PHP installed (or a local server like XAMPP/MAMP/WAMP).

• MySQL database.

• Postman or cURL for testing endpoints.

Step 1: Database Setup

First, create a database and a table to store your data. We will create a simple users API.

Run this SQL in your PHPMyAdmin or SQL terminal:

SQL

CREATE DATABASE api_db;USE api_db;CREATE TABLE restful_users (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(255) NOT NULL,    email VARCHAR(255) NOT NULL UNIQUE,    created_at timeSTAMP DEFAULT CURRENT_timeSTAMP);

Step 2: Database Connection

Create a file named db.php. We will use PDO (PHP Data Objects) because it is secure, supports prepared statements (preventing SQL injection), and connects to various database types.

File: db.php

DATABASE CONNECTION SCRIPT

<?phpdefine("HOST", 'localhost');   // The host you want to connect to.
define("USER", 'root');    		// The database username. 
define("PASSWORD", '');    		// The database password. 
define("DATABASE", 'demos');   // The database name.

try {
    $conn = new PDO("mysql:host=".HOST.";dbname=".DATABASE, USER, PASSWORD);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Step 3: The API Logic (The Controller)

For simplicity, we will handle all CRUD operations in a single file named api.php. This file will check which HTTP Method (GET, POST, PUT, DELETE) the client is using and run the corresponding code.

File: api.php

REST API SCRIPT

<?php
header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *"); // Allow requests from any origin
header("Access-Control-Allow-Methods: GET, POST, PUT, DELETE");
header("Access-Control-Allow-headers: Content-Type");


$method = $_SERVER['REQUEST_METHOD'];

include 'db.php';

// Get the HTTP method

// Get input data (for POST/PUT)
$input = json_decode(file_get_contents('php://input'), true);

switch ($method) {
	case 'GET':
		handleGet($conn);
		break;
	case 'POST':
		handlePost($conn, $input);
		break;
	case 'PUT':
		handlePut($conn, $input);
		break;
	case 'DELETE':
		handleDelete($conn, $input);
		break;
	default:
		echo json_encode(["message" => "Invalid Request Method"]);
		break;
}

/**
 * READ: Fetch all restful_users or a specific user by ID
 */
function handleGet($conn) 
{
	if (isset($_GET['id'])) 
	{
		$stmt = $conn->prepare("SELECT * FROM restful_users WHERE id = ?");
		$stmt->execute([trim(strip_tags($_GET['id']))]);
		$user = $stmt->fetch(PDO::FETCH_ASSOC);
		echo json_encode($user ?: ["message" => "User not found"]);
	} 
	else 
	{
		$stmt = $conn->query("SELECT * FROM restful_users");
		$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
		echo json_encode($users);
	}
}

/**
 * CREATE: Add a new user
 */
function handlePost($conn, $input) 
{
	if (!isset($input['name']) || empty($input['name']) || !isset($input['email']) || empty($input['email'])) 
	{
		echo json_encode(["message" => "Invalid input"]);
		return;
	}

	$sql = "INSERT INTO restful_users (name, email) VALUES (?, ?)";
	$stmt = $conn->prepare($sql);
	
	if ($stmt->execute([trim(strip_tags($input['name'])), trim(strip_tags($input['email']))])) 
	{
		echo json_encode(["message" => "User Created Successfully"]);
	} 
	else 
	{
		echo json_encode(["message" => "Failed to create user"]);
	}
}

/**
 * UPDATE: Update existing user data
 */
function handlePut($conn, $input) 
{
	if (!isset($input['id']) || empty($input['id']) || !isset($input['name']) || empty($input['name']) || !isset($input['email']) || empty($input['email'])) 
	{
		echo json_encode(["message" => "Invalid input"]);
		return;
	}

	$sql = "UPDATE restful_users SET name = ?, email = ? WHERE id = ?";
	$stmt = $conn->prepare($sql);
	
	if ($stmt->execute([trim(strip_tags($input['name'])), trim(strip_tags($input['email'])), trim(strip_tags($input['id']))])) 
	{
		echo json_encode(["message" => "User Updated Successfully"]);
	} 
	else 
	{
		echo json_encode(["message" => "Failed to update user"]);
	}
}

/**
 * DELETE: Remove a user
 */
function handleDelete($conn, $input) 
{
	if (!isset($input['id'])) 
	{
		echo json_encode(["message" => "ID is required"]);
		return;
	}

	$sql = "DELETE FROM restful_users WHERE id = ?";
	$stmt = $conn->prepare($sql);
	
	if ($stmt->execute([trim(strip_tags($input['id']))])) 
	{
		echo json_encode(["message" => "User Deleted Successfully"]);
	} 
	else 
	{
		echo json_encode(["message" => "Failed to delete user"]);
	}
}
?>

Step 4: Testing Your API

You cannot test POST, PUT, or DELETE methods directly in the browser address bar. You should use a tool like Postman or Curl

1. Create a User (POST)

• URL: https://vasplus.info/demos/crud-restful-api/api.php

• Method: POST

• Body (raw JSON):

  JSON

  {
    "name": "John Doe",
    "email": "john@example.com"
  }

2. Read Users (GET)

• URL: https://vasplus.info/demos/crud-restful-api/api.php (for all users)

• URL: https://vasplus.info/demos/crud-restful-api/api.php?id=1 (for user ID 1)

3. Update a User (PUT)

• URL: https://vasplus.info/demos/crud-restful-api/api.php

• Method: PUT

• Body (raw JSON):

  JSON

  {
    "id": 1,
    "name": "Jane Doe",
    "email": "jane@example.com"
  }

4. Delete a User (DELETE)

• URL: https://vasplus.info/demos/crud-restful-api/api.php

• Method: DELETE

• Body (raw JSON):

  JSON

  {
    "id": 1
  }

USING CURL TO TEST OUR END-POINTS - api_request.php

Please download the script to get the complete API Scripts which includes the End-points function named CallAPI used below:

// API endpoint URL
$api_url = "https://vasplus.info/demos/crud-restful-api/api.php";


// Create (POST): Create a new resource
$data = array("name" => "Janet Doe", "email" => "janet@example.com");
$result = CallAPI('POST', $api_url, $data);
echo '<pre>';
print_r($result);
echo '<pre>';


// Read (GET): Retrieve resources
// Read all users
$result = CallAPI('GET', $api_url);
echo '<pre>';
print_r($result);
echo '<pre>';


// Read a specific user by ID
$user_id = 1;
$result = CallAPI('GET', $api_url .'?id='. $user_id);
echo '<pre>';
print_r($result);
echo '<pre>';


// Update (PUT): Modify an existing resource
$user_id = 1;
$data = array("id" => $user_id, "name" => "Jane Doe", "email" => "jane@example.com");
$result = CallAPI('PUT', $api_url, $data);
echo '<pre>';
print_r($result);
echo '<pre>';



// Delete (DELETE): Remove a resource.
$user_id = 1;
$data = array("id" => $user_id);
$result = CallAPI('DELETE', $api_url, $data);
echo '<pre>';
print_r($result);
echo '<pre>';
?>

Critical Best Practices

1. Input Sanitization: Always validate input. In a production app, verify that the email is valid and the ID is numeric before running SQL.

2. HTTP Status Codes: Enhance the code by returning specific status codes (e.g., http_response_code(201) for creation, 404 for not found) rather than just JSON messages.