Create a REST API Using PHP & MySQL

Learn how to create a RESTful web-service with PHP & MySQL in less than five minutes

I am writing this tutorial to show you how to create a a RESTful web-service with PHP 7 & MySQL 8 in a very short time. You can think of this post as a primer. I'll try to explain the core topics as much as possible while avoiding generic things.

Please note that mysql extension for PHP has been completely discontinued in PHP 7. That is why you can only use mysqli extension. My entire API is based on mysqli extension only.

This tutorial assumes that you know: -

  • What an API & REST means.
  • What is a REST client.
  • What is a RESTful service.
  • How to setup a web-server. I am using Apache/2.4.34.
  • How to install necessary Apache modules.

Application description

It is a very simple To-Do app which is completely based on REST architecture & doesn't have any GUI. You create or retrieve your To-Do's using the REST API only.

Newly created To-Do's are stored in a MySQL database.

I have kept this API extremely simple on purpose. My sole aim here is to get you (& me) started with writing an API & interacting with it. I have skipped database security intentionally & will modify the code later. This tutorial is all about knowing how APIs are created & how you can see REST in action.

Requirements

  • PHP 7
  • MySQL 8
  • REST Client such as Postman. cURL or your favorite browser can also be used.

I am using PHP 7.1.23, MySQL 8.0.16 & Postman for this tutorial. You can use any REST client of your choice. I like Postman better as it makes it very easy & convenient to work with REST.

Steps to perform

Create Database & table

Create a database & table in MySQL to store the data.

CREATE DATABASE IF NOT EXISTS `my_to_do_db`;
USE my_to_do_db
--
-- Table structure for table `my_to_do_tb`
--

CREATE TABLE IF NOT EXISTS `my_to_do_tb` (
  `task` text NOT NULL,
  `date` text NOT NULL,
  `priority` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

Create config.php script

This PHP script will store database connection related information. I have kept database credentials in a separate file to-do.ini to avoid hard-coding the password. This file resides in a directory one level above the DocumentRoot.

config.php script references to-do.ini file.

<?php
$config = parse_ini_file('/Users/admin/Sites/to-do.ini');
$conn = mysqli_connect($config['dbhost'], $config['username'], $config['password']);
mysqli_select_db($conn, $config['db']);

Create a PHP script add-to-do.php to add To-Do's

Now write a PHP script that will put the data in the MySQL table. The idea here is to take three values viz task, date & priority as payload & POST it to the database.

<?php
include_once('config.php');
if($_SERVER['REQUEST_METHOD'] == "POST"){
	// Get data from the REST client
	$task = isset($_POST['task']) ? mysqli_real_escape_string($conn, $_POST['task']) : "";
	$date = isset($_POST['date']) ? mysqli_real_escape_string($conn, $_POST['date']) : "";
	$priority = isset($_POST['priority']) ? mysqli_real_escape_string($conn, $_POST['priority']) : "";
	// Insert data into database
	$sql = "INSERT INTO `my_to_do_db`.`my_to_do_tb` (`task`, `date`, `priority`) VALUES ('$task', '$date', '$priority');";
	$post_data_query = mysqli_query($conn, $sql);
	if($post_data_query){
		$json = array("status" => 1, "Success" => "To-Do has been added successfully!");
	}
	else{
		$json = array("status" => 0, "Error" => "Error adding To-Do! Please try again!");
	}
}
else{
	$json = array("status" => 0, "Info" => "Request method not accepted!");
}
@mysqli_close($conn);
// Set Content-type to JSON
header('Content-type: application/json');
echo json_encode($json);

Create a PHP script info.php to fetch To-Do information from the list of To-Do's

This script GETs the data from the MySQL database using task as the request query parameter. In other words, this script allows us to fetch a To-Do from the list using a To-Do task. For example, let's assume we have a To-Do whose task name is Write Code. Now you can retrieve it's information by using Write Code. More on it is covererd later in this post.

<?php
	include_once('config.php');
	$task = isset($_GET['task']) ? mysqli_real_escape_string($conn, $_GET['task']) :  "";
	$sql = "SELECT * FROM `my_to_do_db`.`my_to_do_tb` WHERE task='{$task}';";
	$get_data_query = mysqli_query($conn, $sql) or die(mysqli_error($conn));
		if(mysqli_num_rows($get_data_query)!=0){
		$result = array();
		
		while($r = mysqli_fetch_array($get_data_query)){
			extract($r);
			$result[] = array("Task" => $task, "Date" => $date, 'Priority' => $priority);
		}
		$json = array("status" => 1, "info" => $result);
	}
	else{
		$json = array("status" => 0, "error" => "To-Do not found!");
	}
@mysqli_close($conn);

// Set Content-type to JSON
header('Content-type: application/json');
echo json_encode($json);

We're now done with the coding. Time to see our API in action.

Try creating a To-Do

Now that you have written the code, it is time to test the API. For this, open your favorite REST client & send a POST call like below. You need to make sure you choose the Body as x-www-form-urlencoded & enter key-values accordingly. See the screenshot for better understanding.

REST endpoint

{
    https://localhost/~admin/REST-TO-DO/add-to-do
}

POST call to REST endpoint

If you have followed the steps correctly, you'll see that your To-Do has been added to the database successfully! You should see an output similar to below.

{
    "status": 1,
    "Success": "To-Do has been added successfully!"
}

You can grab the entire code from my GitHub Repository. It also has the SQL script to create the database & its table. To download the repository directly, click the button below.

Download

Fetch To-Do information

To retrieve a To-Do's information, invoke a GET call against the below REST endpoint. Note how I am using ?task=Write Code as the request query parameter.

REST endpoint

{
    https://localhost/~admin/REST-TO-DO/info?task=Write Code
}

Output

{
    "status": 1,
    "info": [
        {
            "Task": "Write Code",
            "Date": "18/06/2019",
            "Priority": "1"
        }
    ]
}

You have now reached the end of this tutorial. I hope it was informative & helpful. In my next post, I will show you how you can add Basic Authentication to this API so that only authenticated users are able to add/fetch the To-Do's. I'll also demonstrate how you can enable HTTPS for this API.

Thanks for reading this post!

Share this post

Tags

#php  #mysql  #rest  #api