Location: PHPKode > scripts > MySQL_data_table > mysql_data_table/data_table.class.php
<?php
/*
    Class: data_table
    Author: Sven Zurnieden
    Email: hide@address.com
    
    DESCRIPTION:
    The class data_table generates a html-table from an array of datasets.
    This array must have 2 dimensions. You can set the table to be orderable,
    then the headers will be links and on click the table will be sorted.
    It also has an automatic next-previous site function which will show links
    at the bottom of the table, if count of data will be bigger than the count
    of shown datasets. It is also easy to change the layout of the table and its data.
    More in the HOW TO USE section. If you've questions just email me.
    
    HOW TO USE:
    The dataset array:
        The first dimension must be num-indexed, the second dimension doesn't matter 
        if it is num- or assoc-indexed. If it is num-indexed and you will see text 
        in the header line of the table, just use the set_header() method to set the headers.
        Of course you can set headers even if the second dimension is assoc-indexed.
    Make it orderable:
        Just call the method is_sortable(1) and the headers will change from standard text
        to links and on click on a link the table sort itself by the column you have chosen.
    Hide columns:
        Just call the method set_hidden_column() and assign the column nr to it.
    Hide empty datasets:
        Just call the method show_empty_data(0) and all data which will be completely empty
        or hasn't the correct amount of columns will be dropped from the dataarray.
    Setting maximum shown data
        Call the method set_max_data() and assign the amount of data you will see on each site.
    Standard sorting
        If you want your data to be sorted before the first table is on screen, call
        sort_data() and assign the array key to it on which the array should be sorted.
        Also assign if you want to sort ascending("ASC") or descending("DESC")
    Controlling the layout of the table:
        You have several options to control the layout of the table and its data.
        You can set bgcolors for each tablerow, you can define as many you want.
        Just use the set_color() method if you want to set the colors per array or 
        use the add_color() method to add one color at the end of the color property.
        
        With setting the th-config (set_th_config() method) or the td-config (set_td_config() method)
        you can define how the colums should look. Just create an array with html_options
        as key and give them correct values (e.g $th_conf["align"] = "center") and assign it
        to one of these two methods.
        
        For changing the layout of the table just use set_tbl_config() and assign an array to it.
        This array is like the th- or td-config array (e.g $tbl_conf["cellspacing"] = 2).
        
        If you don't want the standard html-text font or size for the headers or for the datasets
        you can set prefixes and suffixes for the header (methods: set_header_prefix(),
        set_header_suffix()) or the data (methods: set_data_prefix(), set_data_suffix()).
        To this methods you can assign html_code which will be written in front and behind
        the headers or data (e.g $header_prefix="<h4>", $header_suffix="</h4>").
        
        Changing the next or previous text is quite easy. Just call one of the methods
        set_next_text() or set_previous_text() and assign a string to it.
        
        Changing the column type is also easy. If you want a column to be a link column or 
        an email column, assign an array with column types to the method 
        set_col_config() or call the method set_column_type() and assign the 
        column nr (column 1 is 0, column 2 is 1 ...) and its type.
        Known types are link, email and date(needs a unix timestamp for correct format).
        
        You can also disable displaying the page count by calling method show_page_count(0).
        
    Automatic previous-next analysis
        The class has an automatic next-previous analysis implemented. If your dataarray
        has 200 values and your maximum data you will see is set to 20 then you will see
        next and previous links at the bottom of the table.
        On the left side will be the previous and on the right side the next link.
        In the middle of the bottom you will see the amount of pages the site contains,
        if it isn't disabled.
    Getting the html_code
        call the method get_html() to get the html_code. It will be returned from method
        as a string.
        
        
    NOW HAVE FUN, hope it is useful.
*/

class data_table{
    
