Ajax HTML5 jQuery MySql PHP

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

PHP MySQL CRUD (Create, Read, Update, Delete) Operations using jQuery
Written by Yogesh Koli

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.

Note: Don’t 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 )

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

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

IMAGE : Users Table Structure

Users Table Structure

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 a head 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 addRecord, 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:

crud-basic-screen

Add New Record Popup:

Add New Record Modal 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 files is use 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:

Add New Test Record

Add New Test Record

Dummy Records List

Dummy Records List

Handling Read Event on Page Load:

Whenever we load the page we needs to have our existing records to be list on the page right? So go ahead ad 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:

Folder Structure

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 the all above code.

Happy coding..!!

About the author

Yogesh Koli

Yogesh Koli is a software engineer & a Blogger lives in India. He’s driven by an addiction to learning and a love for adventure. he has 5+ years of experience working with the front-end, back-end, web application development, and system design.

  • loplop loplop

    Excellent turorial many thanks!!!
    …but I’ve failed to put a select2 in update modal …
    Any idea?

    • Are you trying add new field ? can you explain more on your question ?

      • loplop loplop

        Hi Yogesh Koli!

        Thanks for your time. I’ve finaly found my mistake.
        First I place select2 code directly after the form, then I put it in the getuserdetail function like that :

        function (data, status) {
        // PARSE json data
        var objet = JSON.parse(data);
        // Assing existing values to the modal popup fields
        makeDetails(); ?>
        $(“#update #id_client”).select2({});

        // Open modal popup
        $(“#update_modal”).modal(“show”);
        $(“#update #id_client”).select2({});
        }

        But here is the good way :

        function (data, status) {
        // PARSE json data
        var objet = JSON.parse(data);
        // Assing existing values to the modal popup fields
        makeDetails(); ?>
        $(“#update #id_client”).select2({});

        // Open modal popup
        $(“#update_modal”).modal(“show”);
        }

        Again thanks for your great tuto (hope others great code tocome ;)) & time!

  • Vic Devic

    Very good thank you.
    Please add the tutorial 6 functions to insert, for example:
    Date Field: datepicker
    Field Select: placeholder (query id, name or another table)
    Field Option
    Field Check
    Upload Picture (database or path on the server)
    HTML editor field.

    With this I would be very full.

    Thanks.

    • Thanks Victor I am working on the same requirement, I will be posting new tutorials soon. stay tune!

  • Marcello

    And what about pagination ?

  • IRHAS NH

    error in download 🙁

    • Thanks for pointing out, I am going to get it resolve and let you know.

    • @irhasnh:disqus Download feature is working…!!

  • Afro

    Sir how to Sending image file on that form?

  • Leo

    Thanks for the great tutorial.
    I’ve set one of the fields im my database to ENUM and added some values, how do I change the form in “Add New Record” to show a dropdown menu with the values from the database.

    Thanks.

  • Matheus Schulz

    And what about pagination? [2]

  • benjamin

    Hello, at first I would like to say thank You for your tutorial. I have a question, how can I do the sorting of columns?

  • Edchelle de Leon

    Great tutorial but I have a question, when I tried to add new record it doesn’t add but when I try to delete one record and then add another one, I can. And also when I try to update, it doesn’t work. Can you please help me?

  • Siddharth Shah

    sir i can`t update details can you send your code to this email id:-siddharthshah607@gmail.com

  • Jery Ichigo Manuhutu

    i’ve tried your code. its working perfectly with display and add
    data. but when i trying to update or delete function it wont working.
    your delete query statement need an ‘id’ as input but i dont know where
    is it.

  • konduru cnu

    I added new column like ‘contact’ then .. i try insert data into first_name,last_name,email,contact and then data not insert to contact…?

  • konduru cnu

    I created new field ‘contact’, then Data is not inserted into contact?

  • Jery Ichigo Manuhutu

    I already managed to add and delete data but it wont update data. when update button pressed there’s no details appear in update modal. I want to ask where the GetUserDetail Function was called?. what i’m missing here? Thank you

  • Branco

    Hi,
    Thank you very much for your tutorial it’s was what I was looking for.
    I’m just learning to code and sometimes I got stuck!
    I noted that if the user ID is alphanumeric the script no longer works as it should do!?
    Is there a way to make it work with a VARCHAR row on the db table?
    Please can you help me?

    Thanks a lot

  • faizan

    they can update data using multiple primary key like composite key because ur grid using one primary key if i have multiple primary key they can update data…?
    please reply me

  • Fabiola Silva

    Hello, thank you for the tutorial. It’s all working, but when I add the user, it is added correctly but I’m not able to load the page. It get’s darker and I have to refresh. I mean, I can see the results page, but it’s faded, dark, and no commands work. Did I forget something?

  • DJ KN

    Thank you very much for your Tutorial.
    I have tested OK all but after I uploaded image cannot view with readRecords.php(for text data is OK,but for image have problem.
    please kindly refer to code below:
    if(mysql_num_rows($result) > 0)
    {
    $number = 1;
    $image=”;
    while($row = mysql_fetch_assoc($result))
    {
    //$image =”;
    $image =’ ‘;
    $data .= ‘
    ‘.$number.’
    ‘.$row[‘paragraph’].’
    ‘.$row[‘pro_type’].’
    ‘.$image.’

    Update
    Delete

    ‘;
    $number++;

    }
    }
    else
    {
    // records now found
    $data .= ‘Records not found!’;
    }

    $data .= ”;

    echo $data;

    I do hope you handle this error.

    Thank you.

  • Jayson Soriano

    Sir how can I validate the form elements without form tag can you give me a sample validation without form tag please

  • Thanks so much for this tutorial. While trying to test the code, i received the error below: “Fatal error: Uncaught Error: Call to undefined function mysql_connect() in C:xampphtdocseniwonderphp-mysql-crud-operationsajaxdb_connection.php:10 Stack trace: #0 C:xampphtdocseniwonderphp-mysql-crud-operationsajaxreadRecords.php(3): include() #1 {main} thrown in C:xampphtdocseniwonderphp-mysql-crud-operationsajaxdb_connection.php on line 10”

    Please can anyone help with this? Thanks once again

  • Adam PM

    thank you so much sir… now I know about CRUD with OOP style like with ur article