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();
?>

Answer 1

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'; ?>

Answer 2

A simple answer for you would be:

1. 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>

2. 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");
   }
   });
    });