What is an API ? Create a simple REST API using php and MySQL

Well, we have always been there especially those non technical ones..

Let’s begin with something really simple here, What is an API ?
API stands for Application Programming Interface where it allows two applications to communicate with each other. The communication could handle data such as details or a transaction, numbers, strings or text, statuses and so on.. Example: Each time you like your friends post on Instagram or on Facebook you are actually fire up an API that sends this ‘Like’ to appear on the post.

API is an intermediary that speaks through a web server, retrieves information from a storage and then presents it back to the user. The below diagram can simplify it further.

There are various types of APIs that you can develop as per your business need and they could be REST, SOAP, XML and so on.. The mostly commonly used APIs are REST.

REST stands for — representational state transfer which is a very popular web API architecture. To be a REST API, an API must adhere to certain architectural constraints, or principles. REST APIs can be developed using various server side technologies such as php, python, .NET, Node, Express and so on. To keep it simple and know how a basic REST API works, lets learn how to build a REST API using php.

What you need to cook up your 1st REST API locally / on your personal computer?

Before we move ahead let me tell you a bit about our webserver — XAMPP. It is a package that contains X — which means its cross platform, Apache as a web server — that will serve your application, M — formerly called as mySQL but in recent times its termed as MariaDB, P — Perl and finally P — php.

Once you have installed XAMPP you should be able to a screen that looks like .. this is the XAMPP Control Panel.

Usually in online shared hosting agents such as GoDaddy, you will have something similar but more browser intuitive and it will be called cPanel.

The Module column will be grey and it denotes the status of the services, in the above case all services are currently inactive. To activate lets start by clicking Start next to Apache (our web server) and MySQL (our database).

As soon as they are started this is how it should look (in Windows , you may get an additional popup that asks you permission to Allow Access on activating Apache)

The section below will always keep updating (kind of a logging) that will tell you if Apache / MySQL is running or not. To verify whether the site is working, go to your browser and type in ‘localhost’ in the address bar. This is what it you should see..

If you click on phpMyAdmin you will be redirected to ..

This is where we will create our database table and store some data into it ..

Lets create a new database and add data to it.

Click on new, the left hand of the screen should have an empty text box where you will enter your database name, for our example I have named our database as api-tutorial and format selected is utf8mb4_general_ci

After submitting the above you will be asked to create tables within your database and should look like this

Tables as in general have rows and columns, in our case lets create a users table with 4 columns in it. This represents each user will have 4 different attributes : user_id, username, email_id, contact_number below is how it should be filled in:

The column type is an important factor here as it determines what kind of data type will be assigned to the column name so for example contact_number will be assigned to the datatype integer. For more details on various data types you can refer to this article There are other attributes such as string length and so on that can be added to, but we wont be going indepth in this article.

Above we have assigned the various columns their respective datatypes and length of each of the entries that we expect. Click on save and you will return to the main page, lets add values / data to our newly create table- users.

There are two ways of adding data into tables, the manual way using the UI —

or a SQL query, this has to be entered into the text box available in the SQL tab -

INSERT INTO `users` (`user_id`, `username`, `email_id`, `contact_number`) VALUES (‘1’, ‘clevin’, ‘mail@mail.com’, ‘1234567890’);

Click on Go — and you should see these values in the table

Accordingly you can add as many as records you wish to. For now lets go with 2 more records- 3 in total and this is how it should look:

Open a new file in VS code and name it api.php, remember the php file will run through XAMPP using Apache, location should be C:\xampp\htdocs

Note: host = localhost, username = root, password =<blank>, db name = api-tutorial

This is probably the most default connection parameters you will need to connect with any database locally on any machine. They can be replaced with a real time database by replace the credentials and host where the database is being hosted.

In our api.php file, we will type the below code using Visual Studio Code

$con = mysqli_connect("localhost", "root","","api-tutorial");
if($con) {
echo "Database connected";

in your browser you should browse the file localhost/api.php and you should see Database Connected as a message in your browser. Our first success!

Next lets continue to write our API below the above code. (I have removed the Database Connected response in the below code to provide a clean JSON response.)

<?php$con = mysqli_connect("localhost", "root","","api-tutorial");$response = array();if($con) {$sql = "select * from users";$result = mysqli_query($con,$sql);if($result) {header("Content-Type: JSON");$i=0;while($row = mysqli_fetch_assoc($result)){$response[$i]['user_id'] = $row['user_id'];$response[$i]['username'] = $row['username'];$response[$i]['email_id'] = $row['email_id'];$response[$i]['contact_number'] = $row['contact_number'];$i++;}echo json_encode($response, JSON_PRETTY_PRINT);}}

refresh the page localhost/api.php and you should see the response of your API thus fetching data from our Database api-tutorial.

Hurray! we have created our first API and retrieved the data in a JSON format. You can use the above code as a base and keep adding variations in your data. This data can also be represented in the form for a simple table in a html file. Please comment below in case you want me to make an extended version of this tutorial to consume APIs in the frontend and I will try to keep it as simple as possible. Thank you for your time.

Please note: This is a very basic explanation of how a REST API works and how it can be achieved using just 1 php file. In live environments you should avoid keeping the database connection settings in your php file and rather store it in a hidden ENV file or a encrypted string.

Product Management, building better user experiences by understanding personas, passion for design , Tech enthusiast and musician. Always learning..