How to Write a REST API in PHP and MySQL (Step by Step)

How to Build a REST API in PHP with MySQL

In this article, I will teach you how to build a REST web service that would connect to a MySQL database. Then it would fetch records from MySQL table and return them as json. Finally, we would host this service in IIS (Internet Information Service on Windows 10)

Let’s go!

These are the steps we would follow:

  1. Make sure IIS is installed and tested
  2. Install and test PHP
  3. Create a MySQL database and table
  4. Write the REST API
  5. Create an Application in IIS
  6. Deploy and Test the API
  7. Next Steps

This is How to Build REST API in .Net using Visual Studio

1. Make sure IIS is installed and tested

To test our PHP API, we need to host it in a server. Since we are using Windows, it is likely, you already have IIS.To check if IIS is installed:

  • Open Programs and Features
  • Make sure IIS and other features are selected as shown below

Installing Internet Information Services

  • To test the installation, just open the browser and visit http://localhost. If it is installed ok, then you will see the page below;
IIS Start page
IIS Start page

 

2. Install and test PHP

Simply download PHP from this link.

Unzip it into a folder C:/php

To test the php installation:

Create a test file test.php. Write the following code inside

<?php
   echo phpinfo():
?>

Save it in in  this directory C:/inetpub/wwwroot

Open the browser and visit http://localhost/test.php. If you see the page below, then the installation was successful

Use Web Platform Installer: You can also install PHP using Web Platform Installer. Download it from here. Install and run it. Then install PHP following the instruction.

 

3. Create a MySQL database and table

I’m sure you already have MySQL installed. If not, download it from here(the community edition) and install.

Create a table called tblUsers with the following fields:

id, firstname, lastname, location

Insert a few records into the table

To be able to connect to your MySQL server from an API, run the query below:

ALTER USER 'root'@'localhost' 
IDENTIFIED WITH mysql_native_password
BY 'root';

 

4. Write the REST API

To write the REST API, you will need an editor to make it easier. You can used Notepad++. Download it here.

Create a file called services.php. Write the following code inside

<?php
   $url = "localhost:3301";
   $database = "dcsdb";
   $username = "root";
   $password = "*****";	

   $conn = mysqli_connect($url, $username, $password, $database);
   if(!$conn) {
	die("Unable to connect: " . $conn->connect_error);
   }

   $sql = "SELECT * FROM dcsdb.tblformats";
   $results = mysqli_query($conn, $sql);

   $rows = array();
   if(mysqli_num_rows($results) > 0){
	while($record = mysqli_fetch_assoc($results)) {
		array_push($rows, $record);
	}		
	print json_encode($rows);
   }
   else {
	echo("No records found");
   }
?>

 

5. Create a Web Application in IIS

Before can deploy your API, you need to create a web application in IIS. This is very easy too.

First create a folder inside C:/inetpub/wwwroot. Name this folder demoweb(you can also choose another name)

Open IIS,

Right-click on the DefaultWebsite and choose ‘Add Application’

Give it an alias, demoweb. The Add Applicaiton dialog box is shown below

Set the Physical Path to the folder you created.

Defaultwebsite in IIS
Adding new Application in IIS

 

6. Deploy and Test the API

To deploy our API, simply copy the service.php file into the directory C:/inetpub/wwwroot/demoweb.

Then visit http://localhost/demo/services.php.

You would see that the data in the MySQL database is returned as json.

If you have any errors, then watch the video and follow the step by step.

 

7. Next Steps

So we succeeded in creating a REST API that performs a GET operation.

What about POST, PUT and DELETE?

Also, how do we get a single record by the Id.

We would explain this in the next article and in the video as well.

I recommend you subscribe to my channel so you get updates when new lessons are made. Besides, if you have challenges, you can let me know

2 Comments on “How to Write a REST API in PHP and MySQL (Step by Step)”

Leave a Reply

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