[SOLVED] PHP MySQL - explode, while, foreach issue

I'm relatively new to PHP/MySQL, so it's possible that I'm missing something pretty simple.  The short story is that I have two databases - one contains about 50 items that are outputted as a list of checkboxes in a form.  Since the checkboxes are displayed dynamically as part of a query, the value of the checkbox is the ID of that option in the database.  The user checks the boxes that correspond to what they want and those values are compiled using implode and stored in the second database (one that has the person's name and the list of items they checked).  The reason I do this is because that list of 50 items will change frequently, and they may increase or decrease in number.  If I gave each item it's own field in the second database, I'd have to add/decrease fields on a regular basis.  I couldn't come up with a more dynamic or maintenance-free solution, so this is what I've got.  Anyway, if they want to update their list, I have them go to a page that dynamically generates the list and checks the boxes they've already selected.  To do this, I use this code:

$query_checkboxlist = "SELECT ID, TYPE, VALUE FROM table1 WHERE TYPE='typeofbox'";
$result_checkboxlist = mysql_query($query_checkboxlist);
$query = "SELECT ID, CHECKBOXES FROM table2 WHERE ID=4";
$result = mysql_query($query);
echo "<ul>\n";
while($row_checkboxlist = mysql_fetch_array($result_checkboxlist)){
while($row = mysql_fetch_assoc($result)) {
	$markedboxes = explode(",",$row['CHECKBOXES']);
	foreach($markedboxes as $key=>$value) {
		echo '<li><input type="checkbox" name="involvement[]" value="'.$row['ID'].'" ';
		if($row["ID"] == $value) {
			echo 'checked="yes" ';
		}
		echo "/>".$row['VALUE']."</li>\n";
	}
}
}
echo "</ul>\n";

ID=4 is just random - I chose some test data that was populated.  The code processes without errors, but it's only applying the checked="yes" to one item, not all the ones that it should be.  What am I missing?  I can provide more code if necessary - also, if there's something blatantly wrong in what I posted, it might be because I changed some values to have it make sense to you guys.

Thanks!

I don't see an obvious problem. Try this

$query_checkboxlist = "SELECT ID, TYPE, VALUE FROM table1 WHERE TYPE='typeofbox'";
$result_checkboxlist = mysql_query($query_checkboxlist);
$query = "SELECT ID, CHECKBOXES FROM table2 WHERE ID=4";
$result = mysql_query($query);
echo "<ul>\n";
while($row_checkboxlist = mysql_fetch_array($result_checkboxlist)){
   while($row = mysql_fetch_assoc($result)) {
      $markedboxes = explode(",",$row['CHECKBOXES']);
      foreach($markedboxes as $key=>$value) {

         echo '<li><input type="checkbox" name="involvement[]" value="'.$row['ID'].'" ';
         $checked=""; 
         if($row["ID"] == $value) {
          $checked="checked='yes'"; 
         }
         echo $checked."/>".$row['VALUE']."</li>\n";
      }
   }
}
echo "</ul>\n";

just guessing about your table structure here, is `ID` your table index ?

If that is the case, then this will only ever match one item

        if($row["ID"] == $value) {

          $checked="checked='yes'";

        }

^^ That didn't seem to do anything :-\

just guessing about your table structure here, is `ID` your table index ?

