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)

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>

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. :-\

sounds like you need to link up all the common fields in the two tables. Just joining them will give you cartesian results

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 />'; }

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

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?

Is foreach the wrong approach?

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

OMG, that was all it was! I'd been banging my head against the wall for too long on this one! Looks like I need to revisit my coding habits! Thanks!

Hi, I know this has to be possible but I have been unable to get this to work properly. I have a page with two forms. The first form is a list of sources associated with a subject, with checkboxes, so that it is possible to remove these associations, like so: $qs = "SELECT s.source_id, s.source_name 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'); while($row = mysqli_fetch_array($rs)) { echo '<input type="checkbox" value="' . $row['source_id'] . '" name="markdelete[]">' . $row['source_name'] . '<br />' ; } The second form is a list of sources that are not associated with this subject, with checkboxes enabling the addition of more sources to this subject, like so: $r = "SELECT source_id, source_name FROM source WHERE source_id NOT IN (select s.source_id from source s, source_subject ss where s.source_id = ss.source_id and ss.subject_id = '$subject_id')"; $r1 = mysqli_query($dbc, $r) or die ('Update Error: '.mysqli_error($dbc)); while ($row = mysqli_fetch_array($r1)) { echo '<input type="checkbox" id="source_id" name="source_id[]" ' ; echo 'value="'. $row['source_id'] .'"'; echo '> ' . $row['source_name'] . '<br />'; } The first form works fine... when the "remove" submit button is clicked, it successfully removes any selected sources and then shows the remaining sources still associated, and then automatically populates this source in the below list (form 2) as one that is not selected. On the second form, when adding a new source to the subject, it successfully enters the data into the database, and removes this source from the list of unselected sources, but it does not seem to re-populate in the first form. The data is correct on the tables, but the page needs to reload the first query... how do I get it to do this? I'd like to be able to make this page editable, and re-editable (in case someone makes a mistake) without having to go back and reload the entire page. Note, both forms call the page itself; not sure if this is part of it. Using this: <form method="POST" action="<?php echo $_SERVER['PHP_SELF'].'?subject_id='.$subject_id ; ?>"> Does anyone have any ideas?

your problem may exist in different part of your code... in my experience, the only thing that stops IE is some bad HTML syntax (e.g. an unclosed HTML tag).

Okay, I ended up taking an entirely different tack to my problem. I had managed to keep the messages from looping by taking portions out of the foreach, but was still failing at causing an error if more than one duplicate entry occurred (something very possible). I decided to rethink this, and realized that though validation may be useful, it may not be the most user-friendly option. Since I was already grabbing the choices from the database, and I already had the $person_id variable, it seemed to make more sense to eliminate the chance of error, and only show sites to which this person was not already associated in the first place, which was handled by way of a simple sql query. anyway, problem solved. If anyone's curious, this is what I used: <?php $siteinfo = "select site_id, site_name from site where site_id not in (select s.site_id from site s, person_site ps where ps.person_id = $person_id and s.site_id = ps.site_id) order by site_name"; $r = mysqli_query($dbc, $siteinfo ); while ($row = mysqli_fetch_array($r)) { // if (mysqli_fetch_array($rexist) != mysqli_fetch_array($r)) { echo '<input type="checkbox" id="site_id" name="site_id[]" ' ; echo 'value="' . $row[0] .'"'; echo '> ' . $row[1] . '<br />'; } ?>

this is still bugging me, and I haven't been able to come up with a solution I apologize if this is asking too much, but can anyone take a look at this and point me in the right direction for a logical way of handling this? Should I go back to trying to include a loop inside the "die" statement? thanks

okay, though the above code works, I have a minor problem which, though not crucial to the running of the application, produces very ugly results, which I'd like to avoid. What in this is that the data above is being grabbed from a series of checkboxes. It works and looks perfect if a person only adds ONE site (site_id) at a time, or selects ONE site_id which already exists. The problem is that because it is within a foreach loop, it 1. produces the exact same row for each duplicate entry for the error rows with if (mysqli_num_rows($rexist) != 0 ) 2. for the rows which are successfully inserted, it produces a confirmation row for each one, concatenating the list with each entry. The form runs fine, it's just that the visible output is ugly. I can't figure out a way around this, as I need the operations to occur within the foreach loop, otherwise the variables are not passed properly. Does anyone have any suggestions for how to limit the output statements to be only once for each of the two above circumstances?