How to Build a Simple CRUD App with Native PHP and MySQL

CRUD stands for Create, Read, Update, Delete — the four basic operations found in almost every web application that uses a database. Understanding CRUD with native PHP is an essential foundation to master before moving on to a framework. In this tutorial, we will build a simple CRUD application to manage book data using PHP and MySQL with PDO.

Preparing the Database

First, create the database and table in MySQL. Run the following SQL in phpMyAdmin or the MySQL CLI:

CREATE DATABASE library;
USE library;

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100) NOT NULL,
    year INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Database Connection File

Create a db.php file that we will use on every page:

<?php
// db.php
$dsn = "mysql:host=localhost;dbname=library;charset=utf8mb4";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];

try {
    $pdo = new PDO($dsn, 'root', '', $options);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

Read — Displaying Data

Create an index.php file to display all book records along with links to the add, edit, and delete pages:

<?php
require 'db.php';

$stmt = $pdo->query("SELECT * FROM books ORDER BY id DESC");
$bookList = $stmt->fetchAll();
?>
<!-- HTML table of the book list -->
<a href="add.php">Add Book</a>
<table border="1">
  <tr>
    <th>Title</th><th>Author</th><th>Year</th><th>Actions</th>
  </tr>
  <?php foreach ($bookList as $book): ?>
  <tr>
    <td><?= htmlspecialchars($book['title']) ?></td>
    <td><?= htmlspecialchars($book['author']) ?></td>
    <td><?= $book['year'] ?></td>
    <td>
      <a href="edit.php?id=<?= $book['id'] ?>">Edit</a> |
      <a href="delete.php?id=<?= $book['id'] ?>" onclick="return confirm('Are you sure?')">Delete</a>
    </td>
  </tr>
  <?php endforeach; ?>
</table>

Note the use of htmlspecialchars() when printing data to HTML — this prevents XSS attacks.

Create — Adding Data

Create an add.php file with an input form and the saving logic:

<?php
require 'db.php';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $title  = trim($_POST['title']);
    $author = trim($_POST['author']);
    $year   = (int) $_POST['year'];

    if ($title && $author && $year) {
        $stmt = $pdo->prepare(
            "INSERT INTO books (title, author, year) VALUES (:title, :author, :year)"
        );
        $stmt->execute([
            ':title'  => $title,
            ':author' => $author,
            ':year'   => $year,
        ]);
        header('Location: index.php');
        exit;
    }
}
?>
<form method="POST">
  <input type="text" name="title" placeholder="Book Title" required>
  <input type="text" name="author" placeholder="Author" required>
  <input type="number" name="year" placeholder="Publication Year" required>
  <button type="submit">Save</button>
</form>

We use prepared statements with prepare() and execute() — the safe way to insert data into the database, because user input is never concatenated directly into the SQL query.

Update — Editing Data

Create an edit.php file to fetch the existing record and update it:

<?php
require 'db.php';

$id = (int) ($_GET['id'] ?? 0);

// Fetch the existing record
$stmt = $pdo->prepare("SELECT * FROM books WHERE id = :id");
$stmt->execute([':id' => $id]);
$book = $stmt->fetch();

if (!$book) {
    die("Book not found.");
}

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $title  = trim($_POST['title']);
    $author = trim($_POST['author']);
    $year   = (int) $_POST['year'];

    $stmt = $pdo->prepare(
        "UPDATE books SET title = :title, author = :author, year = :year WHERE id = :id"
    );
    $stmt->execute([
        ':title'  => $title,
        ':author' => $author,
        ':year'   => $year,
        ':id'     => $id,
    ]);
    header('Location: index.php');
    exit;
}
?>
<form method="POST">
  <input type="text" name="title" value="<?= htmlspecialchars($book['title']) ?>" required>
  <input type="text" name="author" value="<?= htmlspecialchars($book['author']) ?>" required>
  <input type="number" name="year" value="<?= $book['year'] ?>" required>
  <button type="submit">Update</button>
</form>

Delete — Removing Data

Create a delete.php file that is simple but safe:

<?php
require 'db.php';

$id = (int) ($_GET['id'] ?? 0);

if ($id > 0) {
    $stmt = $pdo->prepare("DELETE FROM books WHERE id = :id");
    $stmt->execute([':id' => $id]);
}

header('Location: index.php');
exit;

The ID is always cast to an integer ((int)) before use — a quick way to prevent dangerous parameter manipulation.

Conclusion

With native PHP and PDO, you can already build a functional and secure CRUD application. The key is to always use prepared statements for INSERT, UPDATE, and DELETE operations, and htmlspecialchars() when printing data to HTML. From this foundation, you can develop the application further by adding validation, authentication, and a better interface.