Borrowed Code
data api
MySQL

How to create RESTful API using PHP & MySQL

By 5 Mins Read

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 simple words, a REST API is nothing but a service endpoint.

In even much simpler words, REST API is exactly like a website, but without HTML, CSS, JavaScript; It simply accepts a user request, processes it, and responds with the results.

See? Not so difficult to understand right? I don’t know why some developers fear it so much. In my opinion, it is much easier to develop a full-stack app.

If you learn from a practical example like me instead of learning from all text and theory and you are in for a treat. We will now look into a simple example in which we will create a basic login system.

Creating Users Table

Assuming that you already created a database in your PhpMyAdmin dashboard, we are going to create a table of users.

CREATE TABLE `users` (
  `user_id` int(11) 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(11) NOT NULL AUTO_INCREMENT;
 
INSERT INTO `users` (`user_id`, `user_email`, `user_password`) VALUES
(1, '[email protected]', '$2y$10$FXxqEZfiiThcCRyJbJJm8.Yktp39YlQcuntrqVABWGkQ1RJIKe5rC');

Here we are creating users table where user_id is primary key & auto increment and user_email is unique.

We are also inserting dummy entry where email is [email protected] and password is 12345.

User Class and Method

<?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) { die($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) {
      return $this->query(
        "INSERT INTO `users` (`user_email`, `user_password`) VALUES (?,?)",
        [$email, password_hash($pass, PASSWORD_BCRYPT)]
      );
    } else {
      return $this->query(
        "UPDATE `users` SET `user_email`=?, `user_password`=? WHERE `user_id`=?", 
        [$email, password_hash($pass, PASSWORD_BCRYPT), $id]
      );
    }
  }

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

    // (F2) PASSWORD CHECK
    if (password_verify($pass, $user['user_password'])) {
      $_SESSION['user'] = [
        "id" => $user['user_id'],
        "email" => $user['user_email']
      ];
      return true;
    } else { return false; }
  }
  
  // (G) DELETE USER
  function del ($id) {
    return $this->query(
      "DELETE FROM `users` WHERE `user_id`=?", [$id]
    );
  }
}

// (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 edit an existing one.
    • function get() Get a user by ID or email.
    • function verify() Verify a given email and password (for login).
    • function del() Delete a user.

API Endpoints

<?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
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) GET USER
  case "get":
    lcheck();
    $user = $USR->get($_POST['id']);
    respond(true, "OK", $user);
    break;
  
  // (D4) DELETE USER
  case "del":
    lcheck();
    $pass = $USR->del($_POST['id']);
    respond($pass, $pass?"OK":$USR->error);
    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'] = "[email protected]", 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.

Start the engine and test the API

<!-- (A) LOGIN -->
<form method="post" action="3-users-api.php">
<input type="text" name="req" value="in" readonly />
<input type="email" name="email" value="[email protected]" required />
<input type="text" name="password" value="123456" required />
<input type="submit" value="1 - Login"/>
</form>

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. If you want to read more visit Borrowed Code.

Related Posts

Write A Comment