edit database problem

[lucan]

I am using the following code to allow user to update there own information from the database but for some reason when you submit the new info it will not update but it tell's you that you are successful can some one let me know what i'm missing.  :'(

 

View page

<?php
$host="localhost"; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name=""; // Database name 
$tbl_name=""; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
?>

<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td><? echo $rows['category']; ?></td>
<td><? echo $rows['productname']; ?></td>
<td><? echo $rows['price']; ?></td>

// link to example.php and send value of id 
<td align="center"><a href="example.php?image_id=<? echo $rows['image_id']; ?>">update</a></td>
</tr>
<?php
}
?>

update page

 

<?php
$host="localhost"; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name=""; // Database name 
$tbl_name=""; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar
$id=$_GET['image_id'];


// Retrieve data from database 
$sql="SELECT * FROM $tbl_name WHERE image_id='$id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="ttr.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td> </td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"><strong>category</strong></td>
<td align="center"><strong>productname</strong></td>
<td align="center"><strong>price</strong></td>
</tr>
<tr>
<td> </td>
<td align="center"><input name="category" type="text" id="category" value="<?php echo $rows['category']; ?>"></td>
<td align="center"><input name="productname" type="text" id="productname" value="<?php echo $rows['productname']; ?>" size="15"></td>
<td><input name="price" type="text" id="price" value="<?php echo $rows['price']; ?>" size="15"></td>
</tr>
<tr>
<td> </td>
<td><input name="image_id" type="hidden" id="image_id" value="<?php echo $rows['image_id']; ?>"></td>
<td align="center"><input type="submit" name="Submit" value="Submit"></td>
<td> </td>
</tr>
</table>
</td>
</form>
</tr>
</table>

<?

// close connection 
mysql_close();

?>

 

 

update script

 

<?php
$host="localhost"; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name=""; // Database name 
$tbl_name=""; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// update data in mysql database 
$sql="UPDATE $tbl_name SET category='$category', productname='$productname', price='$price' WHERE image_id='$id'";
$result=mysql_query($sql);

// if successfully updated. 
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='view.php'>View result</a>";
}

else {
echo "ERROR";
}

?>

[WebStyles]

in your update script, $id is not set.

[gizmola]

I don't see anything that jumps out at me as wrong.  An update can "succeed" even when it doesn't actually update any rows.  So long as the update is valid, it will run.  You can see if there were any rows actually updated with mysql-affected-rows

 

You should also note this:

 

When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query.

[gizmola]

in your update script, $id is not set.

 

Unless register globals is on, which hopefully it is not.

 

$sql="UPDATE $tbl_name SET category='$category', productname='$productname', price='$price' WHERE image_id='$id'";

 

All of these variables need to be gotten from the $_POST, as alluded to bey WebStyles.