Update MySql database issues

[Seapoeschl]

Hello All,

 

I have come across a problem when trying to update my table on MySql. I have set up an update form that allows the user to click on a link, then change the field(ex. Part#, Mold#, etc.), and then click submit to finalize the update. I have it set up so it will take you to a page telling you if there was any errors or if it was successful. Right now it says it is successful, but does not actually change the field on MySql. I can't seem to figure out why it won't work. Here is the code that I currently have. I think that my problem is somewhere in update_ac.php page.

 

quality.php

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);
?>

<tr>
<table width='1020px' border='0' align='center' cellpadding='0' cellspacing='0' bgcolor='#ebebeb' >
<tr bgcolor='#004c8a'>
<td width='5%' align='center' height='10' ><p><font color='white'><strong><B>Part#</th>
<td width='5%' align='center' height='10' ><p><font color='white'><strong><B>Mold#</th>
<td width='5%' align='center' height='10' ><p><font color='white'><strong><B>Press#</th>
<td width='5%' align='center' height='10' ><p><font color='white'><strong><B>Update</th>
</tr>

<?php
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td width='5%' align='center' height='40' ><? echo $rows['Part']; ?></td>
<td width='5%' align='center' height='40' ><? echo $rows['Mold']; ?></td>
<td width='5%' align='center' height='40' ><? echo $rows['Press']; ?></td>

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

<?php
}
?>

 

update.php

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['id'];

// Retrieve data from database 
$sql="SELECT * FROM $tbl_name WHERE 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="update_ac.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>Part#</strong></td>
<td align="center"><strong>Mold#</strong></td>
<td align="center"><strong>Press#</strong></td>
</tr>
<tr>
<td> </td>
<td align="center">
<input name="part" type="text" id="name" value="<? echo $rows['Part']; ?>">
</td>
<td align="center">
<input name="mold" type="text" id="lastname" value="<? echo $rows['Mold']; ?>" size="15">
</td>
<td>
<input name="press" type="text" id="email" value="<? echo $rows['Press']; ?>" size="15">
</td>
</tr>
<tr>
<td> </td>
<td>
<input name="id" type="hidden" id="id" value="<? echo $rows['ID']; ?>">
</td>
<td align="center">
<input type="submit" name="Submit" value="Submit">
</td>
<td> </td>
</tr>
</table>
</td>
</form>
</tr>
</table>

<?php
// close connection 
mysql_close();
?>

 

update_ac.php

$Part    = $_POST['part'];
$Mold    = $_POST['mold'];
$Press    = $_POST['press'];

// 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 parts SET part='$Part', mold='$Mold', press='$Press' WHERE id='ID'";
$result=mysql_query($sql);

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

else {
echo "ERROR";
}

?>

[mikosiko]

what have you done to debug your code?

 

a basic debug will be to display in update_ac.php the values for the POST'ed variables 'parts', 'press', 'mold' and 'ID' and see if they have the right values. Also a display of your raw query ($sql) could help you to evaluate if the UPDATE sentence is constructed correctly.

 

 

[Seapoeschl]

I tried to debug with the php code error_reporting(E-ALL) but it is still not showing any errors and says it was successful without changing the information in the table. I looking in my sql to see if there was any errors or problems, but everything seemed fine.

[Jessica]

a basic debug will be to display in update_ac.php the values for the POST'ed variables 'parts', 'press', 'mold' and 'ID' and see if they have the right values. Also a display of your raw query ($sql) could help you to evaluate if the UPDATE sentence is constructed correctly.

 

Do that, and then also run the SQL in an interface like phpMyAdmin to make sure it works.

[Seapoeschl]

I created the SQL table with phpadmin and it works fine. I have another form that allows the user to add to the table and that works fine. It is when I try to have the ability to update the information that is giving me the issues.

[PFMaBiSmAd]

Your WHERE clause in the update query is trying to test: id='ID'. You would need to use a variable that actually holds the id value.

 

The reason your code reports that the update was successful, is because the update query is successfully running (it did not fail due to an actual error.) To test if the update query actually updated a row, you would need to use the mysql_affected_rows function.

[Jessica]

I created the SQL table with phpadmin and it works fine. I have another form that allows the user to add to the table and that works fine. It is when I try to have the ability to update the information that is giving me the issues.

That's nothing to do with what we suggested, if you try what I actually suggested, you'd see that your query will never update the information.

[Seapoeschl]

Your WHERE clause in the update query is trying to test: id='ID'. You would need to use a variable that actually holds the id value.

 

The reason your code reports that the update was successful, is because the update query is successfully running (it did not fail due to an actual error.) To test if the update query actually updated a row, you would need to use the mysql_affected_rows function.

 

 

Thank you it was the problem with the "WHERE id" part the whole time. I just added a variable that holds the id value and it works now. Thanks everyone for the help