Delete records from database with an Ajax request

Question

hi all friends

I am using php / mysql and protype.js to delete record from a table. The problem is that the record in the database is not deleted.

index.php:

       <a href="javascript: deleteId('<?php echo $studentVo->id?>')">Delete</a></td>

Script is

   function deleteId(id)
   {
       alert("ID : "+id);
       new Ajax.Request('delete.php?action=Delete&id='+id,{method:'post'});
       $(id).remove(); // because <tr id='".$row[id]."'> :)

   }

delete.php

     <?php
      /* Database connection */
      include('configuration.php');
      echo "hello,...";
      if(isset($_POST['id'])){
          $ID = $_POST['id'];
          $sql = 'DELETE FROM student where id="'.$ID.'"';
          mysql_query($sql);
      } 
      else { echo '0'; }
     ?>

alert("ID : "+id); is working properly but the code after that is not.

Answer 1

You are using a GET request, from JS :

{method:'get'}

And your PHP code uses data he thinks arrives as POST :

$ID = $_POST['id'];

You should use the same method on both sides.

(As you are modifying / deleting data, you should probably use POST)

As a sidenote, you should definitly escape/protected/check the data you are using in the SQL query, to avoid SQL injections, using, for instance, intval as you are working with an integer ; you'd use mysql_real_escape_string if you were working with a string.

Another way would be to stop using the old mysql extension, and start using mysli or PDO, which means you could use prepared statements (mysqli, pdo)

EDIT after the comment : you also, now that the request is made in POST, need to change the way parameters are passed : they should not be passed in the URL anymore.

I suppose that something like this should work :

var myAjax = new Ajax.Request(
  'delete.php',
  {
    method: 'post',
    parameters: {action: id}
  });

Or you could also use something like this, building the parameters string yourself :

var myAjax = new Ajax.Request(
  'delete.php',
  {
    method: 'post',
    parameters: 'action=' + id
  });

(Not tested, so you might have to change a few things ;-) )

For more informations, take a look at Ajax.Request and Ajax options :-)

Answer 2

It seems like your ajax request is using GET but your delete script is trying to retrieve the id via POST. Try setting {method:'get'} to {method:'post'}.

Answer 3

Change delete.php like this:

<?php
      /* Database connection */
      include('configuration.php');
      echo "hello,...";
      if(isset($_GET['id'])){
          $ID = $_GET['id'];
          $sql = 'DELETE FROM student where id="'.$ID.'"';
          mysql_query($sql);
      } 
      else { echo '0'; }
     ?>

Or alternatively you cold use $_REQUEST instead of $_GET (or $_POST in the first example) as it aggregates $_GET and $_POST arrays.