Php mysql web services

Create Simple REST API with PHP & MySQL

In our previous tutorial, we have explained how to develop School Management System with PHP & MySQL. In this tutorial, we will explain how to Create Simple REST API with PHP & MySQL.

REST (Representational State Transfer) is a way to define the architectural style for creating web services. The REST API are created at the server side with GET, POST, PUT or DELETE HTTP requests to perform certain tasks. The HTTP requests like create, read, update or delete are made from the client side.

If you’re a PHP developer and looking for solution to create CRUD (create, read, update, delete) operations REST API, then you’re here at the right place. In our previous tutorial, you have learned to Create RESTful API using Python & MySQL. In this tutorial you will learn to how create CRUD operations REST API with PHP and MySQL.

We will cover this tutorial in easy steps with live demo to create simple REST API to perform read, create, update and delete records.

So let’s start creating simple REST API with PHP and MySQL. Before we begin, take a look on files structure. We need following files for this tutorial.

We will create api/emp/ directory and keep all the required files for this REST api. We will create REST API with PHP to play with employee data to create, read, update and delete employee data.

Step1: Create MySQL Database Table

As we will play with employee data create and consume REST API, so first we will create emp table.

CREATE TABLE `emp` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `skills` varchar(255) NOT NULL, `address` varchar(255) NOT NULL, `designation` varchar(255) NOT NULL, `age` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step2: Simple REST API to Create Record

We will create PHP file emp/create.php to insert employee records to MySQL database. We will check for POST HTTP request and call method insertEmployee() to insert employee data to MySQL database table.

insertEmployee($_POST); break; default: header("HTTP/1.0 405 Method Not Allowed"); break; > ?>

In method insertEmployee() from class Rest.php , we will insert record into emp table and return JSON response.

empTable." SET name='".$empName."', age='".$empAge."', skills='".$empSkills."', address='".$empAddress."', designation='".$empDesignation."'"; if( mysqli_query($this->dbConnect, $empQuery)) < $messgae = "Employee created Successfully."; $status = 1; >else < $messgae = "Employee creation failed."; $status = 0; >$empResponse = array( 'status' => $status, 'status_message' => $messgae ); header('Content-Type: application/json'); echo json_encode($empResponse); > ?>

Now you need to make HTTP POST request to https://webdamn.com/demo/api/emp/create/ to insert http POST data to database.

Step3: Simple REST API to Read Record

We will create PHP file emp/read.php to create employee records from MySQL database table. We will check for GET http request and call method getEmployee() to get employee record according to request to get a single record or all records.

 $api->getEmployee($empId); break; default: header("HTTP/1.0 405 Method Not Allowed"); break; > ?>

In method getEmployee() from class Rest.php , we will get record from emp table and return as JSON response.

 $empQuery = " SELECT id, name, skills, address, age FROM ".$this->empTable." $sqlQuery ORDER BY id DESC"; $resultData = mysqli_query($this->dbConnect, $empQuery); $empData = array(); while( $empRecord = mysqli_fetch_assoc($resultData) ) < $empData[] = $empRecord; >header('Content-Type: application/json'); echo json_encode($empData); > ?>

Now you need to make HTTP GET request to https://webdamn.com/demo/api/emp/read/ to read employee records and display as JSON response.

Step4: Simple REST API to Update Record

We will create PHP file emp/update.php to update employee record. We will check for POST http request and call method updateEmployee() to perform employee record update.

updateEmployee($_POST); break; default: header("HTTP/1.0 405 Method Not Allowed"); break; > ?>

In method updateEmployee() from class Rest.php , we will update record into emp table and return status as JSON response.

empTable." SET name='".$empName."', age='".$empAge."', skills='".$empSkills."', address='".$empAddress."', designation='".$empDesignation."' WHERE "; echo $empQuery; if( mysqli_query($this->dbConnect, $empQuery)) < $messgae = "Employee updated successfully."; $status = 1; >else < $messgae = "Employee update failed."; $status = 0; >> else < $messgae = "Invalid request."; $status = 0; >$empResponse = array( 'status' => $status, 'status_message' => $messgae ); header('Content-Type: application/json'); echo json_encode($empResponse); > ?>

Now you need to make HTTP POST request to https://webdamn.com/demo/api/emp/update/ to update employee records and display status as JSON response.

Step5: Simple REST API to Delete Record

