PHP MySQL Removing row from database through a delete button attached to an "object"

Question

[FIXED] I fixed my problem. As suggested by Fred -ii- I visited this Q&A on SO. I saw he used checkboxes which to seems much more useful than buttons since you can take multiple objects out at a time. Also he attached the id of the object to the button like so just like Subin suggested as well.

<form action="" method='POST'> <input style='display:block; margin:0 auto;'type='submit' name='delete_button' value='<?php echo row['id']; ?>'/> </form>

Here is the fixed code. I am now able to delete the boxes individually. Thank you for all the suggestions. I am now using mysqli as well.

$query = mysqli_query($connect, "SELECT * FROM shouts ORDER BY `id` DESC LIMIT 5") or die('<p id="formbox" style="text-align:center;">There was an unexpected error grabbing news from the database</p>');

    while ($row = mysqli_fetch_array($query)) {
        $title2 = $row['title'];
        $post2 = $row['post'];
        $date2 = $row['date'];
        $author = $row['author'];

        echo '<div class="news-title"><b style="float:left;">'.$author.'</b><b style="text-align:center; color:green;">'.$title2.'</b><a href="">'.$date2.'</a></div>';
        echo '<div class="news-body">'.$post2.'</div>';
        if (isset($_SESSION['username']) && ($_SESSION['level'] >= 3 || $_SESSION['group'] == 'Admin')) {
            ?>
            <form action="" method='POST'>
            <input style='display:block; margin:0 auto;'type='submit' name='delete_button[]' value="<?php echo $row['id']; ?>"/>
            </form>
            <?php
        }
        echo '<br>';
    }
    if(isset($_POST['delete_button'])) { 
    $boxid = $_POST['delete_button'];
    for($i=0;$i<count($boxid);$i++){
        $del_id = $boxid[$i];
        mysqli_query($connect, "DELETE FROM `shoutbox`.`shouts` WHERE `shouts`.`id` = '$del_id'") or die('<p id="formbox" style="text-align:center;">There was an unexpected error deleting the post from the database</p>');
        }
    }

[QUESTION] So currently I've been working on a website just for my learning purposes and Google thus far has been good help. Although, I can't seem to figure this problem out. I have a "news feed"

https://i.stack.imgur.com/oieXJ.png

it doesn't let me post images but its the only visual idea that I can give. Unless you want to visit my main page http://yuriah.net the news feed is just there without the delete buttons of course.

I want to add a "delete" button to each post so that I can delete each of them individually when I want. I am having problems with the current code im using. It deletes all of them from the database but I only want to delete the "post" that I click delete. Here is my source:

$query = mysql_query("SELECT * FROM shouts ORDER BY `id` DESC LIMIT 5") or die('<p id="formbox" style="text-align:center;">There was an unexpected error grabbing news from the database</p>');

    while ($row = mysql_fetch_array($query)) {
        $title2 = $row['title'];
        $post2 = $row['post'];
        $date2 = $row['date'];
        $author = $row['author'];
        $boxid = $row['id'];

        echo '<div class="news-title"><b style="float:left;">'.$author.'</b><b style="text-align:center; color:green;">'.$title2.'</b><a href="">'.$date2.'</a></div>';
        echo '<div class="news-body">'.$post2.'</div>';
        if ($_SESSION['level'] > 3 || $_SESSION['group'] == 'Admin' || $_SESSION['group'] == 'Owner') {
            ?>
            <form action="" method='post'>
            <input style='display:block; margin:0 auto;'type='submit' name='delete_button' value='Delete' />
            </form>
            <?php
        if(isset($_POST['delete_button'])) { 
        $con = mysql_query("DELETE FROM `shoutbox`.`shouts` WHERE `shouts`.`id` = '$boxid'") or die('<p id="formbox" style="text-align:center;">There was an unexpected error deleting the post from the database</p>');
        header('refresh:1; url=/');
        mysql_close($con);
    }
        }
        echo '<br>';
    }

I am fairly new at PHP HTML CSS MYSQL etc. I am open to all suggestions and comments. Any help will be appreciated thank you.

