Create Simple RESTful API with PHP & MySQL

The RESTful APIs in PHP with MySQL allows you to create a web service that enables communication between your PHP application and a MySQL database. In this tutorial, you will learn how to create and use a REST API in PHP MySQL to perform CRUD operations from a json formatted database in PHP.

How to Create a RESTful APIs in PHP and MySQL

Steps to create and use rest API in PHP Mysql to retrieve, create, update, and delete data from a database in PHP JSON format:

• Step 1: Create Database and Table
• Step 2: Connect to MySQL Database
• Step 3: Create APIs Endpoints
• Step 4: Test the API in POSTMAN

Before building the Rest API, you need to create a new project directory in the document root of your server. and open your project directory in a code editor.

Step 1: Create a Database and Table

First of all, you need to create a MySQL database and table to store the data for your bootstrap form data. Open your preferred MySQL management tool (e.g., phpMyAdmin) and execute the following SQL query to create a table named users and database name demo:

CREATE DATABASE Demo;

CREATE TABLE users (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,

);

Step 2: Connect to MySQL Database

Next, you need to connect to the MySQL database using PHP. So, Create a new PHP file named db.php and add the following code:

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

Step 3: Create APIs Endpoints

Now, you need to create APIs to add, edit, update and delete data from MySQL database. So, create a new PHP file, such as index.php, which will act as the entry point for our API, add the following code for the respective endpoints

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

// Retrieve user by ID
if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['id'])) {
    $id = $_GET['id'];
    $sql = "SELECT * FROM users WHERE id = $id";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        echo json_encode($row);
    } else {
        echo json_encode("User not found");
    }
}

// Create a new user
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['name']) && isset($_POST['email'])) {
    $name = $_POST['name'];
    $email = $_POST['email'];
    $sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
    $result = $conn->query($sql);

    if ($result === TRUE) {
        echo json_encode("User created successfully");
    } else {
        echo json_encode("Error creating user: " . $conn->error);
    }
}

// Update an existing user
if ($_SERVER['REQUEST_METHOD'] === 'PUT' && isset($_GET['id']) && isset($_POST['name']) && isset($_POST['email'])) {
    $id = $_GET['id'];
    $name = $_POST['name'];
    $email = $_POST['email'];
    $sql = "UPDATE users SET name='$name', email='$email' WHERE id=$id";
    $result = $conn->query($sql);

    if ($result === TRUE) {
        echo json_encode("User updated successfully");
    } else {
        echo json_encode("Error updating user: " . $conn->error);
    }
}

// Delete an existing user
if ($_SERVER['REQUEST_METHOD'] === 'DELETE' && isset($_GET['id'])) {
    $id = $_GET['id'];
    $sql = "DELETE FROM users WHERE id=$id";
    $result = $conn->query($sql);

    if ($result === TRUE) {
        echo json_encode("User deleted successfully");
    } else {
        echo json_encode("Error deleting user: " . $conn->error);
    }
}

Step 4: Test the API in POSTMAN

Send HTTP requests to the API endpoints using a tool like cURL, Postman, or a web browser extension.

Here are some example requests:

• To retrieve a user by ID: GET http://localhost/index.php?id=1
• To create a new user: POST http://localhost/index.php with form data containing name and email fields.
• To update an existing user: PUT http://localhost/index.php?id=1 with form data containing name and email fields.
• To delete an existing user: DELETE http://localhost/index.php?id=1

Conclusion

That’s it! You have learned how to create a REST API in PHP with MySQL.

Recommended Tutorials