We will create PHP table emp/delete.php to perform employee record. We will check for http GET request and call method deleteEmployee() to delete employee record from database.

 $api->deleteEmployee($empId); break; default: header("HTTP/1.0 405 Method Not Allowed"); break; > ?>

In method deleteEmployee() from class Rest.php , we will delete record from emp table and return status as JSON response.

empTable." WHERE ORDER BY id DESC"; if( mysqli_query($this->dbConnect, $empQuery)) < $messgae = "Employee delete Successfully."; $status = 1; >else < $messgae = "Employee delete failed."; $status = 0; >> else < $messgae = "Invalid request."; $status = 0; >$empResponse = array( 'status' => $status, 'status_message' => $messgae ); header('Content-Type: application/json'); echo json_encode($empResponse); > ?>

Now you need to make HTTP GET request to https://webdamn.com/demo/api/emp/delete/ to delete employee record and display status as JSON response

Step6: Create .htaccess Rewrite Rule with PHP for Clean URLs

We will also create emp/.htaccess file to write some rule to access rest api with pretty URLs. We will add following rules.

RewriteEngine On # Turn on the rewriting engine RewriteRule ^read/([0-9a-zA-Z_-]*)$ read.php?id=$1 [NC,L] RewriteRule ^delete/(5*)$ delete.php?id=$1 [NC,L] RewriteRule ^create create.php [NC,L] RewriteRule ^update update.php [NC,L]

Step7: Consume Simple REST API with PHP

We will create table index.php to consume REST API to read employee records. We will make HTTP request to https://webdamn.com/demo/api/emp/read/ to get employee records. We will make HTTP request with CURL to read employee records. you can check this in live demo.

Here we have handled CRUD (create, read, update, delete) REST API operations to perform with employee data. You can further implement this in your project with enhancement related to security to restrict access etc.

You may also like:

• User Management System with PHP & MySQL
• Datatables Add Edit Delete with Ajax, PHP & MySQL
• Build Helpdesk System with jQuery, PHP & MySQL
• Build Online Voting System with PHP & MySQL
• School Management System with PHP & MySQL
• DataTables Add Edit Delete with CodeIgniter
• Create RESTful API using CodeIgniter
• Build Reusable Captcha Script with PHP
• Product Search Filtering using Ajax, PHP & MySQL
• Image Upload and Crop in Modal with jQuery, PHP & MySQL
• Build Push Notification System with PHP & MySQL
• Project Management System with PHP and MySQL
• Hospital Management System with PHP & MySQL
• Build Newsletter System with PHP and MySQL
• Skeleton Screen Loading Effect with Ajax and PHP
• Build Discussion Forum with PHP and MySQL
• Customer Relationship Management (CRM) System with PHP & MySQL
• Online Exam System with PHP & MySQL
• Expense Management System with PHP & MySQL

You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download

30 thoughts on “ Create Simple REST API with PHP & MySQL ”

Hi, Its not working, just inserts empty rows. First of all i think id should be primary key and auto increment. and If you can provide working sql create table query then it should work. Thank you.

How can I insert data through parameters in Postman, will it accept data through parameters too? or it will accept data in body>raw>json only? please guide

Источник

Create a Basic Web Service Using PHP, MySQL, XML, and JSON

Web services are taking over the world. I credit Twitter’s epic rise to the availability of a simple but rich API. Why not use the same model for your own sites? Here’s how to create a basic web service that provides an XML or JSON response using some PHP and MySQL.

The PHP / MySQL

/* require the user as the parameter */ if(isset($_GET['user']) && intval($_GET['user'])) < /* soak in the passed variable or set our own */ $number_of_posts = isset($_GET['num']) ? intval($_GET['num']) : 10; //10 is the default $format = strtolower($_GET['format']) == 'json' ? 'json' : 'xml'; //xml is the default $user_id = intval($_GET['user']); //no default /* connect to the db */ $link = mysql_connect('localhost','username','password') or die('Cannot connect to the DB'); mysql_select_db('db_name',$link) or die('Cannot select the DB'); /* grab the posts from the db */ $query = "SELECT post_title, guid FROM wp_posts WHERE post_author = $user_id AND post_status = 'publish' ORDER BY ID DESC LIMIT $number_of_posts"; $result = mysql_query($query,$link) or die('Errant query: '.$query); /* create one master array of the records */ $posts = array(); if(mysql_num_rows($result)) < while($post = mysql_fetch_assoc($result)) < $posts[] = array('post'=>$post); > > /* output in necessary format */ if($format == 'json') < header('Content-type: application/json'); echo json_encode(array('posts'=>$posts)); > else < header('Content-type: text/xml'); echo ''; foreach($posts as $index => $post) < if(is_array($post)) < foreach($post as $key =>$value) < echo ''; if(is_array($value)) < foreach($value as $tag =>$val) < echo '',htmlentities($val),''; > > echo ''; > > > echo ''; > /* disconnect from the db */ @mysql_close($link); >

