html comparison table/chart

[eluser]bugboy[/eluser]
Hello all

Been on this all day now i was wondering if anyone could help.

I need to make a comparison table for a website i'm building pulling data from a MySQl database.

Now i have set up my MySQL tables that hold the data like this

-----cms_packages----- (holds the package information)
package_id
package_info

-----cms_features----- (holds the features)
feature_id
feature_info

-----cms_package_features----- (links the pack to its features)
package_id
feature_id


I'm using the MVC approach and i need to create a table that list the packages and features putting a yes or no if the package has that feature or not.

Now my little brain can't seem to work this out and i was wondering if any of you lot have done this or something similer.

I do have code but its pointless showing any as its not working.

help ha ha ha

Cheers (yikes!)

[eluser]bugboy[/eluser]
Hello all

So far i have worked this out to some degree.

This is my model

Code:

function getComparison()
    {
    
    // select packages
    $sql = 'SELECT a .*
    FROM cms_package a, cms_package_global b
    WHERE a.package_id = b.package_id
    AND b.global_id = '.$this->_global_id;
    $query = $this->db->query($sql);
    $selected_packages = $query->result();
    
    
    // gets features
    $this->db->orderby("feature_id", "desc");
    $query = $this->db->get($this->_feature);
    $features = $query->result();
    
    $output = '';
        
    foreach($selected_packages as $key => $value){
        
        //gets all the features from the selected package
        $sql = 'SELECT a .*
        FROM cms_feature a, cms_package_feature b
        WHERE a.feature_id = b.feature_id
        AND b.package_id = '.$value->package_id.'
        ORDER BY a.feature_id DESC';
        $query = $this->db->query($sql);
        $get_features = $query->result();
        
        // puts the features into an array on there own
        $whatFeatures = array();
        foreach($get_features as $gfi => $gf){
            $whatFeatures[] = $gf->feature_id;
        }
        
        $output .='<tr>';
        $output .='<td>'.$value->package_title.'</td>';
        
        foreach($features as $no => $get){
            if(in_array($get->feature_id, $whatFeatures)){
                $output .='<td><img src="'.base_url().'default_image_folder/tick.png" alt="yes" /></td>';
            }else{
                $output .='<td><img src="'.base_url().'default_image_folder/cross.png" alt="no" /></td>';
            }
        }
        
        $output .= '</tr>';
    }
    
    return $output;
    }

this is my view

Code:

<?php
        /*print_r($packages);
        print_r($features);
        print_r($package_feature);*/
        ?>
        
        <table summary="List of all the package on the site.">
        <caption>List of all the package on the site.</caption>

        <thead>
        <tr>
        <th></th>
        
        &lt;?php
        
        
        
        foreach($features as $key => $value)
        {
        echo '<th scope="col">'.$value->feature_title.'</th>';
        }
        
        ?&gt;
        </tr>
        </thead>
        
        <tfoot>
        <tr>
        <td colspan="5">compasion table for all packages on site</td>
        </tr>
        </tfoot>
        
        <tbody>
            &lt;?php
            echo $comparison;
            ?&gt;
        </tbody>    
        </table>

this is my controller

Code:

function compare()
    {
    // global meta data
    $data['meta_replyto'] = $this->_meta_replyto;
    $data['meta_copyright'] = $this->_meta_copyright;
    $data['meta_author'] = $this->_meta_author;
    $data['meta_title'] = $this->_meta_title;
    $data['meta_dscpn'] = $this->_meta_dscpn;
    $data['meta_keyword'] = $this->_meta_keyword;
    $data['meta_revisit'] = $this->_meta_revisit;
        
    $data['sectionList'] = $this->_sectionList;
    $data['pageListNav'] = $this->_pageList;
    
    $data['h1'] = 'Sections';
    $data['contact'] = $this->_sitewide;
    
    $drawn_features= $this->multi_packages->getPackageFeatures();
    
    
    $data['packages'] = $this->multi_packages->getPackage();
    $data['features'] =$this->multi_packages->getFeatures();
    $data['package_feature'] =  $drawn_features;
    
    
    /*//////////////create compasion table ////////////////////*/
    
    
    $data['comparison'] = $this->multi_packages->getComparison();    
        
    $data['page'] = 'packages/compare';
    $this->load->vars($data);
    $this->load->view($this->_container);
    }

Now this does what its supposed to do but i need to change it because at the moment its putting the features along the top of the html table and the packages down the side

what i need it to do is put the packages along the top and the features down the side.

I just can't for the life of me work this on out.

Also if there is a better way to do what my model is doing please tell me as i need to learn.

Cheers all

[eluser]bugboy[/eluser]
worked it out

But is there a more effieant way to do this in the model then running querys over and over?

Code:

function getComparison()
    {
    
    // get the features
    $this->db->orderby("feature_id", "desc");
    $query = $this->db->get($this->_feature);
    $feature = $query->result();
    

    $sql = 'SELECT a .*
    FROM cms_package a, cms_package_global b
    WHERE a.package_id = b.package_id
    AND b.global_id = '.$this->_global_id.'
    ORDER BY a.package_id DESC';
    $query = $this->db->query($sql);
    $packages = $query->result();
    
    $output = '';
    
    foreach($feature as $key => $value)
    {
    
        $sql = 'SELECT a .*
        FROM cms_package a, cms_package_feature b
        WHERE a.package_id = b.package_id
        AND b.feature_id = '.$value->feature_id.'
        ORDER BY a.package_id DESC';
        $query = $this->db->query($sql);
        $get_packages = $query->result();
    
        $whatPackages = array();
        foreach($get_packages as $gfi => $gf){
            $whatPackages[] = $gf->package_id;
        }
    
        $output .='<tr>';
        $output .='<td>'.$value->feature_title.'</td>';
        
        foreach($packages as $no => $get){
            if(in_array($get->package_id, $whatPackages)){
                $output .='<td><img src="'.base_url().'default_image_folder/tick.png" alt="yes" /></td>';
            }else{
                $output .='<td><img src="'.base_url().'default_image_folder/cross.png" alt="no" /></td>';
            }
        }
        
        $output .= '</tr>';
    }
    
    return $output;    
    
    
    }

[eluser]Nick Husher[/eluser]
You could do a left join with an orderby on your package ID. I think it would go like this in your active record class:

Code:

$this->db->select('cms_packages.package_info, cms_features.feature_info');
$this->db->from('cms_packages, cms_features, cms_package_features');
$this->db->where('cms_packages.package_id','cms_package_features.package_id');
$this->db->where('cms_features.feature_id','cms_package_features.feature_id');

if( /* you want to display by feature */) {
  $this->db->orderby('cms_features.feature_id');
} else {
  $this->db->orderby('cms_packages.package_id');
}

$result = $this->db->get();

This returns a lot of repetition that'll look like this:

Code:

'cms1 -> cms_feature1'
'cms1 -> cms_feature2'
'cms1 -> cms_feature3'
'cms2 -> cms_feature1'
'cms2 -> cms_feature3'
etc...

Or the opposite effect if you order it by features.

Note that I didn't test it, but it should be the Active Record equivalent to the SQL I tested this with, which looks like this:

Code:

mysql> select cms.cms_name, feature.feature_text from cms, feature, cms_features where cms_features.cms_id = cms.id and cms_features.feature_id = feature.id order by cms.cms_name;

Also: It won't display cms entries that have no features.

[eluser]bugboy[/eluser]
cheers for that i'll try it and see what i come back with.

Is it normal to do what i have done as for this in the model. I'm still learning and i have never worked on a html comparison table before