How to Make Simple CRUD in PHP and MySQL

by Ahmed Khan  January 28, 2016

In the previous installment of this MySQL series, I provided an overview of table manipulation in MySQL.In this part of the tutorial series, I will teach you how to execute insert, update and delete queries. These queries can be executed in three different ways.

  1. Single Query Execution
  2. Multi Queries Execution
  3. Prepared Statement Execution

crud in mysql php

Single Query Execution

It executes a single query at a time.  Let’s start by inserting data into our previously created database. Create a new php file in the folder “practice” that we have previously created and name it crud.php. CRUD is the abbreviation for Create, Read, Update and Delete queries. Now in the new file, add this line at the top require_once ‘db_connection.php’ and create this new function:

The function takes a single parameter as your required query and executes it. Now, create a new file, index2.php in the same folder and at top add this line require_once ‘crud.php’. Now add these lines in your index2.php:

Then open your browser and locate to localhost/practice/index2.php and you will find success if you have typed the right query.

Multi Queries Execution

Using this method, you can execute more than one insert, select or update query. Let’s start by writing a function for Multi Insert or Update queries. Write this new function in your crud.php file

 

The function takes a single parameter as your required query and executes it. Now, call this function in your index2.php:

Now, open your browser and locate to localhost/practice/index2.php and you will find success if you have typed the right query.

Prepared Statements

Prepared statements are used to execute same query multiple times with high efficiency. Now write this new function in your crud.php file:

 

We will be selecting data using prepared statements. Let’s see how prepared statement works:

  1. First, you prepare your statement like INSERT INTO myguests(firstname, lastname, email, subject) VALUES (?,?,?,?). We left ? where we want to insert the values.
  2. Second, we will bind those values.The first parameter takes the data types of the value  and after that, the values. The data type argument can be of four types.
    1. i – integer
    2. s – string
    3. d – double
    4. b – blob
  3. We will execute it.

The function takes four parameters as your required value and executes it. Now, call this function in your index2.php:

 

Now, open your browser and locate to localhost/practice/index2.php and you will be successful if you have typed the right query.

Select Query Execution

Now, let us select the data from our database using single query execution. Write a new function in your crud.php written below:

 

The function first checks that the query is executed successfully. If not, it sends an error. Second, it checks whether number of rows is greater than 0 or not. If so, it sends “Zero results found”. Now, call this function in index2.php write the following code:

After this, open your browser and locate to localhost/practice/index2.php and you will find all the data store in your database in your index2.php page.

Update Query Using Prepared Statement

Let us write a new function in your crud.php file to execute update statement:

The update parameter takes three parameters. One is the column name which needs to be updated, second is the value which will be replaced by the previous value and third is the ID of the row in which it will be changed. Now, execute this function in index2.php :

When you’ve done that, open your browser and locate to localhost/practice/index2.php and you will be successful if there is no error.

Delete Query Using Prepared Statement

Let us write a new function in your crud.php file to execute a delete statement:

The delete parameter takes one parameter which will be the ID of the row which needs to be deleted from the table. Now, execute this function in index2.php:

After that, open your browser and locate to localhost/practice/index2.php and you will find success if there is no error.

Conclusion

In this tutorial, we have learned how to execute select, insert, update and delete queries using three different ways. In the next installment of this MySQL series, I will discuss MySQL data fetch clauses. Here is a short introduction and list of major topics in this MySQL series.

If you’ve got any questions about what we learned in this tutorial, feel free to comment below! Good luck! 🙂

Start Creating Web Apps on Managed Cloud Servers Now!

Easy Web App Deployment for Agencies, Developers and E-Commerce Industry

About Ahmed Khan

Ahmed was a PHP community expert at Cloudways - A Managed PHP Hosting Cloud Platform. He is a software engineer with extensive knowledge in PHP and SEO. He loves watching Game of Thrones is his free time. Follow Ahmed on Twitter to stay updated with his works. You can email him at ahmed.khan@cloudways.com

Stay Connected:

You Might Also Like...