Postgre multiple record delete query in the database with checkboxes using Ajax function returns nothing and doesn't work

Question

I have ajax function that retrieve the ids from a checked checkbox. Then pass the ids to the delete file as a condition to where clause using postgre query.

If the query succeed:

                    if($qry){
                      $note = "Success";
                      echo json_encode(array('notify'=>$note));
                     }` else...... `else{
                      $note = "Failed";
                      echo json_encode(array('notify'=>$note));
                     }

And then my ajax success function will receive this through:

  success: function(data)
  {
    if(data.notify=="Success"){
      console.log(data.notify);
    }
    else{
      console.log(data.notify);
    }
  }

But after I click the delete button, the console.log says nothing and no record has been deleted. How would I able to make this work right? Thanks a lot. Here are my codes.

interface.php

<?php

include ('connection.php');

$result = pg_query("SELECT h.hholdnumber,h.yr_residing,h.purok_number,h.brgy_name,f.f_id,f.fname,f.birthday,f.mname,f.age,f.lname,f.civilstatus,f.gender,f.civilstatus,f.job from house_hold as h,f_member as f where h.hholdnumber=f.hholdnumber");

while($row = pg_fetch_array($result))
{

$fid=$row['f_id'];

echo "<tr>";

echo "<td><center><input type=\"checkbox\"  class=\"check_id\"  name=\"check_id[]\" value=".$row['f_id']." /></center></td> ";
echo "<td><center>" . $row['hholdnumber'] . "</center></td>";
echo "<td><center>" . $row['brgy_name'] . "</center></td>";
echo "<td><center>" . $row['purok_number']."</center></td>";
echo "<td><center>" . $row['yr_residing'] . "</center></td>";
echo "<td><center>" . $row['fname'] . "</center></td>";
echo "<td><center>" . $row['lname'] . "</center></td>";
echo "<td><center>" . $row['mname'] . "</center></td>";
echo "<td><center>" . $row['gender'] . "</center></td>";
echo "<td><center>" . $row['birthday'] . "</center></td>";
echo "<td><center>" . $row['age'] . "</center></td>";
echo "<td><center>" . $row['civilstatus'] . "</center></td>";
echo "<td><center>" . $row['job'] . "</center></td>";


echo "</tr>";
}

?>

</tbody>

</table>
</form>

<button id="delete_button" name="delete_button" >Delete</button>

<script type="text/javascript">

$("#delete_button").click(function(){
var array_id= $('input[name="check_id[]"]:checked').map(function(){
return this.value;
}).get();

var postdata = {'f_id':array_id};

$.ajax({
  type: "POST",                                    
  url: 'mem_del.php',
  dataType: 'json',
  data: postdata,
  success: function(data)
  {
    if(data.notify=="Success"){
      console.log(data.notify);
    }
    else{
      console.log(data.notify);
    }
  }

});

});

</script>

mem_del.php

 <?php



  include ('connection.php');

  $f_id = $_POST['f_id'];

                    $qry = pg_query("DELETE FROM f_member WHERE f_id in($f_id)");

                     if($qry){
                      $note = "Success";
                      echo json_encode(array('notify'=>$note));
                     }
                     else{
                      $note = "Failed";
                      echo json_encode(array('notify'=>$note));
                     }

  ?>

1. Log the query-string and $f_id and you'll almost certainly find it's not a valid comma-separated list of IDs. Then stop building your queries by hand and use parameters too. — Richard Huxton

Chris Travers Chris Travers · Accepted Answer · 2013-11-09T02:55:54

As always, don't guess, check logs, debug, etc. If you need to log statements in PostgreSQL temporarily you can do something like:

ALTER database foo SET log_statements=all'

Then when you are done:

ALTER DATABASE foo RESET log_statements;

But the point is, see what is being passed, if it is being quoted before you send it, etc.

And also note that your current approach is prone to SQL injection. You want to go a parameterized route and this may mean being a little more creative in how you generate your query string.

Postgre multiple record delete query in the database with checkboxes using Ajax function returns nothing and doesn't work

1 Answers