Easy PHP MySQL API for SysAdmins

Welcome to SysAdmin-Tools!

This blog explains how to create a simple PHP API in CPanel. This can also be completed locally, but takes a little more work. 

This API will allow you to post data to MySQL via a URL and read the data in MySQL by browsing to a URL. 
It also has an example of calling a specific row in the database based on an input. 

This is quite useful once you understand how it works and can customize the API to your needs. I really tried to make it as easy as possible so that almost anyone can understand that is willing to learn. 

These instructions already assume you have set your domain DNS A record to the IP address of your CPanel account or relevant server. 

I will only vaguely explain how to create the database and user account for the database, because there are quite a few ways to do this.
A simple 2 minute YouTube tutorial should do the trick. 

IMPORTANT: This how-to only covers the basics and is not a secure real-world scenario. It can be used for any purpose, but if the data that is being added or read from the database is confidential, then a more secure solution should be created by calling the database configuration (which contains the password) from a different location and ensuring HTTPS is used.
For this purpose, to keep it easy I have added the database config inside the main file.

There is also very little error checking in this code to keep it simple.

PREPARE THE DATABASE

1. Select the Database Wizard option if you have one. This is the easiest way.
   1. Give your database a name.
   2. Choose a username
   3. Give the user SELECT and INSERT permissions.
2. If you do not see a Database wizard, just create a database, then a user and assign the required permissions (SELECT and INSERT).

PREPARE THE TABLE

3. Decide what kind of info you may need in the database and create your table. To do this, follow these steps.
   1. Open phpMyAdmin
   2. Click your database on the left
   3. Open the SQL tab/button from the top row buttons.
   4. Paste the following script (in green below) in the script area and click Go.
      This creates your table and can be modified as needed.
      Here are the values I included.
      1. Id = INT (number) , this auto increments, so does not need to be specified in the URL later.
      2. Name = String (varchar 128 chars)
      3. Count = INT (number)
      4. Datetime = datetime of the entry, default value is now(), which means that even if you don’t specify it, it adds the current date and time of the entry. EASY!
      5. Primary Key in this case is just a row counter.

CREATE TABLE `api_data` (
`id` int(6) unsigned NOT NULL auto_increment,
`name` varchar(128) collate utf8_unicode_ci NOT NULL default '',
`count` int(10) unsigned NOT NULL default '0',
`datetime` datetime NOT NULL default now(),
PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CLICK ON THE BANNER TO CHECK OUT OUR FREE AND PREMIUM TOOLS HERE

PREPARE THE FILE

4. Using the File Manager, create a new file in your public_html folder and call it anything with a .php extension, like api.php. You can also download the example below which has everything for you, you just need to modify the database connection settings. 
5. Open the GitHub link below.
6. Read through the code like it's an article with all the comments to get a feel of what is happening.
   I tried to make it as easy as possible (you don't have to understand everything in the code).

CODE : https://github.com/EasyAppscc/Blogs/blob/main/api.php
(code looks horrible on the site so its better to read it on Github) 

5. Either upload the file directly to your public_html directory or copy and paste the code in the file you created.

There are two methods in the API, called 'type' in this case.
Modify 'yourdomain.com' with your actual purchased domain. 

When you call your API GET type by browsing to http://yourdomain.com/api.php?type=GET  then you should see a blank page, because there is no data yet.

Try adding some data by browsing to this URL using the POST type with the additional 2 variables added. 

http://yourdomain.com/api.php?type=POST&name=file1.zip&count=2
This will add an entry with name as file1.zip, count as 2 and the date-time stamp will be added automatically because of the default value.

When you run http://yourdomain.com/api.php?type=GET  again, it will show the info. You can modify the output display in the foreach loop under the GET section.
HTML Tables can also easily be added by following some YouTube tutorial.

You can use this in many scenarios, even while scripting to add remote data to your own database.

ENJOY!

Struggling? 
Send me an email at support@sysadmin-tools.com

Liked the Blog? 
Go check out all our awesome tools on the homepage. 

CLICK ON THE BANNER TO CHECK OUT OUR FREE AND PREMIUM TOOLS HERE