Wednesday, February 5, 2025

Develop simple REST API with PHP and MySQL

 Overview

In this article we will develop is simple REST API with PHP. The database backend is MySQL running in a Docker container and the sample data represents students.

Source code: https://github.com/medhatelmasry/school-api

Getting Started

Start MySQL in a Docker container with:

docker run -d -p 3333:3306 --name maria -e MYSQL_ROOT_PASSWORD=secret mariadb:10.7.3

In a working directory named school-api, create the following sub-folders:

mkdir src
cd src
mkdir Controller
mkdir System
mkdir TableGateways
cd ..
mkdir public

Add a composer.json file in the top directory with just one dependency: the DotEnv library which will allow us to keep our authentication details in a .env file outside our code repository:


Contents of the composer.json file:

{
    "require": {
        "vlucas/phpdotenv": "^2.4"
    },
    "autoload": {
        "psr-4": {
            "Src\\": "src/"
        }
    }
}

We also configured a PSR-4 autoloader which will automatically look for PHP classes in the /src directory.

We can install our dependencies with:

composer install

We now have a /vendor directory, and the DotEnv dependency is installed (we can also use our autoloader to load our classes from /src with no include() calls).

Let’s create a .gitignore file for our project with two lines in it, so the /vendor directory and our local .env file are ignored if our code is pushed to source control. Add this to .gitignore:

vendor/
.env

Next, add a .env file where we’ll put our database connection details:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3333
DB_DATABASE=apidb
DB_USERNAME=root
DB_PASSWORD=secret

Add a bootstrap.php file to load environment variables.

<?php
error_reporting(E_ALL & ~E_DEPRECATED);

require 'vendor/autoload.php';

use Dotenv\Dotenv;

use Src\System\DatabaseConnector;

$dotenv = new DotEnv(__DIR__);
$dotenv->load();

$dbConnection = (new DatabaseConnector())->getConnection();

Configure DB for PHP REST API

We can now create a class to hold our database connection and add the initialization of the connection to our bootstrap.php file. In the src/System/ folder, add a file named DatabaseConnector.php with this code:

<?php
namespace Src\System;
class DatabaseConnector {
    private $dbConnection = null;
    public function __construct() {
        $host = getenv('DB_HOST');
        $port = getenv('DB_PORT');
        $db   = getenv('DB_DATABASE');
        $user = getenv('DB_USERNAME');
        $pass = getenv('DB_PASSWORD');
        try {
            $pdo = new \PDO("mysql:host=$host;port=$port;charset=utf8mb4", $user, $pass);
            $pdo->exec("CREATE DATABASE IF NOT EXISTS `$db`");
        } catch (\PDOException $e) {
            die("DB ERROR: " . $e->getMessage());
        }
        try {
            $this->dbConnection = new \PDO(
                "mysql:host=$host;port=$port;charset=utf8mb4;dbname=$db",
                $user,
                $pass
            );
        } catch (\PDOException $e) {
            exit($e->getMessage());
        }
    }
         public function getConnection() {
        return $this->dbConnection;
    }
}

Let’s create a dbseed.php file which creates a students table and inserts some records for testing. Code for dbseed.php is shown below:

<?php
require 'bootstrap.php';
$statement = "
    CREATE TABLE IF NOT EXISTS students (
        id INT NOT NULL AUTO_INCREMENT,
        firstname VARCHAR(40) NOT NULL,
        lastname VARCHAR(40) NOT NULL,
        school VARCHAR(40) NOT NULL,
        PRIMARY KEY (id)
    );
    INSERT INTO students
        (firstname, lastname, school)
    VALUES
        ('Mark', 'Fisher','Computing'),
        ('Lisa', 'Fisher','Computing'),
        ('Judy', 'Fisher','Computing'),
        ('Jane', 'Smith','Nursing'),
        ('Mary', 'Smith','Nursing'),
        ('Andy', 'Smith','Nursing'),
        ('Bill', 'Smith','Business'),
        ('Fred', 'Plumber','Business'),
        ('Anna', 'Plumber','Business');
