need help with a sql query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
roseplant
Forum Newbie
Posts: 4
Joined: Thu Mar 16, 2006 10:23 am

need help with a sql query

Post by 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.

I am able to list what attributes each widget has on its relevant Widget Page. But 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. This is just beyond my level of SQL. Can anyone offer me a hint?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT `widget_attributes`.*, `attribute_map`.`widget_id`
FROM `widget_attributes`
LEFT JOIN `attribute_map`
ON `widget_attributes`.`attribute_id` = `attribute_map`.`attribute_id`
AND `attribute_map`.`widget_id` = '3'
gchrome
Forum Newbie
Posts: 3
Joined: Thu Mar 16, 2006 2:27 pm

Post by gchrome »

Sorry feyd gave an answer while I was writing that, feyd is much more efficient, but hopefully my code can give you an idea of how to display what you want.

Code: Select all

<?php

$db = mysql_connect ( $db_server , $db_user , $db_pass ) or die ( "Cannot connect to database server." );
mysql_select_db ( $db_name , $db ) or die ( "Cannot connect to database." );

function grab_widget_attributes ( $widget_id ) {
  $q = "select * from attribute_map where widget_id = '$widget_id'";
  $r = mysql_query($q);
  while ($row=mysql_fetch_assoc($r)) {
    $attribute_id = $row['attribute_id'];
    $widget_attributes[$attribute_id] = $attribute_id;
  }
  return $widget_attributes;
}

function print_checkboxes ( $attributes, $widget_attributes ) {
  foreach ($attributes as $k=>$v) {
    if (in_array($k, $widget_attributes )) $checked=" checked";
    else $checked=FALSE;
    echo "$v <input type=\"checkbox\" name=\"$k\"$checked><br />\n";
  }
}

$q = "select * from widget_attributes";
$r = mysql_query($q);
while ($row=mysql_fetch_assoc($r)) {
  $attribute_id = $row['attribute_id'];
  $attributes[$attribute_id] = $row['attribute_name'];
}

$q = "select * from widgets";
$r = mysql_query($q);
while ($row=mysql_fetch_assoc($r)) {
  $widget_id = $row['widget_id'];
  $widget_name = $row['widget_name'];
  
  echo "<p>$widget_name<br />\n";
  $widget_attributes = grab_widget_attributes ( $widget_id );  
  print_checkboxes ( $attributes, $widget_attributes );
  
}

?>
roseplant
Forum Newbie
Posts: 4
Joined: Thu Mar 16, 2006 10:23 am

Post by roseplant »

Sorry for the late reply - we have an extended paddy's day here in Ireland :D

That solution worked a charm , thanks a lot guys!
Post Reply