Jump to content


Photo

best way to approach this?


  • Please log in to reply
3 replies to this topic

#1 roseplant

roseplant
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 16 March 2006 - 06:38 PM

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

#2 Steveo31

Steveo31
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationSan Jose, CA

Posted 16 March 2006 - 08:11 PM

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

#3 roseplant

roseplant
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 16 March 2006 - 08:57 PM

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

$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')"
        . " )";

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!

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 16 March 2006 - 08:59 PM

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

Outputs -->

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

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users