RESTFul API Using PHP - OOP, PDO - MySQL


Sahil Kumar | 9 months ago | PHP |

In this post, I'm going to show you about RESTFul  API implementation using PHP - OOP and PDO - MySQL. A RESTful web service helps us to perform CRUD operations with MySQL database. In this post, We'll learn how to create RestFul API of all popular HTTP verbs like GET, POST, PUT & DELETE. 

Okay, here I'll post all the codes of this project. You only need to follow all the steps properly. In last I'll also use Postman to consume or test this RestFul API.

Step 1: 

First, open your htdocs or www folder and create a new project directory and name it "restful". Now open this directory inside your code editor.

 

Step 2: 

In this step, I'll create a database and table for this RestFul API. So open your "PHPMyAdmin" and create a new database, name it "restful". Now inside this database create a new table and name it "users". See the below image to define all the columns.

Restful API Database

 

Step 3: 

Our database and table are ready, now in this step, We'll create a new file in the project's root directory with name "config.php", in this file We'll write a class for connection to the database. Here We'll use PDO (PHP Data Output).

<?php  
	class Config {
	  // Database Details
	  private const DBHOST = 'localhost';
	  private const DBUSER = 'root';
	  private const DBPASS = '';
	  private const DBNAME = 'restful';
	  // Data Source Network
	  private $dsn = 'mysql:host=' . self::DBHOST . ';dbname=' . self::DBNAME . '';
	  // conn variable
	  protected $conn = null;

	  // Constructor Function
	  public function __construct() {
	    try {
	      $this->conn = new PDO($this->dsn, self::DBUSER, self::DBPASS);
	      $this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
	    } catch (PDOException $e) {
	      die('Connectionn Failed : ' . $e->getMessage());
	    }
	    return $this->conn;
	  }

	  // Sanitize Inputs
	  public function test_input($data) {
	    $data = strip_tags($data);
	    $data = htmlspecialchars($data);
	    $data = stripslashes($data);
	    $data = trim($data);
	    return $data;
	  }

	  // JSON Format Converter Function
	  public function message($content, $status) {
	    return json_encode(['message' => $content, 'error' => $status]);
	  }
	}

?>

 In this file, I've used constructor for database connectivity and I've also defined two more methods for utility purpose, the first test_input function will sanitize the inputs and message function will return a message and error status in JSON format.

Step 4: 

In this step, We'll create another file i.e., "db.php" file, in this file We'll write all the methods that directly interact with the database and return some results. Okay! So let's see the coding of this file below :

<?php  
	// Include config.php file
	include_once 'config.php';

	// Create a class Users
	class Database extends Config {
	  // Fetch all or a single user from database
	  public function fetch($id = 0) {
	    $sql = 'SELECT * FROM users';
	    if ($id != 0) {
	      $sql .= ' WHERE id = :id';
	    }
	    $stmt = $this->conn->prepare($sql);
	    $stmt->execute(['id' => $id]);
	    $rows = $stmt->fetchAll();
	    return $rows;
	  }

	  // Insert an user in the database
	  public function insert($name, $email, $phone) {
	    $sql = 'INSERT INTO users (name, email, phone) VALUES (:name, :email, :phone)';
	    $stmt = $this->conn->prepare($sql);
	    $stmt->execute(['name' => $name, 'email' => $email, 'phone' => $phone]);
	    return true;
	  }

	  // Update an user in the database
	  public function update($name, $email, $phone, $id) {
	    $sql = 'UPDATE users SET name = :name, email = :email, phone = :phone WHERE id = :id';
	    $stmt = $this->conn->prepare($sql);
	    $stmt->execute(['name' => $name, 'email' => $email, 'phone' => $phone, 'id' => $id]);
	    return true;
	  }

	  // Delete an user from database
	  public function delete($id) {
	    $sql = 'DELETE FROM users WHERE id = :id';
	    $stmt = $this->conn->prepare($sql);
	    $stmt->execute(['id' => $id]);
	    return true;
	  }
	}

?>

In this file first I've included the config.php file at the top and then I've created a class Database that is extending the class Config that is already written in config.php file. I've also defined some methods in the class like fetch, insert, update & delete and these methods are self-explanatory, there is no need to explain the content of the methods. So now We'll go to further steps.

Step 5: 

Now in this step, We'll create another new file i.e., "users.php", Now this file will be our main entry file okay! So in this file first We'll see coding part by part then I'll post full code below.

So, first We'll use some headers :

