can't delete from specific row from generate table from mySQL

Question

Hye, i'm working on a small project of Inventory system. Everything is okay until this last part. I have included a delete button at the end of each row for user to delete any item of choice, but when user press the delete button, it deleted the last row of the table, instead of the row/item of choices, where is my mistake in my coding?

Thank you!

<?php 
 $result= mysql_query("SELECT * from Staff ORDER BY status");
 $count=1;
 
 while($row=mysql_fetch_array($result)){
?>  
   <tr>
       <td align="left"><?php echo $count; ?></td>
    <td align="left"><?php echo $row['staffId']; ?></td>
    <td align="left"><?php echo $row['name']; ?></td>
    <td align="left"><?php echo $row['address'];?></td>
    <td align="left"><?php echo $row['pNum'];?></td>
    <td align="left"><?php echo $row['status'];?></td>
    <td align="left"><?php echo $row['type'];?></td>
                <td><input type="submit" name="deleteStaff" value="Delete" onClick="displayMessage()">
             <input type="hidden" name="staffId1" value="<?PHP echo $row['staffId']; ?>">
             </td>
   </tr>
   </tbody> 
<?php 
 $count++; } 
 if(isset($_POST['deleteStaff'])){
  $id=$_POST['staffId1'];
  $result=mysql_query("DELETE from Staff WHERE staffId='$id' ");
  if($result){
   echo '<script> location.replace("viewStaff.php"); </script>';
   }
   else{
    ?>
    <script>
     alert ("Fail to delete data")
     window.location.href='viewStaff.php'
    </script>
                <?PHP
    }
  }
?>

Don't use the `mysql_*` functions. They have been deprecated since v5.5 (Jun 2013) and removed since v7.0 (Dec 2015). Instead use the [**mysqli_***](https://secure.php.net/manual/en/book.mysqli.php) or [**PDO**](https://secure.php.net/manual/en/book.pdo.php) functions with [**prepared statements**](https://secure.php.net/manual/en/pdo.prepare.php) and [**bound parameters**](https://secure.php.net/manual/en/pdostatement.bindparam.php). — Alex Howansky, Jun 07 '17 at 20:48
Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, Jun 07 '17 at 20:48
no, this project will not be published to anyone, and it is just a test project. All data are just test data — Amuro Hirai, Jun 07 '17 at 20:56

score 0 · Answer 1 · answered Jun 07 '17 at 21:02

since the name for the hidden field is the same for all rows being generated the post request is pulling the value of the last row( also "staffId1" ).

I recommend either using a individual form for each row in which case your code doesn't change as much or use js to get the required staffID using selectors

score 0 · Answer 2 · answered Jun 07 '17 at 21:05

If you include the same hidden field (StaffId1) for every record it will contain more then one value.

You are now listening to the pressed SUBMIT so you don't need the hidden field. You can just get the value if the pressed SUBMIT.

Button

type="submit" name="staffId" value="<?php echo $id ?>"

Php

$id=$_POST['staffId'];

score 0 · Accepted Answer · answered Jun 07 '17 at 21:07

I've encountered this error before as well. I think the error is happening since you're calling the same "onclick" function for each array result.

Instead, create a link to a page where you can run the php deletion script and pass the staffID into the URI and Get that in the php deletion script that you created.

example

<form action="deletion_script.php?staffid=<?php echo $staffid; ?>">

Put that inside of the td tag

Then in your deletion_script.php file, put something like this

<?php
//get staffid from URI
$staffid = $_GET['staffid']; 

$sql="DELETE FROM $table_name WHERE staffid = '$staffid'"
$result = mysqli_query($connect, $sql);
if($result) {
//send back to the page you want
header("Location: ../inventory.php");
}
?>

This worked for me, and how I handle all situations like this from now on. You'll need to adjust the code a little to fit your set up.

can't delete from specific row from generate table from mySQL

3 Answers3