How to delete a MySQL row with PHP

**Samo** · 06-27-2009, 03:49 AM

Hi, i am new in PHP, and i am facing the following problem:
I have two php files; info.php and delete.php.
info.php displays my records(rows) in MySQL in a table and next to each row in this table there are three buttons:view, mod and del.
what i want is when i press "del" button next to each row, this record (row) will be deleted from my database. I need your help

info.php:
<html>
<head>
<style type="text/css">
table {font-family: Calibri; font-size: 8pt;font-weight:bold;}
</style>
</head>
<body>
<?php

$user = "root";
$pwd = "";
$host = "localhost";
$dbname = "s2hdatabase";

$conn = mysql_connect($host,$user,$pwd) or ("<br>[en] ERROR on MySQL server connection");
(is_resource($conn));

(mysql_select_db($dbname,$conn));

$query="SELECT * FROM Player";
$result=mysql_query($query,$conn);

mysql_close($conn);

echo "<b><center>All Records</center></b><br><br>";

echo "".'<a href="mainform.php"><IMG SRC="add.gif">Add Player</a>'."";

echo "<table width = 100% border = '0' cellspacing = '0' cellpadding = '0'>";
echo "<tr bgcolor= #95E4AF >
<td ><b>Last Name </b></td>
<td ><b>First Name</b></td>
<td><b>Shirt number</b></td>
<td><b>Position</b></td>
<td><b>Alternate Position</b></td>
<td><b>Phone 1</b></td>
<td><b>Cell Phone</b></td>
<td><b>Email</b></td>
<td><b>Parent 1 First Name</b></td>
<td><b>Parent 2 First Name</b></td>
<td><b>View</b></td>
<td><b>Mod</b></td>
<td><b>Del</b></td>
</tr>";

$num=0;
$id = 0;
while ($row = mysql_fetch_array($result))
{

if($num % 2 ==0)
{
$bgcolor='#FFFFCC';
}
else
{
$bgcolor='#CCFFCC';
}
echo "<tr bgcolor=$bgcolor>";
echo "<td>" . $row['Last_Name'] . "</td>";
echo "<td >" . $row['First_Name'] . "</td>";
echo "<td>" . $row['Shirt_Number'] . "</td>";
echo "<td>" . $row['Primary_Playing_Postion'] . "</td>";
echo "<td>" . $row['Alternate_Playing_Position_1'] . "</td>";
echo "<td>" . $row['Primary_Phone'] . "</td>";
echo "<td>" . $row['Cell_Phone'] . "</td>";
echo "<td>" . $row['Contact_email'] . "</td>";
echo "<td>" . $row['Parent_1_First_Name'] . "</td>";
echo "<td>" . $row['Parent_2_First_Name'] . "</td>";
echo"<td>" .'<form action="display.php" method="post"><input type="image" src="view.gif" ></form>'."</td>";
echo"<td>" .'<input type="image" src="modify.GIF" value="Modify" />'."</td>";
echo"<td>" .'<form action="delete.php" method="post"><input type="image" src="delete.gif"></form>'."</td>";
echo "</tr>";
$num++;
$id++;

}
echo "</table>";
echo"$num";

?>

</body>
</html>

and this is delete.php

<html>
<body>
<?php
$user = "root";
$pwd = "";
$host = "localhost";
$dbname = "s2hdatabase";

$t1 = $_POST['id'];

$conn = mysql_connect($host,$user,$pwd) or ("<br>[en] ERROR on MySQL server connection");
if(is_resource($conn))
{
echo "<b>your resource to MySQL Server is ok";
}
if(mysql_select_db($dbname,$conn))
{
echo "<br><b>you have selected $dbname</br>";
}
$sql = "DELETE FROM Player WHERE Last_Name='$t1'";

if(mysql_query($sql,$conn)){
echo "<br><b>The player $t1 was deleted successfully from $dbname</br>";
}

mysql_close($conn);

?>
<a href ="info.php">click here to go back to the records</a>

</body>
</html>

i tried to pass a variable "id" from info to delete when pressing delete button but it is not working

thanks in advanced.

**rfeio** · 06-27-2009, 07:33 AM

I see several problems with your script;

1) If you echo $t1 on the delete.php file, you will see that the $_POST['id'] is always empty.
To solve this you need to put $id on an <input> tag inside a form. The best option would probably be an <input type="hidden" ...>

2) $id is a sequential numeric value that increments with every record you have in the table. For example, if you have 5 records, $id will be 5. If you try to delete record 1, the value that is passed to the delete.php is 5 instead of 1. To solve this issue you can use arrays for example or add an extra field to the table (record_id or something of the sort).

3) Another problem is in the following code:

$sql = "DELETE FROM Player WHERE Last_Name='$t1'";

Now, $t1 assumes the value from $_POST['id'] which is a numeric value and not a last name. So, this delete would never work, unless your last_name field is indeed a numeric value which I doubt it. :-)

Hope this helps.

Cheers!

**Samo** · 06-27-2009, 07:51 AM

thanks for your help rfeio,

i tried to fix these errors, but still doesn't work. can you please modify my code to see how it should be in order to make info.php and delete.php work properly?!

i really need this to work , thanks

Thread: How to delete a MySQL row with PHP

Thread Tools

Display

How to delete a MySQL row with PHP

Tags for this Thread

Posting Permissions