Yes...I set up all of my tables the same way (which, perhaps, I shouldn't) - ID is the auto incremented index field.

If that is the case, then this will only ever match one item

That's true, but I think that's what I want it to do.  Since it's part of a foreach loop, shouldn't it be going through the exploded string and comparing the ID of the checkbox item (found in table1, where there's a list of all the items) and the value in the string from table2 (where it was stored when they first selected all of their options)?  Does that make sense?  Maybe I'm making this more complex than it should be, but it makes sense in my head (and it seems to be the most maintenance-free way of doing this).

First checked="yes" is incorrect - you should be using checked="checked"

Second, I think the logic is falling over in the way you're storing the checkbox data in table 2 (can we see some test data please?) and how you're re-checking them in your loop.

         if($row["ID"] == $value) {
          $checked='checked="checked"';
         }

Okay, table1 looks like this (although it's much bigger):

+----+------+-------+
| ID | TYPE | VALUE |
+----+------+-------+
| 1  | foo1 | green |
| 2  | foo1 | brown |
| 3  | foo1 | white |
| 4  | foo2 | blue  |
| 5  | foo2 | pink  |
| 6  | foo3 |  red  |
+----+------+-------+

and table2 looks like this:

+----+------------+
| ID | CHECKBOXES |
+----+------------+
| 1  |    1,3,5   |
| 2  |    2,4,6   |
| 3  |   1,3,4,6  |
| 4  |     5,6    |
+----+------------+

Does that help?  The goal is to dynamically generate a list of checkboxes from table1 on the first page.  The user selects whichever boxes apply to them and hits submit.  Their checkbox selections are imploded into a string separate by commas and are then entered into table2.  When I pull the record from table2 for display, I want to show ALL of the checkbox options listed table1, but make sure that the ones that have already been selected are still marked (using explode).  That way, if the options in table1 have changed, bringing up the record will show the new options and the selections from before are still marked.  They can then change their selections (uncheck old ones, check new ones, whatever) and hit submit, where the selections will be imploded and stored.  Make sense?  Suggestions?

You'd be better of using an SQL JOIN here.

<?php

/* FOR QUERY SEE BELOW */

$result = mysql_query($query);

echo "<ul>\n";
while($row = mysql_fetch_assoc($result))
{
    $checked = (in_array($row['id'], explode(',', $row['selected']))) ? 'checked="checked"' : '';

    echo '<li><input type="checkbox" name="involvement[]" value="'.$row['id'].'" '.$checked.'/>'.$row['value']."</li>\n";
}

echo "</ul>\n";

?>

Code is untested tho

EDIT Sorry the query should of been

$query = "SELECT t1.ID as id,
                 t1.TYPE as type,
                 t1.VALUE as value,
                 t2.CHECKBOXES as selected
          FROM table1 as t1
          JOIN table2 as t2 ON t2.TYPE = t1.TYPE
          WHERE t1.TYPE='foo1'";

You'll now need to setup your table2 table as

+----+------------+
| ID | TYPE | VALUE |
+----+------------+
| 1  | foo1 | 1,3,5   |
+----+------------+

^ That won't work, though.  I simplified things quite a bit, but maybe I should have included more information.  In table2, "ID" is essentially a unique identifier for a single user.  Each user has multiple checkbox selection fields.  See the example below:

+----+-----------+-----------+
| ID | BOXGROUP1 | BOXGROUP2 |
+----+-----------+-----------+
| 1  |   1,3,5   |   7,8,9   |
| 2  |   2,4,6   |    7,8    |
| 3  |  1,3,4,6  |     9     |
| 4  |    5,6    |   7,8,9   |
+----+-----------+-----------+

So, let's say that BOXGROUP1 is a list of pets and you select which ones you own.  BOXGROUP2 is a list of states and you check how many you've lived in.  There are dozens more like this - so ID #1 is one user with all of their information, ID #2 is another person, etc.  Again, I chose the implode/explode method because the number of items stored in table1 will change on a pretty regular basis - some might be removed, some might be added.  Is that a bit more helpful?

 

and table2 looks like this:

+----+------------+
| ID | CHECKBOXES |
+----+------------+
| 1  |    1,3,5   |
| 2  |    2,4,6   |
| 3  |   1,3,4,6  |
| 4  |     5,6    |
+----+------------+

Maybe its just me, but isn't that table setup wrong?

It might be - I'm pretty new to this.  I know that, generally, storing data the way I have (using commas a separators for independent and unrelated values) is considered poor structure.  But I can't think of a better way due to the dynamic and constantly-changing nature of the data.

I'm happy to entertain any suggestions and I appreciate any help!

It might be - I'm pretty new to this.  I know that, generally, storing data the way I have (using commas a separators for independent and unrelated values) is considered poor structure.  But I can't think of a better way due to the dynamic and constantly-changing nature of the data.

 

I'm happy to entertain any suggestions and I appreciate any help!

what are you storing and what are the rules?

I see different types of foo and each foo has a color and an ID....

what are the rules?

^ I don't understand what you mean by "rules".  Let me outline how the page is setup and how the tables relate to each other and maybe that'll make it more clear.  Hopefully I can better explain what I'm trying to do.

Okay, so table1 looks like this, except that there's a lot more data in it:

+----+------+-------+
| ID | TYPE | VALUE |
+----+------+-------+
| 1  | foo1 | green |
| 2  | foo1 | brown |
| 3  | foo1 | white |
| 4  | foo2 | apple |
| 5  | foo2 | grape |
+----+------+-------+

On page select.php, the user checks the boxes that match their favorite color or whatever (this is just an example, the data isn't actually colors, but you get the idea).  I dynamically generate all of the checkboxes using this script on select.php:

<ul>
<?php
    $query = "SELECT ID, TYPE, VALUE FROM table1";
    $result = mysql_query($query);
    while($row = mysql_fetch_array($result)) {
        echo '<li><input type="checkbox" name="'.$row["TYPE"].'[]" value="'.$row["ID"].'" />'.$row["VALUE"].'</li>';
    }
?>
</ul>

What you SHOULD see as the source of that page after it's processed is:

<ul>
    <li><input type="checkbox" name="foo1[]" value="1" />green</li>
    <li><input type="checkbox" name="foo1[]" value="2" />brown</li>
    <li><input type="checkbox" name="foo1[]" value="3" />white</li>
    <li><input type="checkbox" name="foo2[]" value="4" />apple</li>
    <li><input type="checkbox" name="foo2[]" value="5" />grape</li>
</ul>

The user then checks whichever boxes they want.  When they submit the form, it passes the values into table2 using this code:

$foo1 = implode(",",$_POST['foo1']);
$foo2 = implode(",",$_POST['foo2']);
$query = "INSERT INTO table2 (FOO1, FOO2) VALUES ('$foo1','$foo2')";
mysql_query($query);

Now table2 should look like this (I'm going to pretend that they checked ALL of the boxes):

+----+-------+------+
| ID |  FOO1 | FOO2 |
+----+-------+------+
| 1  | 1,2,3 |  4,5 |
+----+-------+------+

If another user comes along and submits the form, table2 might look like this:

+----+-------+------+
| ID |  FOO1 | FOO2 |
+----+-------+------+
| 1  | 1,2,3 |  4,5 |
| 2  |  2,3  |   5  |
+----+-------+------+

Make sense?  Now, in my REAL table, I have more identifying information than just an auto-incremented ID, but I'm trying to simplify.  Okay, so let's say we add another option to table1 and it now looks like this:

+----+------+-------+
| ID | TYPE | VALUE |
+----+------+-------+
| 1  | foo1 | green |
| 2  | foo1 | brown |
| 3  | foo1 | white |
| 4  | foo2 | apple |
| 5  | foo2 | grape |
| 6  | foo2 | kiwis |
+----+------+-------+

The next step is that when the first user (ID #1) comes back and wants to see what s/he checked, I want to 1.) display ALL of the options (including the new one) found in table1 and 2.) make sure that the options they've already checked are STILL checked.  I want the new code, after processing, to look like this:

<ul>
    <li><input type="checkbox" name="foo1[]" value="1" checked="checked" />green</li>
    <li><input type="checkbox" name="foo1[]" value="2" checked="checked" />brown</li>
    <li><input type="checkbox" name="foo1[]" value="3" checked="checked" />white</li>
    <li><input type="checkbox" name="foo2[]" value="4" checked="checked" />apple</li>
    <li><input type="checkbox" name="foo2[]" value="5" checked="checked" />grape</li>
    <li><input type="checkbox" name="foo2[]" value="6" />kiwis</li>
</ul>

What I'm having trouble with is getting the PHP to compare the IDs in table1 with the imploded strings in table2 and then automatically apply the checked="checked" attribute dynamically to the generated code.  Does that make sense?  For example, using the generated HTML code above, the PHP would have compared the IDs in table1 and found that FOO1 field in table2 contains ID #1, so it applies the checked="checked" attribute when it generates that checkbox.

It makes sense in my head, and seems like it should work.  The code that I originally posted does actually work, but ONLY for one checkbox.  After that, it doesn't apply the attribute.  I really kind of like this solution, since it means I don't have to modify the code much if we add or remove items from table2.  The code should be compact, too.

Anyone?

try

$query_checkboxlist = "SELECT ID, TYPE, VALUE FROM table1 WHERE TYPE='typeofbox'";
$result_checkboxlist = mysql_query($query_checkboxlist);
//save checkbox in array
$ch = array();
while($r = mysql_fetch_assoc($result_checkboxlist)) $ch[$r['ID']]=$r['VALUE'];
$query = "SELECT ID, CHECKBOXES FROM table2 WHERE ID=4";
$result = mysql_query($query);

while($row = mysql_fetch_array($result)){ // browse users
   echo "<ul>\n";
   $markedboxes = explode(",",$row['CHECKBOXES']);
   foreach($ch as $id => $value)) {
      if(in_array($id, $markedboxes)) $checked =' checked="checked"'; else $checked = ''; 
      echo '<li><input type="checkbox" name="involvement[".$row['ID']."]" value="'. $id.'"'. $checked. "/>".$value."</li>\n";
   }
   echo "</ul>\n";
}

^ I had to remove an extra parenthesis and swap some quotation marks around (not criticizing, just making notes for anyone who stumbles across this thread and uses your solution, the modified code is below)...but it works!  Thanks for your help...it does exactly what I want!  You rock!

$query_checkboxlist = "SELECT ID, TYPE, VALUE FROM table1 WHERE TYPE='typeofbox'";
$result_checkboxlist = mysql_query($query_checkboxlist);

//save checkbox in array
$ch = array();
while($r = mysql_fetch_assoc($result_checkboxlist)) {
    $ch[$r['ID']]=$r['VALUE'];
}

$query = "SELECT ID, CHECKBOXES FROM table2 WHERE ID=4";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) { // browse users
    echo "<ul>\n";
    $markedboxes = explode(",",$row['CHECKBOXES']);
    foreach($ch as $id => $value) {
        if(in_array($id, $markedboxes)) {
            $checked =' checked="checked"';
        }
        else {
            $checked = '';
        }
        echo '<li><input type="checkbox" name="involvement['.$row['ID'].']" value="'. $id.'"'. $checked. "/>".$value."</li>\n";
    }
    echo "</ul>\n";
}