开发者

Categorizing mysql data into seperate html tables?

I have a database query that returns ItemID, ItemName, CategoryID and CategoryName. I am trying to group my results into separate html tables by their CategoryName so the end result looks something like this:

 ________________________________
|____________CategoryName________|
| ItemName |                     |
|__________|                     |
| ItemName |                     |
|__________|                     |
| ItemName |                     |
|__________|_____________________|
 ________________________________
|____________CategoryName________|
| ItemName |                     |
|__________|                     |
| ItemName |                     |
|__________|                     |
| ItemName |               开发者_如何学Go      |
|__________|_____________________|

Currently I can output my data into one table, but I am uncertain how to go about the rest. Seems i have better ascii art skills than php skills :/


The HTML is off, but this should get you started:

<?php

$query = 'SELECT CategoryID, CategoryName, ItemID, ItemName
FROM tableName
ORDER BY CategoryID';

$result = mysql_query($query);
if (!$result || mysql_num_rows($result) == 0) {
    echo "No rows found";
    exit;
}

$lastCatID = 0; //or some other invalid category ID

while ($row = mysql_fetch_assoc($result)) {
    if($lastCatID != $row['CategoryID']) {
        //starting a new category
        if($lastCatID != 0) {
            //close up previous table
            echo '</table>';
        }

        //start a new table
        echo '<table><th><td colspan="2">Category '
                . $row['CategoryName'] .'</td></th>';
        $lastCatID = $row['CategoryID'];
    }

    echo '<tr><td>' . $row['ItemName'] . '</td><td></td></tr>';
}

if($lastCatID != 0) {
    //close up the final table
    echo '</table>';
}

mysql_free_result($result);
?>


Sort your data by CategoryName and then start loop. In loop, write starting HTML for first table and start outputing row by row, but before each row check for CategoryName - if it's changed, then close current table and start new one.


I like working with foreach better - like so:

$query = "SELECT CategoryID, CategoryName, ItemID, ItemName
FROM tableName
ORDER BY CategoryID";

$result = mysql_query($query);
if (!$result || mysql_num_rows($result) == 0) {
    echo "No rows found";
    exit;
}

$rows = mysql_fetch_assoc($result);

// create an array with the data structure closest to the HTML 
foreach( $rows as $row){
    $cats_data[$row['CategoryID']]['CategoryName'] = $row['categoryName'] ;
    $cats_data[$row['CategoryID']]['Items'][] = $row['ItemName'] ;
}

// Iterate through each category
foreach ( $cats_data as $category ) {
     echo '<table>';

     // echo out the table header
     echo '<tr><td>Item List for '.$category['CategoryName'].'</td></tr>' ;

     // Iterate through all the items in eaceh category
     foreach( $category['items'] as $item ) {
           echo '<tr><td>'.$item.'</td></tr>';
     }

     echo '</table>';
}

// cleanup
mysql_free_result($result);

it feels a lot simpler to me :)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