Building a REST API with PHP & MySQL (Using XAMPP)

In this guide, you'll learn how to set up a PHP-based REST API with a MySQL database, all running locally using XAMPP. We'll cover the full process: setting up the environment, creating the database, building the API, and testing it with Postman.

Step 1: Set Up XAMPP

1.1 Download and Install XAMPP

XAMPP is an open-source web server solution that includes Apache, MySQL (MariaDB), and PHP. Download it from the official website and follow the installation instructions.

1.2 Start Apache and MySQL

After installing XAMPP, open the XAMPP Control Panel. Start the Apache and MySQL services by clicking the ‘Start’ buttons next to each. This will allow you to run PHP scripts and manage your MySQL database.

Step 2: Create a MySQL Database

2.1 Access phpMyAdmin

Go to http://localhost/phpmyadmin in your web browser to access phpMyAdmin, a web interface for managing MySQL databases.

2.2 Create a Database

Click "New" in the left sidebar.
Name your database (e.g., users) and click "Create".

2.3 Create a Table

With your database selected:

Click “New” to create a new table.
Name it users and define the columns:
- id (INT, Primary Key, Auto Increment)
- name (VARCHAR, 200)
- email (VARCHAR, 200)
Click “Save” to create the table.

Step 3: Build the PHP REST API

3.1 What is a REST API?

A REST API uses HTTP requests to interact with a database, supporting CRUD operations: Create, Read, Update, Delete. Each HTTP method (GET, POST, PUT, DELETE) corresponds to one of these operations.

3.2 Create the Project Folder

Navigate to C:\xampp\htdocs (or your XAMPP installation directory) and create a new folder (e.g., restapi). This will hold your API files.

3.3 Create a Database Connection File (db.php)

Create a db.php file inside your project folder. This file handles the MySQL connection using PDO (PHP Data Objects).

<?php
$host = "localhost";
$user = "root";
$password = "";
$dbname = "users";

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

3.4 Create the API Logic (api.php)

Create an api.php file to handle different HTTP methods. Depending on the request method (GET, POST, PUT, DELETE), the API will perform the corresponding CRUD operation.

<?php
header("Content-Type: application/json");
include 'db.php';

$method = $_SERVER['REQUEST_METHOD'];
$input = json_decode(file_get_contents('php://input'), true);

switch ($method) {
    case 'GET':
        handleGet($pdo);
        break;
    case 'POST':
        handlePost($pdo, $input);
        break;
    case 'PUT':
        handlePut($pdo, $input);
        break;
    case 'DELETE':
        handleDelete($pdo, $input);
        break;
    default:
        echo json_encode(['message' => 'Invalid request method']);
        break;
}

function handleGet($pdo) {
    $sql = "SELECT * FROM users";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($result);
}

function handlePost($pdo, $input) {
    $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(['name' => $input['name'], 'email' => $input['email']]);
    echo json_encode(['message' => 'User created successfully']);
}

function handlePut($pdo, $input) {
    $sql = "UPDATE users SET name = :name, email = :email WHERE id = :id";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(['name' => $input['name'], 'email' => $input['email'], 'id' => $input['id']]);
    echo json_encode(['message' => 'User updated successfully']);
}

function handleDelete($pdo, $input) {
    $sql = "DELETE FROM users WHERE id = :id";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(['id' => $input['id']]);
    echo json_encode(['message' => 'User deleted successfully']);
}
?>

Step 4: Test the API Using Postman

4.1 Install Postman

Download and install Postman to test your API.

4.2 Test the GET Request

Open Postman and create a new GET request.
Enter the URL: http://localhost/restapi/api.php.
Click “Send”. You should see a JSON response with all the users in the database.

4.3 Test the POST Request

Create a new POST request.
Enter the URL: http://localhost/restapi/api.php.
Go to the “Body” tab, select “raw” and “JSON”, and enter the following data:

   {
     "name": "John Doe",
     "email": "john@example.com"
   }

Click “Send”. You should see a success message.

4.4 Test the PUT Request

Create a new PUT request.
Enter the URL: http://localhost/restapi/api.php.
Go to the “Body” tab, select “raw” and “JSON”, and enter the following data:

   {
     "id": 1,
     "name": "Jane Doe",
     "email": "jane@example.com"
   }

Click “Send”. You should see a success message.

4.5 Test the DELETE Request

Create a new DELETE request.
Enter the URL: http://localhost/restapi/api.php.
Go to the “Body” tab, select “raw” and “JSON”, and enter the following data:

   {
     "id": 1
   }

Click “Send”. You should see a success message.

Conclusion

In this tutorial, we've set up a REST API using PHP and MySQL with XAMPP, created a MySQL database, built a PHP API, and tested it using Postman. This simple REST API is a solid foundation to build on for more complex applications.

This should fit well as a detailed Dev.to post on creating a REST API with PHP and MySQL.