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 :)
精彩评论