I did something similar (although in Java, not PHP) a while back.
Rather than trying to do the processing in the code, I did some facy SQL
work.
How it worked was by creating a join between the master list of items and
table of selected items - placing a new column with a 1 or 0 value
indicating which table it is in.
Then the code was pretty straght-forward, render everything & set to checked
where value = 1.
Unfourtunately, I don't have my source anymore (it was for a prior empoyer)
but I don't think it should be too hard to work up again if you're not a
facy SQL kinda guy =).

Andrew

Andrew Forman
University of Iowa Libraries
ISST Development
319 335 9152

-----Original Message-----
From: Code for Libraries [mailto:[log in to unmask]] On Behalf Of
Andrew Darby
Sent: Friday, September 23, 2005 9:27 AM
To: [log in to unmask]
Subject: [CODE4LIB] PHP MySQL question

PHP people, I fall upon your mercy!  I'm trying to do something which
shouldn't be so hard:  to allow a user to edit an existing entry to a FAQ
database, I want to query the table of subjects to generate tick boxes for
all subjects, and query the faq_subjects intervening table to determine
which subjects are associated with that FAQ, and then combine them into one
long list of checkboxes, with the already selected subjects CHECKED.

So, after spending way too much time on it, I made it work using is_array to
compare the two arrays of results, only to discover, when putting it on my
live server, that is_array doesn't accept an array for the second value in
MySQL versions < 4.2.

So, can anyone suggest another way to do this?  The code using is_array is
below.  Any help would be greatly appreciated; i'm going around in circles
on this, and as a one man shop, i've got no one to bounce this off of
[insert frowny face here].

Thanks,

Andrew

p.s. if this sort of nuts and bolts question is inappropriate for this list,
please let me know!

------- Used to Work PHP Code --------

//selected_subjects_query determines which subjects should be pre-ticked.

$selected_subjects_query = "SELECT faq.faq_id, faq_supersubs.supersubs_id
FROM faq, faq_supersubs WHERE faq.faq_id = faq_supersubs.faq_id AND
faq.faq_id = '$this_id'";

// this will create an array of all items already checked

$selected_subjects_result = MYSQL_QUERY($selected_subjects_query);
$checked_ones = "";
while($myrow =  mysql_fetch_array($selected_subjects_result)) {
$checked_ones[] .= $myrow['1']; }

$full_query = "SELECT supersub_id, supersubject FROM supersubs ORDER BY
supersubject";

/* Select all active records (this is based on a db connection made above)*/

$full_result = MYSQL_QUERY($full_query);

// create the checkboxes

while($myrow =  mysql_fetch_array($full_result)) {
        $sub_id = $myrow["0"];
        $sub_name = $myrow["1"];

if (!in_array($sub_id, $checked_ones)) {

        $checkboxes .= "<input name=\"subject[]\" type=\"checkbox\"
value=\"$sub_id\">$sub_name</input><br />";

} else {

        $checkboxes .= "<input name=\"subject[]\" type=\"checkbox\"
value=\"$sub_id\" CHECKED>$sub_name</input><br />";

}

}