best way to approach this?

[roseplant]

I'm quite inexperienced in mysql so this one has me stumped. The mission, should you care to accept it, is as follows:

A database of, oh lets be classical and say, 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.

Now how on Earth do I code this??? Is this even the right structure to use here? Any help with the queries would be appreciated!

[Roger_Ramjet]

Yes, that is the standard solution. You need to use JOIN the tables to get the data you want. Pretty straightforward, all you need is INNER JOINS. Do not take any notice of the examples in the manual that use a comma; NEVER use a join like that unless want the wrong results. Just write out the full join syntax and keep all of your join conditions out of the WHERE clause and you will not go far wrong.

Post again when you get stuck, but give it a try first.

[roseplant]

OK got that covered. I can now list what attributes the widget has.

But now it's crunch time. I'm in the admin page. I must print a list of widget attributes with checkboxes next to them, and the box is checked if the widget in question has that particular attribute.

I gave it a shot but can't see how it could be done without several queries.

edit: And of course I'll need to submit the changes too. I'm stumped.

[barand]

You will need a LEFT JOIN or you wont pick up attributes that aren't currently set.

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

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);
}

Gives-->

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

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

[roseplant]

Sorry for the late reply - we have an extended paddy's day here in Ireland
😉 😕 :queasy: :xbones:

That solution worked a charm , thanks a lot guys! I would never have thought of doing it that way, don't want to sound cheesy but it opened my eyes to other possibilities of DB queries.