PHP CRUD with MySQL and REST API
CRUD is one of the most important concepts in backend development. It stands for Create, Read, Update, and Delete. These four operations represent the basic actions used to manage data in almost every web application.
When CRUD is combined with MySQL and a REST API, PHP can be used to build backend services that allow websites, mobile applications, dashboards, and frontend frameworks to interact with database records through HTTP requests.
This article explains PHP CRUD with MySQL and REST API concepts, including database structure, PDO connection, prepared statements, JSON responses, HTTP methods, create endpoint, read endpoint, update endpoint, delete endpoint, validation, error handling, and security best practices.
What Is CRUD?
CRUD describes the four main operations used to manage data in a database. Most applications use CRUD in one form or another.
Create: add a new record to the database.
Read: retrieve one or more records from the database.
Update: modify an existing record.
Delete: remove a record from the database.
For example, in a blog application, CRUD can be used to create posts, display posts, update post content, and delete posts. In an e-commerce system, CRUD can manage products, categories, customers, and orders.
CRUD is not limited to admin panels. It is also the foundation of APIs, dashboards, content management systems, user profiles, task managers, and many backend services.
What Is a REST API?
REST API stands for Representational State Transfer Application Programming Interface. In simple terms, a REST API allows different applications to communicate using HTTP requests.
A frontend application can send a request to a PHP API, and PHP can return data as JSON. The frontend can then display that data without needing to know the internal database structure.
A REST API usually uses HTTP methods to describe the action:
GET: retrieve data.
POST: create new data.
PUT: update existing data completely.
PATCH: update part of existing data.
DELETE: delete data.
For example, a posts API may use routes such as:
GET /api/posts to list posts.
GET /api/posts/5 to show one post.
POST /api/posts to create a post.
PUT /api/posts/5 to update a post.
DELETE /api/posts/5 to delete a post.
REST APIs commonly return JSON because JSON is easy to read and easy to use with JavaScript, mobile apps, and other backend systems.
Database Table for CRUD
To build a CRUD example, we need a database table. In this article, we will use a simple posts table.
CREATE TABLE posts (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
status VARCHAR(50) NOT NULL DEFAULT 'draft',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL
);This table contains basic fields for a blog post. The id column is the primary key. The title and content columns store the post data. The status column can be used to mark the post as draft or published.
The created_at field stores when the record was created, while updated_at can store the last update time.
You can use the same CRUD logic with other tables such as users, products, categories, comments, tasks, or messages.
PDO Database Connection
PHP can connect to MySQL using PDO. PDO is a good choice for CRUD APIs because it supports prepared statements and structured error handling.
Create a file named db.php for the database connection.
<?php
$host = "localhost";
$dbname = "php_api";
$username = "root";
$password = "";
try {
$pdo = new PDO(
"mysql:host=$host;dbname=$dbname;charset=utf8mb4",
$username,
$password
);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (PDOException $exception) {
http_response_code(500);
echo json_encode([
"success" => false,
"message" => "Database connection failed."
]);
exit;
}
?>The utf8mb4 charset is important for multilingual applications because it supports Arabic, Turkish, English, and many special characters.
In production, database credentials should be stored in environment variables or configuration files outside the public directory.
API Response Helper
REST APIs should return consistent JSON responses. A helper function can make responses cleaner and easier to maintain.
<?php
function jsonResponse($data, $statusCode = 200) {
http_response_code($statusCode);
header("Content-Type: application/json; charset=UTF-8");
echo json_encode($data);
exit;
}
?>This helper sets the HTTP status code, sets the JSON content type, encodes the response, and stops the script.
A successful response may look like this:
{
"success": true,
"message": "Post created successfully.",
"data": {
"id": 1,
"title": "PHP CRUD"
}
}An error response may look like this:
{
"success": false,
"message": "Validation failed.",
"errors": {
"title": "Title is required."
}
}Consistent responses make the API easier to use from frontend applications.
Reading JSON Request Data
When building an API, clients often send data as JSON instead of normal form data. PHP can read the raw request body using php://input.
<?php
function getJsonInput() {
$json = file_get_contents("php://input");
$data = json_decode($json, true);
if (!is_array($data)) {
return [];
}
return $data;
}
?>For example, a frontend may send this JSON when creating a post:
{
"title": "Learning PHP REST API",
"content": "This is a post about PHP API development.",
"status": "published"
}The PHP API can decode this JSON and use the values for validation and database operations.
Create Record with POST
The create operation adds a new record to the database. In a REST API, creating data is usually done using the POST method.
The following example creates a new post using JSON input and a prepared statement.
<?php
require "db.php";
require "helpers.php";
$data = getJsonInput();
$title = trim($data["title"] ?? "");
$content = trim($data["content"] ?? "");
$status = trim($data["status"] ?? "draft");
$errors = [];
if ($title === "") {
$errors["title"] = "Title is required.";
}
if ($content === "") {
$errors["content"] = "Content is required.";
}
if (!in_array($status, ["draft", "published"], true)) {
$errors["status"] = "Status must be draft or published.";
}
if (count($errors) > 0) {
jsonResponse([
"success" => false,
"message" => "Validation failed.",
"errors" => $errors
], 422);
}
$statement = $pdo->prepare("
INSERT INTO posts (title, content, status)
VALUES (:title, :content, :status)
");
$statement->execute([
"title" => $title,
"content" => $content,
"status" => $status
]);
$postId = $pdo->lastInsertId();
jsonResponse([
"success" => true,
"message" => "Post created successfully.",
"data" => [
"id" => $postId,
"title" => $title,
"content" => $content,
"status" => $status
]
], 201);
?>This example validates the input, inserts the record, gets the last inserted ID, and returns a JSON response with status code 201 Created.
Prepared statements protect the insert query from SQL injection because user input is passed separately from the SQL structure.
Read Records with GET
The read operation retrieves data from the database. In a REST API, reading data is usually done using the GET method.
The following example returns a list of posts.
<?php
require "db.php";
require "helpers.php";
$statement = $pdo->query("
SELECT id, title, content, status, created_at, updated_at
FROM posts
ORDER BY id DESC
");
$posts = $statement->fetchAll();
jsonResponse([
"success" => true,
"data" => $posts
]);
?>This endpoint returns all posts as a JSON array. In real applications, you may also add pagination, search, filters, or status conditions.
For example, to return only published posts, you can use a prepared statement:
<?php
$status = "published";
$statement = $pdo->prepare("
SELECT id, title, content, status, created_at
FROM posts
WHERE status = :status
ORDER BY id DESC
");
$statement->execute([
"status" => $status
]);
$posts = $statement->fetchAll();
?>Read endpoints are used by frontend pages, dashboards, mobile apps, and API clients to display stored data.
Read Single Record with GET
Sometimes an API needs to return one record by ID. For example, a frontend may request one post to display a detail page.
<?php
require "db.php";
require "helpers.php";
$id = filter_input(INPUT_GET, "id", FILTER_VALIDATE_INT);
if (!$id) {
jsonResponse([
"success" => false,
"message" => "Valid post ID is required."
], 400);
}
$statement = $pdo->prepare("
SELECT id, title, content, status, created_at, updated_at
FROM posts
WHERE id = :id
LIMIT 1
");
$statement->execute([
"id" => $id
]);
$post = $statement->fetch();
if (!$post) {
jsonResponse([
"success" => false,
"message" => "Post not found."
], 404);
}
jsonResponse([
"success" => true,
"data" => $post
]);
?>This example validates the ID, searches for the post, returns a 404 response if it does not exist, and returns the post data if found.
Returning correct HTTP status codes helps frontend developers handle different cases properly.
Update Record with PUT or PATCH
The update operation modifies an existing record. In REST APIs, PUT is commonly used for full updates, while PATCH is used for partial updates.
The following example updates a post using JSON input.
<?php
require "db.php";
require "helpers.php";
$id = filter_input(INPUT_GET, "id", FILTER_VALIDATE_INT);
if (!$id) {
jsonResponse([
"success" => false,
"message" => "Valid post ID is required."
], 400);
}
$data = getJsonInput();
$title = trim($data["title"] ?? "");
$content = trim($data["content"] ?? "");
$status = trim($data["status"] ?? "draft");
$errors = [];
if ($title === "") {
$errors["title"] = "Title is required.";
}
if ($content === "") {
$errors["content"] = "Content is required.";
}
if (!in_array($status, ["draft", "published"], true)) {
$errors["status"] = "Status must be draft or published.";
}
if (count($errors) > 0) {
jsonResponse([
"success" => false,
"message" => "Validation failed.",
"errors" => $errors
], 422);
}
$statement = $pdo->prepare("
UPDATE posts
SET title = :title,
content = :content,
status = :status,
updated_at = NOW()
WHERE id = :id
");
$statement->execute([
"id" => $id,
"title" => $title,
"content" => $content,
"status" => $status
]);
if ($statement->rowCount() === 0) {
jsonResponse([
"success" => false,
"message" => "Post not found or no changes made."
], 404);
}
jsonResponse([
"success" => true,
"message" => "Post updated successfully."
]);
?>This example validates the ID and input, updates the record, and returns a JSON response.
In real applications, you may first check whether the record exists before updating it. This can make the response more accurate when there are no changes.
Delete Record with DELETE
The delete operation removes a record from the database. In REST APIs, deleting data is usually done using the DELETE method.
The following example deletes a post by ID.
<?php
require "db.php";
require "helpers.php";
$id = filter_input(INPUT_GET, "id", FILTER_VALIDATE_INT);
if (!$id) {
jsonResponse([
"success" => false,
"message" => "Valid post ID is required."
], 400);
}
$statement = $pdo->prepare("DELETE FROM posts WHERE id = :id");
$statement->execute([
"id" => $id
]);
if ($statement->rowCount() === 0) {
jsonResponse([
"success" => false,
"message" => "Post not found."
], 404);
}
jsonResponse([
"success" => true,
"message" => "Post deleted successfully."
]);
?>Delete operations should always be protected in real applications. Users should not be able to delete records unless they have permission.
Some systems use soft delete instead of permanent delete. Soft delete marks the record as deleted using a field such as deleted_at, while keeping the data in the database.
Simple REST Router in PHP
Instead of creating a separate file for each endpoint, you can build a simple router that checks the request method and performs the correct action.
A very simple router can use $_SERVER["REQUEST_METHOD"].
<?php
require "db.php";
require "helpers.php";
$method = $_SERVER["REQUEST_METHOD"];
$id = filter_input(INPUT_GET, "id", FILTER_VALIDATE_INT);
if ($method === "GET" && $id) {
// Show one post
} elseif ($method === "GET") {
// List posts
} elseif ($method === "POST") {
// Create post
} elseif ($method === "PUT" || $method === "PATCH") {
// Update post
} elseif ($method === "DELETE") {
// Delete post
} else {
jsonResponse([
"success" => false,
"message" => "Method not allowed."
], 405);
}
?>This is a basic example. Professional applications usually use a router, framework, or MVC structure to organize API endpoints more cleanly.
However, understanding this simple structure helps beginners understand how REST APIs work behind the scenes.
Validation in CRUD APIs
Validation is essential in CRUD APIs. Every create and update request should check the submitted data before saving it to the database.
For a posts API, validation may include:
Title is required.
Title must not be too long.
Content is required.
Status must be draft or published.
ID must be a valid integer.
A simple validation function can make the code cleaner:
<?php
function validatePostData($data) {
$errors = [];
$title = trim($data["title"] ?? "");
$content = trim($data["content"] ?? "");
$status = trim($data["status"] ?? "draft");
if ($title === "") {
$errors["title"] = "Title is required.";
} elseif (strlen($title) > 200) {
$errors["title"] = "Title must not exceed 200 characters.";
}
if ($content === "") {
$errors["content"] = "Content is required.";
}
if (!in_array($status, ["draft", "published"], true)) {
$errors["status"] = "Status must be draft or published.";
}
return $errors;
}
?>Returning validation errors as JSON helps the frontend show clear messages to the user.
Prepared Statements in CRUD APIs
Prepared statements are necessary in CRUD APIs because API input often comes from users, forms, frontend apps, or external systems.
Unsafe SQL should be avoided:
<?php
$sql = "SELECT * FROM posts WHERE id = " . $_GET["id"];
?>Safe SQL uses prepared statements:
<?php
$statement = $pdo->prepare("SELECT * FROM posts WHERE id = :id");
$statement->execute([
"id" => $id
]);
?>Prepared statements protect against SQL injection by separating the query structure from the input values.
Use prepared statements for SELECT, INSERT, UPDATE, and DELETE operations whenever input is involved.
HTTP Status Codes in REST API
HTTP status codes help API clients understand the result of a request. A good REST API should return meaningful status codes.
Common status codes include:
200 OK: the request succeeded.
201 Created: a new record was created.
400 Bad Request: the request is invalid.
401 Unauthorized: authentication is required.
403 Forbidden: the user is not allowed to perform the action.
404 Not Found: the requested record does not exist.
405 Method Not Allowed: the HTTP method is not supported.
422 Unprocessable Entity: validation failed.
500 Internal Server Error: something failed on the server.
For example, if a post is created successfully, return 201. If validation fails, return 422. If the post does not exist, return 404.
Status codes make APIs more professional and easier to integrate with frontend applications.
Search and Filtering in CRUD API
CRUD APIs often need search and filtering. For example, a posts API may allow searching by title or filtering by status.
<?php
$keyword = trim($_GET["q"] ?? "");
$status = trim($_GET["status"] ?? "");
$sql = "SELECT id, title, content, status, created_at FROM posts WHERE 1 = 1";
$params = [];
if ($keyword !== "") {
$sql .= " AND title LIKE :keyword";
$params["keyword"] = "%" . $keyword . "%";
}
if ($status !== "") {
$sql .= " AND status = :status";
$params["status"] = $status;
}
$sql .= " ORDER BY id DESC";
$statement = $pdo->prepare($sql);
$statement->execute($params);
$posts = $statement->fetchAll();
jsonResponse([
"success" => true,
"data" => $posts
]);
?>This example builds a dynamic query while still using prepared statements for input values.
Search and filtering are useful in admin panels, dashboards, blog systems, product lists, and reporting pages.
Pagination in CRUD API
Pagination limits how many records are returned at once. This improves performance and makes large datasets easier to display.
A simple pagination system can use LIMIT and OFFSET.
<?php
$page = max(1, (int) ($_GET["page"] ?? 1));
$perPage = 10;
$offset = ($page - 1) * $perPage;
$statement = $pdo->prepare("
SELECT id, title, content, status, created_at
FROM posts
ORDER BY id DESC
LIMIT :limit OFFSET :offset
");
$statement->bindValue(":limit", $perPage, PDO::PARAM_INT);
$statement->bindValue(":offset", $offset, PDO::PARAM_INT);
$statement->execute();
$posts = $statement->fetchAll();
jsonResponse([
"success" => true,
"page" => $page,
"per_page" => $perPage,
"data" => $posts
]);
?>Pagination is important when working with many records. Without pagination, an API may return too much data and become slow.
CORS in PHP REST APIs
CORS stands for Cross-Origin Resource Sharing. It controls whether a frontend from another domain can call your API.
During development, you may need to allow requests from a frontend application running on another domain or port.
<?php
header("Access-Control-Allow-Origin: https://example.com");
header("Access-Control-Allow-Methods: GET, POST, PUT, PATCH, DELETE, OPTIONS");
header("Access-Control-Allow-Headers: Content-Type, Authorization");
if ($_SERVER["REQUEST_METHOD"] === "OPTIONS") {
http_response_code(204);
exit;
}
?>For production, avoid allowing all origins unless it is truly necessary. It is safer to allow only trusted frontend domains.
CORS is not authentication. It only controls browser-based cross-origin access. Your API should still use proper authentication and authorization for protected actions.
Authentication for CRUD REST API
Some CRUD APIs are public, but many need authentication. For example, anyone may read published posts, but only logged-in admins should create, update, or delete posts.
Authentication can be handled using sessions, API tokens, JWT, or framework-specific authentication tools.
A very simple API token check may look like this:
<?php
$headers = getallheaders();
$token = $headers["Authorization"] ?? "";
$expectedToken = "Bearer your-secret-token";
if ($token !== $expectedToken) {
jsonResponse([
"success" => false,
"message" => "Unauthorized."
], 401);
}
?>This is only a simple demonstration. Real API authentication should be designed more carefully, with secure token storage, expiration, permissions, and HTTPS.
Authorization is also important. A logged-in user should only perform actions they are allowed to perform.
Complete CRUD API Structure
A small PHP CRUD REST API project can be organized like this:
project/
├── api/
│ └── posts.php
├── config/
│ └── db.php
├── helpers/
│ └── response.php
└── public/
└── index.htmlThe db.php file contains the database connection. The response.php file contains helper functions such as JSON responses and JSON input reading. The posts.php file contains CRUD logic.
For larger projects, the structure should be cleaner and may include controllers, models, services, repositories, middleware, and routing.
Frameworks such as Laravel provide these structures out of the box, but building a simple pure PHP CRUD API helps you understand the core idea.
Security Notes for PHP CRUD and REST API
CRUD APIs handle database data, so security is very important. Any endpoint that receives input, modifies data, or exposes private information should be protected.
Important security practices include:
Use prepared statements for all database queries.
Validate all request data before saving it.
Return safe error messages without exposing database details.
Use authentication for protected endpoints.
Use authorization checks for update and delete actions.
Use HTTPS in production.
Limit CORS to trusted domains.
Escape output when displaying API data in HTML.
Use pagination for large datasets.
Log server errors privately instead of showing them to users.
Security should be applied at every layer: input validation, database access, authentication, authorization, response handling, and deployment configuration.
CRUD API in Modern PHP Frameworks
Modern PHP frameworks such as Laravel make CRUD API development faster and more organized. Laravel provides routing, controllers, request validation, Eloquent models, migrations, API resources, middleware, authentication, and authorization.
However, understanding CRUD with plain PHP and PDO is still important. It teaches the foundation behind database operations, JSON responses, prepared statements, HTTP methods, and API structure.
Once you understand pure PHP CRUD APIs, moving to Laravel APIs becomes easier because the same concepts still exist, but they are organized through framework tools.
Conclusion
PHP CRUD with MySQL and REST API is a fundamental backend development topic. It combines database operations, HTTP methods, JSON responses, validation, prepared statements, error handling, and security practices.
The main CRUD operations are create, read, update, and delete. In a REST API, these operations are usually connected to HTTP methods such as POST, GET, PUT, PATCH, and DELETE.
After learning CRUD with MySQL and REST API in PHP, the next step is to build a small practical project such as a blog API, task manager API, product management API, or admin dashboard API. These projects will help connect PHP, MySQL, and frontend applications in a real development workflow.
