Results 1 to 15 of 15

Thread: comparison table

  1. #1

    comparison table

    Hello all its me again

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

    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


    now 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

  2. #2
    maybe im missing something, but your wrote: "now 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"

    well, if you have a table called cms_package_features, only associate the ID's if there is one (which would effectively be your Yes or No).

    So if package 2 has feature 1 and feature 2, then you have two entries in cms_package_features:

    PackageID - 2
    FeatureID - 1
    PackageID - 2
    FeautureID - 2

    then to get all features of a package, you do:
    Code:
    $query = mysql_query("SELECT PackageID, FeatureID FROM cms_package_features WHERE PackageID = $packagenumber");
    OR
    to get all packages having a particular feature, you do:
    Code:
    $query = mysql_query("SELECT PackageID, FeatureID FROM cms_package_features WHERE FeatureID = $featurevalue");
    or did i completely misunderstand your question?
    lol.
    "First time it's a mistake, do it again and it's stupidity."

  3. #3
    sorry i meant a html table to display the data.

    ha ha ha my mistake

  4. #4

    Afrostyle

    You could run a select statement for your packages
    then loop through with following nested in the while statement.

    select * packages...
    while($r=...){
    select f.feat_id, fp.feat_id as fp_id, fp.pack_id, f.info
    from feat as f LEFT JOIN feat_pack as fp ON fp.feat_id=f.feat_id
    while ($fp=...){
    if($r['pack_id==$fp['pack_id'] && isset($fp['fp_id'])){
    echo "yes";
    } else { echo "no";}
    }
    }
    You could then run a conditional on the pack_id to see if it equals the pack_id in the original select statement, and a conditional ont he fp_id to see if it has a value I guess, it should show up as an empty if it is not in the feat_pack table, or something close to that should work...

  5. #5
    i'm not fully sure if i follow you.

    are you saying put the code given in a select statement for the sql query? As i have never seen that done before

  6. #6

    Robot1

    Its ok, I don't follow me either
    Well you need to select all of the packages

    Then you need to select all of the features and check weather or not the package had the feature.
    I assume since there is a pack_feat table that each package can have more then one feature so I looped it again. I am sure there is a better way to do this but I have done stuff like this before.
    It would end up

    PACKAGE
    ALL FEATURES YES/NO
    PACKAGE
    ALL FEATURES YES/NO


    Code:
    $pack = mysql_query("SELECT * from package");
     while ($r = mysql_fetch_array($pack)){
     //select all packages
      $fpack = mysql_query("SELECT f.feat_id, fp.feat_id as fp_id, fp.pack_id, f.info from feat as f LEFT JOIN feat_pack as fp ON fp.feat_id=f.feat_id ");
        while ($fp = mysql_fetch_array($fpack)){
        //select all features and pack_features
         if($r['pack_id']==$fp['pack_id'] && isset($fp['fp_id'])){
          echo "yes";
          } else { echo "no";}
       }
    }
    You of course would need to chang the above items to your proper field names.
    I should just stop posting in these forums...I never make sense to anyone but myself heh....
    Web Site Design and Stuff....One day I will get it.

  7. #7
    No in fact you did make sense before it was my reading it all wrong

    I followed your suggestion to begin with changing the sql fields names to ones i use and such used a foreach loop instead but the basic principle is the same.

    this is what i got and it works thanks to you.

    PHP 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();
        
        
    $output '<table>';
            
        foreach(
    $selected_packages as $key => $value){
            
            
    $output .='<tr>';
            
            
    $sql 'SELECT f.feature_id, fp.feature_id as fp_id, fp.package_id, f.feature_title
            FROM cms_feature as f LEFT JOIN cms_package_feature as fp ON fp.feature_id = f.feature_id
            WHERE fp.package_id ='
    .$value->package_id;
            
            
    $query $this->db->query($sql);
            
    $run_packages $query->result();
            
            
    $output .='<td>'.$value->package_title.'</td>';
            
            foreach(
    $run_packages as $no => $get){
                if(
    $value->package_id == $get->package_id && isset($get->package_id)){
                    
    $output .='<td>yes</td>';
                }else{
                    
    $output .='<td>no</td>';
                }
            }
            
    $output .='</tr>';
        }
        
        
    $output .= '</table>';
        
        return 
    $output;
        
        } 
    I'm using the codeignitor frame work so excuse the the class stuff but you get the idea.

    Now all i need to do is reverse this so the packages go along the top row and the features go down the column.

    Sweet

  8. #8
    Cool, I did someting right for once heh...I did start looking into subselects, as I should figure out if this could do the same thing some day.
    Web Site Design and Stuff....One day I will get it.

  9. #9
    ok so i now have it working.

    But once again my head is rubbish.

    At the moment i have the features going along the top of the html table
    and the packages going down the side.

    I need to swap them round so the packages are going along the top and the features going down the side.

    here is my code.

    PHP 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;
        } 
    Once again any help would be greatly welcomed.

    Cheers

  10. #10
    Quote Originally Posted by bugboy View Post
    ok so i now have it working.

    But once again my head is rubbish.

    At the moment i have the features going along the top of the html table
    and the packages going down the side.

    I need to swap them round so the packages are going along the top and the features going down the side.

    here is my code.

    PHP 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 = '';
        //changed so row is outside of foreach loop
        //one row
        
    $output .='<tr>';
        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;
            }
     
            
    //no td end tag leave it till the end
            //if you dont like the ul just add a new table there
            //
            
    $output .='<td>'.$value->package_title.<ul>'';
     
            foreach(
    $features as $no => $get){
                if(
    in_array($get->feature_id$whatFeatures)){
                    
    $output .='<li><img src="'.base_url().'default_image_folder/tick.png" alt="yes" /></li>';
                }else{
                    
    $output .='<li><img src="'.base_url().'default_image_folder/cross.png" alt="no" /></li>';
                }
            }
    $output .='</ul></td>';
    //end of list tag
    //this is where the end of the table would go if you want to use that.
     
        
    }
         
    $output .= '</tr>';
         
    //end of the single tr    
         //end of changes
    return $output;
        } 
    Once again any help would be greatly welcomed.

    Cheers
    As I try to avoid tables as much as possible anymore what this should do is produce one row, with a td per package. It will then list the features in a unordered list which you will allow you to apply a style to it, so it can appear the way you want.

    package
    • feature
    • feature
    Web Site Design and Stuff....One day I will get it.

  11. #11
    tables are ok for tabular data so i'm cool with that.

    i was wondering how i'd reverse everything round though?

  12. #12
    worked it out

    looked at it by reversing the data

    PHP 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;    
        
        
        } 
    Cheers for all your help though very much appreciated

  13. #13
    what I did should reverse it around
    it should be
    Code:
    <tr>
       <td>package       <td>package       <td>package
          feature           feature           feature
          feature           feature           feature
          feature</td>      feature</td>      feature</td>
    </tr>
    the features can be in a second table or in the list
    Is this not what you want? If not I am lost!
    Web Site Design and Stuff....One day I will get it.

  14. #14
    you know what cause you quoted me and i didn't look at the code i didn't see your solutions. As i thought it was a copy of what i posted.

    Very sorry.

  15. #15
    As long as it works! Thats all that matters
    Web Site Design and Stuff....One day I will get it.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012