How to Create API in PHP with MySQL – Step By Step

1 Comment / Code / By

In this tutorial I will share how to create API in PHP with database connection like MySQL.

APIs, or application programming interfaces, are essential tools for businesses of all sizes. APIs are important from a technical standpoint because they allow one computer program’s capabilities to be used by another. They allow two different programmes to communicate with one another.

In this article, we’ll create a simple demo application that helps you retrieving a list of users from a MySQL database using an API endpoint.

#Step1. Setup the project Structure

In the first step on how to create API in PHP, we will create the project with given structure

├── Controller
│   └── Api
│       ├── BaseController.php
│       └── UserController.php
├── inc
│   ├── bootstrap.php
│   └── config.php
├── index.php
└── Model
    ├── Database.php
    └── UserModel.php

Now, lets understand the structure one by one

  • index.php: this file will be the entry point of our API which acts like a front-controller of our application.
  • inc/config.php: this file stores the configuration information of our application such as database credentials.
  • inc/bootstrap.php: this file will be used to bootstrap our application by including the necessary files.
  • Model/Database.php: this file will be used to interact with MySQL database.
  • Model/UserModel.php: this UserModel.php file will implement the methods to interact with the users table in the MySQL database.
  • Controller/Api/BaseController.php: our BaseController.php file which stores common utility methods for our project.
  • Controller/Api/UserController.php: the UserContoller.php file which stores the necessary application code to make API calls.

This is the basic file structure we are going implement to create APIs

#Step2. Create Important Database and Model Classes

In this step on how to create API in PHP we’ll create database and users table. Also we’ll create the models classes which is going to used to fetch users data from database.

Create a Database and create Users Table

Create the api_demo database by executing the following command in your MySQL terminal. (You can access it with MySQL command in command line)

$CREATE DATABASE api_demo;

My choice is using XAMPP phpmyadmin for working with database or you can also use MySQL workbench.

When the database api_demo is created then we are going to create the users table by running the following statements.

