[RESOLVED] while loop generated form update data problem

[nomis]

Hi,

I'm trying to update a field called "order_by" with an integer provided from a form which lists sources by subject, queried from the database.

The problem I'm having (as I was able to determine by echoing rows) is that instead of looping through each of the source_ids and assigning it an associated order_by, it just picks the very last source_id on my list, and then applying each order_by to that in sequence, resulting in only one eventual update to one source_id.

When echoing the rows below, I get in order: subject_id, source_id, order_by as the following (subject id is correct; these are all drawn from the GET from the URL)
3, 106, 5
3, 106, 3
3, 106, 2
3, 106, 1
3, 106, 9
3, 106, 7

the actual source_ids are all different, and I was able to tell because I have echoed them before each row in the form. Any ideas how to get around this?

Here's my (simplified) code:

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
  or die('Error connecting to MySQL server.');

//grab subject id

$subject_id = $_GET['subject_id'];


//grabs the subject name to associate with the id
$q1 = "SELECT subject_id, subject_name from subject where subject_id = $subject_id ";
$r1  = mysqli_query($dbc, $q1)
  or die ('Error querying database');


?>

<?php


//grabs the source name, and other relevant information for display, and subject name to associate with id.

$qs = "SELECT s.source_id, s.source_name, s.description, s.url, s.proxy_ind,  sb.subject_id, sb.subject_name, ss.order_by
		from source s, source_subject ss, subject sb
		where s.source_id = ss.source_id
		and sb.subject_id = ss.subject_id
		and sb.subject_id = $subject_id
		order by source_name";
$rs  = mysqli_query($dbc, $qs)
  or die ('Error querying database');



//$subject_name = $row['subject_name'];

//echo the subject name, create link associated with id

//update row order
if (isset($_POST['submit']))  {

	//get variables, and assign order

		$source_id = $_POST['source_id'];

//echo 'Order by entered as ' . $order_by . '<br />';


	 foreach ($_POST['order_by'] as $order_by) {

	 //$order_by = $_POST['order_by'];


			$qorder = "UPDATE source_subject set order_by = '$order_by'
						WHERE source_id = '$source_id'
						AND subject_id = '$subject_id'";

			mysqli_query($dbc, $qorder)
					or die ('could not insert order'); 

		echo $subject_id . ', ' . $source_id . ', ' . $order_by;
		echo '<br />';
		}  

}	

while($row = mysqli_fetch_array($rs)) {

	$subject_id = $_GET['subject_id'];
	$order_by = $row['order_by'];
	$source_id = $row['source_id'];

?>
<form method="POST" action="<?php echo $_SERVER['PHP_SELF'].'?subject_id='.$subject_id ;    ?>">


<?php


echo $source_id . '<input type="hidden" name="source_id" value="'. $row['source_id']. '" ><input type="text" id="order_by" name="order_by[]" size="1" value="'. $order_by .'"/> <a href="'.  $row['url']  .'" target="_blank">'. $row['source_name'] . '</a> <br />';


}
?>

<input type="submit" value="update" name="submit" /><p />
</form>

[bradgrafelman]

The problem starts with the HTML for the form. All of your hidden "source_id" elements have the same name, thus you're only going to be able to access the last value in the POST string. It's essentially the same as trying to do this:

$variable = 1;
$variable = 2;
$variable = 3;
$variable = 4;

and then wondering why $variable is equal to just 4 rather than all four values.

Rather than fixing that element, however, I would suggest that you get rid of it altogether and instead incorporate the ID value with the "order_by" form element instead. For example, you could use the source_id as the index for the element:

echo '<input type="text" name="order_by[' . $row['source_id'] . ']" ...';

Also note that I got rid of the "id" attribute, since ID values must be unique throughout the entire HTML document (and outputting the same ID value inside a loop sure doesn't sound very unique or useful at all).

Then, you'd process the $_POST['order_by'] array using the '$key => $value' form of a [man]foreach/man loop to get both the order_by value entered into the textbox ($value) as well as the sort_id associated with it ($key).

By the way, also note that user-supplied data should never be placed directly into a SQL query string, else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors. Instead, you must first sanitize the data such as with a function like [man]mysql_real_escape_string/man (for string data) or by using prepared statements.

[nomis]

Hi thank you for looking at this. I think (after playing around with it a bit) your advice worked! 🙂 I very much appreciate it!

I'm aware that I'm not escaping the input; I was just to get it to work first before I did that; also this isn't for end-users...it's more for my administration tool which will live behind a password-protected wall, but I do appreciate those comments. 🙂

(Thanks for the mention about the id element; I'm not even sure why I use those, except for CSS, and I rarely even do that)

I'm not fully sure I understand the "'$key => $value' form of a foreach() loop" but I will read up on it more on php.net

Thanks a ton!