Member Avatar for aianne

Hi everyone! So, I'm trying to create a program which users can add, update, delete, view, and search records but I am having a hard time about updating records in my database. In my Update form, I've got 1 dropdown menu or option that contains ID number and when I click the Search button, a form contains students info from selected ID number will display in the same form which where users can update it, but after clicking the Update button, no results posted and when I looked in my database nothing gets updated. I hope it made sense. Any help would be appreciated. Here's my code so far:

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

<br /><td>&nbsp;</td><label>ID Number: </label>

<?php

include 'collegeinfo_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 collegeinfo_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('collegeinfo_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 collegeinfo_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>Gender: </label>
<select name="Gender" value="<?php echo $row['Gender']; ?>"/><br />
<br /><option value="Male">Male</option><br />
<br /><option value="Female">Female</option><br />
</select><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="BSIT">Information Technology</option><br />
<br /><option value="BSHRM">Hotel and Restaurant Management</option><br />
<br /><option value="BSED">Education</option><br />
<br /><option value="BSBA">Business Administration</option><br />
<br /><option value="BSA">Accountancy</option><br />
<br /><option value="BSN">Nursing</option><br />
<br /><option value="BSPsych">Psychology</option><br />
<br /><option value="BSCS">Computer Science</option><br />
<br /><option value="BSBM">Business Management</option><br />
</select><br />


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

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

<?php

include('collegeinfo_connect.php');
 
 
 if (isset($_POST['update']))
 

 {
$ID = mysql_real_escape_string(htmlspecialchars($_POST['ID']));
 $FN = mysql_real_escape_string(htmlspecialchars($_POST['FN']));
 $LN = mysql_real_escape_string(htmlspecialchars($_POST['LN']));
 $Gender = mysql_real_escape_string(htmlspecialchars($_POST['Gender']));
 $Year = mysql_real_escape_string(htmlspecialchars($_POST['Year']));
 $Course = mysql_real_escape_string(htmlspecialchars($_POST['Course']));
 
 if ($ID == '' || $FN == '' || $LN == '' || $Gender == '' || $Year == '' || $Course == '')
 {
 echo "ERROR: Please fill in all required fields!";

 }
 else
 {
 mysql_query("UPDATE collegeinfo_tbl SET ID='$ID', FirstName='$FN', LastName='$LN', Gender='$Gender', Year='$Year', Course='$Course' WHERE ID='$ID'")
 or die(mysql_error()); 
 

echo "<br />Record Updated!";
	header("Location: view.php"); 
 }
 }
}

}
}




?>
  • 2 Contributors
  • 2 Replies
  • 4K Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by aianne

Dani AI

Generated

Likely culprits here are markup/flow issues rather than SQL alone. You have two forms on the same page and they appear to overlap; browsers ignore nested/invalid forms, so the UPDATE inputs may not actually be posted. Keep a single, well-formed form for the edit step and put all processing PHP before any output, especially if you plan to redirect. If you call header('Location: view.php') after an echo, the redirect will fail because headers have already been sent; redirect first, then exit. See PHP header().

Make sure you are not trying to UPDATE by a value you are simultaneously changing. A common pattern is to keep the row’s immutable key in a hidden field (e.g. id_original) and use that in the WHERE clause, while allowing the visible ID field to change. Also, your <select> elements should not use a value attribute on the <select> tag itself. Instead, mark the matching option as selected, for example:

<option value="Male" <?php if ($gender === 'Male') echo 'selected'; ?>>Male</option>

Reference: MDN: select element.

Since you are still on the old mysql_* API, consider moving to prepared statements with mysqli or PDO. That will simplify validation, prevent SQL injection, and give clearer error reporting. Example update flow with PDO: fetch the record, render the form with a hidden id_original, then on POST validate types, run a prepared UPDATE ... WHERE ID = :id_original, check rowCount(), and redirect. See PDO::prepare and OWASP’s guidance on parameterized queries SQL Injection Prevention Cheat Sheet.

Diafol’s advice about moving PHP before the DTD is spot on. In addition, log print_r($_POST, true) and the DB error/affected rows during testing to confirm the form is posting what you expect.

Recommended Answers

All 2 Replies

Member Avatar for Member #120589

place all the php before the DTD.

"UPDATE collegeinfo_tbl SET FirstName='$FN', LastName='$LN', Gender='$Gender', Year=$Year, Course='$Course' WHERE ID=$ID"

Also copy the sql to phpmyadmin, insert for hard-coded values and run it in the query window to see if it works. Check the table/fieldnames.

I would go further and validate input data for type rather than just simply/blindly sanitizing.

E.g.

if(is_int($_POST['Year']) && $_POST['Year'] > 1800 && $_POST['Year'] < (date('Y')+1)){
  $year = $_POST['Year']; 
}else{
  $error['year'] = 1;
}
Member Avatar for aianne

place all the php before the DTD.

"UPDATE collegeinfo_tbl SET FirstName='$FN', LastName='$LN', Gender='$Gender', Year=$Year, Course='$Course' WHERE ID=$ID"

Also copy the sql to phpmyadmin, insert for hard-coded values and run it in the query window to see if it works. Check the table/fieldnames.

I would go further and validate input data for type rather than just simply/blindly sanitizing.

E.g.

if(is_int($_POST['Year']) && $_POST['Year'] > 1800 && $_POST['Year'] < (date('Y')+1)){
  $year = $_POST['Year']; 
}else{
  $error['year'] = 1;
}

Oh! Yeah. Thanks! :)

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.