2 Votes Vote

SQl query to select relational data

Posted by topdog 202 days ago Questions| feature null product All

I am trying to write the SQL that selects all products and available features.

My database is as follows:

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) NOT NULL,
  `product_description` varchar(255) NOT NULL,
  `product_weight` varchar(255) NOT NULL,
  `product_price` decimal(11,2) NOT NULL,
  `product_image` varchar(255) NOT NULL,
  PRIMARY KEY (`product_id`)
);

CREATE TABLE IF NOT EXISTS `features` (
  `feature_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `feature_uri` varchar(255) NOT NULL,
  `feature_name` varchar(100) NOT NULL,
  `feature_title` varchar(150) DEFAULT NULL,
  `feature_body` text,
  `feature_body_short` varchar(255) DEFAULT NULL,
  `feature_image` varchar(255) DEFAULT NULL,
  `parent_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`feature_id`),
  UNIQUE KEY `feature_uri_UNIQUE` (`feature_uri`),
  KEY `parentFK` (`feature_id`),
  FULLTEXT KEY `feature_name_FT` (`feature_name`),
  FULLTEXT KEY `feature_body_FT` (`feature_body`)
);

CREATE TABLE IF NOT EXISTS `feature_products` (
  `feature_product_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `feature_product_order` smallint(6) DEFAULT NULL,
  `feature_product_standard` tinyint(1) NOT NULL,
  `feature_id` int(11) unsigned NOT NULL,
  `product_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`feature_product_id`),
  KEY `productFK` (`product_id`),
  KEY `featureFK` (`feature_id`)
);

I would like to be able to do this in one loop e.g:

{foreach}
<tr>
    <td>{name}</td>
    <td>{weight}</td>
    <td>{if product_id == 1}yes{/if}</td>
    <td>{if product_id == 2}yes{/if}</td>
etc
<tr>
{/foreach}

I am using Zend, if this can be of use.

Trying to achieve this HTML: example

Originally asked by: John Magnolia on Stack Overflow

Discuss Bury


Who Voted for this Question