Create Simple PHP 8 CRUD REST API with MySQL & PHP PDO

How to Create a PHP 8 & MySQL CRUD RESTful API

In this tutorial, we will guide you through creating a simple PHP 8 CRUD RESTful API with MySQL, covering how to Create, Read, Update, and Delete records from a MySQL database. This tutorial assumes you have a basic understanding of PHP and MySQL.

What is an API?

An API (Application Programming Interface) is a set of protocols and tools that allows software applications to communicate with each other. APIs define the methods and data formats that applications use to interact.

What is a REST API?

A REST API (Representational State Transfer) is a type of web service that follows the REST architectural style. It allows you to interact with resources (like database records) through standard HTTP methods such as GET, POST, PUT, and DELETE. A RESTful API is stateless, meaning each request from the client contains all the information the server needs to process it.

Setting Up the Project

PHP Project File Structure

Create the following folder structure for your PHP project:

/php-api-project
  /config
    database.php
  /class
    employees.php
  /api
    create.php
    read.php
    update.php
    delete.php

You can use MAMP or WAMP to set up the local PHP development environment, but we'll use the command line to run the PHP project.

Run the following command to start your PHP app:

php -S 127.0.0.1:8080

MySQL Database Setup

Before we start, we need to set up the MySQL database. You can do this using PhpMyAdmin or directly through the command line.

Create a new database:

CREATE DATABASE phpapidb;

Create a table for storing employee data:

CREATE TABLE IF NOT EXISTS `Employee` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(256) NOT NULL,
  `email` VARCHAR(50),
  `age` INT(11) NOT NULL,
  `designation` VARCHAR(255) NOT NULL,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Insert sample data into the Employee table:

INSERT INTO `Employee` (`name`, `email`, `age`, `designation`, `created`) VALUES 
('John Doe', 'johndoe@gmail.com', 32, 'Data Scientist', '2012-06-01 02:12:30'),
('David Costa', 'sam.mraz1996@yahoo.com', 29, 'Apparel Patternmaker', '2013-03-03 01:20:10');

Building the API

Database Connection

To connect PHP to the MySQL database, we'll use PDO (PHP Data Objects). In the /config/database.php file, we define the database connection:

<?php
class Database {
    private $host = "127.0.0.1";
    private $database_name = "phpapidb";
    private $username = "root";
    private $password = "xxxxxxxx"; // Replace with your password
    public $conn;

    public function getConnection() {
        $this->conn = null;
        try {
            $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->database_name, $this->username, $this->password);
            $this->conn->exec("set names utf8");
        } catch (PDOException $exception) {
            echo "Database could not be connected: " . $exception->getMessage();
        }
        return $this->conn;
    }
}
?>

Create PHP Class for CRUD Operations

The Employee class will handle all CRUD operations. Here’s an example of how you can structure it:

<?php
class Employee {
    private $conn;
    private $db_table = "Employee";

    public $id;
    public $name;
    public $email;
    public $age;
    public $designation;
    public $created;

    public function __construct($db) {
        $this->conn = $db;
    }

    // Create a new employee record
    public function createEmployee() {
        $sqlQuery = "INSERT INTO " . $this->db_table . " SET name = :name, email = :email, age = :age, designation = :designation, created = :created";
        $stmt = $this->conn->prepare($sqlQuery);

        $stmt->bindParam(":name", $this->name);
        $stmt->bindParam(":email", $this->email);
        $stmt->bindParam(":age", $this->age);
        $stmt->bindParam(":designation", $this->designation);
        $stmt->bindParam(":created", $this->created);

        if ($stmt->execute()) {
            return true;
        }
        return false;
    }

    // Fetch all employee records
    public function getEmployees() {
        $sqlQuery = "SELECT id, name, email, age, designation, created FROM " . $this->db_table;
        $stmt = $this->conn->prepare($sqlQuery);
        $stmt->execute();
        return $stmt;
    }

    // Fetch a single employee by ID
    public function getSingleEmployee() {
        $sqlQuery = "SELECT id, name, email, age, designation, created FROM " . $this->db_table . " WHERE id = ?";
        $stmt = $this->conn->prepare($sqlQuery);
        $stmt->bindParam(1, $this->id);
        $stmt->execute();
        $dataRow = $stmt->fetch(PDO::FETCH_ASSOC);

        $this->name = $dataRow['name'];
        $this->email = $dataRow['email'];
        $this->age = $dataRow['age'];
        $this->designation = $dataRow['designation'];
        $this->created = $dataRow['created'];
    }

    // Update an existing employee record
    public function updateEmployee() {
        $sqlQuery = "UPDATE " . $this->db_table . " SET name = :name, email = :email, age = :age, designation = :designation, created = :created WHERE id = :id";
        $stmt = $this->conn->prepare($sqlQuery);

        $stmt->bindParam(":name", $this->name);
        $stmt->bindParam(":email", $this->email);
        $stmt->bindParam(":age", $this->age);
        $stmt->bindParam(":designation", $this->designation);
        $stmt->bindParam(":created", $this->created);
        $stmt->bindParam(":id", $this->id);

        if ($stmt->execute()) {
            return true;
        }
        return false;
    }

    // Delete an employee record
    public function deleteEmployee() {
        $sqlQuery = "DELETE FROM " . $this->db_table . " WHERE id = ?";
        $stmt = $this->conn->prepare($sqlQuery);
        $stmt->bindParam(1, $this->id);

        if ($stmt->execute()) {
            return true;
        }
        return false;
    }
}
?>

API Endpoints

Now, create the PHP files to implement the CRUD operations as API endpoints.

Create (POST request): create.php
Read (GET request): read.php
Update (POST request): update.php
Delete (POST request): delete.php

Each file will correspond to a specific action like creating a new record or updating an existing one. You can refer to the previous tutorial for the complete code for each endpoint.

Testing the API

You can test the PHP API using Postman or any HTTP client. Here are the endpoints to test the operations:

Create a new employee (POST): http://localhost:8080/api/create.php
Get all employees (GET): http://localhost:8080/api/read.php
Get a single employee (GET): http://localhost:8080/api/single_read.php?id=2
Update an employee (POST): http://localhost:8080/api/update.php
Delete an employee (POST): http://localhost:8080/api/delete.php

Conclusion

This tutorial covered how to create a simple CRUD API using PHP 8 and **MySQL

Souy Soeng