Page 1 of 1

Trouble with Update Statement

Posted: Fri Jun 13, 2003 3:19 pm
by Keanman
Hey everybody,

I'm having trouble debugging some code. I believe the problem lies somewhere in the Update statement because when I print the input variables they are working fine. If anybody could take a look at it and give me a hand it would be greatly appreciated. Here is the snippet of code:

<?
include("header.inc");
include("details.inc");
?>

<H2>Update a Record</H2><P><HR><P>
<A HREF="enterForm.php">Enter a Record</A>
<A HREF="searchForm.php">Search Database</A>
<A HREF="userForm.php">Create a New User</A><P><HR></CENTER><P>

<form name="frmCheck" method=post action="<?php echo $PHP_SELF?>">

<?
if(isset($_POST['btnUpdate'])) {
$asset = $_POST['asset'];
$name = $_POST['name'];
$location = $_POST['location'];
$description = $_POST['description'];
$warranty = $_POST['warranty'];
$serial = $_POST['serial'];
$checkbox= $_POST['chkBox'];

for($i=0;$i < count($serial);$i++) {
if($checkbox[$i]=="on") {
$query = "UPDATE assets SET serialNum =" . $serial . ", assetNum =" . $asset . ", itemName =" . $name . ", location =" . $location . ", description =" . $description . ", warranty =" . $warranty . "WHERE serialNum =" . $serial[$i];
mysql_query($query) or die (mysql_error());
}
}
}

include("title.inc");

$query="SELECT * FROM assets ORDER BY location ASC";
$result=mysql_query($query);
$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {

$serials=mysql_result($result,$i,"serialNum");
$asset=mysql_result($result,$i,"assetNum");
$name=mysql_result($result,$i,"itemName");
$location=mysql_result($result,$i,"location");
$description=mysql_result($result,$i,"description");
$doe=mysql_result($result,$i,"entryDate");
$warranty=mysql_result($result,$i,"warranty");

printf("<TR ALIGN=CENTER><TD><input type=\"checkbox\" name=\"chkBox[$i]\"><input type=\"hidden\" name=\"serial[$i]\" value=\"$serials\"></TD><TD>$name</TD><TD>$serials</TD><TD>$asset</TD><TD>$location</TD><TD>$description</TD><TD>$doe</TD><TD>$warranty</TD></TR>");

$i++;
}

?>

</TABLE>

Serial Number: <input type="text" name="serial"><BR>
Asset Number: <input type="text" name="asset"><BR>
Item Name: <input type="text" name="name"><BR>
Location: <input type="text" name="location"><BR>
Description: <input type="text" name="description"><BR>
Warranty: <input type="text" name="warranty"><BR>
<input type="Submit" name="btnUpdate" Value="Update Record">
<input type="Reset" name="Reset">
</FORM>

<?
include("footer.inc");
?>

test

Posted: Fri Jun 13, 2003 3:36 pm
by phpScott
It looks like there is a problem with how you are quoting your string.
When you are using text base coluimns the value that you are searching on has to be in quotes. Try

Code: Select all

$query = "UPDATE assets SET serialNum =$serial, assetNum = $asset, itemName =' $name', location =' $location', description =' $description' , warranty = $warranty WHERE serialNum = $serial&#1111;$i]";
assuming that itemName, location, description are text. The only other problem is the $serial[$i] array value might be a problem you might have to assign that out to a temp varialble for each iteration of the loop.
ie $tmpSerial = $seiall[$i];


phpScott

Posted: Fri Jun 13, 2003 3:57 pm
by cactus
In addtion, you will probably need to single quote your variables, to ensure the data in them is encapsulated safely.

Code: Select all

SELECT * FROM '".$blah."' WHERE date='".$date."'";
You should also try and make your variables safe before using them in your queries (esp. since they are straight from $_POST vars) using methods like:

Ref: XCIX. String functions

addslashes()
htmlspecialchars()
etc.

Regards,