HOW TO CREATE A SIMPLE REST API IN PHP MySQL

Pass this to the next buddy!

Welcome to howto-daily.com blog where you learn how to do everything. People access the Internet today from different devices – Desktops, laptops, tablets, and smartphones; Modern web is not just about websites but also Android apps, iOS apps, and even the need for cross communications between servers.

This is most likely why you are looking for an API, to create web services for mobile apps and/or open a channel of communication between servers. That is exactly what I’m going to walk you through – The concepts, a simple example of how to create a user management API in PHP and MySQL

Assumptions!

I sincerely assume you are aware of how PHP works and you know what type of animal MySQL is.

seriously, oka okay…

Basics,

WHAT IS AN API?

API stands for Application Programming Interface, and it is most definitely not the brand name of a ice cream. Good Grandpa Wikipedia defines it as

A set of clearly defined methods of communication between various components

Nice example, you run an online shop and want to develop an Android/iOS app for it. It will be kind of foolish (and cumbersome actually) to develop 3 different sets of code to deal with each platform, and so, we will have one single “standard” API to process all the user registration, products catalog, checkout, and orders management.

And btw, don’t get confused! We sometimes call API “web service” or “endpoint”. They literally mean the same thing.

REST?

REST is shorthand for Representational State Transfer. From good old Wikipedia again:

REST is an architectural style that defines a set of constraints to be used for creating web services. Web Services that conform to the REST architectural style, or RESTful web services, provide interoperability between computer systems on the Internet. REST-compliant web services allow the requesting systems to access and manipulate textual representations of web resources by using a uniform and predefined set of stateless operations.

CRUD

Speaking of a predefined fixed set of functions, you should also know CRUD:

  • Create
  • Read
  • Update
  • Delete

Yep, the concept of CRUD is simple but it stays true to almost any process and API that you build. For example:

  • Creating a new user.
  • Reading the user data.
  • Updating the user.
  • Deleting the user.

So for whatever system that you are working on, remember that CRUD is the first 4 basic processes that your system should cover.

Let us get to really stuff!

The database

Create table in MySQL using your preferred db client tool (phpmyadmin). See below:

CREATE TABLE `users` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `users`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `email` (`email`),
ADD KEY `name` (`name`);

Add some dummy data into the db


