Deleting specific row from a table in MySQL using PHP

Question

I know this question has been asked too many times but I've searched and found nothing to solve my problem. In the table I have 4 columns: id (auto increment and primary key), type, quantity and date. The problem is when I press delete link it won't delete specific row I want and please forget about code injection, this is meant to be a sample program. Thanks.

The code for a table is like this:

<div id="content" align="center">
<table border="1">
<tr>
<td>Item</td>
<td>Quantity</td>
<td>Date</td>
<td></td>
</tr>
<?php
include("connect.php");
$query=("SELECT * FROM purchase");
$result=mysql_query($query);
while($row=mysql_fetch_array($result))
{
?>
<tr>
<td><?php echo $row['1'];?></td>
<td><?php echo $row['2'];?></td>
<td><?php echo $row['3'];?></td>
<td><a href="delete.php">Delete</a></td>
</tr>
<?php
}
?>
</table>
</div>

And the delete function:

<?php
include("connect.php");

$host="localhost";
$user="root";
$pass="";
$db_name="proyek";
$tbl_name="purchase";

mysql_connect("$host", "$user", "$pass")or die("Cannot connect to SQL."); 
mysql_select_db('$db_name');
$query=("SELECT * FROM purchase");
$result=mysql_query($query);
$row=mysql_fetch_array($result);
$id=$row[0];

mysql_query("DELETE from purchase WHERE id='$id'");

header("location:purchasehistoryadmin.php");
?>

score 3 · Accepted Answer · answered Nov 29 '13 at 00:43

3

when user clicks "Delete" then delete.php is called, so purchase id which want to be deleted should be transfer via delete.php,

as you about "Injection", below code is just example. moreover, CSRF is more dangerous, to preventing SQL Injection is easy, but CRSF is little bit difficult.

list.php

<td><a href="delete.php?id=<?echo $row['id'];?>">Delete</a></td>

delete.php

$id = $_GET['id'];
mysql_query("DELETE from purchase WHERE id='$id'");

answered Nov 29 '13 at 00:43

Jason Heo

9,956
2
36
64

1

+1. I think this was implied, but there's no need to run a "SELECT" query in *delete.php*. The mysql_ interface is deprecated; should use mysqli or PDO. – spencer7593 Nov 29 '13 at 00:45
@spencer7593 Thank you very much! if OP wants to check purchase is owned by user and there really exists, then SELECT is good! – Jason Heo Nov 29 '13 at 00:47

score 1 · Answer 2 · answered Nov 29 '13 at 01:04

You visited delet.php but you didnt say which id you want to delete. You have to build url like

delete.php?param=5

use $_GET to catch id and pass it to query.( Now you are deleting just first row)

Also check this http://www.w3schools.com/php/php_forms.asp

Or

http://php.net/manual/en/reserved.variables.get.php

Deleting specific row from a table in MySQL using PHP

2 Answers2