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?

Okay, I realize I was going about this the wrong way... I was under the assumption that it would always be erring for the same reason which, even though I may have been right, is a bad method for going about this... (even if it is possible). I ended up handling duplicate entries before the insert (which makes more sense): foreach ($_POST['site_id'] as $site_id) { $personsite ="select s.site_name ". "from site s, person_site ps " . "where s.site_id = ps.site_id " . "and ps.person_id = '$person_id'" ; $result = mysqli_query($dbc, $personsite) ; $exist = "SELECT * FROM person_site WHERE person_id= $person_id and site_id = $site_id "; $rexist = mysqli_query($dbc, $exist) ; if (mysqli_num_rows($rexist) != 0 ) { echo 'this person already exists in: <br /> <br /> '; while ($rr = mysqli_fetch_array($result)) { echo $rr['site_name'] . '<br />'; } } else { $ptquery = "INSERT into person_site (person_id, site_id)". "VALUES ('$person_id', '$site_id')" ; mysqli_query($dbc, $ptquery) or die ('Error: '.mysqli_error($dbc)); } }

Hi, this is a portion of a script which will associate individuals with specific sites, by use of a link table. I have the script working well, but I'm trying to make the error screen more readable to end-users. I'm wondering if I can get the results entered (from a checkbox) reported in the error message, but keep getting a parse error. Is it possible to run a while loop inside the "die" condition? $personsite ="select s.site_name ". "from site s, person_site ps " . "where s.site_id = ps.site_id " . "and ps.person_id = '$person_id'" ; $result = mysqli_query($dbc, $personsite) ; foreach ($_POST['site_id'] as $site_id) { $ptquery = "INSERT into person_site (person_id, site_id)". "VALUES ('$person_id', '$site_id')" ; mysqli_query($dbc, $ptquery) or die ('<div class="error">Error: ' . $site_id . ' '.$person_id . ' One or more of those sites has already been selected for this person. </div> <p>Please click your back button and verify your entries.</p> <p>' . while($rr = mysqli_fetch_array($result)) { echo $rr['site_name']; echo '<br />' . } .'</p>'); //or die ('Error: '.mysqli_error($dbc)); } If I remove this piece, it works fine; it's bringing back $site_id and $person_id fine. . while($rr = mysqli_fetch_array($result)) { echo $rr['site_name']; echo '<br />' . } .'</p>' Is there something wrong with my syntax, or with my logic, or is this simply not possible this way? The error is right on the line where the while statement starts. Thanks!

Got it... thanks! (of course it had to do with it being in an array!) much appreciated

Hi, I'm sorry if this has been answered elsewhere, but I'm having some trouble keeping some checkboxes that were called as an array to stay sticky if the form errs on other required fields. Here's what I have have: <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> <table width=500 border=0 cellpadding=5> <tr> <td valign="top"><label for "person_type_code">Person Type:</label> </td> <td><?php $r = mysqli_query($dbc, 'select * from person_type order by person_type'); while ($row = mysqli_fetch_array($r)) { echo '<input type="checkbox" id="person_type" name="person_type_code[]" ' ; echo 'value="' . $row[0] .'"'; if (isset($_POST['person_type_code'])) { echo ' checked="checked"'; } echo '> ' . $row[1] . '<br />'; } ?> </td> This is unfortunately making EVERY checkbox show as checked, not just the ones that the user has selected. I need this form to come back with only the fields that a person has selected (one or more... a person may have many types) to appear checked. Can someone see what is wrong with my code? I have tried using $row in place of $_POST['person_type_code'] but it has the same effect. Thanks!

Aha! that makes sense. After a few edits and trims added in a few places, it is working perfectly. Thanks a ton!

I should add that the form is echoing the right values properly (blank if NULL, the value if a value is entered), it's just submitting NULL no matter what

Okay, I fixed the query back to what it should be (which is what I expected). Then I removed my earlier NULL check, and entered in if ( strlen( trim($middle_name) )) { $middle_name = 'NULL'; // Set middle_name to the string NULL, which MySQL will insert as the value NULL because it is not wrapped in single quotes }else{ $middle_name = "'" . mysqli_real_escape_string( $dbc,$middle_name ) . "'"; } When entering nothing in the $middle_name, I got these results: $first_name: string(4) "John" $last_name: string(3) "Doe" $middle_name: string(4) "NULL" You entered: First name: John Last name: Doe Middle name: NULL However, even though the string length appears as 4, it really did insert a null into the tables (which is good). Unfortunately, when I entered a name into the middle name field, I got exactly the same results; the script seems to be interpreting anything in the $middle_name field as null. I did enter $middle_name = $_POST['middle_name']; in the same routine that echoes the form, but it seemed to make no difference... it outputs a null no matter what I do... what am I doing wrong? I did also check to see if there were magic quotes turned on, using echo get_magic_quotes_gpc() and it returned a "1," so they ARE apparently on... would that make the difference? I would have thought that this would have prevented NULLs from going in properly, but not from submitting actual values. Thanks!

I do understand the difference between a null string and a null value; that's why I've been trying to get this straightened out; I really want real nulls to appear in the table if nothing is entered in the non-required fields. Here's what I tried if (empty($_POST['middle_name'])) { $middle_name = "" ; } else { $middle_name = "'$middle_name'" ; } And purposely did not enter a required field and got this output. The results are got were: $first_name: string(0) "" $last_name: string(5) "Smith" $middle_name: string(0) "" which should be good... I'm getting a string length of zero for $middle_name. Then I add in a first name and submit. It first gives this SQL error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 Here's the query line: $query = "INSERT into person (last_name, first_name, middle_name)" . "VALUES ('$person_type','$last_name','$first_name',$middle_name)" ; I'm familiar with this problem, because I've been fighting with this for a while, so I add quotes back around $middle_name to be this: $query = "INSERT into person (last_name, first_name, middle_name)" . "VALUES ('$last_name','$first_name','$middle_name')" ; and script works as suspected, but instead of inserting a NULL value into the person for $middle_name, it inserts a blank space, which was the initial problem. I also tried getting the var_dump information with a succesful input and got these results (in blue) $first_name: string(4) "John" $last_name: string(5) "Smith" $middle_name: string(0) "" You entered: First name: John Last name: Smith Middle name: Though Middle name appears blank, it is actually in the database as " " (a blank space) The form field has this: <input type="text" id="middle_name" name="middle_name" value="<?php echo trim($middle_name) ; ?>" /> The way to get a true null in the database seems to be change the query to take $middle_name instead of '$middle_name', but the only way I can seem to make this work is putting the word "NULL" instead of the "" in the conditional. Then of course, I end up with a string of 4, which is NOT what I want, because that will echo the word "NULL" into the input field if there is an error, which again will then go into the database as the text string "NULL" I'm stuck in a loop still

Okay, I spoke to soon. of course,what I want instead is <?php if (!empty($middle_name)) echo $middle_name; ?> And of course, I'm talking about two scripts instead of one (ARGH... don't kill me please). One is the data entry form (person.php), and the other is the edit form (edit.php). They use almost identical code. The difference is this: person.php (the one I showed you)- checks for valid data, if not, it returns the form with the items that had been entered pre-filled. When doing so, all the required fields report fine (because I have not needed to run a validation check on these for nulls). If one of the required fields is not included AND middle_name is NOT filled out, it brings back the form, and the word NULL appears in the form under middle_name. The user may then correct this, submit, and then the word "NULL" is entered into the database. edit.php - looks exactly like the person.php field, but retrieves its values using GET from a separate page. On first click, this form correctly brings in empty fields for values that are not entered, because if there really is a null in the database (the ppmanage.php form does a select on the database to get all values, then transmits these values into an HTML link which appends the data; this script works with no issues). However, if someone submits this form a second time, the true nulls (the empty values) get converted into "NULL" because of the same null control being done on the data (as shown earlier, with either the version I did or the one suggested by roopurt1). I know I can change the edit.php script to only allow one data change (which is at least a partial solution, if not ideal, and someone could still find a way to get bad data into the database, simply by deleting required fields and getting an error). Anyway, my real question is whether or not there is a function in php to assign an EMPTY value to a text field. In other words, I can use if(empty()) to determine if a field is empty... is there a way to do this backwards, and assign a variable or at least the contents of an HTML field to be truly empty? I'm thinking I can use unset to remove the word "NULL", but I know this is bad syntax, but something like this: if ($middle_name == 'null') $middle_name = unset($middle_name); I tried this and it didn't err, but of course it didn't work either.

Okay, I finally figured it out. It had nothing to do with the way we were assigning variables but did have to do at least partially with the verification loop. I started over and relooked at my earliest assumptions and discovered it was the way that the data was being echoed back to the form if there was an error. The wasn't checking to see whether a value was equal to NULL, but whether there was anything in the value field at all. This is what worked: <input type="text" id="middle_name" name="middle_name" value="<?php if (empty($middle_name)) echo $middle_name; else echo null; ?>" /> I still thank you for all your help and I learned a few new things in the process.