    var $colors             = false;    //the array of colors
    var $color_index        = 0;        //the actual color index
    var $max_colors         = 0;        //maximum amount of colors, will change if colors are added
    var $tbl_config         = false;    //the layout of the table tag
    var $th_config          = false;    //the layout of the header th tags
    var $td_config          = false;    //the layout of the data td tags
    var $col_config         = false;    //column config, type of columns (link or email)
    var $data               = false;    //the dataarray
    var $sortable           = false;    //boolean, true if the table is sortable
    var $headers            = false;    //the array of the headers
    var $html               = false;    //the HTML code
    var $data_prefix        = false;    //HTML prefix of data
    var $data_suffix        = false;    //HTML suffix of data
    var $header_prefix      = false;    //HTML prefix of headers
    var $header_suffix      = false;    //HTML suffix of headers
    var $order_by           = false;    //key on which will be sorted
    var $order_mode         = false;    //ordermode, ascending("ASC") or descending("DESC")
    var $hidden_col         = array();    //array of columns which shouldn't be seen
    var $max_col            = 0;        //maximum count of columns
    var $max_data           = 100;      //maximum count of shown data
    var $data_count         = 0;        //count of data
    var $data_keys          = false;    //the keys of the data
    var $next               = false;    //boolean, true if next link is shown
    var $previous           = false;    //boolean, true if previous link is shown
    var $next_text          = "Next";   //the text for the next link
    var $previous_text      = "Previous";//the text for the previous link
    var $start_index        = 0;        //the index which will be the first dataset shown on site
    var $show_data          = 0;        //most of time this will be max_data but if you want to see 100 datasets and there are only 20 this var is quite important
    var $page_count         = 0;        //the amount of pages
    var $show_empty_data    = true;     //boolean, false if empty datasets shouldn't be seen
    var $empties            = false;    //the array of indexes which datasets are empty
    var $show_page_count    = true;     //boolean, false if amount of pages should not be shown at the bottom
    var $no_data_msg        = "The data contains no values.";
    var $date_format        = "m/d/Y H:i:s";
    
    function draw_table(){
        //non public funtion, generates the html code
        $this->next_previous();
        $this->html = '';
        $this->html('<table', 0);
        if(is_array($this->tbl_config)){
            $help = $this->tbl_config;
            while(list($key, $val) = each($help)){
                $this->html(" ".$key.'="'.$val.'"', 0);
            }
        }
        $this->html('>');
        $this->html('<tr bgcolor="'.$this->get_color().'">');
        if(is_array($this->headers)){
            $j = 0;
            foreach($this->headers as $header){
                if(!isset($this->hidden_col[$j])){
                    $this->html('<th', 0);
                    if(isset($this->th_config[$j]) && is_array($this->th_config[$j])){
                        $help = $this->th_config[$j];
                        while(list($key, $val) = each($help)){
                            $this->html(" ".$key.'="'.$val.'"', 0);
                        }
                    }
                    $this->html('>', 0);
                    if($this->sortable){
                        if($this->order_by == $header["order"]){
                            if($this->order_mode == "ASC"){
                                $order_mode = "DESC";
                            }
                            else{
                                $order_mode = "ASC";
                            }
                        }
                        else{
                            $order_mode = "ASC";
                        }
                        $this->html('<a href="'.$_SERVER["PHP_SELF"].'?order_by='.$header["order"].'&order_mode='.$order_mode.'">', 0);
                    }
                    if($this->header_prefix){
                        $this->html($this->header_prefix, 0);
                    }
                    $this->html($header["show"], 0);
                    if($this->header_suffix){
                        $this->html($this->header_suffix, 0);
                    }
                    if($this->sortable){
                        $this->html('</a>',0);
                    }
                    $this->html('</th>');
                }
                $j++;
            }
        }
        $this->html('</tr>');
        if($this->data_count <= 0){
            $this->html('<tr bgcolor="'.$this->get_color().'">');
            if(is_array($this->headers)){
                $colspan = count($this->headers);
            }
            else{
                $colspan = 1;
            }
            $this->html('<td align="center" colspan="'.$colspan.'">'.$this->no_data_msg.'</td>');
            $this->html('</tr>');
        }
        else{
            for($i = $this->start_index; $i < ($this->start_index + $this->show_data); $i++){
                $data_row = $this->data[$i];
                $show = true;
                if($show){
                    $this->html('<tr bgcolor="'.$this->get_color().'">');
                    for($j = 0; $j < $this->max_col; $j++){
                        if(!isset($data_row[$this->data_keys[$j]]) || empty($data_row[$this->data_keys[$j]])){
                            $cell = "&nbsp;";
                        }
                        else{
                            $cell = $data_row[$this->data_keys[$j]];
                        }
                        if(!isset($this->hidden_col[$j])){
                            $this->html('<td', 0);
                            if(isset($this->td_config[$j]) && is_array($this->td_config[$j])){
                                $help = $this->td_config[$j];
                                while(list($key, $val) = each($help)){
                                    $this->html(" ".$key.'="'.$val.'"', 0);
                                }
                            }
                            $this->html('>', 0);
                            if($this->data_prefix){
                                $this->html($this->data_prefix, 0);
                            }
                            if(isset($this->col_config[$j])){
                                switch($this->col_config[$j]){
                                    case "link":
                                        $this->html('<a href="'.$cell.'">'.$cell.'</a>', 0);
                                        break;
                                    case "email":
                                        $this->html('<a href="mailto:'.$cell.'">'.$cell.'</a>', 0);
                                        break;
                                    case "date":
                                        $this->html(date($this->date_format, $cell), 0);
                                        break;
                                    default:
                                        $this->html(nl2br($cell), 0);
                                }
                            }
                            else{
                                $this->html(nl2br($cell), 0);
                            }
                            if($this->data_suffix){
                                $this->html($this->data_suffix, 0);
                            }
                            $this->html('</td>');
                        }
                    }
                    $this->html('</tr>');
                }
            }
            if($this->next || $this->previous){
                $this->html('<tr bgcolor="'.$this->get_color().'">');
                $query_string = $_SERVER["QUERY_STRING"];
                $query_string = str_replace('&start_index='.($this->start_index), "", $query_string);            
                $this->html('<td align="center">', 0);
                if($this->previous){
                    $this->html('<a href="'.$_SERVER["PHP_SELF"].'?'.$query_string.'&start_index='.($this->start_index - $this->max_data).'">'.$this->previous_text.'</a>', 0);
                }
                else{
                    $this->html('&nbsp;', 0);
                }
                $this->html('</td>');
                $this->html('<td align="center" colspan="'.($this->max_col - 2 - count($this->hidden_col)).'">', 0);
                if($this->show_page_count){
                    for($i = 0; $i < $this->page_count; $i++){
                        $this->html('<a href="'.$_SERVER["PHP_SELF"].'?'.$query_string.'&start_index='.($i * $this->max_data).'">'.($i + 1).'</a>&nbsp;');
                    }
                }
                else{
                    $this->html('&nbsp;', 0);
                }
                $this->html('</td>');
                $this->html('<td align="center"', 0);
                if($this->next){
                    $this->html('<a href="'.$_SERVER["PHP_SELF"].'?'.$query_string.'&start_index='.($this->start_index + $this->max_data).'">'.$this->next_text.'</a>', 0);
                }
                else{
                    $this->html('&nbsp;', 0);
                }
                $this->html('</td>');
                $this->html('</tr>');
            }
        }
        $this->html('</table>');
    }
    
