Simple REST API With PHP MySQL (Step-by-Step Example)

Welcome to a tutorial and example on how to create a simple REST API with PHP and MYSQL. The term “REST API” probably sounds intimidating to some beginners, but in actual fact, it is actually something really simple.

In layman’s terms, a REST API is nothing but a service endpoint. It simply accepts a user request, processes it, and responds with the results. For example, we send $_POST["email"]="jon@doe.com" and $_POST["password"]="123456" to https://site.com/api/login/. The system processes the login request and replies OK.

Yep, that’s all. Maybe walking through a detailed example will help – Read on!

ⓘ I have included a zip file with all the example source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes API Development Useful Bits & Links
The End

 

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Create a test database and import 1-database.sql.
  • Change the database settings in 2-users-lib.php to your own.
  • Launch 4-api-test.html in your browser.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

REST API DEVELOPMENT

All right, let us now get into a simple example of creating a user API.

 

STEP 1) DUMMY USER DATABASE TABLE

1-database.sql
CREATE TABLE `users` (
  `user_id` int(20) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_password` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`);
 
ALTER TABLE `users`
  MODIFY `user_id` int(20) NOT NULL AUTO_INCREMENT;
Field Description
user_id Primary key, auto-increment.
user_email User email, unique.
user_password User password.

 

 

STEP 2) PHP USER LIBRARY

2-users-lib.php
<?php
class Users {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct () {
    try {
      $this->pdo = new PDO(
        "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET, 
        DB_USER, DB_PASSWORD, [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
      );
    } catch (Exception $ex) { exit($ex->getMessage()); }
  }

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt!==null) { $this->stmt = null; }
    if ($this->pdo!==null) { $this->pdo = null; }
  }

  // (C) SUPPORT FUNCTION - SQL QUERY
  function query ($sql, $data) {
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($data);
      return true;
    } catch (Exception $ex) {
      $this->error = $ex->getMessage();
      return false;
    }
  }

  // (D) CREATE/UPDATE USER
  function save ($email, $pass, $id=null) {
    if ($id===null) {
      $sql = "INSERT INTO `users` (`user_email`, `user_password`) VALUES (?,?)";
      $data = [$email, password_hash($pass, PASSWORD_BCRYPT)];
    } else {
      $sql = "UPDATE `users` SET `user_email`=?, `user_password`=? WHERE `user_id`=?";
      $data = [$email, password_hash($pass, PASSWORD_BCRYPT), $id];
    }
    return $this->query($sql, $data);
  }

  // (E) DELETE USER
  function del ($id) {
    return $this->query("DELETE FROM `users` WHERE `user_id`=?", [$id]);
  }
 
  // (F) GET USER
  function get ($id) {
    $this->query("SELECT * FROM `users` WHERE `user_".(is_numeric($id)?"id":"email")."`=?", [$id]);
    return $this->stmt->fetch();
  }
 
  // (G) VERIFY USER (FOR LOGIN)
  function verify ($email, $pass) {
    // (G1) GET USER
    $user = $this->get($email);
    if (!is_array($user)) { return false; }

    // (G2) PASSWORD CHECK
    if (password_verify($pass, $user["user_password"])) {
      $_SESSION["user"] = [
        "id" => $user["user_id"],
        "email" => $user["user_email"]
      ];
      return true;
    } else { return false; }
  }
}

// (H) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (I) START!
session_start();
$USR = new Users();

Yikes! This looks massive, but keep calm and study slowly:

  • (A & B) When the $USR = new Users() object is created, the constructor will automatically connect to the database. The destructor closes the connection when done.
  • (C) function query() is nothing but a helper function to run SQL queries.
  • (D to G) There are only 4 functions, and all of them are essentially just SQL statements!
    • function save() Add a new user, or update an existing one.
    • function del() Delete a user.
    • function get() Get a user by ID or email.
    • function verify() Verify a given email and password (for login).
  • (H & I) The database settings and user object itself. Doh.

 

 

STEP 3) API ENDPOINT 

3-users-api.php
<?php
// (A) LOAD USER LIBRARY
require "2-users-lib.php";

// (B) STANDARD JSON RESPONSE
function respond ($status, $message, $more=null, $http=null) {
  if ($http !== null) { http_response_code($http); }
  exit(json_encode([
    "status" => $status,
    "message" => $message,
    "more" => $more
  ]));
}

