Learn how you can create CRUD API with PHP and MySQL. It’s a Beginner’s guide for performing select, insert, update and delete operations on the database.
CRUD APIs provide access to database operations from different platforms and environments. One can create a web, desktop or even mobile application by using the same API. The combination of PHP & MySQL is popular world-wide for creating such APIs. This guide includes a full example code of basic CRUD API that can be used to perform all the operations i.e. Create, Read, Update and Delete on the MySQL Database with plain PHP code and without using any other frameworks or libraries.
Table of Contents
PHP MySQL CRUD API Code
The code given below needs to be written in a single PHP file. In our case, this file is named as api.php
. More than the four basic operations, this code includes one-additional operation and i.e. select only one. Consequently, the code can be used to create an API that performs the 5 different database operations mentioned below.
- Insert A Record Into Database
- Select All Records From Database
- Select Only One Record According To Condition
- Update A Record According To Condition
- Delete A Record According To Condition
PHP Code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
<?php //Accessable From Everywhere header("Access-Control-Allow-Origin: *"); //Establish Connection $servername = "localhost"; $username = "root"; $password = ""; $dbname = "demo"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $requestType = $_SERVER["REQUEST_METHOD"]; //Create if($requestType == "POST"){ $val_1 = $_POST['val_1']; $val_2 = $_POST['val_2']; $insertQuery = "INSERT INTO demo (col_1, col_2) VALUES ('$val_1','$val_2');"; if ($conn->query($insertQuery) === TRUE) echo "New Record Added Successfully."; else echo "Error: " . $insertQuery . "<br>" . $conn->error; } //Read if($requestType == "GET" AND !isset($_GET["id"])){ $selectQuery = "SELECT * FROM demo"; $result = $conn->query($selectQuery); $rows = array(); while($row = $result->fetch_assoc()) $rows[] = $row; header("Content-Type: application/json"); echo json_encode($rows); } //Read Only One if($requestType == "GET" AND isset($_GET["id"])){ $id = $_GET["id"]; $selectQuery = "SELECT * FROM demo WHERE ID = $id"; $result = $conn->query($selectQuery); $rows = array(); if ($result->num_rows > 0){ while($row = $result->fetch_assoc()) $rows[] = $row; header("Content-Type: application/json"); echo json_encode($rows); } else echo "Not Found."; } //Update if($requestType == "PUT"){ $str = file_get_contents("php://input"); $_PUT = array(); parse_str($str, $_PUT); $val_1 = $_PUT['val_1']; $val_2 = $_PUT['val_2']; $id = $_PUT['id']; $updateQuery = "UPDATE demo SET col_1 = '$val_1', col_2 = '$val_2' WHERE id = $id;"; if ($conn->query($updateQuery) === TRUE) echo "Record Updated Successfully."; else echo "Error: " . $updateQuery . "<br>" . $conn->error; } //Delete if($requestType == "DELETE"){ $str = file_get_contents("php://input"); $_DELETE = array(); parse_str($str, $_DELETE); $id = $_DELETE['id']; $deleteQuery = "DELETE FROM demo WHERE id = $id"; if ($conn->query($deleteQuery) === TRUE) echo "Record Deleted Successfully."; else echo "Error: " . $deleteQuery . "<br>" . $conn->error; } ?> |
Here below I’ve also provided the Database queries to create the table and to perform different CRUD operations on these tables.
- Database Name. demo
- Table Name. demo
Create Table Query
CREATE TABLE demo (id int NOT NULL AUTO_INCREMENT, val_1 TEXT, val_2 TEXT, PRIMARY KEY (id));
Insert Query
INSERT INTO demo (val_1, val_2) VALUES ('$val_1', '$val_2');
Update Query
UPDATE demo SET val_1 = '$val_1', val_2 = '$val_2' WHERE id = $id;
Insert Or Update On Duplicate (Not Required For The Tutorial, Just For More Knowledge!)
INSERT INTO demo (val_1, val_2) VALUES ('$val_1', '$val_2') ON DUPLICATE KEY UPDATE val_1 = '$val_1', val_2 = '$val_2';
Select Query
SELECT * FROM demo
SELECT * FROM demo WHERE id = $id
Delete Query
DELETE FROM demo WHERE id = $id
Understanding The Code
Now, let’s understand the code by each atomic segment of the code that performs a single CRUD operation. One this to notice here is the way of programming. I am using the Object-Oriented way but you can also achieve the same thing using procedural programming with PHP.
Setting Required Header
First of all, I’ve specified a header Access-Control-Allow-Origin
as *. This will allow the API to be used un-restrictedly from anywhere, rather than just from the local server. Not setting this header will result in Cross-Origin Resource Sharing (CORS) error when being accessed from other servers or static HTML files. You can set any header using the PHP header() function.
header("Access-Control-Allow-Origin: *");
- Related Guide. PHP Redirect – How To Redirect With PHP header() Function?
Establishing Connection With The MySQL Database
In order to communicate with the database for each of the CRUD operations, you’ll first require to establish a connection with the database. In PHP, you can easily establish a connection with the MySQL database using the MySQLi extension. Most of the PHP servers these days have this extension pre-installed. If the code does not work on your server, you need to check if you have this extension installed or not.
A new mysqli object has to be created and assigned to the connection variable $conn. This mysqli object function takes four arguments.
- Server Name
- Database Username
- Password for the Database User
- Database Name
In our code, all of these are stored in the variables $servername
, $username
, $password
and $dbname
respectively and then these variables are passed to the mysqli object function in the same order. Now, the $conn is the connection object which we’ll be used later on to run queries on this database. We’re also checking for any database connectivity errors using the connect_error
property of the $conn
variable.
1 2 3 4 5 6 7 8 9 |
$servername = "localhost"; $username = "root"; $password = ""; $dbname = "demo"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } |
Getting The Request Type
If you’re aware of the concept of CRUD APIs, you might be knowing that different request methods are used for different database operations. For all the beginners out there, let me review this concept quickly.
- GET Request – Used For Getting the retrieve the data from the database. (Select Query)
- POST Request – Used For Inserting the data into the database. (Insert Query)
- PUT Request – Used for Updating the records in the database. (Update Query)
- DELETE Request – Used for deleting the database records. (Delete Query)
In PHP, we can use the REQUEST_METHOD
property of the $_SERVER
superglobal variable to get the current request type.
$requestType = $_SERVER["REQUEST_METHOD"];
Now the $requestType
variables hold the request type as string i.e. GET or POST or PUT or DELETE.
Shifting Control To Required Block
Now, we’re using if conditions blocks to execute different CRUD operations as per the Request Method. There are 5 different if blocks and only one of which will be executed in one request and that completely depends upon the type of request. So, now let us discuss each of these operations individually.
CRUD Operations
Create or Insert Operation (C)
1 2 3 4 5 6 7 |
if($requestType == "POST"){ $val_1 = $_POST['val_1']; $val_2 = $_POST['val_2']; $insertQuery = "INSERT INTO demo (col_1, col_2) VALUES ('$val_1','$val_2');"; if ($conn->query($insertQuery) === TRUE) echo "New Record Added Successfully."; else echo "Error: " . $insertQuery . "<br>" . $conn->error; } |
This block of code is executed when the request method is POST. We’re extracting all the key-value pair data present in the request variable $_POST into the individual variables. Now, in the next line, we’re writing the SQL Insert Query and storing it into variable $insertQuery. The next line of code executes the query.
$conn->query($insertQuery)
executest the query and return true if the query has been executed successfully. We’re checking the same in an if-else block and printing the success or error output accordingly. That’s it for the create operation of the API.
Read & Read Only One (R)
Read and Read only-one both are similar, even the code for both are similar. To get all the records from the database table we need not any inputs from the request variables and we can simply execute the query to fetch all records. Both of these blocks execute when the request method is GET. But as in one request, we’ve to execute only one block, so we’re checking if the id parameter is set or not.
If the id parameter is not set that means the user requested for all records and if the id parameter is set then it means the user requested for one particular record corresponding to that id. We’re using the PHP’s isset() function to check so in the if the condition that is further AND
with request type condition.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
//Read if($requestType == "GET" AND !isset($_GET["id"])){ $selectQuery = "SELECT * FROM demo"; $result = $conn->query($selectQuery); $rows = array(); while($row = $result->fetch_assoc()) $rows[] = $row; header("Content-Type: application/json"); echo json_encode($rows); } //Read Only One if($requestType == "GET" AND isset($_GET["id"])){ $id = $_GET["id"]; $selectQuery = "SELECT * FROM demo WHERE ID = $id"; $result = $conn->query($selectQuery); $rows = array(); if ($result->num_rows > 0){ while($row = $result->fetch_assoc()) $rows[] = $row; header("Content-Type: application/json"); echo json_encode($rows); } else echo "Not Found."; } |
In both of these blocks, before printing the results, we’re also setting the Content-Type
header as application/json
and then further printing the data in JSON encoded format. It is a recommended practice as reading JSON data is easy for many front-end development tasks.
We’re using the PHP function json_encode() for converting the output array into JSON format. The select code is a bit longer than the other operations and the reason is more than just executing the query, we also have to store the data into variables to further showcase at the end.
First of all, we’re executing the query the same way as other operations and then we’re checking if the result contains any records using the num_rows
property of the $result
variable. Using the condition, $row = $result->fetch_assoc()
in the while loop will keep on executing the loop until all the records are not loop-through. Each data record is stored as an element of the $rows
array. This is the same array which we’ll present to the user in JSON format.
Update Operation (U)
In PHP, there is no default $_PUT request method. So, we’ve to get the request variables from the PHP standard input. It’s similar to reading a file located at the address php://input
. Now, the $str variable will contain the query string containing all the request parameters. We are using the PHP parse_str
function that will convert this query string into a key-value pair array $_PUT like the default request arrays $_POST and $_GET.
1 2 3 4 5 6 7 8 9 10 11 12 |
if($requestType == "PUT"){ $str = file_get_contents("php://input"); $_PUT = array(); parse_str($str, $_PUT); $val_1 = $_PUT['val_1']; $val_2 = $_PUT['val_2']; $id = $_PUT['id']; $updateQuery = "UPDATE demo SET col_1 = '$val_1', col_2 = '$val_2' WHERE id = $id;"; $updateQuery = "UPDATE demo SET gurmeet = '$gurmeet' WHERE id = $id;"; if ($conn->query($updateQuery) === TRUE) echo "Record Updated Successfully."; else echo "Error: " . $updateQuery . "<br>" . $conn->error; } |
Further, the query execution is similar to the create function. We’re storing the query in the $updateQuery variable and executing the query and checking if it successfully executed or not, exactly like the way we did in the insertion part.
Delete Operation (D)
Nothing different in the delete operation that the condition that this block will execute on the DELETE request method. The API request user also has to provide the id of the record to be deleted.
1 2 3 4 5 6 7 8 9 |
if($requestType == "DELETE"){ $str = file_get_contents("php://input"); $_DELETE = array(); parse_str($str, $_DELETE); $id = $_DELETE['id']; $deleteQuery = "DELETE FROM demo WHERE id = $id"; if ($conn->query($deleteQuery) === TRUE) echo "Record Deleted Successfully."; else echo "Error: " . $deleteQuery . "<br>" . $conn->error; } |
Similar to Update block, we’re getting the request input variable id after manually creating a request variables array $_DELETE
by first getting the query string and then converting it into an array of key-value pairs. The $deleteQuery
variable holds the delete query and executes it inside the if condition to further check if the record has been deleted successfully or not and accordingly it shows the output to the user.
Testing the CRUD API
Once, you’ve successfully written the code for this PHP MySQL CRUD API, now you can proceed further to test it. You can use any API Test tool like PostMan.
But just for you people, I’ve also created a Full GUI interface that you can use to send requests for all CRUD operations. This interface sends requests to the API using $.ajax()
function provided with jQuery.
This code show all the table data using DataTable and the options are provided to perform the various options.
Steps to Run This Code.
- Replace
http://localhost/smart/api.php
with your API URL. - Save the code in an HTML file and run it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 |
<!DOCTYPE html> <html> <head> <title>Testing the API With jQuery AJAX</title> <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.19/js/jquery.dataTables.min.js"></script> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.19/css/jquery.dataTables.css"> <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"> <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"> </head> <body class="container"> <div class="row"> <div class="col col-md-12"> <center class="m-3"> <button class="btn btn-success" onclick="addModal();">Add New <i class="fa fa-plus"></i></button> </center> </div> </div> <table id="dataTable" class="table table-hover"> <thead> <td>ID</td> <td>val_1</td><td>val_2</td> <td>Options</td> </thead> </table> <script> $(document).ready(function(){ $('#dataTable').DataTable({ "processing" : true, "ajax" : { "url" : "http://localhost/smart/api.php", dataSrc : '' }, "columns" : [ {"data" : "id"}, {"data" : "val_1"}, {"data" : "val_2"}, {"render": function (data, type, row, meta) { return '<button class="m-1 btn btn-secondary" onclick="editModal(' + row.id + ')">Edit <i class="fa fa-edit"></i></button>' + '<button class="m-1 btn btn-danger" onclick="deleteModal(' + row.id + ')">Delete <i class="fa fa-trash"></i></button>'; } } ] }); }); function editModal(id){ $('#editModal').modal('show'); $('#serial').attr("count",id); $('#editModal [name=val_1]').val(""); $('#editModal [name=val_1]').prop('disabled', true); $('#editModal [name=val_2]').val(""); $('#editModal [name=val_2]').prop('disabled', true); $.ajax({ type: 'GET', url: "http://localhost/smart/api.php", data: {id: id}, success: function(response){ data = response[0]; $('#editModal [name=val_1]').val(data.val_1); $('#editModal [name=val_1]').prop('disabled', false); $('#editModal [name=val_2]').val(data.val_2); $('#editModal [name=val_2]').prop('disabled', false); } }); } function deleteModal(id){ $('#deleteModal').modal('show'); $('#deleteSerial').attr("count",id); } function deleteField(id){ $.ajax({ type: 'DELETE', url: "http://localhost/smart/api.php", data: {id: id}, success: function(response){ $('#dataTable').DataTable().ajax.reload(); toast(response); } }); $('#deleteModal').modal('hide'); } function addModal(){ $('#addModal').modal('show'); type = $('#addModal [name=val_1]').attr('type'); if(type != 'checkbox' && type != 'radio'){ $('#addModal [name=val_1]').val(""); } type = $('#addModal [name=val_2]').attr('type'); if(type != 'checkbox' && type != 'radio'){ $('#addModal [name=val_2]').val(""); } } function addNew(){ formData = $("#addModal form").serializeArray(); $.ajax({ type: 'POST', url: "http://localhost/smart/api.php", data: formData, success: function(response){ $('#dataTable').DataTable().ajax.reload(); toast(response); } }); $('#addModal').modal('hide'); } function save(id){ formData = $("#editModal form").serializeArray(); formData.push({name: "id", value: id}); $.ajax({ type: 'PUT', url: "http://localhost/smart/api.php", data: formData, success: function(response){ $('#dataTable').DataTable().ajax.reload(); toast(response); } }); $('#editModal').modal('hide'); } function toast(message){ $('#toastMessage').text(message); $('#toast').toast('show'); } </script> <!-- Edit Modal --> <div class="modal fade" id="editModal" data-backdrop="static" tabindex="-1" role="dialog" aria-labelledby="staticBackdropLabel" aria-hidden="true"> <div class="modal-dialog" role="document"> <div class="modal-content"> <div class="modal-header"> <h5 class="modal-title" id="staticBackdropLabel">Edit Field</h5> <button type="button" class="close" data-dismiss="modal" aria-label="Close"> <span aria-hidden="true">×</span> </button> </div> <div class="modal-body"> <form action="" method="POST" enctype="multipart/form-data"> <!-- val_1 --> <div class="form-group"> <label>val_1</label> <input class="form-control" type="text" name="val_1" placeholder="val_1"> </div> <!-- val_2 --> <div class="form-group"> <label>val_2</label> <input class="form-control" type="text" name="val_2" placeholder="val_2"> </div> </form> </div> <div class="modal-footer"> <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button> <button id="serial" type="button" class="btn btn-primary" onclick="save(this.getAttribute('count'));">Save Changes</button> </div> </div> </div> </div> <!-- Insert Modal --> <div class="modal fade" id="addModal" data-backdrop="static" tabindex="-1" role="dialog" aria-labelledby="staticBackdropLabel" aria-hidden="true"> <div class="modal-dialog" role="document"> <div class="modal-content"> <div class="modal-header"> <h5 class="modal-title" id="staticBackdropLabel">Add New Field</h5> <button type="button" class="close" data-dismiss="modal" aria-label="Close"> <span aria-hidden="true">×</span> </button> </div> <div class="modal-body"> <form action="" method="POST" enctype="multipart/form-data"> <!-- val_1 --> <div class="form-group"> <label>val_1</label> <input class="form-control" type="text" name="val_1" placeholder="val_1"> </div> <!-- val_2 --> <div class="form-group"> <label>val_2</label> <input class="form-control" type="text" name="val_2" placeholder="val_2"> </div> </form> </div> <div class="modal-footer"> <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button> <button type="button" class="btn btn-primary" onclick="addNew();">Submit Data</button> </div> </div> </div> </div> <!-- Delete Modal --> <div class="modal fade" id="deleteModal" data-backdrop="static" tabindex="-1" role="dialog" aria-labelledby="staticBackdropLabel" aria-hidden="true"> <div class="modal-dialog" role="document"> <div class="modal-content"> <div class="modal-header"> <h5 class="modal-title" id="staticBackdropLabel">Delete Field</h5> <button type="button" class="close" data-dismiss="modal" aria-label="Close"> <span aria-hidden="true">×</span> </button> </div> <div class="modal-body"> <p>Are you sure that you want to delete this field?</p> </div> <div class="modal-footer"> <button type="button" class="btn btn-secondary" data-dismiss="modal">NO, TAKE ME BACK</button> <button id="deleteSerial" type="button" class="btn btn-primary" onclick="deleteField(this.getAttribute('count'));">YES, DELETE</button> </div> </div> </div> </div> <div id="toast" class="toast" role="toast" aria-live="assertive" aria-atomic="true" data-delay="5000" style="display: block; position: fixed; top: 10px; right: 10px; min-width: 200px;"> <div class="toast-header"> <i class="fa fa-paper-plane"></i> <strong class="mr-auto">Response</strong> <small class="text-muted">Just Now</small> <button type="button" class="ml-2 mb-1 close" data-dismiss="toast" aria-label="Close"> <span aria-hidden="true">×</span> </button> </div> <div class="toast-body" id="toastMessage"> </div> </div> </body> </html> |
Conclusion
This is a basic CRUD tutorial using plain PHP and MYSQL. In a production environment, APIs can be made much more powerful, robust and secure. Most of the time in the industry, we do not use plain PHP for creating CRUD APIs with MySQL or any other database environment. There are more powerful frameworks like Laravel and Codeignitor which can be used for better CRUD APIs.
I personally prefer to use Laravel for creating any kind of CRUD APIs. But if you’re a beginner you should start with Codeignitor and then further should move to Laravel as it is a bit more complex than Codeignitor. Both of these follow the MVC (Model View Controller) approach but with Codeignitor understanding this concept becomes much easier.
Related Articles.
- Add Text To Image In PHP (Using GD Library)
- 15 Best Code Editors For Windows, Mac & Linux
- What is Cron? All You Need To Know About Cron Jobs
- Top 10 Programming Languages
That’s it for the PHP MySQL CRUD tutorial. I hope as a beginner, you found this article useful. If so, do share it with others who might get benefits from this article as well. Feel free to ask any kind of questions related to this article in the comments section.
Also, don’t forget to Subscribe to WTMatter!