    function next_previous(){
        //analyses if next or previous link should be set
        if(isset($_GET["start_index"])){
            $this->start_index = $_GET["start_index"];
        }
        $this->show_data = $this->max_data;
        if($this->start_index + $this->max_data < $this->data_count){
            $this->next = true;
            if($this->start_index + (2 * $this->max_data)  > $this->data_count){
                $this->show_data = $this->data_count - $this->start_index;
            }
            if($this->show_data > $this->max_data){
                $this->show_data = $this->max_data;
            }
        }
        else{
            if($this->start_index + $this->show_data > $this->data_count){
                //$this->show_data = ($this->start_index + $this->max_data) - ($this->data_count );
                $this->show_data = $this->data_count - $this->start_index;
            }
        }
        if($this->start_index - $this->max_data >= 0){
            $this->previous = true;
        }
        if($this->next || $this->previous){
            $this->page_count();
        }
    }
    
    function page_count(){
        //calculates the amount of pages
        $this->page_count = $this->data_count / $this->max_data;
        if(preg_match("/./", $this->page_count)){
            $this->page_count = ceil($this->page_count);
        }
    }
    
    function set_date_format($format){
        //sets the data format for data columns(format like function date() in php)
        $this->date_format = $format;
    }
    
    function set_column_type($column_nr, $type){
        //function for setting the column type
        $this->col_config[$column_nr] = $type;
    }
    
    function set_col_config($config_array){
        //function for setting the column types
        $this->col_config = $config_array;
    }
    
    function set_tbl_config($tbl_config_array){
        //layout of the table
        $this->tbl_config = $tbl_config_array;
    }
    
    function set_td_config($td_config_array){
        //layout of data td tags
        $this->td_config = $td_config_array;
    }
    
    function set_th_config($th_config_array){
        //layout of header th tags
        $this->th_config = $th_config_array;
    }
    
    function set_colors($color_array){
        //setting the bgcolors of tr tags
        $this->colors = $color_array;
        $this->max_colors = count($this->colors);
    }
    
    function set_data($data_array){
        //sets the dataarray
        $this->data = $data_array;
        $this->data_count = count($this->data);
        if($this->data_count > 0){
            $max_col = 0;
            $i = 0;
            foreach($this->data as $data){
                $col_count = count($data);
                if($col_count > $max_col){
                    $max_col = $col_count;
                    $index = $i;
                }
                $i++;
            }
            $help = $this->data[$index];
            $i = 0;
            while(list($key, $val) = each($help)){
                $this->data_keys[$i] = $key;
                $i++;
            }
            $this->max_col = $max_col;
        }
    }
    
