Create APIs to access MYSQL database in PHP

In the previous tutorial, you learnt how to do migration in PHP using Phinx and insert sample data to MYSQL database. In this tutorial, learn how to create APIs to list all products, list product by id, search products by name, count products, etc.

Update config/config.php file to add database credentials:
.................................

define('DB_HOST', 'localhost');

define('DB_USER', 'user');

define('DB_PASS', 'password');

define('DB_NAME', 'database name');


Then, create DB folder in app folder and add Database.php file to the DB folder. In the Database.php file, we define Database class that will be used to connect to Mysql database from controller classes.

DB/Database.php

<?php
// Connect with the database.
namespace App\DB;
class Database
{
  public function connect()
  {
    $connect = mysqli_connect(DB_HOST,DB_USER,DB_PASS,DB_NAME);

    if (mysqli_connect_errno($connect)) {
      die("Failed to connect:" . mysqli_connect_error());
    }

    mysqli_set_charset($connect, "utf8");

    return $connect;
  }
}

In the app/Controllers directory, create ProductController.php file. We define three methods: getProductById(),  countProducts(), and getProducts(). The getProductById returns a product that its id is equal to pid parameter. The countProducts() method accepted an optional searchText argument returns the number of products that their names contain the  searchText or all products if the searchText is not specified. The getProducts() method is to return products from the products table. It has three optional parameters - start, limit, and searchText. 


Controllers/ProductController.php

<?php

namespace App\Controllers;

use App\Models\Product;
use App\DB\Database;

class ProductController
{
    private $con;

    function __construct()
    {
         
        $db = new Database();
        $this->con = $db->connect();
       
           
    }
   
    public function getProductById(int $pid)
    {
     
       
        $products = [];
        $sql = "SELECT * FROM products WHERE id=$pid";

        if($result = mysqli_query($this->con,$sql))
        {
          $cr = 0;
          while($row = mysqli_fetch_assoc($result))
          {
            $products['id']    = $row['id'];
            $products['name'] = $row['name'];
            $products['description'] = $row['description'];
            $products['price'] = $row['price'];
            $products['slug'] = $row['slug'];
            $products['category'] = (int)$row['category_id'];
            $products['subcategory'] = (int)$row['subcategory_id'];
            $cr++;
          }
           
          echo json_encode(['data'=>$products]);
        }
        else
        {
          http_response_code(404);
        }
       
    }
   
   
    public function countProducts( string $searchText)
    {
     
       
        $num=0;
        $sql = "SELECT count(*) as num_rows FROM products WHERE name LIKE '%$searchText%'";
       

        if($result = mysqli_query($this->con,$sql))
        {
         
          while($row = mysqli_fetch_assoc($result))
          {
            $num=$row['num_rows'];
          }
         
          try {
         
          echo json_encode(['data'=>$num,'status' => 200]);
          }
          catch (Exception $e) {
            echo json_encode(['status'=>300,'data' => 'error']);
          }
         
        }
        else
        {
         
          echo json_encode(['data'=>0]);
        }
       
    }
    public function getProducts(int $start,int $limit, string $searchText)
    {
     
       
        $products = [];
   
        $sql = "SELECT * FROM products WHERE name LIKE '%$searchText%'";

        if($start!==''){
            $sql .= " LIMIT $start,$limit";
        }
       

        if($result = mysqli_query($this->con,$sql))
        {
          $cr = 0;
          while($row = mysqli_fetch_assoc($result))
          {
            $products[$cr]['id']    = $row['id'];
            $products[$cr]['name'] = $row['name'];
            $products[$cr]['description'] = $row['description'];
            $products[$cr]['price'] = $row['price'];
            $products[$cr]['slug'] = $row['slug'];
            $products[$cr]['thumbnail'] = $row['thumbnail'];
            $products[$cr]['category'] = $row['category_id'];
            $products[$cr]['subcategory'] = $row['subcategory_id'];
            $cr++;
          }
          echo json_encode(['data'=>$products,'status' => 200]);
         
        }
        else
        {
          http_response_code(404);
        }
       
    }
   
   
}