header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE');
header('Access-Control-Allow-Headers: X-Requested-With');
header('Content-Type: application/json');

Cross-Origin Resource Sharing (CORS) is a mechanism that uses additional HTTP headers to tell browsers to give a web application running at one origin, access to selected resources from a different origin. A web application executes a cross-origin HTTP request when it requests a resource that has a different origin (domain, protocol, or port) from its own.

Now next We'll include "db.php" file and create an object of Database class i.e., $user see the below codes

// Include action.php file
include_once 'db.php';
// Create object of Users class
$user = new Database();

// create a api variable to get HTTP method dynamically
$api = $_SERVER['REQUEST_METHOD'];

// get id from url
$id = intval($_GET['id'] ?? '');

I've also created a variable $api that stores the HTTP method dynamically and I've also created another variable to get the id from the URL okay! 

Now the most important part is here, first I'll get a single user from the database if the id is passed in the URL or if id is not passed in the URL then We'll get all users from the database. See the below codes :

// Get all or a single user from database
if ($api == 'GET') {
	if ($id != 0) {
		$data = $user->fetch($id);
	} else {
		$data = $user->fetch();
	}
	echo json_encode($data);
}

 Here I'm checking a condition that $api == 'GET', by this We are using GET HTTP verb for fetching records from the database.  Here I'm using $user object to call the fetch method that is already defined in the db.php file and finally echoing the $data as JSON format using json _encode function.

Now We'll see how to handle a POST method to insert data into the database : 

// Add a new user into database
if ($api == 'POST') {
	$name = $user->test_input($_POST['name']);
	$email = $user->test_input($_POST['email']);
	$phone = $user->test_input($_POST['phone']);

	if ($user->insert($name, $email, $phone)) {
		echo $user->message('User added successfully!',false);
	} else {
		echo $user->message('Failed to add an user!',true);
	}
}

 Here also I'm checking for POST HTTP verb because POST is used to send data to the server. In this, if-statement I've defined some variable and in variable I'm assigning the value of respective fields using $_POST superglobal variable, but here I'm also checking all the fields using test_input method that is already defined in the config.php file. Now on successful insertion, I'm printing a success message with a status of error using message method that is already defined in config.php file and same for unsuccessful insertion I'm printing a failed message.

Now next We'll see how to update a row in the database : 

// Update an user in database
if ($api == 'PUT') {
	parse_str(file_get_contents('php://input'), $post_input);

	$name = $user->test_input($post_input['name']);
	$email = $user->test_input($post_input['email']);
	$phone = $user->test_input($post_input['phone']);

	if ($id != null) {
		if ($user->update($name, $email, $phone, $id)) {
			echo $user->message('User updated successfully!',false);
		} else {
			echo $user->message('Failed to update an user!',true);
		}
	} else {
		echo $user->message('User not found!',true);
	}
}

Here I'm using PUT HTTP method, this method is used to update the existing record that is already present in the database using id. This method works differently than the POST method so you can notice I've used $post_input variable to assign the value in the input fields. Next, I'm checking if id is not null it means some id is passed in the URL then We'll update the particular row that is associated with the passed id in the URL else We are printing error message.

Now the last method is DELETE. This is used to delete an existing row from the database with a matching id which is passed in the URL.

// Delete an user from database
if ($api == 'DELETE') {
	if ($id != null) {
		if ($user->delete($id)) {
			echo $user->message('User deleted successfully!', false);
		} else {
			echo $user->message('Failed to delete an user!', true);
		}
	} else {
		echo $user->message('User not found!', true);
	}
}

 Here also I'm checking for the HTTP verb first then I'm checking if id is not null it means some id is passed in the URL then We'll delete a particular row from the database and print the success message with an error status. Similarly, if id is passed but this id is not found in the database then We are printing a failed message and in case id is not passed then also We are printing User not found error message. 

Now let's see the whole code of users.php file : 