    function set_header($header_array){
        //sets the headers
        $this->headers = $header_array;
    }
    
    function set_data_prefix($prefix){
        //set the html data prefix
        $this->data_prefix = $prefix;
    }
    
    function set_data_suffix($suffix){
        //sets the html data suffix
        $this->data_suffix = $suffix;
    }
    
    function set_header_prefix($prefix){
        //sets the html header prefix
        $this->header_prefix = $prefix;
    }
    
    function set_header_suffix($suffix){
        //sets the html header suffix
        $this->header_suffix = $suffix;
    }
    
    function set_hidden_column($col_nr){
        //set a column to be hidden
        $this->hidden_col[$col_nr] = true;
    }
    
    function set_max_data($max_data){
        //set the amount of data will be shown on each site
        $this->max_data = $max_data;
    }
    
    function set_next_text($text){
        //sets the link text for next link
        $this->next_text = $text;
    }
    
    function set_previous_text($text){
        //sets the link text for previous link
        $this->previous_text = $text;
    }
    
    function set_no_data_msg($text){
        //sets the error message, used if the dataarray contains no data
        $this->no_data_msg = $text;
    }
    
    function show_empty_data($empty_data){
        //sets if empty datasets should be shown or not
        $this->show_empty_data = $empty_data;
        if(!is_array($this->data)){
            echo "Error: No data set yet in data_table class.";
        }
        else{
            $help = $this->data;
            $i = 0;
            foreach($help as $data_row){
                $empty = false;
                if(count($data_row) < $this->max_col){
                    $empty = true;
                }
                else{
                    $j = 0;
                    foreach($data_row as $data){
                        if(empty($data) && $data != "0"){
                            $empty_val[$j] = true;
                        }
                        else{
                            $empty_val[$j] = false;
                        }
                        $j++;
                    }
                    $all_empty = true;
                    foreach($empty_val as $col){
                        if(!$col){
                            $all_empty = false;
                        }
                    }
                    if($all_empty){
                        $empty = true;
                    }
                }
                if($empty){
                    $this->empties[$i] = true;
                }
                else{
                    $this->empties[$i] = false;
                }
                $i++;
            }
        }
        $i = 0;
        $j = 0;
        foreach($this->data as $data){
            if(!$this->empties[$i]){
                $new_data[$j] = $data;
                $j++;
            }
            else{
                $this->data_count--;
            }
            $i++;
        }
        $this->data = $new_data;
    }
    
    function show_page_count($page_count){
        //sets if the amount of pages should be shown in the middle of bottom
        $this->show_page_count = $page_count;
    }
    
    function add_color($color){
        //adds a color to color array
        $this->colors[$this->max_colors] = $color;
        $this->max_colors++;
    }
    
    function is_sortable($sortable){
        //sets if the table should be sortable or not
        $this->sortable = $sortable;
    }
    
    function get_color(){
        //non public funtion, just for returning the correct color for tr tags
        if(is_array($this->colors)){
            $color = $this->colors[$this->color_index];
            if($this->color_index == $this->max_colors - 1){
                $this->color_index = 0;
            }
            else{
                $this->color_index++;
            }
        }
        else{
            $color = "#FFFFFF";
        }
        return $color;
    }
    
    function sort_data($order_by = "", $order_mode = "ASC"){
        //sorts the table by $order_by and $order_mode
        if($this->data_count > 0){
            if(isset($_GET["order_by"])){
                $order_by = $_GET["order_by"];
            }
            if(isset($_GET["order_mode"])){
                $order_mode = $_GET["order_mode"];
            }
            $help = $this->data;
            $order_array = array();
            while(list($key, $val) = each($help)){
                $order_array[$key] = @$val[$order_by];
            }
            switch($order_mode){
                case "ASC":
                    asort($order_array);
                    break;
                case "DESC":
                    arsort($order_array);
                    break;
                default:
                    asort($order_array);
            }
            $help = $order_array;
            $i = 0;
            while(list($key, $val) = each($help)){
                $data[$i] = $this->data[$key];
                $i++;
            }
            $this->data = $data;
            $this->order_by = $order_by;
            $this->order_mode = $order_mode;
        }
    }
    
    function html($string, $new_line = 1){
        //non public funtion, just for generating readable html code
        if($new_line){
            $this->html .= $string."\r\n";
        }
        else{
            $this->html .= $string;
        }
    }
    
    function get_html(){
        //generates the html code and returns it
        $this->draw_table();
        return $this->html;
    }
}
?>
Return current item: MySQL_data_table