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

Question

aianne 23 Light Poster

12 Years Ago

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>

mysql php seo

3 Contributors
10 Replies
317 Views
1 Week Discussion Span
Latest Post 12 Years Ago Latest Post by rajeevphp2011

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

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

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

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!";
}

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

What do you see as output?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

aianne 23 Light Poster · Answer 1 · 2012-03-12T16:50:23+00:00

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>

aianne 23 Light Poster · Answer 2 · 2012-03-12T19:57:58+00:00

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.

aianne 23 Light Poster · Answer 3 · 2012-03-12T20:41:14+00:00

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. :)

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 4 · 2012-03-12T21:12:35+00:00

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.

aianne 23 Light Poster · Answer 5 · 2012-03-17T15:25:04+00:00

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.

aianne 23 Light Poster · Answer 6 · 2012-03-17T15:25:19+00:00

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.

rajeevphp2011 -3 Newbie Poster · Answer 7 · 2012-03-18T02:33:04+00:00

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!");
}