nomis Posted March 22, 2012 Share Posted March 22, 2012 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 Link to comment https://forums.phpfreaks.com/topic/259519-using-foreach-array-to-update-multiple-data-rows-with-different-data/ Share on other sites More sharing options...
nomis Posted March 23, 2012 Author Share Posted March 23, 2012 Is foreach the wrong approach? Link to comment https://forums.phpfreaks.com/topic/259519-using-foreach-array-to-update-multiple-data-rows-with-different-data/#findComment-1330374 Share on other sites More sharing options...
AyKay47 Posted March 23, 2012 Share Posted March 23, 2012 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. } Link to comment https://forums.phpfreaks.com/topic/259519-using-foreach-array-to-update-multiple-data-rows-with-different-data/#findComment-1330392 Share on other sites More sharing options...
nomis Posted March 23, 2012 Author Share Posted March 23, 2012 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? Link to comment https://forums.phpfreaks.com/topic/259519-using-foreach-array-to-update-multiple-data-rows-with-different-data/#findComment-1330489 Share on other sites More sharing options...
nomis Posted March 23, 2012 Author Share Posted March 23, 2012 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... Link to comment https://forums.phpfreaks.com/topic/259519-using-foreach-array-to-update-multiple-data-rows-with-different-data/#findComment-1330506 Share on other sites More sharing options...
nomis Posted March 23, 2012 Author Share Posted March 23, 2012 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 />'; } Link to comment https://forums.phpfreaks.com/topic/259519-using-foreach-array-to-update-multiple-data-rows-with-different-data/#findComment-1330512 Share on other sites More sharing options...
nomis Posted March 26, 2012 Author Share Posted March 26, 2012 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. :-\ Link to comment https://forums.phpfreaks.com/topic/259519-using-foreach-array-to-update-multiple-data-rows-with-different-data/#findComment-1331270 Share on other sites More sharing options...
nomis Posted March 26, 2012 Author Share Posted March 26, 2012 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> Link to comment https://forums.phpfreaks.com/topic/259519-using-foreach-array-to-update-multiple-data-rows-with-different-data/#findComment-1331291 Share on other sites More sharing options...
nomis Posted March 26, 2012 Author Share Posted March 26, 2012 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) Link to comment https://forums.phpfreaks.com/topic/259519-using-foreach-array-to-update-multiple-data-rows-with-different-data/#findComment-1331333 Share on other sites More sharing options...
blurFromMalaysia Posted July 17, 2013 Share Posted July 17, 2013 nomis can u send the whole code to me Link to comment https://forums.phpfreaks.com/topic/259519-using-foreach-array-to-update-multiple-data-rows-with-different-data/#findComment-1440993 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.