ecommerce site and product options displaying

[eluser]mmatti[/eluser]
Hi, I'm new to CI and not very experienced in php either. Just started building ecommerce website (store with clothes), based on a book "Professional CodeIgniter". One of the changes I'm trying to apply is having as many options for products as it's needed- so quite a usual requirement for ecommerce website.
So there's a table for products:

Code:

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `shortdesc` varchar(255) NOT NULL,
  `longdesc` text NOT NULL,
  `path` varchar(255) NOT NULL,
  `thumbnail` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `grouping` varchar(16) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL,
  `category_id` int(11) NOT NULL,
  `featured` enum('true','false') NOT NULL,
  `price` float(4,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

And three tables for supporting product options:

1) product_options:

Code:

CREATE TABLE IF NOT EXISTS `products_options` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `option_value_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2) options

Code:

CREATE TABLE IF NOT EXISTS `options` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

3) options_values

Code:

CREATE TABLE IF NOT EXISTS `options_values` (
  `id` int(11) NOT NULL,
  `option_id` int(11) NOT NULL,
  `option_value` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Now, I've managed to create function that extracts available Option names (e.g.Colour, Size) for a specific product (input data for displaying products is $path which is seo slug, eg. www.mydomain.com/product/great-shirt, where 'product' is name of the controller function as I hid controller name in routes file).

Here's the controller function:

Code:

function product($path){
    $product = $this->MProducts->getProduct($path);
    if (!count($product)){
        redirect('index','refresh');
    }
    $data['path'] = $path;
    $data['product'] = $product;
    $data['title'] = $product['name'];
    $data['main'] = 'product';
    $data['navlist'] = $this->MCats->getCategoriesNav();
    $data['id'] = $this->MProducts->getProductId($path);
    $data['options'] = $this->MOptions->getProductOptions($path);
    $data['option_values'] = $this->MOptions->getOptionValues($path);

    $this->load->vars($data);
    $this->load->view('template');
  }

Here's the MOptions model 2 functions:

Code:

function getProductOptions($path){
     $data = array();
     $sql = "SELECT options.name
      FROM options
      JOIN products
      JOIN options_values
      JOIN products_options
      ON products_options.option_value_id = options_values.id
      AND options.id = options_values.option_id
      AND products.path = '".$path."'      
      AND products_options.product_id = products.id";
     $Q = $this->db->query($sql);      
    
     if ($Q->num_rows() > 0){
       foreach ($Q->result_array() as $row){
         $data[$row['name']] = $row['name'];
       }
    }
    $Q->free_result();  
    return $data;
}
    

    
function getOptionValues($product_path){
    $data = array();
    $sql = "SELECT option_value
     FROM options_values
     JOIN products
     JOIN products_options
     ON products_options.product_id = products.id
     AND products_options.option_value_id = options_values.option_id
     AND products.path = '".$product_path."'";
    $Q = $this->db->query($sql);    
    
    

    if ($Q->num_rows() > 0){
      $data = $Q->row_array();
    }
    $Q->free_result();
    return $data;
}

And here's the view for displaying single product:

Code:

<div class='product'>
&lt;?php
    if ($this->session->flashdata('conf_msg')){ //change!
        echo "<div class='message'>";
        echo $this->session->flashdata('conf_msg');
        echo "</div>";
    }

    echo "<a href='".base_url()."".$product[' title='".$product[' class='lightbox'><img src='".base_url()."".$product[' /></a>\n";
    echo "<h2>".$product['name']."</h2>\n";
    echo "<p>".$product['longdesc'] . "</p>\n";
    echo form_open('shop/cart/'.$product['id'].'');
    echo form_hidden('id', $product['id']);
    echo form_hidden('path', $product['path']);
    echo form_hidden('price', $product['price']);
    echo form_hidden('name', $product['name']);
    //echo form_hidden('option_name', 'color');
    echo "<p class='price'>Price: <strong>&pound;".$product['price']. "</strong> + VAT</p>";
    
    
    foreach ($options as $key => $opt_name){
        echo "<p>".$opt_name." ";
        echo form_dropdown('option_values', $option_values) ."</p>";
        
    }

    echo "<div class='buttonrow'>".form_submit('buy', 'add to cart')."</div>";
    echo form_close();

?&gt;
</div>

[eluser]mmatti[/eluser]
And it produces the following html:

Code:

<div class='product'>
<a href='http://localhost/images/iStock_000000290732XSmall.jpg' title='Game 1' class='lightbox'><img src='http://localhost/images/iStock_000000290732XSmall.jpg' /></a>
<h2>Game 1</h2>
<p>What a product! You'll love the way your kids will play with this game all day long. It's terrific!</p>

&lt;form action="http://localhost/shop/cart/1" method="post"&gt;
&lt;input type="hidden" name="id" value="1" /&gt;

&lt;input type="hidden" name="path" value="game-1" /&gt;

&lt;input type="hidden" name="price" value="19.95" /&gt;

&lt;input type="hidden" name="name" value="Game 1" /&gt;
<p class='price'>Price: <strong>&pound;19.95</strong> + VAT</p><p>Color <select name="option_values">
<option value="option_value">Red</option>
</select></p><p>Size <select name="option_values">

<option value="option_value">Red</option>
</select></p><div class='buttonrow'>&lt;input type="submit" name="buy" value="add to cart"  /&gt;&lt;/div>&lt;/form&gt;&lt;/div>

What I want to achieve is that in the view there ware listed all the options available for a product being displayed (not all options available in the database) and next to each option name there would be a dropdown list with values for that option, e.g.
Color - Dropdown(Red,Yellow,Blue)
Size - Dropdown(L,XL,XXL)

Any help or suggestions on how to solve that are highly appreciated, as it already gave me a big headache...
If I need to post more information or other parts of the code please let me know.

[eluser]mmatti[/eluser]
maybe I wasn't clear on what I need help with- I don't know how to display- as you can see on the view it shows "Red" for both Color and Size, and I need it to show available colors and sizes as a dropdown.

Please help me... anyone?
Matt

[eluser]danmontgomery[/eluser]

Code:

foreach ($options as $key => $opt_name){
        echo "<p>".$opt_name." ";
        echo form_dropdown('option_values', $option_values) ."</p>";
        
    }

This is poor logic...

You're passing it an $options array with (presumably) each of the dropdown names (size, color, etc), and a $option_values array, which is supposed to produce different options for each iteration, even though nothing is done to it? You need to rethink this...

You're getting $option_values directly from getOptionValues(), so I would look there.

Code:

if ($Q->num_rows() > 0){
      $data = $Q->row_array();
    }

Here, for example, you're only ever going to return one row, there's no iteration through the result.

[eluser]mmatti[/eluser]
Thanks for your reply.
I figured that the way it should be done in the model is:

for current_product {
extract from db options assigned to current_product {
for each of extracted options {
extract values
}


so now my model looks like this:

Code:

function getProductOptions($path){
     $data = array();
     $sql = "SELECT options.name, options.id
      FROM options
      JOIN products
      JOIN options_values
      JOIN products_options
      ON products_options.option_value_id = options_values.id
      AND options.id = options_values.option_id
      AND products.path = '".$path."'      
      AND products_options.product_id = products.id";
     $Q = $this->db->query($sql);      
    
     if ($Q->num_rows() > 0){
       foreach ($Q->result_array() as $row){
        $data[$row['name']] = $row['name'];
        $product_option_value_query = $this->getOptionValues($path,$row['name']);
        foreach ($product_option_value_query as $option_value) {
         $data['option_value'] = $option_value;
            
        }
       }    
    
    }
    $Q->free_result();
    return $data;
}
    
function getOptionValues($product_path,$option_name){
    $data = array();
    $sql = "SELECT option_value
     FROM options_values
     JOIN options
     JOIN products
     JOIN products_options
     ON products_options.product_id = products.id
     AND products_options.option_value_id = options_values.option_id
     AND products.path = '".$product_path."'
     AND options.name = '".$option_name."'";
    $Q = $this->db->query($sql);    
    
    if ($Q->num_rows() > 0){
      $data = $Q->row_array();
    }
    $Q->free_result();
    return $data;
}

but still not sure if it's correct and what to do with controller and the view.