How to Create Simple PHP API with Database, Test with Postman

Hello everyone! Today we let create simple PHP API with MySQL database. We use Postman for test it.
I will try explain step by step from getting data in arrray and then get data from database, add student, get a specific student, update, delete with MySQL.

Step 1: Make Simple API

This is the simplest way to get data from an associated array.

<?php
header("Content-Type: application/json");
$data = [
    ["id" => 1, "name" => "Reaskmey", "gender" => "Male"],
    ["id" => 2, "name" => "Nary", "gender" => "Female"],
];

if (isset($_GET['id'])) { //get data by id
    $id = intval($_GET['id']);
    foreach ($data as $row) {
        if ($row['id'] == $id) {
            echo json_encode($row, JSON_PRETTY_PRINT);
            exit;
        }
    }
    echo json_encode(['message' => "data not found"]);
} else { //get all data
    echo json_encode($data, JSON_PRETTY_PRINT);
}

PHP API get data from array - PHP khmer tutorial

After tested by following the video instruction you got some idea to get start.

Step 2: Set Up Database

First, we make database.

Start XAMPP/+-WAMP

Open phpMyAdmin (come with XAMPP/WAMP).

If you have any error to start Apache or MySQL on XAMPP check HERE

Create new database, name it student_api. Inside student_api, make table call students.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    gender ENUM('Male', 'Female') NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    address VARCHAR(255) NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

some sample data for table students (data is AI generated):

INSERT INTO students (name, gender, phone, email, address) VALUES
('Sokha Chan', 'Male', '0123456789', 'sokha.chan@gmail.com', 'Phnom Penh'),
('Sophea Kim', 'Female', '0987654321', 'sophea.kim@gmail.com', 'Siem Reap'),
('Rithy Heng', 'Male', '0234567890', 'rithy.heng@gmail.com', 'Battambang'),
('Sreyneang Chum', 'Female', '0345678901', 'sreyneang.chum@gmail.com', 'Kampong Cham'),
('Piseth Sok', 'Male', '0456789012', 'piseth.sok@gmail.com', 'Sihanoukville'),
('Sokunthea Meas', 'Female', '0567890123', 'sokunthea.meas@gmail.com', 'Kampot'),
('Vuthy Chea', 'Male', '0678901234', 'vuthy.chea@gmail.com', 'Takeo'),
('Sopheap Khiev', 'Female', '0789012345', 'sopheap.khiev@gmail.com', 'Kandal'),
('Borey Phan', 'Male', '0890123456', 'borey.phan@gmail.com', 'Prey Veng'),
('Srey Pich', 'Female', '0901234567', 'srey.pich@gmail.com', 'Kampong Speu');

Step 3: Write PHP API

Now we make PHP file for API. Call it api.php and put in htdocs folder (XAMPP) or www folder (WAMP).

Connect to Database

<?php
$servername = 'localhost';
$username = "root";
$password = "MySQLPassword"; //leave it blank if you don't have, not set password
$dbname = 'student_api';

$con = new mysqli($servername, $username, $password, $dbname);

if ($con->connect_error) {
    die("Connection failed!!!" . $con->error);
} else {
    // echo "Connection success!!!";
}

Video#1 How to create simple PHP GET API read data from array and test with Postman - PHP khmer tutorial

CREATE PHP GET API

Below are some sample block code for getting student by ID and get all students.

 if (isset($_GET['id'])) { //get by id
     $id = intval($_GET['id']);
     $sql = "SELECT * FROM students WHERE id=$id";
 } else { //get all students
     $sql = "SELECT * FROM students WHERE is_active=1";
 }

 $result = $con->query($sql);
 if ($result->num_rows > 0) {
     while ($row = $result->fetch_assoc()) {
         $respone[] = $row;
     }
 }
 echo json_encode($respone, JSON_PRETTY_PRINT);

Video#2. How to create PHP GET API read data from Database and test with Postman- PHP khmer tutorial

CREATE PHP POST API

Below are some sample block code for getting student by ID and get all students.

// read data from postman input
$data = json_decode(file_get_contents('php://input'), true);
$name = isset($data['name']) ? $data['name'] : "";
$gender = isset($data['gender']) ? $data['gender'] : "";
$phone = isset($data['phone']) ? $data['phone'] : "";
$email = isset($data['email']) ? $data['email'] : "";
$is_active = isset($data['is_active']) ? $data['is_active'] : "";
$address = isset($data['address']) ? $data['address'] : "";

$sql = "INSERT INTO students (name, gender, phone, email, address,is_active)
       VALUES ('$name', '$gender', '$phone', '$email', '$address','$is_active')";
// echo $sql;
if ($con->query($sql) === TRUE) {
    $respone['message'] = "Data inserted";
} else {
    $respone['error'] = "Error $sql " . $con->error;
}
echo json_encode($respone, JSON_PRETTY_PRINT);

Video#3 How to create PHP POST API to Insert data to Database and test with Postman- PHP khmer tutorial

CREATE PHP PUT API

PUT API is used update data by ID. please follow the video for more detail.

Video#4 How to create PHP PUT API to UPDATE data to Database from Postman- PHP khmer tutorial

Create PHP DELETE API

DELETE API is used delete data from database but in real world application we do NOT delete record form database. We update status instead. please follow the video for more detail.

Video#5 How to create PHP DELETE API to delete data from Database via Postman - PHP khmer tutorial

That it! You now have simple PHP API with database. It do CRUD (Create, Read, Update, Delete) and you test with Postman.I hope you understand. You can add more thing to API if want, like better error or more endpoint. Enjoy!

Free Khmer Ebook Download (PDF): Database | Microsoft Access | Python Programming