使用ajax删除后台的mysql数据库条目

Question

I have a query that generating a list of records:

<table class="simplet" width="640" border="0" cellspacing="0" cellpadding="0">
<thead>
<tr>
<th width="300" colspan="2">Product Name</th>
<th width="150">Brand</th>
<th width="100">Quantity</th>
<th width="60">Price</th>
<th width="30">Delete</th>
</tr>
</thead>
<tbody>
<tr><td colspan="6"><div id="status_text_list" /></td></tr>

[insert_php]

$current_user= wp_get_current_user();
$username= $current_user->user_login;

mysql_connect("localhost","xxxx","xxxx");//database connection
mysql_select_db(xxx");

$query= "SELECT * FROM wp_userdata WHERE username='$username'";
$result= mysql_query($query);
if (!$result) { die("Error db request: <br />". mysql_error()); }

while ($result_row = mysql_fetch_array($result, MYSQL_ASSOC)){ echo '<tr class="odd-row"><td width="30"></td><td>'.$result_row['product_name'].'</td><td>'.$result_row['product_brand'].'</td><td>'.$result_row['product_quantity'].'</td><td>'.$result_row['product_link'].'</td><td><a class="link-delete" href="delete.php?id='.$result_row['id'].'">X</a></td></tr>';
}


[/insert_php]

</tbody></table>

Here is my ajax jquery part:

$(document).ready(function(){
//on the click of the submit button 
$(".link-delete").click(function(){
 //get the form values


var current_user= wp_get_current_user();
var username= $current_user->user_login;

 //make the postdata
 var postData = 'username='+username;

 //call your input.php script in the background, when it returns it will call the success function if the request was successful or the error one if there was an issue (like a 404, 500 or any other error status)

 $.ajax({
    url : "delete.php",
    type: "POST",
    data : postData,
    success: function(data,status, xhr)
    {
        //if success then just output the text to the status div then clear the form inputs to prepare for new data
        $("#status_text_list").html(data);
    },
    error: function (jqXHR, status, errorThrown)
    {
        //if fail show error and server status
        $("#status_text_list").html('there was an error ' + errorThrown + ' with status ' + textStatus);
    }
});
}); });

And here is my delete.php:

mysql_connect("localhost","xxxx","xxxx");//database connection
mysql_select_db("xxxx");


if(isset($_REQUEST['id']) && is_numeric($_REQUEST['id']))
{
mysql_query("DELETE FROM wp_userdata WHERE id='".$_REQUEST['id']."'");
}

echo ("DATA DELETED SUCCESSFULLY");

This list of records has a delete "X" link at the end of each table row. When I click on the delete link, i need the deletion process to be done in background without leaving the page.

As of right now when I click delete it brings me to delete.php page. The records gets deleted though.

So how do I make the Ajax part to work?

Also is it possible to have a deleted row to disappear from the screen as well without leaving/refreshing the page?

Answer 1

There are multiple issues with this code.

1. You're mixing Javascript and PHP.

   var current_user= wp_get_current_user();
   var username= $current_user->user_login;
   

   You're attempting to create Javascript variables from PHP calls. This won't work. You might be looking for something akin to:

   <?php
   $current_user = wp_get_current_user();
   echo "var username = '" . $current_user->user_login . "'";
   ?>
   

2. You're getting re-directed because you are using a nodes with an href attribute set. The behavior by default, is to take the browser to that new page.

   Make the href value # and let the jQuery $.click() hook do the heavy lifting, as you have written it to do.

3. Your AJAX function calls delete.php, but with no $_GET attribute indicating the ID.

   When generating your table, give the a tag that calls the AJAX function a custom data-* attribute containing the ID to delete.

   <a class="link-delete" href="#" data-delete-id="' . $result_row['id'] . '">X</a>
   

   You can access this data-* variable by modifying your $.click() function with something like:

   $(".link-delete").click(function(){
    var deleteid = $(this).data("delete-id");
    //big snip
    $.ajax({
       url : "delete.php?id="+deleteid,
       //big snip
     });
   });
   

4. While outside the scope of the question, this code utilizes the deprecated PHP mysql_* extension. Please consider switching to MySQLi or PDO for your database code.

5. While also outside the scope of the question, it appears that you are vulnerable to SQL injection. This is a major security risk and is easily mitigated by utilizing prepared queries, available in both the extensions suggested as alternatives to the mysql_* extension.

---

Update: At this point, you're going to want to remove the row from the view. You can do this by using jQuery's convenient $.closest() directive. $(".link-delete").click(function(){ //big snip $.ajax({ //snip success: function(data,status, xhr) { //if success then just output the text to the status div then clear the form inputs to prepare for new data $("#status_text_list").html(data); $(this).closest("tr").remove(); // remove parent after successful deletion }, //snip again }); });