join two tables, and loop the results to display checked check boxes... david powers please?

[Guest]

Summary

two tables:

table 1 has all of the categories

table 2 has all the categories that someone has selected

HTML form that updates user data:

form needs to display all the category options dynamically from table 1

and also display the category checkbox as being checked dynamically if the person has selected it per table 2

The Nuts and Bolts

Attempt 1

my sql:

SELECT cat.id, cat.category, mcat.category as mcategory

FROM categories cat, members_categories mcat

WHERE mcat.member_id = '".$_GET['id']."'

ORDER BY category ASC

(run on it's own, all of the categories from table 1 being displayed twice, and the results from table 2 being a total cluster.)

form area with checkboxes:

<?php do { ?>

<input <?php if (!(strcmp($row_rsCategories['mcategory'],$row_rsCategories['id']))) {echo "checked=\"checked\"";} ?> name="category[]" type="checkbox" value="<?php echo $row_rsCategories['id'];?>" /> <?php echo $row_rsCategories['category'];?> <br />

<?php } while ($row_rsCategories = mysql_fetch_assoc($rsCategories)); ?>

what i have here results in:

all of the categories being displayed twice with the categories selected from table 2 checked = per the sql results when run alone

Attempt 2

my sql:

SELECT cat.id, cat.category, mcat.category as mcategory

FROM categories cat

LEFT JOIN members_categories mcat ON mcat.category = cat.id

WHERE mcat.member_id = '".$_GET['id']."'

ORDER BY category ASC

(run on it's own, all of the categories from table 1 that have a match in table 2, and none of the others from table 1 displayed)

form area with checkboxes:

same as first attempt

what i have here results in:

all of the categories selected from table 2 displayed and checked, and none of the other choices from table 1 = per the sql results when run alone

Attempt 3

my sql:

SELECT cat.id, cat.category, mcat.category as mcategory

FROM categories cat, members_categories mcat

WHERE mcat.member_id = '".$_GET['id']."'

GROUP BY cat.id

ORDER BY category ASC

(run on its own, all of the category choices from table 1 are displayed, however, the column mcategory is displaying the lowest numbered category (the same one) against all of those options rather then match them up.)

form area with checkboxes:

same as first attempt

what i have here results in:

all of the category choices from table 1 are displayed, however, only the first category from table 2 (which in this case is the lowest number) is marked as checked with the checkboxe, even if there is more then one category in table 2 =   per the sql results when run alone

so, can someone help?  i'm just banging my head against the wall here missing something that must be obvious because the sql isn't giving me what i need to work with the looped form area.  perhaps a different approach needs to be taken and i change the loop as well, but i think this can be resolved with the sql.  thanks in advance.

[jon8 · Accepted solution]

well, you can use two seperate sql queries...

one with the category names

and the second nested with the loop which references the category id from table 1 to pair it up with table 2