DELETE FROM not working deletes wrong row

[marshmellows_17]

Hello

 

I have the following code which i found but it doesnt work properly.. it comes up with a list of the items in the database

e.g.

item 1 - delete

item 2 - delete         

item 3 - delete

 

but when i click delete it deletes a different row and cannot for the life of me figure out why. e.g. if i click delete on item 2 it deletes item 3.

 

 

<?php


//If cmd is not hit
if(!isset($cmd)) 
{
   //display all the tbl_testimonies
$result = @mysql_query('SELECT * FROM tbl_testimonies');

if (!$result) {
exit('<p>Error!<br />'.
'Error: ' . mysql_error() . '</p>');
}

while ($r = mysql_fetch_array($result)) {

extract($r);
      //grab the tenants_house_name and the tenant_house_id of the enws
      $tenants_house_name=$r["tenants_house_name"];//take out the tenants_house_name
      $tenant_house_id=$r["tenant_house_id"];//take out the tenant_house_id

      echo "<a href='delete.php?cmd=delete&tenant_house_id=$tenant_house_id'>$tenants_house_name - Delete</a> <br>";
  
 // echo "$tenants_house_name " . "<a href='delete.php?cmd=delete&tenant_house_id=$tenant_house_id'>Delete</a>" ;

    }
}
?>

<?php
if($_GET["cmd"]=="delete")

{    $sql = "DELETE FROM tbl_testimonies WHERE tenant_house_id=$tenant_house_id";
  
      $result = mysql_query($sql);
    echo "Row deleted!";
}
?>

[rajivgonsalves]

this

 

$sql = "DELETE FROM tbl_testimonies WHERE tenant_house_id=$tenant_house_id";

 

i think should be

 

$sql = "DELETE FROM tbl_testimonies WHERE tenant_house_id={$_GET['tenant_house_id']}";

 

[Psycho]

The problem is that you first loop through ALL the records in the database to create the delete links. In that process you define two variables ($tenants_house_name & $tenant_house_id) based upon the values from each record.

 

Fine so far...

 

But, then at the end of the script you check if the user had submitted a delete request via the GET object (no problem there, either)

 

The problem is that if the script does identify that a delete request was made it deletes the record with the ID from the variable from $tenant_house_id! That variable was only used in the loop to create the delete links - so it will always be the value from the last record in the database. You should be using $_GET['tenant_house_id'] which was the value passed in the delete request.

 

In addition, you should be escaping that value before running it in a query. Also, you should not be using ectract() in the while loop. You already "extract" the values you need on the following two lines and extract() is considered unsafe by many.

[Psycho]

One more thing: The code should do the delete before it creates the delete links. Otherwise, when you do a delete, the page will display the record as still available for deletion after the record is deleted.