1.11M Members

Why I can't UPDATE my database records in PHP?

 
0
 

So I'm trying to create a PHP and MySQL program where users can add, update, delete, view and search records. But the problem is I can't update records whenever I click update button. Here's my code -->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">

<html>
<head>
<title>Update Record</title>
</head>
<body>

<table width="100%" border="0" cellpadding="0" cellspacing="5">
 
<form name="update category" method="POST" action="edit.php">
<tr>
<td>&nbsp;</td>
<td><strong>ID Number:</strong></td>
</tr>
<tr>
<td>&nbsp;</td>
<td >

<?php

include 'studentinfo_connect.php';

mysql_connect("$server", "$user", "$pass")or die("cannot connect"); 
mysql_select_db("$db")or die("cannot select DB");
 
$sql = mysql_query("SELECT ID FROM studentinfo_tbl ORDER BY ID"); 
$row = mysql_fetch_array($sql);
?>
 
<select name="ID">
<?php do{ ?>
<option value="<?php echo $row['ID']; ?>"><?php echo $row['ID']; ?> </option>
<?php } while($row = mysql_fetch_array($sql));?>
</select>
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input name="submit" type="submit" value="Search"></td>
</tr>
</table>
  
  

  
<?php
include('studentinfo_connect.php');
mysql_connect("$server", "$user", "$pass")or die("cannot connect"); 
mysql_select_db("$db")or die("cannot select DB");


if(isset($_POST['submit']))
{
if (is_numeric($_POST['ID']))
 {
	 $ID = $_POST['ID'];
 

$result = mysql_query("SELECT * FROM studentinfo_tbl WHERE ID=$ID") 
or die(mysql_error());  

 while($row = mysql_fetch_array( $result )) {
 ?>
 </form>
 <form name="update category" method="POST" action="edit.php">
<br /><label>ID Number: </label><input type="text" name="ID" value="<?php echo $row['ID']; ?>"/><br />
<br /><label>First Name: </label><input type="text" name="FN" value="<?php echo $row['FirstName']; ?>"/><br />
<br /><label>Last Name: </label><input type="text" name="LN" value="<?php echo $row['LastName']; ?>"/><br /><br />
<label>Year: </label>
<select name="Year" value="<?php echo $row['Year']; ?>"><br />
<br /><option value="1">First Year</option><br />
<br /><option value="2">Second Year</option><br />
<br /><option value="3">Third Year</option><br />
<br /><option value="4">Fourth Year</option><br />
</select><br />
<br /><label>Course: </label>
<select name="Course" value="<?php echo $row['Course']; ?>"><br />
<br /><option value="IT">BS Information Technology</option><br />
<br /><option value="HRM">BS Hotel and Restaurant Management</option><br />
<br /><option value="EDUC">BS Education</option><br />
</select><br />

<br /><input type="submit" value="UPDATE" name="sub" /><br /><br />

<?php

include('studentinfo_connect.php');
 
 
 if (isset($_POST['sub']))
 { 

  $ID = $_POST['ID'];
 

 $result=mysql_query("UPDATE 'studentinfo_tbl' SET 'ID'=[ID], 'FirstName'=[FN], 'LastName'=[LN], 'Year'=[Year], 'Course'=[Course] WHERE 'ID'=$ID")
 or die(mysql_error()); 
 
 

	if(isset($result))
	{
		echo("<br>Record Updated!");
	}
	else
	{
		echo("Failed to Update Record!");
	}

}
}
}
}


?>

</form>
</body>
</html>
 
0
 

Single quotes around your column names is wrong, replace them with backticks. Also remove the brackets, as that is used in MSSQL, not MySQL.

 
0
 

Single quotes around your column names is wrong, replace them with backticks. Also remove the brackets, as that is used in MSSQL, not MySQL.

Yeah! I tried it but it didn't work. Any idea? Here's my updated code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">

<html>
<head>
<title>Update Record</title>
</head>
<body>

<table width="100%" border="0" cellpadding="0" cellspacing="5">
 
<form name="update" method="POST" action="edit.php">
<tr>
<td>&nbsp;</td>
<td><strong>ID Number:</strong></td>
</tr>
<tr>
<td>&nbsp;</td>
<td >

<?php

include 'studentinfo_connect.php';

mysql_connect("$server", "$user", "$pass")or die("cannot connect"); 
mysql_select_db("$db")or die("cannot select DB");
 
$sql = mysql_query("SELECT ID FROM studentinfo_tbl ORDER BY ID"); 
$row = mysql_fetch_array($sql);
?>
 
<select name="ID">
<?php do{ ?>
<option value="<?php echo $row['ID']; ?>"><?php echo $row['ID']; ?> </option>
<?php } while($row = mysql_fetch_array($sql));?>
</select>
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input name="submit" type="submit" value="Search"></td>
</tr>
</table>
  
  

  
<?php
include('studentinfo_connect.php');
mysql_connect("$server", "$user", "$pass")or die("cannot connect"); 
mysql_select_db("$db")or die("cannot select DB");