Update routes/web.php file to add more routes to the route collection:
................................
$routes->add('productbyid', new Route(constant('URL_SUBFOLDER') . '/product/{id}',
array('controller' => 'ProductController', 'method'=>'getProductById'),
array('id' => '[0-9]+')));
$routes->add('productscount', new Route(constant('URL_SUBFOLDER') . '/products/count/{searchText}',
array('controller' => 'ProductController', 'method'=>'countProducts', 'searchText' => ''), array()));
$routes->add('products', new Route(constant('URL_SUBFOLDER') . '/products/{start}/{limit}/{searchText}',
    array('controller' => 'ProductController',
        'method'=>'getProducts',
        'start' => 0,
        'limit' => 3,
        'searchText' => ''
    ),
    array('start' =>'[0-9]+','limit' =>'[0-9]+')));

Now the API endpoints are ready to tested. Save the project. While Mysql server is running, open the browser and access http://localhost/mysite/api/product/1. The product with id 1 is shown.



To get all products, access http://localhost/mysite/api/products. 

To get first five products,  access http://localhost/mysite/api/products/0/5.

To search for product by name containing NIKON and return only the first two products, access http://localhost/mysite/api/products/0/2/NIKON.

To get number of all products, access http://localhost/mysite/api/products/count.

To get number of products that their names contain Camera, access http://localhost:81/mysite/api/products/count/Camera

Comments

Post a Comment

Popular posts from this blog

APIs to Upload form data with file in PHP & MYSQL

Image
In the earlier tutorial, you learnt to filter products by id and by name, limit the number of rows to return, and count products . In this tutorial, we will create more APIs to upload product form data with image file, and to delete products from MYSQL database. The uploaded files are stored in local storage (uploads folder). Open Controllers/ProductController.php file to add three more methods to handle product inserting, updating, and deleting. In PHP, form data submitted from a client app is available in $_POST variable and uploaded files will be in $_FILES variable.  In the addProduct() and updateProduct() methods, we simply get data from the POST variable and insert or update the data to MYSQL database using Prepared Statements.  To save the uploaded files to local storage, use move_uploaded_file() method. The deleteProduct() required one parameter - pid to delete a product by its id from the database. ......................... public function addProduct() { tr...
Read more

Create Angular App & SideBar

Image
In the earlier tutorials, you learnt to create APIs in PHP with MYSQL database and Phinx, do authentication with JWT, and protect APIs. Now you learn to create Angular client/front-end app to access the APIs. After completing the Angular tutorials, you build a full stack web application using Angular, PHP, and MYSQL. Let start the first part of Angular tutorials by creating an Angular app and add side bar menu to the app. Create angcli14 folder in a drive of your choice. Enter the angcli14 and execute the following command to install Angular Cli version 14.0.0. angcli14>npm install @angular/cli@14.0.0 Then run the command below to create ang14-client Angular app. angcli14>ng new ang14-client After the project finishes its initialization, enter the ang14-client folder, and execute the command below to run the app. ang14-client>ng serve By default Angular launches the app on port 4200. So you can access the app on your local machine via http://localhost:4200 Let install followin...
Read more

PHP Mysql Database Migration Using Phinx

 In this tutorial, you will learn to do Mysql database migration with Phinx . You will be able to create tables, add/alter table fields, and insert sample data to Mysql database. I recommend you to install XAMPP. It comes with Apache, Mysql and PhpMyAdmin to manage databases.  This tutorial assumes you already read the previous tutorial: PHP Routing & Restful APIs . In the api folder, run the command below to add Phinx to the project: composer require robmorgan/phinx Then, you need to create Phinx configuration file by the command: ./vendor/bin/phinx init The command created phinx.php configuration file in api folder. Modify the content of phinx.php file below to add credential information to connect to Mysql database on your local machine. <?php return [ 'paths' => [ 'migrations' => '%%PHINX_CONFIG_DIR%%/db/migrations' , 'seeds' => '%%PHINX_CONFIG_DIR%%/db/seeds' ], 'environments' =...
Read more