How to Delete MySQL Rows with Ajax & jQuery

Use AJAX techniques to remove a row of data without refreshing an entire Web page.

The jQuery JavaScript framework can remove a row of data from a table without the need to re-load an entire web page. Using AJAX techniques, jQuery executes a PHP script that removes the data from a MySQL database and updates a website to remove that row of data from the display as well.

Instructions

1. Embed jQuery into your page. You may find the latest version of jQuery from the jQuery site or by connecting to the Google API. Here is an example of how to embed the jQuery feed using the Google API:

   <script language="javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.1/jquery.min.js" type="text/javascript"></script>

2. Write the HTML to display your data. Attach an event item to each item of data. The sample code below places a two-item shopping list into an HTML table. Each item's data has been placed into three cells: The first cell contains the item name; the second contains the item price; and the third hosts an image of a delete button. The table row has been given a unique id so that the jQuery function will be able to identify the proper item to delete.

   <table>

   <tr id="1">

   <td>Milk</td><td>$3.99</td><td><button class="deleteitem"><img src="deletebutton.gif"></button></td>

   </tr>

   <tr id="2">

   <td>Bread</td><td>$1.99</td><td><button class="deleteitem"><img src="deletebutton.gif"></button></td>

   </tr>

   </table>

3. Code the jQuery function to handle the delete event call. Get the id of the parent element of the item that must be deleted. Use the jQuery AJAX method to pass the item of the deleted item to your deletion script. Consider the following sample jQuery function. This function listens for the deleteitem action, captures the id value of the nearest TR element and executes the your_delete_script.php file using the post method to pass the value of the id to the script. After the script executes, the jQuery remove method is used to remove the row from the screen.

   jQuery(document).ready(function(){

   $(".deleteitem").click(function(){

   var parent = $(this).closest('TR');

   var id = parent.attr('id');

   $.ajax({

   type: "POST",

   data: "id=" +id,

   URL: "your_delete_script.php",

   success: function(msg){

   $('#'+id).remove();

   }

   });

   });

   });

4. Write the PHP code. The PHP code must open the MySQL database and execute a MySQL delete query. This sample code checks to see if the id variable has been passed to the file. If it has, then the MySQL delete query executes and deletes the row.

   <?

   mysql_connect("localhost", "yourUserName", "yourPassword") or die(mysql_error());

   mysql_select_db("yourDatabaseName") or die(mysql_error());

   $id = $_POST[id];

   if (isset($id)) {

   $query = "DELETE FROM yourDatabase WHERE id = '$id'";

   mysql_query($query) or die('Error, insert query failed');

   }

   ?>

References

• jQuery: jQueryremove ()
• Google API: jQuery
• jQuery: jQuery.ajax()
• MySQL: Delete