It's almost 2014. Why are you still using depreciated **mysql_*** functions ? Use **PDO** or **MySQLi** — Subin, Dec 25 '13 at 15:29
Sidenote: Use `Header("Location: xxxxxxxx");` instead of `header('refresh...` And see [**this Q&A on SO**](http://stackoverflow.com/questions/14475096/delete-multiple-rows-by-selecting-checkboxes-using-php) which may be of help. — Funk Forty Niner, Dec 25 '13 at 15:37
I posted the fix and I am also using mysqli as well. Thank you for the help. I appreciate it. — Yuriah, Dec 25 '13 at 16:42
However, checkboxes can be a problem, which in my case, I ended up accidentally deleting entries I should not have deleted (using a test DB of course). You could replace `checkbox` with `radio` to be 100% sure, that way you delete only 1 entry at a time. @Yuriah — Funk Forty Niner, Dec 25 '13 at 16:50
But in the case where you would want to delete multiple entries at once, wouldn't the checkbox be a good option since the radio button only allows for one at the time to be selected unless that can be modified @Fred -ii- — Yuriah, Dec 25 '13 at 16:53
Yes indeed, I was just giving you another option, "from experience" ;-) — Funk Forty Niner, Dec 25 '13 at 16:54

score 0 · Answer 1 · answered Dec 25 '13 at 15:35

0

First you are selecting several rows:

mysql_query("SELECT * FROM shouts ORDER BY `id` DESC LIMIT 5")

Then you're grabbing an array for each row

while ($row = mysql_fetch_array($query)) {

Then setting the boxid for that row, and then running a delete query inside the loop for that row, where your WHERE clause looks for that boxid.

So, given this code flow you will ALWAYS delete all rows from the table.

mysql_query("DELETE FROM `shoutbox`.`shouts` WHERE `shouts`.`id` = '$boxid'")

Now, the real question becomes, how does one fix this?

In your select statement, just add a where clause that only returns the row with the boxid or whatever id you actually need so that just one row is returned (or exactly the rows to be deleted) are returned

"SELECT FROM `shoutbox`.`shouts` WHERE `shouts`.`id` = '$someSortOfID'"

Additionally, mysql_* is deprecated, so please see the following documentation on how to "upgrade" as it were to mysqli, the new standard.

answered Dec 25 '13 at 15:35

Zarathuztra

3,215
1
20
34

I appreciate your suggestion! But my select statement does have a WHERE clause. When I take it out of the loop it just deletes the last "post" submitted. – Yuriah Dec 25 '13 at 16:00
No it doesn't, this is what you have in your question: mysql_query("SELECT * FROM shouts ORDER BY `id` DESC LIMIT 5"); There's no where – Zarathuztra Dec 25 '13 at 16:04
That one is just to display the post. I fixed it though I will post the fix. Thank you again though. – Yuriah Dec 25 '13 at 16:10
Yes please, then I'll update this answer once the code is fixed. – Zarathuztra Dec 25 '13 at 16:11
I posted the fix as part of my question above – Yuriah Dec 25 '13 at 16:41

Subin · Answer 2 · 2013-12-25T15:43:38.010

I don't recommend you to use depreciated mysql_* function at all !!!!!

What you need to do is change the input value to row id :

<form action="" method='POST'>
 <input style='display:block; margin:0 auto;'type='submit' name='delete_button' value='<?echo$boxid;?>' />
</form>

Then move the whole code that conatins in if(isset($_POST['delete_button'])) outside the while loop and the whole code will be :

<?php
$query = mysql_query("SELECT * FROM shouts ORDER BY `id` DESC LIMIT 5") or die('<p id="formbox" style="text-align:center;">There was an unexpected error grabbing news from the database</p>'); 
while ($row = mysql_fetch_array($query)) {
 $title2 = $row['title'];
 $post2 = $row['post'];
 $date2 = $row['date'];
 $author = $row['author'];
 $boxid = $row['id'];

 echo '<div class="news-title"><b style="float:left;">'.$author.'</b><b style="text-align:center; color:green;">'.$title2.'</b><a href="">'.$date2.'</a></div>';
 echo '<div class="news-body">'.$post2.'</div>';
 if ($_SESSION['level'] > 3 || $_SESSION['group'] == 'Admin' || $_SESSION['group'] == 'Owner') {
?>
 <form action="" method='post'>
  <input style='display:block; margin:0 auto;'type='submit' name='delete_button' value='<?echo$boxid;?>' />
 </form>
<?php
 }
 echo '<br>';
}
if(isset($_POST['delete_button'])) { 
 $boxid = $_POST['delete_button'];
 $con = mysql_query("DELETE FROM `shoutbox`.`shouts` WHERE `shouts`.`id` = '$boxid'") or die('<p id="formbox" style="text-align:center;">There was an unexpected error deleting the post from the database</p>');
 header('Location: http://'.$_SERVER['HTTP_HOST']);
 mysql_close($con);
}
?>

Thank you for your suggestions, it helped alot! I appreciated. I also posted the fix. — Yuriah, Dec 25 '13 at 16:41

PHP MySQL Removing row from database through a delete button attached to an "object"

2 Answers2