PHP MySQL CRUD (Create, Read, Update, Delete) Operations using jQuery

PHP MYSQL CRUD

PHP MySQL CRUD is all about INSERT, UPDATE, DELETE and SELECT SQL queries using PHP, it will help beginners to know about PHP and MySQL operations.

Live Demo

Note: Do not use this script into your live projects, the motive of the script is to just provide basic ideas to the beginners for learning, if you want to use this feature in the live projects then you should focus on following points:

• Prevent your script from SQL Injections
• Use PDO extension or other DBAL/ORM’s like Laravel Eloquent or Doctrine.

Tutorial Features:

1. Insert records into MySQL Database
2. Read the records from Database and list
3. Update the record
4. Delete the record.

Technologies Used:

1. HTML
2. PHP with MySQL
3. jQuery
4. Bootstrap
5. CSS
6. JSON

Before starting let’s download basic lib files needed: (if you already having this lib files you can use your existing files)

• Download jQuery JS file from – http://jquery.com/download/
• Download Bootstrap from – http://getbootstrap.com/getting-started/#download (Use basic Compiled and minified files don’t go for Source code for now)

Let’s start of creating our demo web application to learn CRUD operations, first thing we are going see is to create a database and tables required. (If you have your database ready in MySQL, go ahead and create tables using following SQL code) I am assuming that you have database created and ready to use.

Create MySQL Database and required Table:

`users table`

CREATE TABLE  `test`.`users` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`first_name` VARCHAR( 40 ) NOT NULL ,
`last_name` VARCHAR( 40 ) NOT NULL ,
`email` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM ;

Take a note: `test.users` – where `test` is the database name and `users` is a table name.

Basic Structure of Index.php:

Create `index.php` file and include basic files for jQuery and Bootstrap as showing below:

So we have our basic file ready to go, now let’s add button to open add new record popup along with basic formatting like to have page heading and record container, refer following code:

The above code is actually part of our index.php file, if you look at it, you will notice we have application heading and `Add New Record` button which refers to add_new_record_modal modal popup. we also have `records_content` div, this div is going to display the data rendering from Ajax, we are going to see that next.

Now we need to add modal popup, we are using bootstrap modal popups here, go ahead and use following code to include popup in the index.php page.

If you’re not familiar with Bootstrap no worries you just need to copy this code later on you can read about it, so now go ahead and add below modal to you index.php page.

If you notice in the above code, we have popup called add_new_record_modal which includes different input field in the ‘modal-body’ tag. so, we have first name, last name and email address here.

modal-footer – that’s the important part of the popup to call action such as add Record, we have button with JS method call with onclick event.

let’s save index.page page and try to load on browser.

Index.php Page:

Add New Record Popup:

Next Step is to create Add New Record and Read Records feature.

We have seen the basic setup of our application now we are going to look at the CREATE and READ operation. That is also called as INSERT and SELECT operation in MySQL, basically to create new record in the table.

It’s time to code jQuery plus JavaScript:

Handling Add New Records Action:

Let’s create our custom JS file called script.js file under JS folder and add following code:

js/script.js

If you notice in the above code, we have `addRecord()` function which is doing following operation: Get the Values from the input fields and send it to addRecord.php file using Ajax call. After that it’s closing the popup and reading records using `readRecords()` that is next function to it.

PHP Script to Add New Record into the database:

Create `ajax/addRecord.php` file and use following code:

Process: Accept the values from the POST variablea and insert record into the database.

PHP Script to Read existing Records from the Database:

Create `ajax/readRecord.php` file and use following code:

In both files above I have included the `db_connection.php` file using php `include()` function, this file is used to define our database connection string. It is better practice to add repetitive code in the separate file, let’s create the file.

PHP to MysqL Database Connection Script:

Create `ajax/db_connection.php` file.

Note: Make change in the connection file according to your server configuration.  (Host, Username, Password and Database name)

Test the application: try to add new record, you will need to be able to add the record and have it listed, have a look on below screen shoot I have added few dummy records:

Fill the fields and click on Add Record button:

Handling Read Event on Page Load:

Whenever we load the page, we need to have our existing records to be list on the page, right? So go ahead and added following code in to the `script.js` file and try to page again.

Are you Good so far?

Handling Delete Action:

So now have we have our `CREATE` and `READ` feature is ready and tested, let’s go to next step and add `DELETE` and `UPDATE` feature as well.

Add `DeleteUser()` function in the custom scrip.js file:

PHP Script to Delete existing record from the database:

Create `ajax/deleteUser.php` file and add following code:

UPDATE Feature

How does it work?

Let me explain in the step:

1. User clicks on update button from the list
2. Popup open up with the existing details field in
3. User can click on Save Changes button to update and save the records.

Get back to the code, so add required modal popup to update the record.

Go ahead and use the following html code and add to the index.php page, next to the existing modal popup.

Handling Existing User Details API:

Add `getUserDetails()` function in to the `script.js` file:

This function is used to read the existing user details and fill input fields from modal popup and open it up.

PHP Script to Fetch existing user details from the database:

Create `ajax/readUserDetails.php` file:

Update Action:

Add another JS function called `UpdateUserDetails()` in to the `script.js` file:

PHP Script to Update Existing Record:

Create `ajax/updateUserDetails.php` file:

Folder Structure:

Complete Source code of `script.js` file:

Complete Source code of `index.php` file:

Finally, we are done with the CREATE, READ, UPDATE and DELETE feature, test it on your system and let me know if you get any issue with all above code.

Happy coding..!!