Deleting bootstrap alerts from database mysql php

Rodi Chamii asked
javascript php jquery mysql
via

So i have a notification page created using bootstrap alerts. here’s a snippet, each notification is an echo from a row of the database

<div class="container" >

<?php 
$fetch = $conn->query("SELECT * FROM notifications"); 

while($row = $fetch->fetch_assoc()) {
  $id = ''.$row["id"].'';
  $notification = ''.$row["notifications"].'';

?>

<div class="alert alert-warning alert-dismissable ">
  <a href="#" class="close" data-dismiss="alert" aria-label="close" id="close">
    <form action='deletenotifications.php' method='post' id='myform' >
        <input type='hidden' name='id' id='id' value="<?php echo $id ?>" />
        <span type="submit">&times;</span>
    </form></a>
  <?php echo $notification; ?>
</div>


<?php } ?> 

</div>

The user when pressing X needs to delete the notification, thus from the database too, so a hidden form containing the id of the alert to be sent to action deletenotication.php using jquery AJAX method

<script type="text/javascript">
 $('#close').click(function(){
   $.post( 
     $('#myform').attr('action'),
     $('#myform :input').serializeArray(),
   );
});
</script>

and here is the deletenotification.php snippet

$id = $_POST['id']; 


$sqlf = $conn->query("SELECT * from notifications");

while($rown = $sqlf->fetch_assoc()){
    $idbase = ''.$rown['id'].'';

    if($id == $idbase){
        $sql = $conn->query("DELETE FROM notifications WHERE id=$id"); 

    }

}

it is deleting from the database but only if the alerts are closed in order, and only one alert is deleted, in order to delete the successive one, the page need to be refreshed.

closing the alerts 2, 3 and 4 wont delete the rows unless notification 1 is deleted,

I need if the user close ANY random alert to be deleted, and not in order

Thank you!


Answer
via

You don’t need to iterate through all of the results just to delete a specific row by its ID. That costs you two queries instead of one, and the first one could potentially be costly if there are a ton of notifications in the database. Instead, you can just delete by the ID, like so.

if ( ! empty( $_POST['id'] ) && (int) $_POST['id'] ) {
    $id  = (int) $_POST['id']; 
    $conn->query("DELETE FROM notifications WHERE id=$id");
}

In the example above, I am casting the ID to an integer, for safety. But really, you should look at the database class you’re using there, and instead use a prepared statement.


Aside: I noticed a couple of lines like this one with two single quotes. You don’t need all of those. They are only helpful if you’re concatenating. Or, if you were you defining a string literal.

$id = ''.$row["id"].'';

Instead, just assign it the value of the ID.

$id = $row['id'];

It’s also a good idea to use and verify a CSRF token before responding to a request to delete a row from the database; i.e., make sure this request is a legitimate one. Maybe you’re already doing this elsewhere, but I wanted to caution you in case.

See: https://en.wikipedia.org/wiki/Cross-site_request_forgery

Share This
Posted in: