Oct 22nd

Using jQuery and AJAX to select, insert and delete from your database using PHP/MySQL

This quick tutorial gives a nice, easy to understand example of using ajax through jquery to write to a database. perfect for beginners.

These days most websites can be broken down to basically a bunch of different nice ways of displaying and storing information in a database. Take a WordPress site for example. All the content is stored in the database and is thrown up on the screen using functions to display information in many beautiful ways.

One of the nicest ways of displaying that information is using AJAX and this tutorial is meant to show you the basics of how to do that. I’m going to take data entered into a form save it into the database and display it in a list that updates when the user adds or deletes from it. For simplicity sake, all the information we’re going to store is first name and last name. You can find a working example of it here.

To do this tutorial I’m going to assume you know how to use XHTML, PHP, MySQL and have a passing knowledge of JavaScript and jQuery and you know how to attach JavaScript files to a web page. I’m also assuming you know how to create a database and a new table and insert and select data from it.

1. Setting It Up

Before we start you’re going to need to download the latest version of jQuery from here. Also download the source code for this example here (Includes jQuery version 1.3.2).

Create a new database called “tests” and a table inside that called “test_ajax” with columns “id” (primary key), “fname”(varchar 255) and “lname”(varchar 255). Also, add a couple of dummy entries into the table so we can display them from the start.

2. The HTML

Ok lets start with the XHTML. Create a new php file called index.php and attach your jquery.js and “style.css” to it. Here’s the code for the form:

<div class="container">
    <form id="submit" method="post">
    <fieldset>
    <legend>Enter Information</legend>
    <label for="fname">Client First Name:</label>
    <input id="fname" name="fname" size="20" type="text">
    <label for="lname">Client Last Name:</label>
    <input id="lname" name="lname" size="20" type="text">
    <button> Add Client </button>
    </fieldset>
 </form>
 <div class="name_list">
 </div>
</div>

Its a straight forward html form with a blank div underneath with the class “list_names”. Inside this div is where we’re going to display our list.

3. Loading The List

Copy the “list_names.php” file into the same folder as “index.php”. To get the list to display paste this code into your index.php file at the bottom, right above the </body> tag:

<script type="text/javascript">
$(document).ready(function(){
    function loadList(){
        $.ajax({
            url: "load-list.php",
            cache: false,
            success : function(html){
                $(".name_list").html(html);
            }
        });
    }
    loadList();
});
</script>

This code is a function called loadList that uses jQuery to simplify AJAX calls. It requests the “load-list.php” file which performs the SQL query, compiles the data into html and then inserts it into the “list_names” div. You could copy the inside “load-list.php” into the “list_names” div and it would display the data in exactly the same way but we’re using AJAX to call it is so we can reload the list when we insert or delete from it.

Now take a look at the index page in your browser. You should see the form and a list of the dummy names you entered into the database earlier. The submit button and the delete links aren’t working yet as we still need to add the functions for them.

4. Saving To The Database

Lets add the save function. Copy “save.php” file and again paste it into the same folder as “index.php”. Copy this code into your “index.php” file right under the line where the code “loadList();” is:

$("form#submit").submit(function() {
    // we want to store the values from the form input box, then send via ajax below
    var fname = $('#fname').attr('value');
    var lname = $('#lname').attr('value');
    $.ajax({
        type: "POST",
        url: "save.php",
        data: "fname="+ fname +"& lname="+ lname,
        success: function(){
            loadList();
        }
    });
    return false;
});

This is function is called when the user clicks the submit button on the form. It then declares 2 variables “fname” and “lname” and gives them the values from the 2 input fields. After that it requests the “save.php” file. You can also see it sets the type of request to POST and passes the “fname” and “lname” variables with the request. On success it calls the “loadList” function which reloads the list to display the new data.

5. Deleting Data

All thats left now is to add the delete function. Copy the “delete.php” file and once again paste it to the same folder as your “index.php”. Then copy and paste this code into “index.php” right under the save function:

$(".delete_button").live("click", function(){
    //this deletes the row clicked on with an alert and then reloads the list
    var id = $(this).attr("id");
    var x=window.confirm("Are you sure you want to delete this item?")
    if (x){
        $.ajax({
            type: "POST",
            url: "delete.php",
            data: "id="+ id,
            success: function(){
                loadList();
            }
        });
    }
    return false;
});

This code is almost the same as the save function. It declares a variable called “id” which it sets to the id of the list item you click delete on. It then pops up an alert to make sure the user actually wants to delete that item. Then it requests the “delete.php” file, passes through the “id” variable and on success calls the “loadList” function to reaload the list without the deleted item.

And BAM! you have a JQuery/AJAX tool that updates your database and only refreshes one small element of the page. You can see a simpler version of this here. If you have any questions please email me or leave a comment below.

UPDATE Updating Data

