PHP MySQL CRUD Tutorial For Beginners

PHP MySQL CRUD Tutorial For Beginners

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.

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.

  1. Insert A Record Into Database
  2. Select All Records From Database
  3. Select Only One Record According To Condition
  4. Update A Record According To Condition
  5. Delete A Record According To Condition

PHP Code.

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: *");

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.

  1. Server Name
  2. Database Username
  3. Password for the Database User
  4. 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.

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)

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.

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.

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.

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.

API TEST GUI

Steps to Run This Code.

  1. Replace http://localhost/smart/api.php with your API URL.
  2. Save the code in an HTML file and run it.

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.

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!

Subscribe To WTMatter!

Receive updates of our latest articles via email. Enter your email address below to get started.

Leave a Reply

Your email address will not be published. Required fields are marked *