<?php 
	// Include CORS headers
	header('Access-Control-Allow-Origin: *');
	header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE');
	header('Access-Control-Allow-Headers: X-Requested-With');
	header('Content-Type: application/json');

	// Include action.php file
	include_once 'db.php';
	// Create object of Users class
	$user = new Database();

	// create a api variable to get HTTP method dynamically
	$api = $_SERVER['REQUEST_METHOD'];

	// get id from url
	$id = intval($_GET['id'] ?? '');

	// Get all or a single user from database
	if ($api == 'GET') {
	  if ($id != 0) {
	    $data = $user->fetch($id);
	  } else {
	    $data = $user->fetch();
	  }
	  echo json_encode($data);
	}

	// Add a new user into database
	if ($api == 'POST') {
	  $name = $user->test_input($_POST['name']);
	  $email = $user->test_input($_POST['email']);
	  $phone = $user->test_input($_POST['phone']);

	  if ($user->insert($name, $email, $phone)) {
	    echo $user->message('User added successfully!',false);
	  } else {
	    echo $user->message('Failed to add an user!',true);
	  }
	}

	// Update an user in database
	if ($api == 'PUT') {
	  parse_str(file_get_contents('php://input'), $post_input);

	  $name = $user->test_input($post_input['name']);
	  $email = $user->test_input($post_input['email']);
	  $phone = $user->test_input($post_input['phone']);

	  if ($id != null) {
	    if ($user->update($name, $email, $phone, $id)) {
	      echo $user->message('User updated successfully!',false);
	    } else {
	      echo $user->message('Failed to update an user!',true);
	    }
	  } else {
	    echo $user->message('User not found!',true);
	  }
	}

	// Delete an user from database
	if ($api == 'DELETE') {
	  if ($id != null) {
	    if ($user->delete($id)) {
	      echo $user->message('User deleted successfully!', false);
	    } else {
	      echo $user->message('Failed to delete an user!', true);
	    }
	  } else {
	    echo $user->message('User not found!', true);
	  }
	}

?>

 

Step 6: 

Now in this step, We'll work on URL rewriting, We'll make Friendly URL that looks nice.

For example : 

https://localhost/restful/users.php?id=5

This is the default URL that does not look clean or good so We'll convert this URL into another form that will be cleaner and nice.

https://localhost/restful/users/5

Now you can see the above URL is perfect. So We'll do this by using .htaccess file. So for this just create a new file in the root directory of your project directory i.e. "restful" and then just use below codes :

#Turn Rewrite Engine On
RewriteEngine On  

#Remove .php extension form users.php
RewriteRule ^users/?$ users.php [NC,L] 

#Rewrite URL
RewriteRule ^users/([0-9]+)/?$ users.php?id=$1 [NC,L]

 

Step 7: 

Now our RestFul API is almost finished, We only have to consume it. So you can consume this RestFul API using any methods like ES6 fetch API, jQuery, Axios, PHP Curl etc. But here I'll test this API using Postman. It is a great tool when trying to dissect RESTful APIs made by others or test ones you have made yourself. It offers a sleek user interface with which to make HTML requests, without the hassle of writing a bunch of code just to test an API's functionality. You can download Postman just by clicking here.

So now let's test the RestFul API

INSERT an User Into Database

Insert record using postman

Here In this screenshot you can see I've selected POST method from the dropdown and written my URL "http://localhost/restful/users" and then clicked on Body tab then clicked on form-data and then added in key-value pair of input fields and their respective values and then clicked on Send after that you can see the success message with an error status in JSON format below.

GET All User From Database

Get all user using postman

Now to get all users from the database you only need to change the method in the dropdown field to GET and rest will be same just click on Send, then you will see all the users are displaying below in JSON format.

GET a Single User From Database

Get a single user using postman

To get a single user from the database you only need to pass id in the URL to get specific user details and method will be same i.e., GET

UPDATE a Single User From Database

Update an user using postman

To update an existing user in the database you need to change the HTTP method to PUT then you have to pass id in the URL and then click on Body tag and then click on x-www-form-urlencoded radio button and then enter your fields name with a new value, after that click on Send. If everything is okay then you will see a successful message.

DELETE a Single User From Database

Delete an user using postmanTo delete an user from the database you need to set the HTTP method to DELETE and in URL you have to pass an id which you want to delete and that's it... click on Send,  if there is any row present with this id then it will be deleted and you will see a success message below.

 

So We've successfully tested our RestFul API using Postman. You can check your table in PHPMyAdmin to be sure that every action is working or not?

Okay, so this post has been finished. If you have any issue regarding this then comment down your issue, I'll try to fix out.

Thanks for being patient for this post. if you liked this post then share this post with your friends and also check my YouTube channel if you want to learn advanced We Development. Go to my channel


Share Post Via
           
Give A Comment
Search
Categories
Youtube Channel
Subscription Box
Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

About Me
User Management System

Sahil Kumar

Full Stack Web Developer
Hello! I'm a part time Blogger & Youtuber living in India. This is my blog where I writes programming tutorial posts!
Follow Me