Illustration of PHP code editor for creating a CRUD REST API with MySQL

How to Make a CRUD REST API in PHP with MySQL: A Step-by-Step Guide by Shahzad Ahmad Mirza

/ Web Development / By

Hey there! Shahzad Ahmad Mirza here. Today, I’m going to walk you through creating a CRUD REST API in PHP with MySQL. It’s going to be a fun ride, so buckle up!

Why CRUD REST API?

Let’s start with a little story. A couple of years back, I was working on a project that needed a backend system for managing data. I was juggling between different technologies, trying to find the perfect fit. That’s when I stumbled upon PHP and MySQL. They were like Batman and Robin, a perfect duo for creating a robust CRUD REST API.

By the end of this guide, you’ll be able to create your own API, which you can use in various applications. So, let’s dive in!

Introduction to CRUD REST API

CRUD stands for Create, Read, Update, Delete. These are the four basic operations you can perform on any data. A REST API is an interface that allows different software applications to communicate with each other using HTTP requests.

In this tutorial, we’ll use PHP to handle the backend logic and MySQL to manage the database.

Setting Up the Environment

Before we start coding, we need to set up our development environment.

Step 1: Install XAMPP

To run PHP on your local machine, you’ll need a server environment. XAMPP is a popular choice because it’s easy to set up and use.

  1. Download XAMPP from Apache Friends.
  2. Install XAMPP and start the Apache and MySQL modules.

Step 2: Create the Project Folder

Create a folder in the htdocs directory of your XAMPP installation. Let’s name it crud_api.

C:\xampp\htdocs\crud_api

Creating the Database

Now, let’s create the database that our API will interact with.

Step 3: Open phpMyAdmin

Open your web browser and go to http://localhost/phpmyadmin. Here, we’ll create a new database.

Step 4: Create a Database

  1. Click on New.
  2. Enter the database name as crud_db and click Create.

Step 5: Create a Table

We need a table to store our data. Let’s create a table named users.

  1. Click on the crud_db database.
  2. Click on New to create a new table.
  3. Name the table users and add the following columns:
id (INT, PRIMARY, AUTO_INCREMENT)
name (VARCHAR 100)
email (VARCHAR 100)
phone (VARCHAR 15)

Building the API

Here comes the exciting part—building the API.

Step 6: Create a Database Connection

In your crud_api folder, create a file named db.php and add the following code:

<?php
$host = 'localhost';
$db_name = 'crud_db';
$username = 'root';
$password = '';

try {
    $conn = new PDO("mysql:host=$host;dbname=$db_name", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection Error: ' . $e->getMessage();
}
?>

Step 7: Create the API Endpoints

We need endpoints for each CRUD operation. Create a file named api.php and add the following code:

<?php
include 'db.php';

// CREATE
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $name = $_POST['name'];
    $email = $_POST['email'];
    $phone = $_POST['phone'];

    $sql = "INSERT INTO users (name, email, phone) VALUES (?, ?, ?)";
    $stmt = $conn->prepare($sql);
    $stmt->execute([$name, $email, $phone]);

    echo json_encode(['message' => 'User created successfully']);
}

// READ
if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    $sql = "SELECT * FROM users";
    $stmt = $conn->query($sql);
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode($users);
}

// UPDATE
if ($_SERVER['REQUEST_METHOD'] === 'PUT') {
    parse_str(file_get_contents("php://input"), $_PUT);
    $id = $_PUT['id'];
    $name = $_PUT['name'];
    $email = $_PUT['email'];
    $phone = $_PUT['phone'];

    $sql = "UPDATE users SET name = ?, email = ?, phone = ? WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->execute([$name, $email, $phone, $id]);

    echo json_encode(['message' => 'User updated successfully']);
}

// DELETE
if ($_SERVER['REQUEST_METHOD'] === 'DELETE') {
    parse_str(file_get_contents("php://input"), $_DELETE);
    $id = $_DELETE['id'];

    $sql = "DELETE FROM users WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->execute([$id]);

    echo json_encode(['message' => 'User deleted successfully']);
}
?>

Step 8: Configure .htaccess

To make our API cleaner, we’ll use an .htaccess file to handle routing. Create an .htaccess file in the crud_api folder and add the following:

plaintextCopy codeRewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ api.php [QSA,L]

Testing the API

Now that our API is ready, let’s test it using Postman.

Step 9: Install Postman

Download and install Postman from Postman.

Step 10: Test the Endpoints

Create a User

  1. Open Postman and select POST.
  2. Enter http://localhost/crud_api/.
  3. Go to the Body tab and select form-data.
  4. Add the fields name, email, and phone.
  5. Click Send.

Read Users

  1. Select GET.
  2. Enter http://localhost/crud_api/.
  3. Click Send.

Update a User

  1. Select PUT.
  2. Enter http://localhost/crud_api/.
  3. Go to the Body tab and select x-www-form-urlencoded.
  4. Add the fields id, name, email, and phone.
  5. Click Send.

Delete a User

  1. Select DELETE.
  2. Enter http://localhost/crud_api/.
  3. Go to the Body tab and select x-www-form-urlencoded.
  4. Add the field id.
  5. Click Send.

Conclusion

Congratulations! You’ve successfully created a CRUD REST API in PHP with MySQL. This is a powerful tool that you can use to manage data in your applications. Whether you’re building a simple app or a complex system, this API can be the backbone of your project.

Remember, coding is a journey. Keep experimenting, keep learning, and, most importantly, have fun! If you have any questions or need further assistance, feel free to contact me.

Related Posts