using foreach array to update multiple data rows with different data

[nomis]

Hi, after banging my head against the wall for a while thinking this would be a simple task, I'm discovering that this is more complicated than I thought.

 

Basically what I have is a link table linking together source_id and subject_id.  For each subject there are multiple sources associated with each.

 

I had created a basic listing of sources by subject... no problem.  I now need a way of having a form to create an ordered list in a user-specified way.  In other words, I can currently order by id or alphabetically (subject name lives on a different table), but I need the option of choosing the order in which they display.  I added another row to this table called order_by.  No problem again, and I can manage all of this in the database, however I want to create a basic form where I can view sources by subject and then enter a number that I can use for sorting.

 

I started off looping through each of the entries and the database (with a where), and creating a foreach like so (with the subject_id being grabbed via GET from the URL on a previous script)

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


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

	//get variables, and assign order

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


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

	 foreach ($_POST['order_by'] as $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 . ', ' . $order_by . ', ' . $source_id;
		// echo '<br />';
		}  
}	
else {
	$subject_id = $_GET['subject_id'];
	$order_by = $row['order_by'];
	$source_id = $row['source_id'];

}

And have the line in the form like so:

echo '<input type="text" id="order_by" name="order_by[]" size="1" value="'. $order_by .'"/>

 

(yes I know I didn't escape the input field... it's all stored in an htaccess protected directory;  I will clean it up later once I get it to work)

 

This, of course, results in every source_id getting the same "order_by" no matter what I put into each field.

 

I'm thinking that I need to do some sort of foreach where I go through foreach source_id and have it update the "order_by" field for each one, but I must admit I'm not sure how to go about this (the flaws of being self-taught I suppose;  I don't have anyone to go to on this).

 

I'm hoping someone here can help?

 

Thanks a ton in advance

[nomis]

Is foreach the wrong approach? 

[AyKay47]

If there are multiple text fields that a user can specify an order, than using an array name is fine, and the foreach is fine.

However you certainly do not want a foreach loop inside of a while loop, and running queries each iteration at that.

This can be done with one query using only the foreach loop.

 

foreach($_POST['order_by'] as $order_by)
{
    $sql = "UPDATE source_subject
    SET order_by = '$order_by'
    WHERE subject_id = '$subject_id'
    AND source_id IN (select source_id from table)"; //replace 'table' with whatever table the column is supposed     to come from. 
}

[nomis]

That's a good idea, but unfortunately it doesn't work.

 

The issue is that this link table source_subject has a dual primary key (source_id and subject_id).  order_by is just an additional field.

 

This form is generated by a while loop, where it grabs the subject_id from the URL (via GET), and then shows all sources associated with that subject_id, with an input box next to them to enter order_by values

 

If I remove the foreach from the while, it doesn't associate with specific rows in the table.  I also can't do the subselect for this reason, this is a mulitple to multiple relationship (in other words, subject_id can be associated with many source_ids, and source_id can be associated with many subject_ids (just no duplicate combinations; hence the dual primary key).

 

Basically I need order_by to be associated with a combination of subject_id and source_id.  All of the source_ids on a given page are associated with just one subject_id (taken from the GET, as mentioned before).   

 

I've attached the look of the form.  The sources shown on the page are all associated with (for example) subject_id = 3, each has a separate source id (example: source_ids equal to 18, 19, 47, etc).

 

Basically what I need is to grab the combination of subject_id (from the GET), the source_id (generated from a while query), and then update the order_by associated with those two rows.

 

The data would look like this:

subject_idorder_bysource_id

3618

3519

3437

This is why I'm calling both variables in the query:

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

 

Any other suggestions?

[nomis]

Okay, I think I got a better understanding of the above comments... I removed the foreach query from the while loop, and though it is correctly grabbing the order_by, it's only inserting the last instance of the source_id, so each one seems to be overwriting the last.  I need to keep each source id separate.

 

The while loop still exists, because that's how I get the rows from the database.  Here's my entire code if that helps (I have a bunch of extra "echo" lines in there to see what's happening with the data).

 

<?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'];

//get the proxy url  
$qprox = "select proxy_url from proxy";
$rq    = mysqli_query($dbc, $qprox)
	or die ('Cant get proxy');


while ($row = mysqli_fetch_array($rq)) 


{
$proxy = $row['proxy_url'];

} 
if (isset($_POST['submit']))  {

	//get variables, and assign order


$order_by = $_POST['order_by'];
$source_id = $_POST['source_id'];
//$subject_id = $_GET['subject_id'];

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

	 foreach ($_POST['order_by'] as $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 . ', ' . $order_by . ', ' . $source_id;
		echo '<br />';
		}  
}	
while($row = mysqli_fetch_array($rs)) {

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

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


<?php
//  check for proxy indicator, and if so provide proxy prepend to url
// add order_by indicator box to both




echo $row['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="'. $proxy . $row['url']  .'" target="_blank">'. $row['source_name'] . '</a> <br />';



}
?>



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

 

I'm not sure how to keep the source_ids separate outside of the while loop... 

[nomis]

Is there a way of doing something like this?  (yes I know there's a syntax issue here, it doesn't like the T_BOOLEAN_AND)

 

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

			$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 . ', ' . $order_by . ', ' . $source_id;
		echo '<br />';
		}  

[nomis]

Okay, still working on this and no further along.

 

There has to be a way of getting multiple rows from a table, and then updating each different row with a new value (not the same value).

 

Should I be using a "for" loop?  I'm not exactly sure how to handle that;  I've been googling like crazy and have found other people with similar questions, but no clear answers.

 

I know this has been done before;  I've seen it!  I keep trying variations of the above, but it just keeps either giving me "array" as a result, running through each source_id, and updating everything with only the last entry.  :-\

[nomis]

Okay, I can see what is happening... I just don't know to make it stop.  What it is doing (as I echo the results), is that it only updates the very last $source_id, but foreach $order_by, and therefor it only updates the very last entry.

 

Any ideas how to make it grab the correct $source_id?  In other words, I need $source_id to be correctly paired with its corresponding $order_by.  Why is this so difficult?  I can see that source_id is definitely coming through in the while statement, but it doesn't get carried to the foreach... it only gets the last row.  I can't stick the foreach inside the while loop; that just does it for each variable.

 

here's the code again.   

$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>

[nomis]

Okay, managed to figure this out with a bit of help elsewhere.

 

If anyone is curious, the row in the form was changed to:

 

<input type="text" name="order_by['. $row['source_id'] . ']" size="1" value="'. $order_by .'"/>

 

and the foreach was changed to this:

foreach ($_POST['order_by'] as $source_id => $order_by)

 

[blurFromMalaysia]

 

nomis can u send the whole code to me