Create a REST API in PHP MySQL

The REST API in PHP with MySQL allows you to create a web service that enables communication between your PHP application and a MySQL database. REST APIs provide a standardized way to access and manipulate data, making it easy to build dynamic and interactive web applications.

If you want to create web services or rest API in PHP MySQL to get, create, update, and delete data in JSON format from MySQL database. So for this, you have to create and use Rest API.

In this tutorial, you will learn how to create and use REST API in PHP MySQL to get/retrieve, create/add, update, and delete data from a database in PHP with json format.

How to Create a REST API in PHP 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: Define API 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,

);

PHP Signature Pad using Jquery Ajax Example

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);
}

Crop and Save Image Using jQuery Croppie and PHP

Step 3: Define API 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);
    }
}

Ajax Image Upload Using PHP and jQuery Without Refreshing Page

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

Send Reset Password Link Email PHP

Conclusion

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