";
try {
    $createTable = $dbConnection->exec($statement);
    echo "Success!\n";
} catch (\PDOException $e) {
    exit($e->getMessage());
}

Run the following command to seed the database with sample data:

php dbseed.php

Add a Gateway Class for students table

In the src/TableGateways/StudentsGateway.php class file, we will implement methods to return all students, return a specific student and add/update/delete a student:

<?php
namespace Src\TableGateways;
class StudentsGateway {
    private $db = null;
    public function __construct($db) {
        $this->db = $db;
    }
    public function findAll() {
        $statement = "
            SELECT 
                id, firstname, lastname, school
            FROM
                students;
        ";
        try {
            $statement = $this->db->query($statement);
            $result = $statement->fetchAll(\PDO::FETCH_ASSOC);
            return $result;
        } catch (\PDOException $e) {
            exit($e->getMessage());
        }
    }
    public function find($id) {
        $statement = "
            SELECT 
                id, firstname, lastname, school
            FROM
                students
            WHERE id = ?;
        ";
        try {
            $statement = $this->db->prepare($statement);
            $statement->execute(array($id));
            $result = $statement->fetchAll(\PDO::FETCH_ASSOC);
            return $result;
        } catch (\PDOException $e) {
            exit($e->getMessage());
        }    
    }
    public function insert(Array $input) {
        $statement = "
            INSERT INTO students 
                (firstname, lastname, school)
            VALUES
                (:firstname, :lastname, :school);
        ";
        try {
            $statement = $this->db->prepare($statement);
            $statement->execute(array(
                'firstname' => $input['firstname'],
                'lastname'  => $input['lastname'],
                'school' => $input['school'] ?? null,
            ));
            return $statement->rowCount();
        } catch (\PDOException $e) {
            exit($e->getMessage());
        }    
    }
    public function update($id, Array $input) {
        $statement = "
            UPDATE students
            SET 
                firstname = :firstname,
                lastname  = :lastname,
                school = :school
            WHERE id = :id;
        ";
        try {
            $statement = $this->db->prepare($statement);
            $statement->execute(array(
                'id' => (int) $id,
                'firstname' => $input['firstname'],
                'lastname'  => $input['lastname'],
                'school' => $input['school'] ?? null,
            ));
            return $statement->rowCount();
        } catch (\PDOException $e) {
            exit($e->getMessage());
        }    
    }
    public function delete($id) {
        $statement = "
            DELETE FROM students
            WHERE id = :id;
        ";
        try {
            $statement = $this->db->prepare($statement);
            $statement->execute(array('id' => $id));
            return $statement->rowCount();
        } catch (\PDOException $e) {
            exit($e->getMessage());
        }    
    }
}

Implement the PHP REST API

Our REST API will have the following endpoints:

return all recordsGET /students
return a specific recordGET /students/{id}
create a new recordPOST /students
update an existing recordPUT /students/{id}
delete an existing recordDELETE /students/{id}

Create a src/Controller/StudentsController.php to handle the API endpoints with this code:

