Hi I'd like some help please. I have created 3 database tables
CREATE TABLE IF NOT EXISTS `products` (
`product_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_name` varchar(100) NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`description` text,
PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
CREATE TABLE IF NOT EXISTS `features` (
`feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`features_name` varchar(100) NOT NULL,
`category_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`feature_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;
CREATE TABLE IF NOT EXISTS `product_features` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned NOT NULL,
`feature_id` int(10) unsigned NOT NULL,
`feature_value` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
The relations between them is many-to-many as one product can have many features and a feature can belong to more than one product, but each product has a specific value for each feature.
My goal is to fetch all features and their values that belong to a specific product and then display them in a form (in my view) where I can assign new values or edit existing ones for the features of the product. To give you a sort example of how my view looks like:
// Assign new values or edit existing ones
<?php echo form_open(); ?>
<table class="table">
<?php echo form_hidden('product_id', $product->product_id); ?>
<thead>
<tr>
<th> </th>
<th>Feature</th>
<th>Value</th>
</tr>
</thead>
<tbody>
<?php if(count($features) > 0): foreach($features as $feature): ?>
<tr>
<td><?php echo form_hidden('feature_id[]', $feature->feature_id); ?></td>
<td><?php echo $feature->feature_name; ?></td>
<td><?php echo form_input('value[]',set_value('value[]', $feature->value)); ?></td>
</tr>
<?php endforeach; else: ?>
<tr>
<td colspan="2"><h4 align="center">No Features available</h4></td>
</tr>
<?php endif; ?>
</tbody>
</table>
<div>
<?php echo form_submit('save_feature', 'Save Features', 'class="btn btn-primary"'); ?>
</div>
<?php echo form_close(); ?>
I'm trying to build the query in phpmyadmin (in order to create it with Active Record) and testing the results. Here's the query
SELECT `features`.`feature_id` , `features`.`feature_name` , `product_features`.`value`
FROM `features`
LEFT JOIN `product_features` ON `features`.`feature_id` = `product_features`.`feature_id`
/* WHERE `product_features`.`product_id` =1 */
Without the WHERE statement I get as result like so:
feature_id |feature_name | value
1 | Processor | NULL
2 | RAM | NULL
3 | Hard Disk | NULL
but if I include and the WHERE statement (remember that I want the values of the specific product) I get no results. How can I fix this, or what changes should I make to correct things??? Any help would be appreciated.
NOTE 1: A little detail: The product_features table is currently empty as there have been no assignments yet (I start from scratch), so I guess I may have to create my query dynamicly.
NOTE 2: I have assigned to features the (parent)category_id eg Computers, Digital Cameras etc etc, so I guess I have also to specify and the category_id to the query in order to get the corect features. (e.g. Apple Imac (in Computers category) should not have the feature optical zoom included as it belongs in Digital Cameras category).
