PHP Mysql delete Query not working properly

Question

I am pulling a list of products from my MYSQL database and using a delete button against each product in case the operator wants to delete the product.

The problem is that every time I hit the delete button on any product in the list, the first element gets deleted.

What's wrong with my code below ?

Products page:

<?php
$link=mysqli_connect("localhost","root","","smartcart");
$prod="select * from products";
$rw=mysqli_query($link,$prod) or die(mysqli_errno()."in query $prod");
$count=1;

while($row=mysqli_fetch_assoc($rw))
{
    echo "<tr>";
    echo "<td>".$count."</td>";
    echo "<td>".$row['prod_id']."</td>";
    echo "<td>".$row['prod_name']."</td>";
    echo "<td>".$row['prod_price']."</td>";
    echo "<td><form action='delete_prod.php' id='delete' method='get'>";
    echo "<input type='hidden' name='prod_id' value='".$row['prod_id']."' />";
    echo "<button type='submit' form = 'delete' class='btn btn-default' name='delete'>Delete</button>";
    echo "</form></td>";
    $count=$count+1;                        
}

mysqli_free_result($rw);
?>

delete_prod.php:

<?php
if(isset($_GET['delete']))
{
    include "connection.php";
    $prod_id=$_REQUEST['prod_id'];
        $del="delete from products where prod_id=$prod_id";
    if (mysqli_query($link,$del))
    {
        echo "Successfully deleted";
        unset($_POST['delete']);
    }
    else
    {
        echo "Delete operation Failed";
    }
    header('location:show_db.php');
}
?>

I think I am terribly missing some simple point, but am unable to get what is it.

Answer 1

Check prod_id is auto incrementing properly or not in your table. Another thing is as your form is in loop the id for all forms will be duplicated. So each time it is submitting first form, thats why only first product is deleted from your record.

Answer 2

$link=mysqli_connect("localhost","root","","smartcart");
    $prod="select * from products";
    $rw=mysqli_query($link,$prod) or die(mysqli_errno()."in query $prod");
    $count=1;
    while($row=mysqli_fetch_assoc($rw))
    {
        echo "<tr>";
        echo "<td>".$count."</td>";
        echo "<td>".$row['prod_id']."</td>";
        echo "<td>".$row['prod_name']."</td>";
        echo "<td>".$row['prod_price']."</td>";
        echo "<td><form action='delete_prod.php'  method='get'>";
        echo "<input type='hidden' name='prod_id' value='".$row['prod_id']."' />";
        echo "<input type='submit' value='Delete' class='btn btn-default' name='delete'/>";
        echo "</form></td>";
        $count=$count+1;                        
    }

for delete action code in delete_prod.php

     if(isset($_GET['delete']))
{
    include "connection.php";
    $prod_id=$_REQUEST['prod_id'];
        $del="delete from products where prod_id=$prod_id";
    if (mysqli_query($link,$del))
    {
        echo "Successfully deleted";
        unset($_GET['delete']);
    }
    else
    {
        echo "Delete operation Failed";
    }
    header('location:show_db.php');
}

try this...

Answer 3

Most likely because you setup the id="delete". Usually id attribute values are not duplicated.

echo "<td><form action='delete_prod.php' id='delete' method='get'>";
echo "<button type='submit' form = 'delete' class='btn btn-default' name='delete'>Delete</button>";

The submit button gets the first ID and thus getting the first hidden input.

Alternatively, you could devise your button like this and serve as your marker:

No need to print each form!. Just wrap it with the table:

echo "<form action='delete_prod.php' id='delete' method='get'>";

echo '<table>';
while($row = mysqli_fetch_assoc($result)) {
    $prod_id = $row['prod_id'];
    echo "<tr>";
        echo "<td>".$count."</td>";
        echo "<td>".$row['prod_id']."</td>";
        echo "<td>".$row['prod_name']."</td>";
        echo "<td>".$row['prod_price']."</td>";
        echo "<td>";
        // each id is assigned to each button, so that when its submitted you get the designated id, the one that you clicked
        echo "<button type='submit' value='$prod_id' class='btn btn-default' name='delete'>Delete</button>";
        echo "</td>";
    echo '</tr>';
}

echo '</table>';
echo '</form>';

Then in PHP processing:

if(isset($_GET['delete'])) // as usual
{
    include "connection.php";
    $prod_id = $_GET['delete']; // get the id
    // USE PREPARED STATEMENTS!!!
    $del="DELETE FROM products WHERE prod_id = ?";
    $delete = $link->prepare($del);
    $delete->bind_param('i', $prod_id);
    $delete->execute();
    // don't echo anything else, because you're going to use header
    if($delete->affected_rows > 0) {
        header('location:show_db.php');
    } else {
        echo 'Sorry delete did not push thru!';
    }
}