Displaying mysql data in tables with group headers

Question

I have an SQL which returns records in a mysql table. Each record has many fields, and one of the field called "type" which is the type of sample. All samples are broadly classified into six types. I am displaying all the records in the table. But I want to group the data by 'type' and show separate tables with type as side heading. At present I use this script.

echo "<table class='wqtable'><tr><th>USIN</th><th>Type</th><th>Date of Coll.</th>   <th>Location</th><th>Description</th><th>H3</th><th>Cs</th><th>Sr</th><th>Analyst</th></tr>";


while($data=mysql_fetch_array($result)){

echo "<tr>";

echo "<td>".$data['usin']."</td>";
echo "<td>".$data['type']."</td>";
echo "<td>".$data['doc1']."</td>";
echo "<td>".$data['location']."</td>";
echo "<td>".$data['description']."</td>";
echo "<td>".$data['h3']."</td>";
echo "<td>".$data['cs']."</td>";
echo "<td>".$data['sr']."</td>";
echo "<td>".$data['name']."</td>";

echo"</tr>";
}
echo "</table>";

This displays all the records in one table. I want to break the table when all the records with same value in type field is displayed and begin a new table for next type of samples. (like group header) Is there any easy way other than running separate SQLs?

OdinX · Accepted Answer · 2014-06-19 11:17:59Z

Make sure your SQL query is ORDER BY type and then in your while loop you could do this:

$lastType = '';
echo '<table>';
while($data=mysql_fetch_array($result)) {
    if($lastType != '' && $lastType != $data['type']) {
        echo '</table>';
        echo '<table>';
    }

    echo "<tr>";
    echo "<td>".$data['usin']."</td>";
    echo "<td>".$data['type']."</td>";
    echo "<td>".$data['doc1']."</td>";
    echo "<td>".$data['location']."</td>";
    echo "<td>".$data['description']."</td>";
    echo "<td>".$data['h3']."</td>";
    echo "<td>".$data['cs']."</td>";
    echo "<td>".$data['sr']."</td>";
    echo "<td>".$data['name']."</td>";
    echo"</tr>";

    $lastType = $data['type'];
}
echo '</table>';

I've not tested it but it whenever the type changes it will insert a closing and opening table tag (you can obviously add whatever else you want in there).

Tyler · Accepted Answer · 2014-06-19 11:19:52Z

0

I set up a SQL stored procedure that I group data using the GROUP BY methodology. I took this technique from SSRS reporting on Microsoft SQL Server. I create Views that hold specific information that I may use a lot. Then I execute stored procedures that act like setting up a data source in report builder or something. That will automatically group the data for you, then I can filter the data in a structure like:

MainGroupA

ChildGroupA

data

MainGroupB{

ChildGroupA{

...

answered Jun 19, 2014 at 11:19

Tyler

18310 bronze badges

Comments

Tom · Accepted Answer · 2014-06-19 11:35:36Z

0

If you don't want to order by the type in the query, you could build up your HTML as an array instead. For example:

$table_types = array();

while($data = mysql_fetch_array($result))
{
    $html = "";
    $html .= "<tr>";
    $html .= "<td>".$data['usin']."</td>";
    $html .= "<td>".$data['type']."</td>";
    $html .= "<td>".$data['doc1']."</td>";
    $html .= "<td>".$data['location']."</td>";
    $html .= "<td>".$data['description']."</td>";
    $html .= "<td>".$data['h3']."</td>";
    $html .= "<td>".$data['cs']."</td>";
    $html .= "<td>".$data['sr']."</td>";
    $html .= "<td>".$data['name']."</td>";
    $html .= "</tr>";

    $table_types[$data['type']][] = $html;
}

foreach($table_types as $type => $rows)
{
    // Use a switch statement to put the correct text heading above the table.
    echo "<h3>Type ".$type."</h3>";
    echo "<table class='wqtable type-".$type."'><tr><th>USIN</th><th>Type</th><th>Date of Coll.</th><th>Location</th><th>Description</th><th>H3</th><th>Cs</th><th>Sr</th><th>Analyst</th></tr>";
    echo implode($rows);
    echo "</table>";
}

answered Jun 19, 2014 at 11:35

Tom

9137 silver badges13 bronze badges

1 Comment

Tom Over a year ago

Note that this wouldn't be suitable if you have a large result set, as it would use a fair amount of memory (because it's storing the entire result before rendering).

Collectives™ on Stack Overflow

Displaying mysql data in tables with group headers

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related