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

Efficiently managing database operations is crucial for PHP application development. CRUD (Create, Read, Update, Delete) is one of the most common database operations. Adopting object-oriented programming (OOP) principles can help make your code simpler and easier to reuse and extend. Using MySQL with PHP also ensures that your application can handle data storage efficiently.

This blog post will walk you through how to build a PHP CRUD application using OOP and MySQL. We'll follow best practices and design patterns to organize the code so that it's beginner-friendly yet powerful enough to be used on larger projects.

After reading 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. Project Settings
3. Create database
4. Folder structure
5. Connect to database
6. Create CRUD operations
   • Create
   • Read
   • Update
   • Delete
7. Summary

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, easier to reuse, and easier to manage.

Applying OOP principles when working with databases means:

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

2. Project settings

Before we start coding, let’s set up an easy-to-maintain folder structure. Your project should be organized as follows:

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

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

3. Create database

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

<code class="language-sql">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
);</code>

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

4. Folder structure

We have set up the basic folder structure. Here's a breakdown of what each folder does:

• config/: Contains database configuration and connection logic.
• controllers/: Handle the request and call the corresponding method in the model.
• models/: Represents business logic and database interaction.
• views/: Display data to the user.
• public/: The public folder contains the index.php file, which will serve as the entry point to the application.

5. Connect to database

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

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

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

6. Create 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)

<code class="language-sql">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
);</code>

Create Controller (UserController.php)

<code class="language-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());
        }
    }
}</code>

Create view (user_list.php)

<code class="language-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();
    }
}</code>

Create entry point (index.php)

<code class="language-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);
    }
}</code>

Summary

By following OOP principles and applying best practices in PHP, we built a simple and scalable CRUD application. This approach allows you to easily extend your project with new functionality or improve database interaction.

In this guide, we cover:

• Simple and easy to maintain folder structure.
• Reusable Database class for MySQL connections.
• User model that encapsulates all CRUD operations.
• UserController for handling business logic.

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

Happy coding! ?

The above is the detailed content of Building a PHP CRUD Application with OOP and MySQL: A Best Practice Guide. For more information, please follow other related articles on the PHP Chinese website!