Show all rows in mysql table then give option to delete specific ones

Question

I want to have the ability to show all the entries in a database table and by each one give the user the ability to delete specific ones.

I am currently using a for each loop that loops through the database showcasing each entry.

$result = mysql_query("SELECT * FROM KeepScores");


$fields_num = mysql_num_fields($result);

echo "<table><tr>";
// printing table headers


echo "<td>Recent Posts</td>";

echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
echo "<tr>";

// $row is array... foreach( .. ) puts every element
// of $row to $cell variable
foreach($row as $cell)
    echo "<td>$cell</td>";

echo "</tr>\n";
 }

How would to add a delete button that appears by each one and removes the entry from the database table?

Answer 1

You can do it with forms:

//main.php

<?php $result = mysql_query("SELECT * FROM KeepScores"); ?>

<table>
  <tr>
    <td>Recent Posts</td>
  </tr>
  <?php while($row = mysql_fetch_array($result)) : ?>
  <tr>
    <td><?php echo $row['field1']; ?></td>
    <td><?php echo $row['field2']; ?></td>
    <!-- and so on -->
    <td>
      <form action="delete.php" method="post">
        <input type="hidden" name="delete_id" value="<?php echo $row['id']; ?>" />
        <input type="submit" value="Delete" />
      </form>
    </td>
  </tr>
  <?php endwhile; ?>
</table>

//delete.php:

<?php
if(isset($_POST['delete_id'] && !empty($_POST['delete_id']))) {
  $delete_id = mysql_real_escape_string($_POST['delete_id']);
  mysql_query("DELETE FROM KeepScores WHERE `id`=".$delete_id);
  header('Location: main.php');
}

---

Or you can do it with jQuery and AJAX:

//main.php

<?php $result = mysql_query("SELECT * FROM KeepScores"); ?>

<table>
  <tr>
    <td>Recent Posts</td>
  </tr>
  <?php while($row = mysql_fetch_array($result)) : ?>
  <tr id="<?php echo $row['id']; ?>">
    <td><?php echo $row['field1']; ?></td>
    <td><?php echo $row['field2']; ?></td>
    <!-- and so on -->
    <td>
      <button class="del_btn" rel="<?php echo $row['id']; ?>">Delete</button>
    </td>
  </tr>
  <?php endwhile; ?>
</table>

<script>
  $(document).ready(function(){
    $('.del_btn').click(function(){
       var del_id = $(this).attr('rel');
       $.post('delete.php', {delete_id:del_id}, function(data) {
          if(data == 'true') {
            $('#'+del_id).remove();
          } else {
            alert('Could not delete!');
          }
       });
    });
  });
</script>

//delete.php

<?php
    if(isset($_POST['delete_id'] && !empty($_POST['delete_id']))) {
      $delete_id = mysql_real_escape_string($_POST['delete_id']);
      $result = mysql_query("DELETE FROM KeepScores WHERE `id`=".$delete_id);
      if($result !== false) {
        echo 'true';
      }
    }

---

It's all untested and sure needs some adjustment for your specific project, but I think you get the idea and I hope it helps.

Next time, please post your schema if you ask stuff about database.

Answer 2

I thought I would improve on this a little bit by wrapping the delete post in a class and function. I was having the same problem. and this worked great for me. Thanks again @ Quasdunk

<?php

    // Class to hold the remove post function 
    class someClass{

        //Function for removing the post
        function removePost(){
            if(isset($_POST['delete_id']) && (!empty($_POST['delete_id']))) {
                $delete_id = mysql_real_escape_string($_POST['delete_id']);

                $result = mysql_query("DELETE FROM post WHERE post_id='".$delete_id."' AND post_member='" . $_SESSION['SESS_USER'] . "'");
                if($result !== false) {
                    echo 'true';
                }
            }
        }
    }

    if(isset($_SESSION['SESS_MEMBER_ID'])){
        $member = $_SESSION['SESS_USER'];
        $res = mysql_query("SELECT * FROM post WHERE post_member='$member' ORDER BY timestamp DESC") or die(mysql_error());
        $i = new someClass;
        while($row = mysql_fetch_array($res)){

            echo '<div style="width:100%;margin:0 auto;border-top:thin solid #000;">';
            echo '<div style="width:600px;margin:0 auto;padding:20px;">';

            echo $row['post_text'] . '<br>';
            $postID = $row['post_id'];
            echo '<div style="border-top:thin solid #000;padding:10px;margin-top:5px;background-color:#CCC;">';
            echo 'You posted this on:&nbsp;' . $row['post_date'] . '@' . $row['post_time'];
            echo '<div style="float:right;">
                    <form method="post" action="'. $i->removePost() .'">
                      <input type="hidden" name="delete_id" value="'.$row['post_id'].'" >
                      <input type="submit" value="Delete Post">
                    </form>
                  </div>';
            echo '</div>';

            echo '</div>';
            echo '</div>';
        }
    }
?>