Print

Print


Andrew,

Thanks so much!  If I were at Villanova, you would see me out your
office window running towards you with a big bouquet of flowers . . . .

The only little tweak to your code was adding the appropriate faq_id to
the $faq_result query, so it gets the ticked items for that record, not
for all records that have a ticked item.  (I've stuck the modified code
below, if anyone is interested.)

Anyway, thanks a million.  And to Andrew Forman for his suggestion.  (I
was already looking into this, but couldn't quite figure out the SQL,
and wasn't sure if it was a dead end or not.)

Andrew

---- Andrew Nagy's Code Slightly Modified --------

// $this_id is the id of the appropriate record
$full_result = mysql_query("SELECT supersub_id, supersubject FROM
supersubs ORDER BY supersubject");
while ($supersub = mysql_fetch_assoc($full_result)) {
    $faq_result = mysql_query("SELECT faq_id FROM faq_supersubs WHERE
supersubs_id = '" . $supersub['supersub_id'] ."' AND faq_id = '$this_id'");
    if (mysql_num_rows($faq_result)) {
       $checkboxes .= '<input type="checkbox" name="subject[]" value="' .
$supersub['supersub_id']. '" CHECKED> ' . $supersub['supersubject'] .
"<br />";
    } else {
        $checkboxes .= '<input type="checkbox" name="subject[]" value="' .
$supersub['supersub_id']. '"> ' . $supersub['supersubject'] . "<br />";
    }
}

Andrew Nagy wrote:
> Andrew, I made a few adjustments to your script, this should do what you
> want.  It appears that you had a mixup in your selected subjects query.
> Although, this may be a little slow because it makes a query after every
> subject to see if it is checked.
>
> $full_result = mysql_query("SELECT supersub_id, supersubject FROM
> supersubs ORDER BY supersubject");
> while ($supersub = mysql_fetch_assoc($full_result)) {
>    $faq_result = mysql_query("SELECT faq_id FROM faq_supersubs WHERE
> supersub_id = '" . $supersub['supersub_id'] ."'");
>    if (mysql_num_rows($faq_result)) {
>        echo '<input type="checkbox" name="id" value="' .
> $supersub['supersub_id']. '" CHECKED> ' . $supersub['supersubject'];
>    } else {
>        echo '<input type="checkbox" name="id" value="' .
> $supersub['supersub_id']. '"> ' . $supersub['supersubject'];
>    }
> }
>
> A much more efficient way would to do some sort of outer join in order
> to get a field that acts as a switch for checking if the checkbox should
> be checked or not.
>
> Andrew Darby wrote:
>
>> 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 />";
>>
>> }
>>
>> }