Jump to content

Archived

This topic is now archived and is closed to further replies.

roseplant

best way to approach this?

Recommended Posts

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)?

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.