This website uses cookies

Our website, platform and/or any sub domains use cookies to understand how you use our services, and to improve both your experience and our marketing relevance.

CloudwaysCDN — a powerful solution that offers superior performance and satisfied global audience for your business. Read More

How to Make Simple CRUD in PHP and MySQL

Updated on  28th January

5 Min Read
Reading Time: 5 minutes

In the previous installment of this MySQL series, I provided an overview of table manipulation in MySQLIn this part of the tutorial series, I will teach you how to create CRUD in PHP and MySQL by executing insert, update and delete queries. These queries can be executed in the following ways.

Creating CRUD in PHP and MySQL
1. Single Query Execution
2. Multi Queries Execution
3. CRUD in PHP and MySQL With Prepared Statements
4. Select Query Execution
5. Update Query Using Prepared Statement
6. Delete Query Using Prepared Statement
7. Conclusion

simple crud in php and mysql

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.

CRUD in PHP and MySQL With 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 MySQL 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 CRUD in PHP and MySQL 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! 🙂

Share your opinion in the comment section. COMMENT NOW

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

Create PHP websites without the worry of server management.

Deploy your PHP app on optimized PHP hosting servers.

Get Our Newsletter
Be the first to get the latest updates and tutorials.

THERE’S MORE TO READ.