Rest api in php mysql

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/(9*)$ 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 REST API in PHP MySQL

The REST API in PHP with MySQL allows you to create a web service that enables communication between your PHP application and a MySQL database. REST APIs provide a standardized way to access and manipulate data, making it easy to build dynamic and interactive web applications.

If you want to create web services or rest API in PHP MySQL to get, create, update, and delete data in JSON format from MySQL database. So for this, you have to create and use Rest API.

In this tutorial, you will learn how to create and use REST API in PHP MySQL to get/retrieve, create/add, update, and delete data from a database in PHP with json format.

How to Create a REST API in PHP MySQL

• Step 1: Create Database and Table
• Step 2: Connect to MySQL Database
• Step 3: Define API Endpoints
• Step 4: Test the API in POSTMAN

Before building the Rest API, you need to create a new project directory in the document root of your server. and open your project directory in a code editor.

Step 1: Create a Database and Table

First of all, you need to create a MySQL database and table to store the data for your bootstrap form data. Open your preferred MySQL management tool (e.g., phpMyAdmin) and execute the following SQL query to create a table named users and database name demo:

CREATE DATABASE Demo; CREATE TABLE users ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, );

Step 2: Connect to MySQL Database

Next, you need to connect to the MySQL database using PHP. So, Create a new PHP file named db.php and add the following code:

connect_error) < die("Connection failed: " . $conn->connect_error); >

Step 3: Define API Endpoints

Now, you need to create APIs to add, edit, update and delete data from MySQL database. So, create a new PHP file, such as index.php , which will act as the entry point for our API, add the following code for the respective endpoints

query($sql); if ($result->num_rows > 0) < $row = $result->fetch_assoc(); echo json_encode($row); > else < echo json_encode("User not found"); >> // Create a new user if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['name']) && isset($_POST['email'])) < $name = $_POST['name']; $email = $_POST['email']; $sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')"; $result = $conn->query($sql); if ($result === TRUE) < echo json_encode("User created successfully"); >else < echo json_encode("Error creating user: " . $conn->error); > > // Update an existing user if ($_SERVER['REQUEST_METHOD'] === 'PUT' && isset($_GET['id']) && isset($_POST['name']) && isset($_POST['email'])) < $id = $_GET['id']; $name = $_POST['name']; $email = $_POST['email']; $sql = "UPDATE users SET name='$name', email='$email' WHERE $result = $conn->query($sql); if ($result === TRUE) < echo json_encode("User updated successfully"); >else < echo json_encode("Error updating user: " . $conn->error); > > // Delete an existing user if ($_SERVER['REQUEST_METHOD'] === 'DELETE' && isset($_GET['id'])) < $id = $_GET['id']; $sql = "DELETE FROM users WHERE $result = $conn->query($sql); if ($result === TRUE) < echo json_encode("User deleted successfully"); >else < echo json_encode("Error deleting user: " . $conn->error); > >

Step 4: Test the API in POSTMAN

Send HTTP requests to the API endpoints using a tool like cURL, Postman, or a web browser extension.

• To retrieve a user by ID: GET http://localhost/index.php?id=1
• To create a new user: POST http://localhost/index.php with form data containing name and email fields.
• To update an existing user: PUT http://localhost/index.php?id=1 with form data containing name and email fields.
• To delete an existing user: DELETE http://localhost/index.php?id=1

Conclusion

That’s it! You have learned how to create a REST API in PHP with MySQL.

Источник