Delete data from mysql using php & jquery

Question

I am using the following PHP code to delete my data from a mysql database. It's working for me, but it's redirecting me another page named delete_ac.php. I want to keep it in the same page (index.php), and if possible I want to use jquery so that data is deleted without redirecting the page.

index.php

<?php
    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
?>

<tr>
    <td bgcolor="#FFFFFF" style="border:1px solid black" >
        <?php echo $row[0].'&nbsp'; ?>
    </td>
    <td bgcolor="#FFFFFF" style="border:1px solid black">
        <?php echo $row[1]; ?>
    </td>
    <td bgcolor="#FFFFFF">
        <a href="delete_ac.php?id=<?php echo $row[0]; ?>">delete</a>
    </td>
</tr>

<?php
    }
?>

<?php include 'footer.php'; ?>

delete.ac.php

<?php
    mysql_connect("localhost", "root", "") or
    die("Could not connect: " . mysql_error());
    mysql_select_db("dbname"); 
    $tbl_name="tablename"; // Table name 

    // get value of id that sent from address bar 
    $id=$_GET['id'];

    // Delete data in mysql from row that has this id 
    $sql="DELETE FROM $tbl_name WHERE id='$id'";
    $result=mysql_query($sql);

    // if successfully deleted
    if($result){
        echo "Deleted Successfully";
        echo "<BR>";
        echo "<a href='index.php'>Back to main page</a>";
    }   
    else {
        echo 'Error';
    }
?>

<?php
    // close connection 
    mysql_close();
?>

You need to submit the ID, perhaps as a JSON string, to delete.ac.php. See this other question for a similar use of $.ajax — Andy Hoffner
– Andy Hoffner, Commented Aug 12, 2015 at 18:21
If you can, you should stop using mysql_* functions. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and consider using PDO, it's really not hard. — Jay Blanchard
– Jay Blanchard, Commented Aug 12, 2015 at 18:41

Njuguna Mureithi · Accepted Answer · 2015-08-12 19:26:32Z

0

A simple answer for you would be:

Include a delete class in your anchor.

<td bgcolor="#FFFFFF"><a class="delete" href="delete_ac.php?id=<?php echo $row[0]; ?>">delete</a></td>

Bind its click to jquery

$('a.delete').on('click', function(e){
    var href = $(this).attr('href');
 $.ajax({

'url' : href,
'type' : 'GET',
'success' : function(data) {              
    alert('Data: '+data);
},
'error' : function(request,error)
{
    alert("Error");
}
});
 });

answered Aug 12, 2015 at 19:26

Njuguna Mureithi

3,8961 gold badge25 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Onimusha Over a year ago

Good answer but no need for .on as the table I assume is not loaded dynamically. I think $('a.delete').click(function(){ ... }); should suffice

Parker · Accepted Answer · 2015-08-12 19:38:09Z

As others have mentioned, look into SQL prepared statements.

To answer your question, you'd use the following for the ajax call

 $.ajax({
  method: "POST",
  url: "delete.ac.php",
  data: { id: PUT_YOUR_ID_VALUE}
});

and change $id=$_GET['id']; to $id=$_POST['id']; in delete.ac.php

Here's a new index.php that uses PDO and removes the need for a second, separate page. It's not perfect, and there's some stuff that can still be cleaned, but this is how I'd change it (while trying to keep it as close as possible to your posted code)

<?php
    //Run the following code if and only if a POST
    //request is made to this page.
    if ($_SERVER['REQUEST_METHOD'] == 'POST') {
        mysql_connect("localhost", "root", "") or
        die("Could not connect: " . mysql_error());
        mysql_select_db("dbname"); 

        //This is really important. This is a predefined statement
        //the code you had is at risk for SQL injection. Please read up on this
        $sql = "DELETE FROM :TABLENAME WHERE id = :ID";
        $stmt = $pdo->prepare($sql);
        $stmt->bindParam(':ID', $_POST['id']);
        $stmt->bindParam(':TABLENAME', "tablename"); //put tablename here   
        $stmt->execute();
}
    // This ends the 'POST' code
    // 
    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
?>

<tr>
    <td bgcolor="#FFFFFF" style="border:1px solid black" >
        <?php echo $row[0].'&nbsp'; ?>
    </td>
    <td bgcolor="#FFFFFF" style="border:1px solid black">
        <?php echo $row[1]; ?>
    </td>
    <td bgcolor="#FFFFFF">
        <a href="javascript:delete(<?php echo $row[0]; ?>);">delete</a>
    </td>
</tr>

<?php
    }
?>

<script>
//We're creating a javascript function that will be called
//when the user clicks 'delete'. It takes the ID and passes it
//in the AJAX call

 function delete(id){
 $.ajax({
      method: "POST",
      url: "index.php",
      data: { id: id}
 });
}
</script>

<?php include 'footer.php'; ?>

Where do i put this script? Is it in index.php ? in delete_ac.php ? or in footer.php. Please note that my jquery google link is in my footer.php file. Please dont mind i am newbie in php & ajax.
I updated with full code for your index.php, This should work, but let me know if there are issues, as PHP isn't really my forte, and I'm a little rusty :)
I want to downvote this answer for so many reasons but I don't downvote genuine efforts. @Nazmul I think you should continue learning php, mysql and ajax calls separately first before combining them all. You're jumping ahead in my opinion
@Onimusha, feel free to edit or call me out, as I said, I did this in a rush. Nazmul, you should use this code to help, but please read about PDO and ajax (I linked to post in my answer), it's important to learn this yourself, but hopefully this answer can help guide you. I'm sure this code can be optimized, or done better, but I don't have time to deal with everything in one answer
You're connecting using mysql_*, You're then performing tasks on a class that doesn't yet exist, $stmt. The full code is in a condition if page was posted to as request method so by default nothing will load. You're advising to use pdo yet the example still uses another mysql query to load the list. It's a very wrong answer on many levels and should be removed I'm afraid

Collectives™ on Stack Overflow

Delete data from mysql using php & jquery

2 Answers 2

1 Comment

9 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

9 Comments

Linked

Related