Building a PHP CRUD Application with OOP and MySQL: A Best Practice Guide

When developing PHP applications, managing database operations effectively is crucial. One of the most common operations you’ll perform is CRUD (Create, Read, Update, Delete). Adopting Object-Oriented Programming (OOP) principles helps make your code cleaner, reusable, and scalable. Using MySQL with PHP also ensures that your application can handle data storage efficiently.

In this blog, we’ll go through building a PHP CRUD application with OOP and MySQL. We’ll organize the code using best practices and design patterns that are beginner-friendly yet robust enough for larger projects.

By the end of this guide, you will have a solid foundation for working with databases using OOP principles in PHP.

Table of Contents

1. The Importance of OOP in PHP
2. Setting Up the Project
3. Creating the Database
4. Folder Structure
5. Connecting to the Database
6. Creating CRUD Operations
   • Create
   • Read
   • Update
   • Delete
7. Final Thoughts

1. The Importance of OOP in PHP

Object-Oriented Programming (OOP) is a programming paradigm that uses "objects" to organize code. In PHP, OOP allows you to create classes that represent real-world entities, making your code more modular, reusable, and easier to manage.

When working with databases, applying OOP principles means:

• Separation of Concerns: Database logic is encapsulated in a class, separate from the rest of your application logic.
• Reusability: You can reuse the database class in multiple parts of the application.
• Maintainability: Your code is easier to update and extend as your application grows.

2. Setting Up the Project

Before diving into coding, let’s set up a folder structure that’s easy to maintain. Here’s how your project should be organized:

php-crud/
├── config/
│   └── Database.php
├── controllers/
│   └── UserController.php
├── models/
│   └── User.php
├── views/
│   └── user_list.php
├── public/
│   └── index.php
└── .gitignore

• config/Database.php: Contains the database connection logic.
• controllers/UserController.php: Handles CRUD operations and communicates with the model.
• models/User.php: Contains the logic to interact with the users table in MySQL.
• views/user_list.php: Displays user data in a table.
• public/index.php: The entry point for the application.

3. Creating the Database

Let’s start by creating the database and a users table in MySQL. You can execute the following SQL queries to set up the database:

CREATE DATABASE php_crud;

USE php_crud;

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

This table will store basic user information such as name, email, and created_at.

4. Folder Structure

We’ve already set up the basic folder structure. Here’s a breakdown of each folder’s purpose:

• config/: Contains the database configuration and connection logic.
• controllers/: Handles requests and calls the appropriate methods from the model.
• models/: Represents the business logic and database interactions.
• views/: Displays the data to the user.
• public/: The public folder contains the index.php file, which will be the entry point for the application.

5. Connecting to the Database

Let’s start by creating a database connection class in config/Database.php:

<?php

namespace Config;

use PDO;

class Database
{
    private $host = '127.0.0.1';
    private $dbName = 'php_crud';
    private $username = 'root';
    private $password = '';
    private $connection;

    public function connect()
    {
        try {
            $this->connection = new PDO(
                "mysql:host={$this->host};dbname={$this->dbName}",
                $this->username,
                $this->password
            );
            $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            return $this->connection;
        } catch (PDOException $e) {
            die("Database connection failed: " . $e->getMessage());
        }
    }
}

This class creates a PDO connection to MySQL and is reusable across your project.

6. Creating CRUD Operations

Let’s create a model for handling user data. This class will interact with the users table and perform CRUD operations.

Create Model (User.php)

<?php

namespace Models;

use Config\Database;

class User
{
    private $conn;

    public function __construct()
    {
        $database = new Database();
        $this->conn = $database->connect();
    }

    public function create($name, $email)
    {
        $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':email', $email);
        return $stmt->execute();
    }

    public function read()
    {
        $sql = "SELECT * FROM users";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function update($id, $name, $email)
    {
        $sql = "UPDATE users SET name = :name, email = :email WHERE id = :id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':id', $id);
        return $stmt->execute();
    }

    public function delete($id)
    {
        $sql = "DELETE FROM users WHERE id = :id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':id', $id);
        return $stmt->execute();
    }
}

Create Controller (UserController.php)

<?php

namespace Controllers;

use Models\User;

class UserController
{
    public function createUser($name, $email)
    {
        $user = new User();
        return $user->create($name, $email);
    }

    public function getUsers()
    {
        $user = new User();
        return $user->read();
    }

    public function updateUser($id, $name, $email)
    {
        $user = new User();
        return $user->update($id, $name, $email);
    }

    public function deleteUser($id)
    {
        $user = new User();
        return $user->delete($id);
    }
}

Create View (user_list.php)

<?php
// This file is responsible for displaying the list of users.

require_once __DIR__ . '/../controllers/UserController.php';

$userController = new \Controllers\UserController();
$users = $userController->getUsers();

echo "<h1>User List</h1>";
echo "<table><tr><th>ID</th><th>Name</th><th>Email</th><th>Actions</th></tr>";
foreach ($users as $user) {
    echo "<tr><td>{$user['id']}</td><td>{$user['name']}</td><td>{$user['email']}</td><td><a href='update.php?id={$user['id']}'>Edit</a> | <a href='delete.php?id={$user['id']}'>Delete</a></td></tr>";
}
echo "</table>";
?>

Create the Entry Point (index.php)

<?php

require_once __DIR__ . '/../models/User.php';
require_once __DIR__ . '/../controllers/UserController.php';

$userController = new \Controllers\UserController();

// Example: Create a user
$userController->createUser('John Doe', '[e-mail]');

// Example: Read users
$userController->getUsers();

Final Thoughts

By following OOP principles and applying best practices in PHP, we’ve built a simple and scalable CRUD application. This approach makes it easy to expand your project with new features or improve database interactions.

In this guide, we’ve covered:

• A simple and maintainable folder structure.
• A reusable Database class for MySQL connections.
• A User model that encapsulates all CRUD operations.
• A UserController to handle business logic.

This structure makes your PHP applications cleaner, more modular, and easier to extend. You can now take this approach and build larger, more complex applications using OOP and MySQL.

Happy coding! 🎉