Delete row from mysql error.

bigkid · #1 (**permalink**) 08-04-04, 05:57 AM

Hi everyone,

Currently I have been using Spoono tutorials to make my add, edit, delete rows part in the script Im making (Im a complete newbie).

What I would love is to have 2 buttons next to each row of data... one saying edit. The other saying delete. When I hit the edit it take me to a form that edits the row, and with the delete, just delete the row all together.

Can anyone help me turn the code I have below into what I need. Thanks guys!

THE CODE STARTS HERE:

<? include('mydb_info.inc.php'); ?>
<html>
<head>
<title>BIG kid - Online Information System</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="../css/style.css" type="text/css">
</head>
<body bgcolor="#FFFFFF" text="#000000" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<table width="100%" border="0" cellspacing="0" cellpadding="0" height="100%">
<tr>
<td height="55" background="../images/blocks_bg_blue.jpg" colspan="2"> <img src="../images/blocks_bg_blue.jpg" width="27" height="55"></td>
</tr>
<tr>
<td height="36" valign="top" colspan="2">
<table width="100%" border="0" cellspacing="0" cellpadding="0" height="100%" class="black_small_text">
<tr>
<td width="98%" height="34">
<div align="right">Welcome <b>Administrator</b> to the Online Information
System<b> </b></div>
</td>
<td width="2%" height="34"> </td>
</tr>
</table>
<div class="black_small_text"></div>
</td>
</tr>
<tr>
<td align="center" valign="middle" class="black_small_text" width="10%"><img src="../images/logo.jpg" width="118" height="171">
<td align="center" valign="top" class="black_textUP" width="53%">

<?
$customer_id = $_GET["id"]; $cmd = $_GET["cmd"];

