best way to approach this?

[roseplant]

I'm quite inexperienced in mysql so this one has me stumped. I'll try to describe the situation.

I have a database of, you guessed it, Widgets.

Table : widgets.
Columns: widget_id,widget_name
Sample data:
1,SnazzyWidget
2,SleepyWidget

Table: widget_attributes
Columns: attribute_id, attribute_name
Sample data:
1, Can fly
2, Can talk
3, Multi-coloured
4, Executive class

Table: attribute_map
Columns: widget_id, attribute_jd
Sample data:
1,1
1,4
1,3
2,3


I hope you get the picture. attribute_map maps the two other tables together. A widget can have more than one attribute, as in the sample SnazzyWidget can fly, is multi-coloured and is Executive class.

So in the widget product page I want to print a list of all the attributes the particular widget has according to the database. I can do this. So the next step is in the Admin pages, I want to print a list of checkboxes and the box is checked if the widget has that particular attribute.

EG. for Snazzywidget the output would be:
Can fly | CHECKED
Can talk | NOT CHECKED
Multi-coloured | CHECKED
Executive class | CHECKED

The user can then check or uncheck the boxes as needed and resubmit the form, updating the database.

Could someone tell me how I can code this (PHP + mysql)?

[Steveo31]

It's a very roundabout way of doing it... little confusing on this end. Can you write a psuedo-code query of what you are after? I get what you want, but it's just... not clicking :)

[roseplant]

Yeah, that's the problem I'm having too :). I don't know where to start the code.

This is what I have so far. This code returns a list of all the attributes of a particular widget ($widget_name).

[code]
$widget_name = 'Snazzy Widget';

    $attributes_query = 'SELECT h_attribute_name,h_attribute_id FROM h_attributes WHERE '
            . ' ('
            . ' (match_attributes.attribute_id = attributes.attribute_id)'
            . ' AND'
            . ' (match_attributes.widget_id = widgets.widget_id)'
            . ' AND'
            . " (widgets.widget_name = '$widget_name')"
        . " )";
[/code]

Now what I need to do is print a list of all possible attributes, with a checkbox next to each one. If $widget_name has an attribute the appropriate box(es) is checked.

The user can check/uncheck boxes and submit the form to change the widget's attributes.

Hope I've made myself clear!

[Barand]

Try this. Note the construction of the query inside the function.

[code]function listAttributes($id) {
            // Use LEFT JOIN so all attributes are listed.
            // Where there is no match in the map table for a widget
            //   the attribute_id from map table is null
         $str = '';
         $sql = "SELECT a.attribute_name, a.attribute_id, m.attribute_id
                 FROM widget_attributes a
                 LEFT JOIN attribute_map m
                 ON a.attribute_id = m.attribute_id
                    AND m.widget_id = '$id'
                 ORDER BY a.attribute_id";
         $res = mysql_query($sql) or die (mysql_error());
         while (list( $name, $aid, $mid) = mysql_fetch_row($res)) {
                   // set as checked where atribute is not null
                $chk = !is_null($mid) ? 'checked' : '';
                $str .= "<input type='checkbox' value='$aid' $chk> $name <br />";
         }
         return $str;
}

// List widgets and attributes

$sql = "SELECT widget_id, widget_name
        FROM widgets
        ORDER BY widget_name";
$res = mysql_query($sql) or die (mysql_error());

while (list ($id, $name)  = mysql_fetch_row($res)) {
       echo "<br /><b>$name</b><br />";
       echo listAttributes($id);
}[/code]

Outputs -->

[code]SleepyWidget
[ ] Can fly
[ ] Can talk
[x] Multi-coloured
[ ] Executive class

SnazzyWidget
[x] Can fly
[ ] Can talk
[x] Multi-coloured
[x] Executive class[/code]