<?php
namespace Src\Controller;
use Src\TableGateways\StudentsGateway;
class StudentsController {
    private $db;
    private $requestMethod;
    private $id;
    private $studentsGateway;
    public function __construct($db, $requestMethod, $id) {
        $this->db = $db;
        $this->requestMethod = $requestMethod;
        $this->id = $id;
        $this->studentsGateway = new StudentsGateway($db);
    }
    public function processRequest() {
        switch ($this->requestMethod) {
            case 'GET':
                if ($this->id) {
                    $response = $this->getById($this->id);
                } else {
                    $response = $this->getAll();
                };
                break;
            case 'POST':
                $response = $this->createRequest();
                break;
            case 'PUT':
                $response = $this->updateFromRequest($this->id);
                break;
            case 'DELETE':
                $response = $this->deleteById($this->id);
                break;
            default:
                $response = $this->notFoundResponse();
                break;
        }
        header($response['status_code_header']);
        if ($response['body']) {
            echo $response['body'];
        }
    }
    private function getAll() {
        $result = $this->studentsGateway->findAll();
        $response['status_code_header'] = 'HTTP/1.1 200 OK';
        $response['body'] = json_encode($result);
        return $response;
    }
    private function getById($id) {
        $result = $this->studentsGateway->find($id);
        if (! $result) {
            return $this->notFoundResponse();
        }
        $response['status_code_header'] = 'HTTP/1.1 200 OK';
        $response['body'] = json_encode($result);
        return $response;
    }
    private function createRequest() {
        $input = (array) json_decode(file_get_contents('php://input'), TRUE);
        if (! $this->validate($input)) {
            return $this->unprocessableEntityResponse();
        }
        $this->studentsGateway->insert($input);
        $response['status_code_header'] = 'HTTP/1.1 201 Created';
        $response['body'] = null;
        return $response;
    }
    private function updateFromRequest($id) {
        $result = $this->studentsGateway->find($id);
        if (! $result) {
            return $this->notFoundResponse();
        }
        $input = (array) json_decode(file_get_contents('php://input'), TRUE);
        if (! $this->validate($input)) {
            return $this->unprocessableEntityResponse();
        }
        $this->studentsGateway->update($id, $input);
        $response['status_code_header'] = 'HTTP/1.1 200 OK';
        $response['body'] = null;
        return $response;
    }
    private function deleteById($id) {
        $result = $this->studentsGateway->find($id);
        if (! $result) {
            return $this->notFoundResponse();
        }
        $this->studentsGateway->delete($id);
        $response['status_code_header'] = 'HTTP/1.1 200 OK';
        $response['body'] = null;
        return $response;
    }
    private function validate($input) {
        if (! isset($input['firstname'])) {
            return false;
        }
        if (! isset($input['lastname'])) {
            return false;
        }
        return true;
    }
    private function unprocessableEntityResponse() {
        $response['status_code_header'] = 'HTTP/1.1 422 Unprocessable Entity';
        $response['body'] = json_encode([
            'error' => 'Invalid input'
        ]);
        return $response;
    }
    private function notFoundResponse() {
        $response['status_code_header'] = 'HTTP/1.1 404 Not Found';
        $response['body'] = null;
        return $response;
    }
}

Finally, create a /public/index.php file to serve as the front controller to process requests:

<?php
require "../bootstrap.php";

use Src\Controller\StudentsController;

header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: OPTIONS,GET,POST,PUT,DELETE");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");  
 
$uri = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH);
$uri = explode( '/', $uri ); 
 
// all of our endpoints start with /students
// everything else results in a 404 Not Found
if ($uri[1] !== 'students') {
    header("HTTP/1.1 404 Not Found");
    exit();
} 
 
// the id is, of course, optional and must be a number:
$id = null;
if (isset($uri[2])) {
    $id = (int) $uri[2];
} 
 
$requestMethod = $_SERVER["REQUEST_METHOD"]; 
 
// pass the request method and user ID to the StudentsController and process the HTTP request:
$controller = new StudentsController($dbConnection, $requestMethod, $id);
$controller->processRequest();

Start the web server with the following command, where switch -t starts the server in the public folder:

php -S 127.0.0.1:8888 -t public

Test your API with postman with these endpoints:

return all recordsGET http://localhost:8888/students
return a specific recordGET http://localhost:8888/students/{id}
create a new recordPOST http://localhost:8888/students
update an existing recordPUT / http://localhost:8888/students/{id}
delete an existing recordDELETE http://localhost:8888/students/{id}


Posted by at
Labels: , , , , , , , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)