$use api_demo;
$CREATE TABLE `users` (
  `user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This will create the users table in the api_demo database. And also you should insert some dummy data in the table for testing purposes.

Create Model Classes

Now, we’re going to create some important model classes.

So now create Model/Database.php file with the following contents

<?php
class Database
{
    protected $connection = null;
 
    public function __construct()
    {
        try {
            $this->connection = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_DATABASE_NAME);
         
            if ( mysqli_connect_errno()) {
                throw new Exception("Could not connect to database.");   
            }
        } catch (Exception $e) {
            throw new Exception($e->getMessage());   
        }           
    }
 
    public function select($query = "" , $params = [])
    {
        try {
            $stmt = $this->executeStatement( $query , $params );
            $result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);               
            $stmt->close();
 
            return $result;
        } catch(Exception $e) {
            throw New Exception( $e->getMessage() );
        }
        return false;
    }
 
    private function executeStatement($query = "" , $params = [])
    {
        try {
            $stmt = $this->connection->prepare( $query );
 
            if($stmt === false) {
                throw New Exception("Unable to do prepared statement: " . $query);
            }
 
            if( $params ) {
                $stmt->bind_param($params[0], $params[1]);
            }
 
            $stmt->execute();
 
            return $stmt;
        } catch(Exception $e) {
            throw New Exception( $e->getMessage() );
        }   
    }
}

Now, this will be our database access layer class, which allows us to setup a connection to our MySQL database. Also it will contain alot of common methods like select and executeStatement which allows us to select records from the database.

Now, we will create the Model/UserModel.php class with these lines

<?php
require_once PROJECT_ROOT_PATH . "/Model/Database.php";
 
class UserModel extends Database
{
    public function getUsers($limit)
    {
        return $this->select("SELECT * FROM users ORDER BY user_id ASC LIMIT ?", ["i", $limit]);
    }
}

Also it is very important that the UserModel class extends the Database class.

Also it contains the getUsers method, which will be used to select users from the MySQL database. It’s important to pass the $limit parameter, which will makes sure that it won’t select all records at once.

Also, you can define more methods in the UserModel class as per your project. For now, we’ll keep things simple for this tutorial.

So now we have our database and model classes setup. In the next step, we are going to create controllers and the remaining files in our demo project.

#Step3. Create Application Layer Components

In this step on how to create API in PHP, we’ll create the files that are required for our demo application to work.

The inc Directory

First, we’ll create some important configuration files.

Create inc/config.php file with the following contents.

<?php
define("DB_HOST", "localhost");
define("DB_USERNAME", "demo");
define("DB_PASSWORD", "demo");
define("DB_DATABASE_NAME", "rest_api_demo");

Also make sure to update all the values with the actual data that you’re using in your database.

Next, create the inc/bootstrap.php file with the following code.

<?php
define("PROJECT_ROOT_PATH", __DIR__ . "/../");
 
// include main configuration file
require_once PROJECT_ROOT_PATH . "/inc/config.php";
 
// include the base controller file
require_once PROJECT_ROOT_PATH . "/Controller/Api/BaseController.php";
 
// include the use model file
require_once PROJECT_ROOT_PATH . "/Model/UserModel.php";
?>

Firstly, we’ve initialized the PROJECT_ROOT_PATH constant with root directory of our project. In this way, we could use the PROJECT_ROOT_PATH constant to prepare absolute paths in our project. Next, we’’ve’ll include the config.php file, which holds the database connection information. Finally, we’ve included controller and model files.

So this is it for setting up the generic files in our application.

#Step4. The Controller Directory

In this step on how to create API in PHP, we’ll implement controllers that hold the majority of our application logic.

The BaseController.php File

Now, create the Controller/Api/BaseController.php file with the following contents. The BaseController class contains the utility methods which will be used by other controllers.

<?php
class BaseController
{
    /**
     * __call magic method.
     */
    public function __call($name, $arguments)
    {
        $this->sendOutput('', array('HTTP/1.1 404 Not Found'));
    }
 
    /**
     * Get URI elements.
     * 
     * @return array
     */
    protected function getUriSegments()
    {
        $uri = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH);
        $uri = explode( '/', $uri );
 
        return $uri;
    }
 
    /**
     * Get querystring params.
     * 
     * @return array
     */
    protected function getQueryStringParams()
    {
        return parse_str($_SERVER['QUERY_STRING'], $query);
    }
 
    /**
     * Send API output.
     *
     * @param mixed  $data
     * @param string $httpHeader
     */
    protected function sendOutput($data, $httpHeaders=array())
    {
        header_remove('Set-Cookie');
 
        if (is_array($httpHeaders) && count($httpHeaders)) {
            foreach ($httpHeaders as $httpHeader) {
                header($httpHeader);
            }
        }
 
        echo $data;
        exit;
    }
}

Let’s go through all the BaseController class methods quickly.

The __call method is a magic method, and it’s called when you try to call a method that doesn’t exist. We’re using this opportunity to throw the HTTP/1.1 404 Not Found error when someone tries to call a method which we haven’t implemented. If this sounds confusing to you, don’t worry—it will make more sense when we test our application in the next step.

Next, there’s the getUriSegments method, which returns an array of URI segments. It’s useful when we try to validate the REST endpoint called by the user. Following that, there’s the getQueryStringParams method, which returns an array of query string variables that are passed along with the incoming request.

Finally, there’s the sendOutput method, which is used to send the API response. We’ll call this method when we want to send the API response to the user.

The UserController.php File

Next, create the Controller/Api/UserController.php file with the following contents.

<?php
class UserController extends BaseController
{
    /**
     * "/user/list" Endpoint - Get list of users
     */
    public function listAction()
    {
        $strErrorDesc = '';
        $requestMethod = $_SERVER["REQUEST_METHOD"];
        $arrQueryStringParams = $this->getQueryStringParams();
 
        if (strtoupper($requestMethod) == 'GET') {
            try {
                $userModel = new UserModel();
 
                $intLimit = 10;
                if (isset($arrQueryStringParams['limit']) && $arrQueryStringParams['limit']) {
                    $intLimit = $arrQueryStringParams['limit'];
                }
 
                $arrUsers = $userModel->getUsers($intLimit);
                $responseData = json_encode($arrUsers);
            } catch (Error $e) {
                $strErrorDesc = $e->getMessage().'Something went wrong! Please contact support.';
                $strErrorHeader = 'HTTP/1.1 500 Internal Server Error';
            }
        } else {
            $strErrorDesc = 'Method not supported';
            $strErrorHeader = 'HTTP/1.1 422 Unprocessable Entity';
        }
 
        // send output
        if (!$strErrorDesc) {
            $this->sendOutput(
                $responseData,
                array('Content-Type: application/json', 'HTTP/1.1 200 OK')
            );
        } else {
            $this->sendOutput(json_encode(array('error' => $strErrorDesc)), 
                array('Content-Type: application/json', $strErrorHeader)
            );
        }
    }
}

It’s important to note that the UserController class extends the BaseController class. Ideally, this class would contain the action methods that are associated with the REST endpoints that are defined for the user entity. In our case, for example, the /user/list REST endpoint corresponds to the listAction method. In this way, you can also define other methods for other REST endpoints.

The listAction method is used to get a list of users from the MySQL database. It contains the whole logic of the /user/list REST endpoint.

In the listAction method, we’ve initialized a couple of variables like $requestMethod and $arrQueryStringParams in the first place. Next, we check if the user has called the user/list endpoint with GET method; otherwise, we will not process further. Finally, we will create the UserModel object and call getUsers method to fetch list of users from the database. We would’ve also used the json_encode function to convert an array into a JSON object before it’s sent to the user.

Atlast, we’ve used the sendOutput method to send the JSON response to the user. It’s important to note that the response of content-type header is set to application/json since we’re sending the JSON response.

Similarly, you could define different methods for different endpoints.

#Step5. The index.php File

In this step on how to create API in PHP, the index.php file is the entry-point of our application. Let’s see how it looks.

<?php
require __DIR__ . "/inc/bootstrap.php";
 
$uri = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH);
$uri = explode( '/', $uri );
 
if ((isset($uri[2]) && $uri[2] != 'user') || !isset($uri[3])) {
    header("HTTP/1.1 404 Not Found");
    exit();
}
 
require PROJECT_ROOT_PATH . "/Controller/Api/UserController.php";
 
$objFeedController = new UserController();
$strMethodName = $uri[3] . 'Action';
$objFeedController->{$strMethodName}();
?>

At first, we’ve used parse_url and explode functions to initialize URI segments into the $uri array variable. Then, we will be validating the URI segments. Finally, would’ve initialized the UserController controller and called the corresponding action method.

With this, we’ve created all the necessary files in our demo application. In the next step, we’ll see how to call it from the end-user perspective.

#Step6. How to Call Our REST API

In this step, we’ll see how to call our demo application. In our application, we’ve built a API endpoint to get a list of users.

This is how the URL of our endpoint looks:

// https://localhost/index.php/{MODULE_NAME}/{METHOD_NAME}?limit={LIMIT_VALUE}
http://localhost/index.php/user/list?limit=20

If you recall the index.php file, we checked if the $uri[2] variable is set to user. Also, the $uri[3] variable value would act as a method name. In the above case, the $uri[3] variable is set to list. Thus, it would end up calling the listAction method of the UserController class.

[
   {
      "user_id":1,
      "username":"Bob",
      "user_email":"bob@gmail.com",
      "user_status":0
   },
   {
      "user_id":2,
      "username":"John",
      "user_email":"john@gmail.com",
      "user_status":1
   },
   {
      "user_id":3,
      "username":"Mark",
      "user_email":"mark@gmail.com",
      "user_status":1
   },
   {
      "user_id":4,
      "username":"Ville",
      "user_email":"ville@gmail.com",
      "user_status":0
   }
]

Now you can see, it returns a list of users as a JSON object. Also, if there’s any application error, then it would’ve been returned as a JSON object as well for debugging purposes.

Conclusion

So in this article, we discussed how you can build a API with PHP and MySQL. For testing purposes, we created a demo application from which you can fetch a list of users from a MySQL database via API.

1 thought on “How to Create API in PHP with MySQL – Step By Step”

  1. Pingback: How to fix 'Error: MySQL shutdown unexpectedly.' in XAMPP - Kamlesh Naidu

Leave a Comment

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

Contact

Get in Touch with Kamlesh

I provide high quality Web Solutions which includes Customized ERP Software, Digital Marketing and SEO tools at affordable price.

Contact Me