// (C) LOGIN CHECK
function lcheck () {
  if (!isset($_SESSION["user"])) {
    respond(0, "Please sign in first", null, 403);
  }
}

// (D) HANDLE REQUEST
if (isset($_POST["req"])) { switch ($_POST["req"]) {
  // (D1) BAD REQUEST
  default:
    respond(false, "Invalid request", null, null, 400);
    break;
  
  // (D2) SAVE USER
  case "save": lcheck();
    $pass = $USR->save(
      $_POST["email"], $_POST["password"],
      isset($_POST["id"]) ? $_POST["id"] : null
    );
    respond($pass, $pass?"OK":$USR->error);
    break;

  // (D3) DELETE USER
  case "del": lcheck();
    $pass = $USR->del($_POST["id"]);
    respond($pass, $pass?"OK":$USR->error);
    break;
 
  // (D4) GET USER
  case "get": lcheck();
    respond(true, "OK", $USR->get($_POST["id"]));
    break;

  // (D5) LOGIN
  case "in":
    // ALREADY SIGNED IN
    if (isset($_SESSION["user"])) { respond(true, "OK"); }
    
    // CREDENTIALS CHECK
    $pass = $USR->verify($_POST["email"], $_POST["password"]);
    respond($pass, $pass?"OK":"Invalid email/password");
    break;

  // (D6) LOGOUT
  case "out":
    unset($_SESSION["user"]);
    respond(true, "OK");
    break;
}

Don’t be intimidated by the “confusing” code again, how this API endpoint work should be self-explanatory.

  • Send a $_POST["req"] to specify the request, followed by the required parameters.
  • For example – To add a new user, we send $_POST["req"] = "save", $_POST["email"] = "jane@doe.com", and $_POST["password"] = "SECRET" to this endpoint.
  • The API will respond in a JSON encoded format – A status flag on the processing, and system message.

 

 

STEP 4) API TESTING

4-api-test.html
<!-- (A) LOGIN -->
<form method="post" action="3-users-api.php">
  <input type="text" name="req" value="in" readonly />
  <input type="email" name="email" value="john@doe.com" required />
  <input type="text" name="password" value="123456" required />
  <input type="submit" value="1 - Login"/>
</form>
 
<!-- (B) ADD USER -->
<form method="post" action="3-users-api.php">
  <input type="text" name="req" value="save" readonly />
  <input type="email" name="email" value="jane@doe.com" required />
  <input type="text" name="password" value="123456" required />
  <input type="submit" value="2 - Add User"/>
</form>
 
MORE...

Actually, that’s all for the “complicated REST API”. But go ahead – POST a login request, add a new user, update a new user. See for yourself how the API endpoint works.

 

USEFUL BITS & LINKS

That’s it for the simple REST API example, and here are some small extras and links that may be useful to you.

 

WHAT IS REST!? API!?

Very important basic question.

  • API – Application Programming Interface. In simple words, a software interface or service endpoint. That is, send your request and parameters to an API to do something.
  • REST – Representational State Transfer, a software architectural style. Basically, an API is considered to be REST if it:
    • Has a base URL (such as https://api.site.com/).
    • Uses the HTTP protocol.
    • Uses POST GET PUT.
    • Utilizes JSON XML.
  • The specifics are too much to explain in a single line, I will just point to a Wikipedia link below.

 

SO WHY BUILD AN API?

Long story short, a single API can support all devices and platforms – Windows, Mac, iOS, Android, smartwatch, and more. Think of it in another way, an API is a system without a user interface.

Yes, if you have not realized, the above is a fully functioning user system. All that’s left is to build the user interface on top – HTML, Andriod, iOS, it does not matter since the base system is already there.

 

 

LINKS & REFERENCES

 

INFOGRAPHIC CHEAT SHEET

Simple REST API With PHP MySQL (Click to Enlarge)

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has given you some insight into creating your own API, and if you have anything to add to the guide, please feel free to comment below. Good luck and happy coding!

2 thoughts on “Simple REST API With PHP MySQL (Step-by-Step Example)”

  1. Hi, create user seems not working …. with this error prompted :-
    {“status”:false,”message”:”Error creating user”}

Leave a Comment

Your email address will not be published. Required fields are marked *