INSERT INTO `users` (`id`, `name`, `email`, `password`) VALUES
(1, 'John Doe', 'john@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(2, 'Jane Doe', 'jane@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(3, 'Apple Doe', 'apple@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(4, 'Beck Doe', 'beck@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(5, 'Charlie Doe', 'charlie@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(6, 'Charles Doe', 'charles@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(7, 'Dion Doe', 'dion@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(8, 'Dee Doe', 'dee@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(9, 'Emily Doe', 'emily@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(10, 'Ethan Doe', 'ethan@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(11, 'Frank Doe', 'frank@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(12, 'Gina Doe', 'gina@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(13, 'Hela Doe', 'hela@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(14, 'Hubert Doe', 'hubert@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(15, 'Ivy Doe', 'ivy@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(16, 'Ingrid Doe', 'ingrid@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(17, 'James Doe', 'james@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(18, 'Jace Doe', 'jace@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(19, 'Kate Doe', 'kate@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg=='),
(20, 'Luke Doe', 'luke@doe.com', 'Xc1HAPhnviO7v6mTfwxsHg==');

PHP Code: 1. db.config.php


<?php
// MUTE NOTICES
error_reporting(E_ALL & ~E_NOTICE);
// DATABASE SETTINGS
define("DB_HOST", "localhost");
define("DB_DB", "test");
define("DB_CHAR", "utf8");
define("DB_USER", "root");
define("DB_PASS", "");
// SECRET KEY FOR PASSWORDS
define("SECRET_KEY", "CodeB0xx");
// FILE PATH
// Manually define the absolute path if you get path problems
define('PATH_LIB', __DIR__ . DIRECTORY_SEPARATOR);
?>

The user Library(We can call this user endpoints business logic. User-lib.php


<?php
class User{
  /* [DATABASE HELPER FUNCTIONS] */
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct(){
    try {
      $this->pdo = new PDO(
"mysql:host=".DB_HOST.";dbname=".DB_DB.";charset=".DB_CHAR,
DB_USER, DB_PASS, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]
);
} catch (Exception $ex) { die($ex->getMessage()); }
}
function __destruct(){
if ($this->stmt!==null) { $this->stmt = null; }
if ($this->pdo!==null) { $this->pdo = null; }
}
function query($sql, $cond=[]){
try {
$this->stmt = $this->pdo->prepare($sql);
$this->stmt->execute($cond);
} catch (Exception $ex) {
$this->error = $ex->getMessage();
return false;
}
$this->stmt = null;
return true;
}
/* [USER FUNCTIONS] */
function getAll(){
$this->stmt = $this->pdo->prepare("SELECT * FROM `users`");
$this->stmt->execute();
$users = $this->stmt->fetchAll();
return count($users)==0 ? false : $users;
}
function getEmail($email){
$this->stmt = $this->pdo->prepare("SELECT * FROM `users` WHERE `email`=?");
$cond = [$email];
$this->stmt->execute($cond);
$user = $this->stmt->fetchAll();
return count($user)==0 ? false : $user[0];
}
function getID($id){
$this->stmt = $this->pdo->prepare("SELECT * FROM `users` WHERE `id`=?");
$cond = [$id];
$this->stmt->execute($cond);
$user = $this->stmt->fetchAll();
return count($user)==0 ? false : $user[0];
}
function create($name, $email, $password){
return $this->query(
"INSERT INTO `users` (`name`, `email`, `password`) VALUES (?,?,?)",
[$name, $email, openssl_encrypt($password, "AES-128-ECB", SECRET_KEY)]
);
}
function update($name, $email, $password="", $id){
$q = "UPDATE `users` SET `name`=?, `email`=?";
$cond = [$name, $email];
if ($password!="") {
$q .= ", `password`=?";
$cond[] = openssl_encrypt($password, "AES-128-ECB", SECRET_KEY);
}
$q .= " WHERE `id`=?";
$cond[] = $id;
return $this->query($q, $cond);
}
function delete($id){
return $this->query(
"DELETE FROM `users` WHERE `id`=?",
[$id]
);
}
/* [LOGIN] */
function login($email, $password){
$user = $this->getEmail($email);
if ($user==false) { return false; }
return openssl_decrypt($user['password'], "AES-128-ECB", SECRET_KEY) == $password ? $user : false ;
}
}
?>

emember CRUD? When we build a library, it should have functions to cover all of those.Database Helper Functions

FunctionDescription
__constructorThe constructor. Will automatically connect to the database when the user object is created.
__destructThe destructor. Will automatically close the database connection when the user object is destroyed.
queryRun a given SQL query on the database.

Get Users (READ)

FunctionDescription
getAllGet all users.
getEmailGet user by the given email address.
getIDGet user by the given user ID.

Set & Delete Users (CREATE, UPDATE, DELETE)

FunctionDescription
createAdd a new user to the database.
updateUpdate data of the specified user.
deleteDelete the given user ID.

Login

FunctionDescription
loginChecks if the given email and password is correct, returns false if not, returns the user data if yes. Take note – This only checks and does not start the cookie session.

Endpoint Script users.php


<?php
if (isset($_POST['req'])) {
  // INIT
  require dirname(__DIR__) . DIRECTORY_SEPARATOR . "lib" . DIRECTORY_SEPARATOR . "db-config.php";
  require PATH_LIB . "user-lib.php";
  $users = new User();
  // PROCESS REQUEST
  switch ($_POST['req']) {
    default:
      echo json_encode([
        "status" => false,
"message" => "Invalid Request"
]);
break;
case "get-all":
$all = $users->getAll();
echo json_encode([
"status" => $all==false?false:true,
"data" => $all
]);
break;
case "get-email":
$usr = $users->getEmail($_POST['email']);
echo json_encode([
"status" => $all==false?false:true,
"data" => $usr
]);
break;
case "get-id":
$usr = $users->getID($_POST['id']);
echo json_encode([
"status" => $all==false?false:true,
"data" => $usr
]);
break;
case "create":
$pass = $users->create($_POST['name'], $_POST['email'], $_POST['password']);
echo json_encode([
"status" => $pass,
"message" => $pass ? "User Created" : "Error creating user"
]);
break;
case "update":
$pass = $users->update($_POST['name'], $_POST['email'], $_POST['password'], $_POST['id']);
echo json_encode([
"status" => $pass,
"message" => $pass ? "User Updated" : "Error updating user"
]);
break;
case "delete":
$pass = $users->delete($_POST['id']);
echo json_encode([
"status" => $pass,
"message" => $pass ? "User Deleted" : "Error deleting user"
]);
break;
case "login":
if (is_array($_SESSION['user'])) {
die(json_encode([
"status" => true,
"message" => "Already signed in"
]));
}
$pass = $users->login($_POST['name'], $_POST['password']);
if ($pass!==false) { $_SESSION['user'] = $pass; }
echo json_encode([
"status" => is_array($pass),
"message" => is_array($pass) ? "OK" : "Error"
]);
break;
case "logoff":
unset($_SESSION['user']);
echo json_encode([
"status" => true,
"message" => "OK"
]);
break;
}
}
?>

😆

I think the script pretty much self-explains why I said the endpoint is a piece of cake when the library is done right.  But how does the whole API endpoint thing work here?

  • API requests will be posted to this api/3-users.php file.
  • All the requests will contain $_POST['req'] to state the required function, followed by the required parameters. For example, if we want to get a user by email, we should post $_POST['req']="get-email" and $_POST['email']="john@doe.com" to this user endpoint.
  • The system will give a response in the JSON format. I usually like to keep a standard format for the response to not confuse any 3rd party using the API. For example:
Response KeyDescription
statusTrue or false, whether the process is a success or failure.
messageSystem message, if any.
dataContains the requested data, for example, this will hold a list of users that you searched for

Testing The API – test.html


<!DOCTYPE html>
<html>
  <head>
    <title>USER API TEST</title>
  </head>
  <body>
    <h1>Get All Users</h1>
    <form action="api/users.php" method="post" target="_blank">
    Request <input type="text" name="req" value="get-all" readonly/><br>
    <input type="submit" value="Get"/>
    </form>

    <h1>Get User By Email</h1>
    <form action="api/users.php" method="post" target="_blank">
    Request <input type="text" name="req" value="get-email" readonly/><br>
    Email <input type="email" name="email" required/><br>
    <input type="submit" value="Get"/>
    </form>

    <h1>Get User By ID</h1>
    <form action="api/users.php" method="post" target="_blank">
    Request <input type="text" name="req" value="get-id" readonly/><br>
    Email <input type="text" name="id" required/><br>
    <input type="submit" value="Get"/>
    </form>

    <h1>Create New User</h1>
    <form action="api/users.php" method="post" target="_blank">
    Request <input type="text" name="req" value="create" readonly/><br>
    Name <input type="text" name="name" required/><br>
    Email <input type="email" name="email" required/><br>
    Password <input type="text" name="password" required/><br>
    <input type="submit" value="Create"/>
    </form>

    <h1>Update</h1>
    <form action="api/users.php" method="post" target="_blank">
    Request <input type="text" name="req" value="update" readonly/><br>
    ID <input type="text" name="id" required/><br>
    Name <input type="text" name="name" required/><br>
    Email <input type="email" name="email" required/><br>
    Password <input type="text" name="password"/><br>
    <input type="submit" value="Update"/>
    </form>

    <h1>Delete</h1>
    <form action="api/users.php" method="post" target="_blank">
    Request <input type="text" name="req" value="delete" readonly/><br>
    ID <input type="text" name="id" required/>
    <input type="submit" value="Delete"/>
    </form>
  </body>
</html>

There are many ways you can test the API – But I have created a few simple HTML forms for you to test it. For the beginners who are still confused over what the previous 3 steps do, I think these simple tests will show you exactly how the whole API works. For example, if you want to create a new user, all you have to do, is to post the necessary data to api/3-users.php:

reqcreate
nameidd juma skylar
emailjuma@howto-daily.com
passwordsky123

If the process is successful, you should get a response from the server:


{"status":true,"message":"User Created"}

Friendly URL .. HTACCESS REWRITE

For the API endpoint to look neat, you might want to change from /users.php to something like /users/v1. To learn how to do this , check out our next article here.

Do not forget to share! and leave a facebook comment or email me at hi@howto-daily.com


Pass this to the next buddy!

Howto-daily Team

Howto-daily team is a group of volunteering writers who are working very hard to achieve the free-knowledge access goals. Ack:- J. Jay, I.O. Iddris, I.J.Kinley, I.I. Kim. Email letmehelp AT howto-daily dot com.

You may also like...

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.