Update Statement
Posted: Fri Jun 05, 2009 7:44 am
I've created an update statement, but when you select to update the record, it deletes the data in the record. It doesn't remove the record, the ID is the same, just the data has been erased.
I want to express my appreciation to everyone who has helped me in my learning of php and mysql. Your advice has proven invaluable! I look forward to when my knowledge allows me to contribute.
Thank you,
Paul
Here is the item_update.php page:
The update.php page
Also the table after the update:
I want to express my appreciation to everyone who has helped me in my learning of php and mysql. Your advice has proven invaluable! I look forward to when my knowledge allows me to contribute.
Thank you,
Paul
Here is the item_update.php page:
Code: Select all
<html>
<!-- Header -->
<? include("../include/header.php"); ?>
<!-- Menu -->
<? include("../include/menu.php"); ?>
<div id="main">
<!-- Begin Content -->
<form action="update.php" method="post">
<table border="0">
<tr>
<th>Category</th>
<th>Short Description</th>
<th>Part No</th>
<th>Vendor</th>
<th>Unit Price</th>
<th>Description</th>
<th>Max Qty</th>
<th>Qty</th>
<th></th>
<th></th>
<th></th>
</tr>
<tr valign="top">
<td>
<?php
require('../include/connect.php');
$id_update = $_GET['id'];
if (! is_numeric($id_update))
{
die('Error: ID is not numeric.'); // Whatever you want to catch errors here.
}
$result = mysql_query('SELECT
item.id,
item.category_id,
category.name,
item.short_desc,
item.part_no,
item.vendor,
item.unit_price,
item.long_desc,
item.max_qty,
item.qty
FROM
item,
category
WHERE
item.id = ' . $id_update . '
AND
category.id = item.category_id');
while($row = mysql_fetch_array($result))
{
/*
echo pre;
print_r($row);
echo pre;
*/
$cat_id = $row['category_id'];
$name = $row['name'];
$short_desc = $row['short_desc'];
$part_no = $row['part_no'];
$vendor = $row['vendor'];
$unit_price = $row['unit_price'];
$long_desc = $row['long_desc'];
$max_qty = $row['max_qty'];
$qty = $row['qty'];
/*echo "<td>
<select name='category'>
<option value = '" . $row['category_id'] . "'>" . $row['name'] . "</option>
</select></td>";*/
echo "<td><input type='text' name='short_desc' value='" . $short_desc . "'></td>";
echo "<td><input type='text' name='part_no' value='" . $part_no . "'></td>";
echo "<td><input type='text' name='vendor' value='" . $vendor . "'></td>";
echo "<td><input type='text' size='8' name='unit_price' value='" . $unit_price . "'></td>";
echo "<td><textarea rows='3' cols='20' name='long_desc'>" . $long_desc . "</textarea></td>";
echo "<td><input type='text' size='1' name='max_qty' value='" . $max_qty . "'></td>";
echo "<td><input type='text' size='1' name='qty' value='". $qty . "'></td>";
echo "<td> <a href='item.php'>No</a> </td>";
echo "<td> <a href='update.php?id=" . $row['id'] . "'>Yes</a> </td>";
}
?>
</tr>
</table>
</form>
<!-- End Content -->
<!-- Footer -->
<? include("../include/footer.php"); ?>Code: Select all
<?php
require('../include/connect.php');
$id_update = $_GET['id'];
if (!is_numeric($id_update)) {
die('Error: ID not numeric.');
}
/*$category = $_POST[category];*/
$short_desc = htmlspecialchars($_POST[short_desc]);
$part_no = htmlspecialchars($_POST[part_no]);
$vendor = htmlspecialchars($_POST[vendor]);
$unit_price = $_POST[unit_price];
$long_desc = htmlspecialchars($_POST[long_desc]);
$max_qty = $_POST[max_qty];
$qty = $_POST[qty];
$sql = "UPDATE
`item`
SET `date_update` = now(),
`short_desc` = '$short_desc',
`part_no` = '$part_no',
`vendor` = '$vendor',
`unit_price` = '$unit_price',
`long_desc` = '$long_desc',
`max_qty` = '$max_qty',
`qty` = '$qty'
WHERE `id` = '$id_update'";
mysql_query($sql);
if (!mysql_query($sql)) {
die('Error: ' . mysql_errno() . ", " . mysql_error());
}
mysql_close($con);
header("Location: item.php");
?>Code: Select all
mysql> select id,deleted,category_id,short_desc,vendor,unit_price,qty from item;
+----+---------+-------------+-----------------------------+---------------------+------------+------+
| id | deleted | category_id | short_desc | vendor | unit_price | qty |
+----+---------+-------------+-----------------------------+---------------------+------------+------+
| 1 | 1 | 1 | Short | Vendor | 43.00 | NULL |
| 2 | 1 | 3 | light bulbs | GE | 14.99 | NULL |
| 3 | 1 | 4 | phone | mittel | 14.99 | 1 |
| 4 | 1 | 4 | phone | mittel | 14.99 | 1 |
| 5 | 1 | 3 | Switch | HP | 499.99 | 3 |
| 6 | 1 | 3 | Switch | HP | 499.99 | 3 |
| 7 | 1 | 3 | <h1>switch</h1> | cisco | 999.99 | 1 |
| 8 | 1 | 3 | <h1>switch</h1> | cisco | 999.99 | 1 |
| 9 | 0 | 0 | | | 0.00 | 0 |
| 10 | 0 | 0 | | | 0.00 | 0 |
| 11 | 1 | 1 | Filter | ACME AC | 19.99 | 5 |
| 12 | 1 | 1 | Filter | ACME AC | 19.99 | 5 |
| 13 | 0 | 0 | | | 0.00 | 0 |
| 14 | 0 | 0 | | | 0.00 | 0 |
| 15 | 1 | 2 | UPS | mitsubishi | 999.99 | 1 |
| 16 | 1 | 2 | UPS | mitsubishi | 999.99 | 1 |
| 17 | 1 | 2 | UPS | mitsubishi | 999.99 | 1 |
| 18 | 1 | 2 | UPS | mitsubishi | 999.99 | 1 |
| 19 | 1 | 4 | Pens | pen maker, inc. | 99.99 | 5 |
| 20 | 1 | 4 | Pens | pen maker, inc. | 99.99 | 5 |
| 21 | 1 | 1 | test | test | 99.00 | 1 |
| 22 | 0 | 1 | test | test | 99.00 | 1 |
| 23 | 0 | 2 | test 2 | test, inc | 88.99 | 3 |
| 24 | 0 | 2 | test 2 | test, inc | 88.99 | 3 |
| 25 | 0 | 4 | pencils | acme pencil company | 9.99 | 1 |
| 26 | 0 | 4 | pencils | acme pencil company | 9.99 | 1 |
| 27 | 1 | 3 | cable | acme cable | 99.99 | 1 |
| 28 | 1 | 3 | cable | acme cable | 99.99 | 1 |
| 29 | 1 | 4 | Clip Boards | Office Depot | 9.99 | 1 |
| 30 | 1 | 4 | Clip Boards | Office Depot | 9.99 | 1 |
| 31 | 0 | 3 | router | hp | 999.99 | 1 |
| 32 | 0 | 3 | router | hp | 999.99 | 1 |
| 33 | 0 | 3 | router | hp | 999.99 | 2 |
| 34 | 0 | 3 | router | hp | 999.99 | 2 |
| 35 | 0 | 3 | swtich | hp | 1299.99 | 2 |
| 36 | 0 | 3 | swtich | hp | 1299.99 | 2 |
| 37 | 0 | 4 | flahlight | mag light | 9.99 | 1 |
| 38 | 0 | 4 | coffee mug | mug maker | 19.99 | 10 |
| 39 | 1 | 4 | coffee mug | mug maker | 19.99 | 10 |
| 40 | 0 | 4 | | | 0.00 | 0 |
| 41 | 1 | 3 | switch | network resale | 2999.99 | 2 |
| 42 | 0 | 4 | | | 0.00 | 0 |
| 43 | 1 | 4 | Keyboards | Apple | 39.99 | 5 |
| 44 | 1 | 4 | PAPER | DUNDER MIFFLIN | 2000000.00 | 1 |
| 45 | 1 | 4 | pens | mypens.inc | 5.99 | 6 |
| 46 | 0 | 3 | | | 0.00 | 0 |
| 47 | 0 | 0 | . . | . . | 0.00 | 0 |
+----+---------+-------------+-----------------------------+---------------------+------------+------+
47 rows in set (0.00 sec)