if(isset($_POST['submit']))
{
if (is_numeric($_POST['ID']))
 {
	 $ID = $_POST['ID'];
 

$result = mysql_query("SELECT * FROM studentinfo_tbl WHERE ID=$ID") 
or die(mysql_error());  

 while($row = mysql_fetch_array( $result )) {
 ?>
 
 </form>
 <form name="edit" method="POST" action="edit.php">
<br /><label>ID Number: </label><input type="text" name="ID" value="<?php echo $row['ID']; ?>"/><br />
<br /><label>First Name: </label><input type="text" name="FN" value="<?php echo $row['FirstName']; ?>"/><br />
<br /><label>Last Name: </label><input type="text" name="LN" value="<?php echo $row['LastName']; ?>"/><br /><br />
<label>Year: </label>
<select name="Year" value="<?php echo $row['Year']; ?>"><br />
<br /><option value="1">First Year</option><br />
<br /><option value="2">Second Year</option><br />
<br /><option value="3">Third Year</option><br />
<br /><option value="4">Fourth Year</option><br />
</select><br />
<br /><label>Course: </label>
<select name="Course" value="<?php echo $row['Course']; ?>"><br />
<br /><option value="IT">BS Information Technology</option><br />
<br /><option value="HRM">BS Hotel and Restaurant Management</option><br />
<br /><option value="EDUC">BS Education</option><br />
</select><br />


<br /><input type="submit" value="UPDATE" name="sub" /><br /><br />

<?php
}
include('studentinfo_connect.php');
 
 
 if (isset($_POST['sub']))
 

 {

 $FN = $_POST['FN'];
 $LN = $_POST['LN'];
 $Year = $_POST['Year'];
 $Course = $_POST['Course'];
 
 
 $query=mysql_query("UPDATE studentinfo_tbl SET ID='$ID', FirstName='$FN', LastName='$LN', Year='$Year', Course='$Course' WHERE ID='$ID'")
 or die(mysql_error()); 
 
 $result=mysql_query($query);

	if($result)
	{
		echo("<br>Record Updated!");
	}
	else
	{
		echo("Failed to Update Record!");
	}

}
}
}



?>

</form>
</body>
</html>
 
0
 

Your query is executed twice. Change lines 104-116:

$query = "UPDATE studentinfo_tbl SET ID='$ID', FirstName='$FN', LastName='$LN', Year='$Year', Course='$Course' WHERE ID='$ID'"; 
$result = mysql_query($query) or die(mysql_error());
if ($result) {
  echo "<br>Record Updated!";
}
else {
  echo "Failed to Update Record!";
}
 
0
 

Your query is executed twice. Change lines 104-116:

$query = "UPDATE studentinfo_tbl SET ID='$ID', FirstName='$FN', LastName='$LN', Year='$Year', Course='$Course' WHERE ID='$ID'"; 
$result = mysql_query($query) or die(mysql_error());
if ($result) {
  echo "<br>Record Updated!";
}
else {
  echo "Failed to Update Record!";
}

Still the same sir. I tried your code and when click the update button nothing gets updated when i looked at my database. It doesn't work. I'm so sorry sir I'm new to php.

 
0
 

What do you see as output?

 
0
 

What do you see as output?

There's an option contains student's ID number *here is the printscreen-->http://www.flickr.com/photos/xetness/6976143911/ and when I click the Search button a form contains student's info from selected ID number will appear like this--> http://www.flickr.com/photos/xetness/6830026468/ where I can update using input type as text, then after will click the Update button there's no result posted. The form which contains student's info where I can update suddenly disappear and only option and search button's stays like the first photo/link and when I looked at my database nothing gets updated. I hope it made sense. Thank you so much for your reply sir. :)

 
0
 

Are you sure that a correct ID is selected when submitting the form? And what do you see after clicking, an error message, or "Record Updated!", or "Failed to Update Record!"?

If the form disappears completely after clicking, there is a syntax error somewhere in your code (before the updating part), so check that too.

 
0
 

I don't see anything sir. there's no "record updated" or "failed to update record" after clicking submit. and I can't find the error sir in my form.

 
0
 

Are you sure that a correct ID is selected when submitting the form? And what do you see after clicking, an error message, or "Record Updated!", or "Failed to Update Record!"?

If the form disappears completely after clicking, there is a syntax error somewhere in your code (before the updating part), so check that too.

I don't see anything sir. there's no "record updated" or "failed to update record" after clicking submit. and I can't find the error sir in my form.

 
0
 

line no 103 to 106

$result=mysql_query("UPDATE studentinfo_tbl SET ID='$ID', FirstName='$FN', LastName='$LN', Year='$Year', Course='$Course' WHERE ID='$ID'")
or die(mysql_error());
 
//$result=mysql_query($query);


 
if($result) // mysql_affected_row()
{
echo("<br>Record Updated!");
}
else
{
echo("Failed to Update Record!");
}
You
This article has been dead for over six months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: