menu

Sorting and Displaying Data in Columns

Jan 31, 2012
 0
 0
 0
 0

There are many ways to display data in tables. I'm going to discuss a couple alternatives to the one record - one row method. Let me jump right in with some examples.

Given the following arrays

{1, 2, 3, 4, 5, 6, 7, 8, 9}
{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}
{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11}

A typical way to loop through and display data is as follows. This first example is fairly trivial; by simply looping through the array you can display a table like the one below. The array is displayed in sorted order, left to right, then top to bottom. Much like reading a book.

123
456
789

This may not be how I want the data displayed. There are cases when I want to display my data sorted alphabetically in columns (top to bottom), then left to right. I also want the data to be distributed as equally as possible among columns.

147
258
369
158
269
3710
4
159
2610
3711
48
AppleLGPioneer
Hewlett PackardMotorolaSamsung
HitachiPanasonicSony
JVC

If I pull an array of data from a database, or any other source, I will pull it in alphabetical order.

    Apple
    Hewlett Packard
    Hitachi
    JVC
    LG
    Motorola
    Panasonic
    Pioneer
    Samsung
    Sony

In order to print the table, it would be much easier if the array was ordered in a way that I can print the array one row at a time. If looking at the table above, with 3 columns, this array is ordered so as I print each row, the results will be alphabetical by column, reading columns left to right.

    Apple
    LG
    Pioneer
    Hewlett Packard
    Motorola
    Samsung
    Hitachi
    Panasonic
    Sony
    JVC
    [blank]
    [blank]

If I know that I will be printing to 3 columns, I can sort the array ahead of time to make the display easier. Take a look at the following function which provides the solution I'm looking for. I'll pass in a sorted array, and the number of columns, and the function will return an array sorted as per above, allowing me to display the table by rows.

This is the same display technique that I use on my code snippets page.

Show Sort Function
function arrayColumns($data, $nbrColumns = 2, $strForEmptyCell = "-")
{
    $total_count = sizeof($data);
    if ($total_count <= 0)
        return;

    if ($nbrColumns <= 0)
        return;

    $total_rows = ceil($total_count / $nbrColumns);
    $rem = $total_count % $nbrColumns;

    $outArray = array();

    if ($rem > 0) {
        $total_count = $nbrColumns * $total_rows;
    }

    $col = 0;
    $row = 0;
    for($x = 0; $x < $total_count; $x++)
    {
        $pos = ($row) + ($col * $total_rows);

        if ($rem > 0) {
            if ($col > $rem) {
                $pos = $pos - ($col - $rem);
            }

            if ($row == $total_rows - 1) {
                if ($col >= $rem) {
                    $outArray[] = $strForEmptyCell;
                }
                else {
                    $outArray[] = $data[$pos];
                }
            }
            else {
                $outArray[] = $data[$pos];
            }
        }
        else {
            $outArray[] = $data[$pos];
        }
        $col++;

        if ($col == $nbrColumns)
        {
            $col = 0;
            $row ++;
        }

    }

    return $outArray;
}
How to display using PHP
echo "<table border='1' cellpadding='2' cellspacing='2'>";
for($x = 0; $x < sizeof($dispArr); $x = $x + $columns) {
    echo "<tr>";
    for ($y = 0; $y < $columns; $y++) {
        echo "" . $dispArr[$x + $y] . "";
    }
    echo "</tr>";
}
echo "</table>";
How to display using Smarty
<table border='1'>
    {section name=main loop=$test_arr step=$columns}
        <tr>
            {section name=mycol loop=$columns step=1}
            {assign var=myindex value=`$smarty.section.main.index+$smarty.section.mycol.index`}
            <td style='padding:10px'>{$test_arr[$myindex]}</td>
            {/section}

        </tr>
    {/section}
</table>
Displaying Data In Groups

In another situation, I am selecting data that will be sorted in groups. In the data below, the data is sorted by make and model. I would like to display the data in columns, sorted top to bottom, and left to right, while displaying data by groups. Review the example below to see what I'm talking about.

{"make" => "Apple", "model" => "1000"}
{"make" => "Apple", "model" => "2000"}
{"make" => "Apple", "model" => "3000"}   
{"make" => "Apple", "model" => "4000"}
{"make" => "Apple", "model" => "5000"} 

{"make" => "HP", "model" => "1000"}
{"make" => "HP", "model" => "2000"}

...
    