//If cmd has not been initialized
if(!isset($cmd))
{
//display all the news
$result = mysql_query("select * from customers order by customer_id");

//run the while loop that grabs all the news scripts
while($r=mysql_fetch_array($result))
{
//grab the title and the ID of the customer table
$firstname=$r["firstname"];//take out the first name
$lastname=$r["lastname"];//take out the surname
$customer_id=$r["customer_id"];//take out the id

//make the title a link
echo "<tr>";
echo "<td width=\"180\" height=\"20\"><a href=\"edit_customers.php?cmd=edit&id=$customer_id \">$firstname $lastname - Edit Customer Information</a></td><br>";
echo "</tr>";

}
}
?>
<?
if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
if (!isset($_POST["submit"]))
{
$id = $_GET["customer_id"];
$sql = "SELECT * FROM customers WHERE customer_id=$customer_id";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
?>
<p> </p>
<p><b>Customer Information Database</b><br>
<br>
<b><i>Edit Existing Customer Information</i></b>
<form action="edit_customers.php" method="post">
<input type=hidden name="id" value="<?php echo $myrow["customer_id"] ?>">

<table width="68%" border="0" cellspacing="0" cellpadding="0" class="black_small_text">
<tr>
<td width="17%">First Name:</td>
<td width="26%">
<input name="firstname" type="text" value="<?php echo $myrow["firstname"] ?>">
</td>
<td width="17%">Surname:</td>
<td width="28%">
<input name="lastname" type="text" value="<?php echo $myrow["lastname"] ?>">
</td>
</tr>
<tr>
<td width="17%">Business Name: </td>
<td width="26%">
<input name="business" type="text" value="<?php echo $myrow["business"] ?>">
</td>
<td width="17%">ABN:</td>
<td width="28%">
<input name="abn" type="text" value="<?php echo $myrow["abn"] ?>">
</td>
</tr>
<tr>
<td width="17%" height="20"> </td>
<td width="26%" height="20">  </td>
<td width="17%" height="20"> </td>
<td width="28%" height="20"> </td>
</tr>
<tr>
<td width="17%">Address: </td>
<td width="26%">
<input name="address" type="text" value="<?php echo $myrow["address"] ?>">
</td>
<td width="17%">Town/Suburb:</td>
<td width="28%">
<input name="town" type="text" value="<?php echo $myrow["town"] ?>">
</td>
</tr>
<tr>
<td width="17%">State:</td>
<td width="26%">
<input name="state" type="text" value="<?php echo $myrow["state"] ?>">
</td>
<td width="17%">Postcode:</td>
<td width="28%">
<input name="postcode" type="text" value="<?php echo $myrow["postcode"] ?>">
</td>
</tr>
<tr>
<td width="17%" height="20"> </td>
<td width="26%" height="20">  </td>
<td width="17%" height="20"> </td>
<td width="28%" height="20">  </td>
</tr>
<tr>
<td width="17%">Phone:</td>
<td width="26%">
<input name="phone" type="text" value="<?php echo $myrow["phone"] ?>">
</td>
<td width="17%">Fax:</td>
<td width="28%">
<input name="fax" type="text" value="<?php echo $myrow["fax"] ?>">
</td>
</TR>
<TR valign="bottom">
<TD colspan="4" height="40">
<div align="center">
<input type="hidden" name="cmd" value="edit">
<input type="submit" name="submit" value="Update Customer Details">
</div>
</TD>
<br>
</TR>
</TABLE>

</form>
<? } ?>
<?
if ($_POST["$submit"])
{
$firstname = $_POST["firstname"];
$lastname = $_POST["lastname"];
$business = $_POST["business"];
$abn = $_POST["abn"];
$address = $_POST["address"];
$town = $_POST["town"];
$state = $_POST["state"];
$postcode = $_POST["postcode"];
$phone = $_POST["phone"];
$fax = $_POST["fax"];

$sql = "UPDATE customers SET firstname='$firstname',lastname='$lastname',busine ss='$business',address='$address',town='$town',sta te='$state',postcode='$postcode',phone='$phone',fa x='$fax' WHERE customer_id=$customer_id";
//replace news with your table name above
$result = mysql_query($sql);
echo "<br>Thank you! Information updated.<br>To return back to the Customer Information Database <a href=\"menu.php?action=customers\">click here</a>";
}
}
?>
<table width="687" border="0" cellspacing="0" cellpadding="0">
<tr><br>
<br>
<br>
<td width="170" class="black_small_text">
<div align="right"><a href="menu.php?action=add" class="linkblack_text"><b>Add
Customer Details</b></a> </div>
</td>
<td width="33">
<div align="center"><b><a href="menu.php?action=add" class="linkblack_text"><img src="../images/add.jpg" width="32" height="28" border="0" alt="Add Customer Details"></a></b></div>
</td>
<td width="22"> </td>
<td width="170" class="black_small_text">
<div align="right"><a href="menu.php?action=edit" class="linkblack_text"><b>Edit
Customer Details</b></a></div>
</td>
<td width="33" class="black_small_text">
<div align="center"><b><a href="menu.php?action=edit" class="linkblack_text"><img src="../images/edit.jpg" width="32" height="28" border="0" alt="Edit Customer Details"></a></b></div>
</td>
<td width="22"> </td>
<td width="154" class="black_small_text">
<div align="right"><a href="menu.php?action=delete" class="linkblack_text"><b>Delete
Customer Details</b></a></div>
</td>
<td width="83" class="black_small_text">
<div align="left"><b><a href="menu.php?action=delete" class="linkblack_text"><img src="../images/delete.jpg" width="32" height="28" border="0" alt="Delete Customer Details"></a></b></div>
</td>
</tr>
</table>

<tr>
<td height="55" background="../images/blocks_bg_green.jpg" valign="bottom" colspan="2">
<img src="../images/blocks_bg_bottom.jpg" width="1000" height="101"></td>
</tr>
</table>
</body>
</html>

THE CODE ENDS HERE

Thanks again!

Jon Grant

AmberLR · #2 (**permalink**) 08-05-04, 09:36 PM

For a delete option, I usually provide a Javascript warning first, and then if the user confirms, I run a simple delete followed by redirection to the page.

Just glancing at your code, I'm unsure of what your different filenames refer to (menu.php, edit_customers.php...) but here's a pretty standard way of handling delete...

Code:

<script type="text/javascript">
// Confirm submission of delete form	
function verify() {
    msg = "Are you sure you want to delete these assignments? There is no undo available.";
    return confirm(msg);
} 
</script>

<form name="myform" action="delete.php" method="POST">
<input type="button" name="delete_this" value="Delete" onclick="return verify()"/>
</form>

Then this is the delete.php script:

PHP Code:


			
<?php



if ($_POST[delete]) {



$result = mysql_query("DELETE FROM table WHERE row='delete_this'") or die("Could not delete record");

echo("The record has been deleted");

// Then redirect them somewhere useful

}

?>

This can easily be placed into a loop for deleting more than one item at a time. Your button (or checkbox, or radio button, or whatever) should be named the same thing as the unique ID of the particular database record. That makes it much easier to use it for loops, and expand it and use it elsewhere, and so on.