If you want to add updating functionality try adding these two functions into your script:

$(".update_button").live("click", function(){
    //this loads the update form
    var id = $(this).attr("id");
    $.ajax({
        url: "update-form.php",
        data: "id="+ id,
        cache: false,
        success : function(html){
            $(".update_form").html(html);
       }
    });
    return false;
});

$("form#update").live("submit", function() {
    // we want to send via ajax and empty the html from the update_form element
    var fname = $('#fname_update').attr('value');
    var lname = $('#lname_update').attr('value');
    var id = $('#id_update').attr('value');
    $.ajax({
        type: "POST",
        url: "update.php",
        data: "fname="+ fname +"& lname="+ lname +"& id="+ id,
        success: function(){
            $(".update_form").empty();
            loadList();
        }
    });
    return false;
});

The first function calls update-form.php which loads an update form and the second one calles update.php which updates the database entry when the form is submitted. You’ll also need to add an update button to each item in the load-list.php file similar to the delete button. I added this code after the delete:

<a href="javascript:void(0);" id="update-<?php echo $result['id']; ?>">Update This</a></div>

I’ve included everything I added in a new file package that you can download here: first-ajax-updates

Tags: AJAX/JQuery/MySQL/php/Tutorial
Posted in Blog by Abban |

27 Responses to “Using jQuery and AJAX to select, insert and delete from your database using PHP/MySQL”

Oct 22nd

John says:

very nice example of CRUD (create, read, update, destroy) command. A very good introduction.
Nov 4th

Barton says:

I like the solution.
How could the Tut be altered to UPDATE a Table Row of first / last name, i.e. each row pair to be updatable? Then the table row to be updated in the Table.

Thanks,

Barton.
Nov 4th

Abban says:

The easiest way to add update functionality is to just add a couple of new functions. One to load an update form and one to perform the change in the database. I’ll add a bit to the end of the tutorial to better explain what I mean.
Nov 5th

Barton says:

Thanks! Your an absolute star….
Nov 6th

vance says:

In IE7, I had to add type=”submit” on the form button to get it to work. great work!
Dec 11th

gian says:

It is ok in firefox but doesn’t work in IE8
any ideas?
Dec 11th

gian says:

I mean, “update” doesn’t work…
Dec 11th

Abban says:

It’s probably the button in the form in update-form.php. Try changing it an input with type=”submit”
Dec 11th

gian says:

no, doesn’t still work… (
Dec 13th

Abban says:

Can you zip up your code and email it to me and I’ll take a look? My email is: abban [at] brightsky [dot] ie
Jan 1st

Duane Charles says:

Nice clean and simple. Most tutorials are throwing the whole kitchen sink at me. Show me a simple version first, then throw in the technical stuff later.

Thanks for the simple version : )
Nice!
Jan 7th

Anwar Hossain says:

Very nice complete solution, Thanks…………..
Jan 12th

vanas says:

@gian : missing “>” on line 23, before “”
Jan 12th

Abban says:

Ah I see it, there’s a small bug in the update-form.php page on line 23. You just need to add the > at the end. Cheers @vanas
Feb 5th

bolven says:

Very nice, simple and useful.
Feb 7th

aleks says:

i think there is lacking of the code on UPDATE (updating data) there must have a type:’POST’,:

$(“.update_button”).live(“click”, function(){
//this loads the update form
var id = $(this).attr(“id”);
$.ajax({
url: “update-form.php”,
type:”POST”,
data: “id=”+ id,
cache: false,
success : function(html){
$(“.update_form”).html(html);
}
});
return false;
});

lacking of type may cause the index ‘id’ undefined..
Mar 22nd

Jimson says:

hEY I HAD TRIED IN MANY WAYS BUT THIS IS NOT UPDATING VALUES …
CAN ANY ONE SOLVE THIS …
EAGERLY WAITING FOR AN SAVIOUR TO SOLVE THIS
Apr 2nd

Jimson says:

is any one here to solve this problem?????
i am awaiting for your responce..?????
Apr 20th

Maksudul Haque says:

This is useful for any programmer. Thanks to all….of you.
Apr 28th

liton babu says:

nice………but not updated
Jun 16th

Jubayer says:

Thanks for your code….This will be really helpful…
Jun 17th

fayjaa says:

Thank you , your code helpful for me. but
i have a probram on thai language (on event load thai language page). you can help me?
Jun 24th

Faisal says:

Thanks Alot . its great for me
Jul 14th

Pon says:

Can use method insert,update,delete in one php page , not use add.php , delete.php,update.php ….may be url:”method.php->add”…..
Jul 22nd

mr tech says:

code not updated
Jul 26th

Faron Domenic says:

Very nice and thank you so much for this. This sure save so much of my time and I was able to move on on core building rather than developing such query. THANKS!
Jul 27th

firman says:

thank’s…….very1000x helpful…..