{"make" => "Sony", "model" => "1000"}
{"make" => "Sony", "model" => "2000"}
{"make" => "Sony", "model" => "3000"}
{"make" => "Sony", "model" => "4000"}

This example shows how I can display the data by grouping, sorted in columns, from left to right. A key point to note here is that I do not want to split a group by starting it in the bottom of one column, and continuing in the next column. A group of data should never be split across columns. This will make column heights slightly different, but we can get them close in height under most circumstances.

Apple IBMMotorola
1000 1000 1000
2000 2000 2000
3000 3000
4000 Panasonic
5000 JVC 1000
1000 2000
Hewlett Packard 2000
1000 3000 Pioneer
2000 4000 1000
5000 2000
Hitachi 2000
1000 LG3000
2000 1000 4000
3000 2000
3000 Sony
4000 1000

The code provided below is a class object designed to provide all the functionality discussed on this page. Just copy the class and include it in your project.

Show me the code
<?php

/* the two functions to call in this class are

    chunkColumns($data, $field_to_group_by, $number_of_columns);
    arrayColumns($data, $number_of_columns, $str_for_blank_cell);

    http://www.swecsoftware.com/blog/
*/

class swec_columns
{
    var $data;
    var $group_field;
    var $number_of_columns;
    var $total_cells;

    function swec_columns() {
        //options
        $this->blank_between_groups = true;
    }

    function chunkColumns($data, $group_field, $number_of_columns = 2)
    {
        $this->data = $data;
        $this->group_field = $group_field;
        $this->number_of_columns = $number_of_columns;

        if (count($data) == 0)
            return "ERROR: No Data";

        if (strlen($group_field) == 0)
            return "ERROR: you must define a group field";

        $this->findHeaderCells();
        return $this->columnSort();
    }

    /*
    * expects data that is sorted by a group field, such as make / model
    * (Ford, F150, F250, F350), will creater header rows and blank rows between groups
    * a group is defined whenever the make field changes
    * each group gets a header cell, and each cell is defined a "cell_type", {blank, data, header}
    */
    function findHeaderCells()
    {
        $make = "";
        $prevMake = "";
        $newArr = array();
        foreach($this->data as $row) {
            $make = $row[$this->group_field];
            if ($make != $prevMake) {
                if (count($newArr) > 0) {
                    //add blank cell
                    if ($this->blank_between_groups == true) {
                        $newArr[] = array("cell_type" => "blank");
                    }
                }
                //add output header cell
                $newRow['cell_type'] = "header";
                $newRow[$this->group_field] = $row[$this->group_field];
                $newArr[] = $newRow;
            }

            $row['cell_type'] = "data";
            $newArr[] = $row;
            $prevMake = $make;

        }

        $this->data = $newArr;
        $this->total_cells = count($this->data);
    }

    //count up an array of values {1, 3, 10, 3, 6} returns 23
    function arraySum($arr)
    {
        $val = 0;
        for($x = 0; $x < count($arr); $x++) {
            $val += $arr[$x];
        }
        return $val;
    }

    //find the largest value in an array
    function arrayLargest($arr)
    {
        $largest = 0;

        for($x = 0; $x < count($arr); $x++) {
            if ($arr[$x] > $largest) {
                $largest = $arr[$x];
            }
        }
        return $largest;
    }

    //count the number of cells in a group, includes header cells and blank cells as added above
    function countCellsByGroup($data)
    {
        $groups = array();
        $sub = 0;
        for ($i = 0; $i < count($data); $i++ )
        {
            $groups[$sub]++;
            if ($data[$i]['cell_type'] == "blank") {
                $sub++;
            }
        }

        return $groups;
    }

    //loop through the array, and determine at which points we break into a new column
    function getColumnBreakPoints($data)
    {
        $total_cells = $this->arraySum($data);
        $avg_cells_per_column = $total_cells / $this->number_of_columns;
        $pos = array();
        $prev_diff = 1;
        for ($x = 0; $x < count($data); $x++)
        {
            $col1 += $data[$x];
            $avg_offset = $col1 / $avg_cells_per_column;

            $diff = (abs($avg_offset - 1));
            if ($diff < $prev_diff) {
                $prev_diff = $diff;
            }
            else {
                $pos[] = $x - 1;
                $prev_diff = 1;
                $col1 = 0;
                $x--;
            }

        }
        //sanity check
        if (count($pos) >= $this->number_of_columns) {
            array_pop($pos);
        }

        return $pos;
    }

