Interesting PHP/MySQL Dropdown Menu Form Problem

cmckeef

I have created a form which uses a dropdown list, that is populated by a table in my database. "Name" values are called into the dropdown list. The other fields in the table are "email" and "points." - All of which are submitted using a form on another page, which is irrelevant to this matter.

When a "name" on the dropdown list is chosen, the form input fields populate with the selection's other field data (via JS change event), which are "email" and "points" - populated from the database table. This works fantastically!Check it out here.

Here is my issue - only the last selection on the dropdown list can be edited. Everything else remains the same after "Submit Changes" is selected. Try to change any input field that is not associated with the last name on the list, nothing happens. Change the "name" for the last on the list? - it moves and then become uneditable (unless it's last on the list). For example "jesse" used to be "zachmorris," until I changed the name - you'll see my point.

Here is the php code that makes the form happen (getuser.php):

<?php
session_start();
// Place Session variable 'id' into local variable
$id = $SESSION['id'];
//Connect to the database through our include
include_once "connect_to_mysql.php";
// Process the form if it is submitted
if ($POST['email']) {
$email = $POST['email'];
$name = $POST['name'];
$points = $_POST['points'];
$sql = mysql_query("UPDATE 'table name here' SET email='$email', name='$name', points='$points' WHERE id='$id' LIMIT 1");
echo 'Your patient information has been updated.<br /><br />
To edit another patient entry, <a href="update_patient.php">click here</a>';
exit();
} // close if post

$sql="SELECT * FROM 'table name here' WHERE id = '".$q."'";

$result = mysql_query($sql);
echo "<meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>";
echo "<form action='getuser.php?q=' method='post' enctype='multipart/form-data' name='uform' id='uform' >";
while($row = mysql_fetch_array($result))
{
echo "<table>";
echo "<tr>";
echo "<td>" . Email . "</td>";
echo "<td><input type=text name='email' value=" . $row['email'] . "></td>";
echo "</tr>";
echo "<tr>";
echo "<td>" . Name . "</td>";
echo "<td><input type=text name='name' value=" . $row['name'] . "></td>";
echo "</tr>";
echo "<tr>";
echo "<td>" . Points . "</td>";
echo "<td><input type=text name='points' value=" . $row['points'] . "></td>";
echo "</tr>";
echo "</table>";
}

echo "<input name='Submit' type='submit' value='Submit Changes' />";
echo "</form>";

exit();
?>

Here is the full code for the page you see the example:

<?php
session_start();
include_once "connect_to_mysql.php";
// Place Session variable 'id' into local variable
$id = $_SESSION['id'];

$sql="SELECT id, name FROM 'table name here' ORDER BY name ASC";
$result=mysql_query($sql);
$options="";
while ($row=mysql_fetch_array($result)) {
$id=$row["id"];
$name=$row["name"];
$options.="<OPTION VALUE=\"$id\">".$name;
}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Edit Your Account Info</title>
<script type="text/javascript">
function showUser(str)
{
if (str=="")
{
document.getElementById("txtHint").innerHTML="";
return;
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("POST","getuser.php?q="+str,true);
xmlhttp.send();
}
</script>

</head>
<body>
<h3>Update a patient in the database.</h3>
Select Patient<br />
<form>
<select name="name" onchange="showUser(this.value)"><OPTION VALUE=0>Choose <?=$options?></SELECT>
</select>
</form>
<br />
<div id="txtHint"><b>Patient info will be listed here.</b></div>
</body>
</html>

Can anyone tell me how I can edit any of the entries on the dropdown list? - as opposed to only the last one?

johanafm

As far as I can tell, nothing is editable and you have security risks, lack error handling and some other things too. Start by fixing these and you might actually get information about what goes wrong.

session_start();
# However low the risk is (and if someone can tamper with session files, you have bigger
# security issues), but to be on the safe side, if this is supposed to be an int, typecast it...
$id = (int) $_SESSION['id'];
# ... and now you now it is! See comments below about SQL Injection. 
include_once "connect_to_mysql.php";

/* While I prefer checking the submit button itself, you can use any other form element
 * as long as you don't have multiple "submit types" - add, delete, etc.
 * However, you should use if (isset($_POST['email'])) instead for two reasons:
 *		1.  If this script is run without form submit, $_POST['email'] is not defined
 *			and an error is generated.
 *		2.  An empty string is typecast into boolean false which may not be the behaviour
 *			you're looking for. But if you are, then use if (!empty($_POST['email']))
 */
# Indent your code!
if ($_POST['email'])
{
	/* -- SQL Injection --
	 * Never ever use user supplied data without treating it like the rabid dog it is
	 * (aka sanitizing input), or you will be very sorry when Little Bobby Tables pays you
	 * a visit (xkcd "Exploits of a Mom" comic strip)
	 *		School: "Did you really name your son Robert'); DROP TABLE Students;-- ?"
	 *		Mom: "Oh. Yes. Little Bobby Tables we call him."
	 */
	# Strings should be handled like this
	$email = mysql_real_escape_string($_POST['email']);
	$name = mysql_real_escape_string($_POST['name']);
	# Integers should be handled like this
	$points = (int) $_POST['points'];
	/* And also stick to the proper datatype in queries! String literals are enclosed in
	 * single quotes, integers are not.
	 */
	# Also, could it be that 'table name here' should either be `table name here` or perhaps
	# more likely replaced with an actual table name?
	$query = "UPDATE 'table name here' SET email='$email', name='$name', points=$points WHERE id=$id LIMIT 1";
	$sql = mysql_query($query);
	/* You tell the user that "Patient information has been updated",
	 * but you do not actually know if it has! And obviously it hasn't
	 * or you wouldn't ask this question.
	 * You also use the word "your", but as far as I can tell there is nothing
	 * preventing people from editing data for other people (it even seems to be
	 * the actual purpose)
	 */
	# See http://se.php.net/manual/en/function.mysql-query.php for return values
	# And also not that this document has no html, head or body elements!
	echo '<html><head><title>Patient Information Update</title></head><body>';
	if ($sql)
	{
		# ... and now you know that the query was successful
		echo 'Your patient information has been updated.<br /><br />';
	}
	else
	{
		#
		echo 'Patient information could not be updated';
		error_log(sprintf('Mysql error %d: %s<br/>%s',
						mysql_errno(),
						mysql_error(),
						$query
		));
	}
	echo 'To edit another patient entry, <a href="update_patient.php">click here</a>';
	# and don't forget the closing tags either
	echo '</body></html>';
	exit;
} # Comment about "end of if" more or less pointless. Indentation makes this much more clear!

# What is $q?
$sql="SELECT * FROM 'table name here' WHERE id = '".$q."'";

$result = mysql_query($sql);
# You don't know if the query was successful!
if ($result)
{
	# But now you do!
	/* However, this output is inserted into an existing document, so you can't have
	 * a meta element here (inside the body element). Meta elements go in the head element only!
	 * Why not simply change the charset from ISO-8859-1 in the document to UTF-8?
	 * Else, perhaps you should use the form attribute accept-charset="utf-8"
	 */
#	echo "<meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>";
	echo "<form action='getuser.php?q=' method='post' enctype='multipart/form-data' name='uform' id='uform' >";
	# Once again, indent your code!
	while($row = mysql_fetch_array($result))
	{
		# Why are you using tables for formatting purposes?
		echo "<table>";
		echo "<tr>";
		echo "<td>" . Email . "</td>";
		echo "<td><input type=text name='email' value=" . $row['email'] . "></td>";
		echo "</tr>";
		echo "<tr>";
		echo "<td>" . Name . "</td>";
		echo "<td><input type=text name='name' value=" . $row['name'] . "></td>";
		echo "</tr>";
		echo "<tr>";
		echo "<td>" . Points . "</td>";
		echo "<td><input type=text name='points' value=" . $row['points'] . "></td>";
		echo "</tr>";
		echo "</table>";
	}

echo "<input name='Submit' type='submit' value='Submit Changes' />";
echo "</form>";
}
else
{
	# error logging / handling here
}