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

Question

aianne 23 Light Poster

14 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

3 Contributors
10 Replies
337 Views
1 Week Discussion Span
Latest Post 14 Years Ago Latest Post by rajeevphp2011

Dani AI

Generated 9 Months Ago

Quick summary of the common causes seen in this thread (and what to try next). correctly called out the quoting issue, and flagged the double-execution problem. In addition, three recurring causes explain why an UPDATE looks like “nothing happened”: the script never gets the original ID on the UPDATE request (so the WHERE matches nothing), HTML/form nesting or mismatched tags that stop inputs from being posted, and use of the old mysql_* API (removed from modern PHP). (php.net)

Practical checklist to diagnose and fix the problem:

Turn on full error reporting during development (ini_set + error_reporting) and dump POST payloads (var_dump($_POST]) to confirm fields arrive.
Ensure the edit inputs are inside a single, well-formed form (no nested <form> tags). A missing/mismatched closing tag will make the edit form disappear after submit.
Preserve the original primary key: include a hidden field (e.g. orig_id) with the record’s ID and use that in the WHERE clause. Do not rely on an editable ID input as the lookup key.
Avoid running the same query twice; build the SQL string (or prepare a statement) and execute it once. As suggested earlier, check the submit button name used in PHP matches the HTML.
Quote identifiers correctly in SQL (backticks for identifiers; single quotes for string literals). (dev.mysql.com)

Better long-term fix (safer and modern): use prepared statements (PDO or mysqli) so input is bound and escaped, and to get clear errors. Example pattern (short):

// connect with PDO (set ERRMODE_EXCEPTION)
$stmt = $pdo->prepare('UPDATE studentinfo_tbl SET FirstName=?, LastName=?, Year=?, Course=? WHERE ID=?');
$stmt->execute([$fn, $ln, $year, $course, $orig_id]);
echo $stmt->rowCount() . ' row(s) updated';

Prepared statements avoid SQL injection and make debugging predictable. For PDO usage and prepared-statement details see the PHP docs. (php.net)

If the above still fails, capture the exact SQL or exception message and confirm the form structure (single form, inputs named and inside the form) before re-running the update.

All 10 Replies

pritaeas 2,276 Most Valuable Poster

14 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,276 Most Valuable Poster

14 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,276 Most Valuable Poster

14 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--> 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,276 Most Valuable Poster 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!");
}

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

Dani AI

Recommended Answers Collapse Answers

All 10 Replies

Recommended Answers