    //determine how many data items in each column
    function getColumnCounts($pos, $groups)
    {
        $y = 0;
        $count = 0;
        $newgroups = array();
        for ($x = 0; $x < count($groups); $x++) {
            $count += $groups[$x];
            if ($x == $pos[$y]) {
                $newgroups[$y++] = $count;
                $count = 0;
            }
        }
        $newgroups[$y++] = $count;
        return $newgroups;
    }

    //sort into one group per column
    function columnPerGroup($groups)
    {
        $tabular = array( ) ;
        $max_rows = $this->arrayLargest($groups);   //get the largest column in the table
        foreach ( $groups as $cols ) { //fill each column, $cols is how many items per column
            for ( $i = 0 ; $i < $max_rows ; $i++ ) { 
                if ($i < $cols) {
                    //cols is how many items exist in that column
                    $tabular[$i][ ] = array_shift($this->data) ; 
                }
                else {
                    //add blank for this row / column
                    $tabular[$i][] = array("cell_type" => "blank");
                }
            }
        }

        return $tabular ;
    }

    function columnSort()
    {
        $groups = $this->countCellsByGroup($this->data);
        $number_of_groups = count($groups);
        if ($number_of_groups <= $this->number_of_columns) {
            //just one group per column to fit table, we have more columns than groups
            while (count($groups) < $this->number_of_columns) {
                $groups[] = 0;
            }
            return $this->columnPerGroup($groups);
        }


        $pos = $this->getColumnBreakPoints($groups);


        $groups = $this->getColumnCounts($pos, $groups);

        //fill each row with data up to max_columns, put blank cells at the end of columns if no more data
        //returns a multi-dimensional array
        //each top level array is an entire row, each sub array item, is per column in that row
        $tabular = array( ) ;
        $max_rows = $this->arrayLargest($groups);   //get the largest column in the table
        foreach ( $groups as $cols ) { //fill each column, $cols is how many items per column
            for ( $i = 0 ; $i < $max_rows ; $i++ ) { 
                if ($i < $cols) {
                    //cols is how many items exist in that column
                    $tabular[$i][ ] = array_shift($this->data) ; 
                }
                else {
                    //add blank for this row / column
                    $tabular[$i][] = array("cell_type" => "blank");
                }
            }
        }

        return $tabular ;

    }


    /*
        sort an array by column, top to bottom, left to right
    */
    function arrayColumns($data, $nbrColumns = 2, $strForEmptyCell = "-")
    {
        $total_count = sizeof($data);
        if ($total_count <= 0)
            return;

        if ($nbrColumns <= 0)
            return;

        $total_rows = ceil($total_count / $nbrColumns);
        $rem = $total_count % $nbrColumns;

        $outArray = array();

        if ($rem > 0) {
            $total_count = $nbrColumns * $total_rows;
        }

        $col = 0;
        $row = 0;
        for($x = 0; $x < $total_count; $x++)
        {
            $pos = ($row) + ($col * $total_rows);

            if ($rem > 0) {
                if ($col > $rem) {
                    $pos = $pos - ($col - $rem);
                }

                if ($row == $total_rows - 1) {
                    if ($col >= $rem) {
                        $outArray[] = $strForEmptyCell;
                    }
                    else {
                        $outArray[] = $data[$pos];
                    }
                }
                else {
                    $outArray[] = $data[$pos];
                }
            }
            else {
                $outArray[] = $data[$pos];
            }
            $col++;

            if ($col == $nbrColumns)
            {
                $col = 0;
                $row ++;
            }

        }

        return $outArray;
    }
};

?>

I will admit I'm not entirely satisfied with my solution. I suspect there must be a better way to mathematically calculate the heights of columns, but this is my solution for now. For those that are interested, the concept behind the chunkColumns function is as follows. First I determine how many items are in each group. I then calculate an average column height (by cell count) based on the number of columns and total cell items. After I have that information, I examine each group, and using a variance algorithm, calculate the closest point that I can get to the average column height, while still breaking on group boundaries. I store the break point position, and build the output array based on these breakpoints.

The class provides two functions which you can call.

chunkColumns($data, $field_to_group_by, $number_of_columns);
arrayColumns($data, $number_of_columns, $str_for_blank_cell);    
Follow Us
Facebook Twitter LinkedIn
Sign up for our mailing list
Copyright © 2014. All Rights Reserved.