With the number of persons hitting your web service (hopefully), you’ll need to do adequate validation before attempting to connect to the database to avoid injection attacks. Once we get the desired results from the database, we cycle through the results to populate our return results array. Depending upon the response type desired, we output the proper header and content in the desired format.

Take the following sample URL for example:

http://mydomain.com/web-service.php?user=2&num=10

Now, we can take a look at the possible results of the URL.

The XML Output

  SSLmatic SSL Certificate Giveaway Winners https://davidwalsh.name/?p=2304  MooTools FileManager https://davidwalsh.name/?p=2288  PHPTVDB: Using PHP to Retrieve TV Show Information https://davidwalsh.name/?p=2266  David Walsh: The Lost MooTools Plugins https://davidwalsh.name/?p=2258  Create Short URLs Using U.Nu https://davidwalsh.name/?p=2218  Create Bit.ly Short URLs Using PHP https://davidwalsh.name/?p=2194  Represent Your Repositories Using the GitHub Badge! https://davidwalsh.name/?p=2178  ZebraTable https://davidwalsh.name/?page_id=2172  MooTools Zebra Table Plugin https://davidwalsh.name/?p=2168  SSLmatic: Quality, Cheap SSL Certificates and Giveaway! https://davidwalsh.name/?p=2158  

Take this next sample URL for example:

http://mydomain.com/web-service.php?user=2&num=10&format=json

Now, we can take a look at the possible results of the URL.

The JSON Output

Creating a basic web service is very simple and encourages your users to spread the word about your website or service. Want more traffic? Want your website to grow without you putting in all the effort? Create a web service!

Источник

Create Basic Web Service using PHP and MySQL

Web Services are getting more and more popularity. There are thousands of Web Services available for updating E-Commerce, schools, stock market database etc. Actually Web services are just Web APIs that can be accessed over a network, such as Internet, and executed on a remote system hosting the requested services. There are three basic platform for We Services, these are SOAP, WSDL and UDDI. So here in this tutorial, we will discuss how to create basic web service that provides an XML or JSON response using PHP and MySQL.

As we have covered this tutorial with live demo to create basic web service with PHP, MySQL, XML and JSON, so the file structure for the example is following.

Steps1: Create View Result HTML

In index.php, we will create HTML to view results as XML or JSON.

 
Create Basic Web Service with PHP, MySQL, XML and JSON
 
 
http://localhost/phpzag/web-service-using-php-mysql-xml-and-json/ result.php?price=1000&num=10&format=xml 
 
http://localhost/phpzag/web-service-using-php-mysql-xml-and-json /result.php?price=1000&num=10&format=json 
 

Steps2: Create Basic Web Service with PHP and MySQL

Now in result.php, we will create basic web service using PHP and MySQL to fetch data into XML or JSON format. In this web service, we will fetch products details based on price.

$product); > > /* output result in required format */ if($format == 'json') < header('Content-type: application/json'); echo json_encode(array('products'=>$products)); > else if($format == 'xml') < header('Content-type: text/xml'); echo ''; foreach($products as $index => $product) < if(is_array($product)) < foreach($product as $key =>$value) < echo ''; if(is_array($value)) < foreach($value as $tag =>$val) < echo '',htmlentities($val),''; > > echo ''; > > > echo ''; > @mysql_close($link); > ?>

• Working with php.ini file Configuration
• Control Statements in PHP
• Convert Associative Array into XML in PHP
• Convert XML into Associative Array in PHP
• Using Prepared Statement with PHP & MySQL
• How to Upload File in PHP
• Converting an Array to JSON in PHP
• Converting JSON to Array or Object in PHP
• Manipulating PHP arrays: push, pop, shift, unshift
• Remove Repeated Words From String in PHP
• Converting a PHP Array to a Query String
• 15+ regular expressions for PHP developers
• 10 Most Important Directory Functions in PHP
• 10 little known but useful PHP functions
• PHP Script to Download Large Files Reliably

You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download

Источник