How to delete row with jquery, ajax and PDO

Question

I have two tables in mysql DB, 'cars' and 'address' which they have relation with primary key and 'on delete restrict' in DB so I can't delete some address which have car and that's good. Addresss in DB have columns 'street', 'number' and 'city'. I want to delete every location which has no cars but I don't know where I'm wrong in code, it doesn't work.I checked all paths and all is ok. Any suggestion appreciate.

This is my modal delete button:

<a href="#" data-toggle="modal" data-target="#delete_loc<?php echo $value['id']?>" role="button" class="btn btn-danger btn-sm">
                <i class="fa fa-trash-o"></i> Delete
            </a>

                <div class="modal fade" id="delete_loc<?php echo $value['id']?>" tabindex="-1" role="dialog" aria-labelledby="mydelModalLabel" aria-hidden="true">
                    <div class="modal-dialog">
                        <div class="modal-content" style="text-align:center;">
                            <div class="modal-header">
                                <button type="button" class="close" data-dismiss="modal"><span aria-hidden="true">&times;</span><span class="sr-only">Close</span></button>
                                <h2 class="modal-title" id="mydelModalLabel" style="color:#a80b27;text-transform:uppercase;font-size:1.6rem;">Warning   !</h2>
                            </div>
                            <div class="modal-body">
                                <h5>Are you shure you want delete address ID <b><?php echo $value['id']?></b>?</h5>
                            </div>
                            <div class="modal-footer">
                            <input type="hidden" name="loc_id" id="loc_id" value="<?php echo $value['id']?>">
                                <a href="" role="button" class="btn btn-danger" id="del_loc">
                                    Yes, I'm shure!
                                </a>
                                <button type="button" class="btn btn-default" data-dismiss="modal">No</button>
                            </div>
                        </div>
                    </div>
                </div>

This is jquery and ajax:

    $(document).ready(function() {
      $('#del_loc').bind('click',function(e){
      e.preventDefault();
        var id = $('#loc_id').val();

        var data = 'loc_id='+id;

     $.ajax({
       type: "POST",
       url: "include/locations/delete.php",
       data: data,
       success: function(result){
        if(result == 1){
          window.location.replace("address.php?id=");
    }
       else{
         err_msg = JSON.parse(result);
       if(err_msg.nAddress){
        $('.panel-footer').append('<span class="help-block"  style="float:right;"><strong>'+err_msg.nAddress+'</strong></span>');
        }
      }
    }
   });
  });
});

This is delete.php:

    <?php

     require '../../config/init.php';
     require '../functions/locations.php';
     require '../services/xss.php';

     $loc_id = _e($_POST['loc_id']);

     $data = deleteLoc($loc_id);

       if(is_array($data)){
        print_r(1);
       }
       else{
          echo $data;
       }

  ?>

And this is function named locations.php:

function deleteLoc($loc_id){
    global $conn;

            try{
                $stmt = $conn->prepare('DELETE FROM address WHERE id=:id');
                $stmt->bindParam(':id',$loc_id);                
                $stmt->execute();
                $row[] = 'deleted';
                return $row;                
            }
            catch(PDOException $e){
                if(DEBUG === true){
                    header('Location:../../error/db_error.php?err_msg='.$e->getMessage());
                }
                else{
                    header('Location:../../error/db_error.php?err_msg');
                }
        }   
}

Try using Soft Delete instead of Hard Delete. in this way you will not get any conflict while deleting from MySql.Add a column in your table named deleted with datatype tinyint & onclick of delete button set deleted flag as 1. — Kunal Gadhia, Dec 14 '16 at 12:28
You say the you *want to delete every location which has no cars* but your SQL is simply `'DELETE FROM address WHERE id=:id'` which will only delete one row from the `address` table (assuming `id` is it's primary key). Nothing about you code suggests that it should *delete every location which has no cars* — Wesley Smith, Dec 14 '16 at 12:37
@KunalGadhia Im not sure what you are trying to say. How does using soft deletes relate to this question? — Wesley Smith, Dec 14 '16 at 12:40
@DelightedD0D My mistake because I didn't tell that I displayed all addresses on one page and at the end of each address I have modal delete button for each row/address which should delete that row/address. But this code doesn't work, nothing happens in DB when I try to delete some row except closing modal window. — Duško Oljača, Dec 14 '16 at 13:00
add `console.log(data);` right before the ajax call, what is the output in the console? — Wesley Smith, Dec 14 '16 at 13:33
Wait, how many of these modals do you have? This line concerns me `` are you creating a whole bunch of these modals in your html? If so, that is not valid, `id`s must be unique, you cant have more than one input all with the same id. — Wesley Smith, Dec 14 '16 at 13:36
If the above is correct, what you need to do instead is have only one modal and set it up so that clicking the button on the row updates the inputs in the modal with the values for that row then opens the modal, rather than having a bunch of different modals — Wesley Smith, Dec 14 '16 at 13:38

Hikmat Sijapati · Answer 1 · 2016-12-14T14:05:32.337

If you are getting the value loc_id value properly.I think following code will help you a lot. Let's try..

JavaScript

<script type="text/javascript">
     $(document).ready(function() {
      $('#del_loc').bind('click',function(e){
      e.preventDefault();
        var id = $('#loc_id').val();

        var data = 'loc_id='+id;
     //try here alert(id); For checking whether you are getting value or not
     $.ajax({
       type: "POST",
       url: "include/locations/delete.php",
       dataType:'JSON',
       data: data,
       success: function(result){
        var res=eval(result);
        if(res.success){
          window.location.replace("address.php?id=");
               }
       else
       {
       if(res.nAddress){
        $('.panel-footer').append('<span class="help-block"  style="float:right;"><strong>'+err_msg.nAddress+'</strong></span>');
        }
      }
    }
   });
  });
});
</script>

PHP delete.php

<?php

     require '../../config/init.php';
     require '../functions/locations.php';
     require '../services/xss.php';

     $loc_id = _e($_POST['loc_id']);

     $data = deleteLoc($loc_id);

       if(is_array($data)){
        echo json_encode(array('success'=>true));
       }
       else{
          echo json_encode($data);
       }
  ?>

No, what I'm saying is that I can see no reason to believe that calling `mysqli_real_escape_string` on the input would have any effect on this issue (unless the OP has apostrophes or such in their ID value, which is super unlikely) . — Wesley Smith, Dec 14 '16 at 13:45
Also, `PDO::prepare()` and `PDOStatement::execute()` which the OP is already using, will automatically take care of any needed escaping, so that cant be the issue here. See http://stackoverflow.com/a/3716402/1376624 — Wesley Smith, Dec 14 '16 at 13:53

How to delete row with jquery, ajax and PDO

1 Answers1