我的应用程序需要创建一个库存管理表,该表将许多项目分类为子组。对于和想法,库存管理表看起来像:
Location1 Location2 Total
Desktops // Main Category
Microsoft //Sub-Category
Windows 7 23000 150000 173000
Office 2011 203300 3002 206302
....
Apple //Sub-Category
OS Snow Leopard 4000 3000 7003
OS Lion 39494 40034 79528
...
Tablets //Main Category
Lenovo //Sub-Cateogry
LX-243 3434 4399 7833
...
这是表应该是什么样子的可视化,现在我有一个巨大的mysql查询,它对我来说是这样做的,这很可怕。总而言之,我正在做的是:
- 选择第一个类别并开始while循环//回声作为主要类别
- 选择与主类别相对应的子类别,然后再次启动while循环。//echo作为子类别
- 选择子类别中的所有项目并开始while循环
- 选择第一个位置
- 从3中选择当前项目,从4中选择当前位置并回显项目。//回显项目名称和位置中的数量
现在我的问题是,有没有比使用几个while循环更好的方法来显示这一点,我尝试过使用函数,但它们也变得一团糟。此外,我也不知道我应该在哪里进行计算,以获得最后一列的总数
数据库结构:
Category: CategoryID, Description
Sub-Cateogry: Sub-Category_ID, Category_ID, Description
Item: Item_ID, Sub-category_id, Description
Location: Location_ID, Description
Stock_Management: Item_ID, Location_ID, Quantity
按要求编码:
$sql = mysql_query("select Board_ID, Title from Board where Company_ID = '$company_id' order by Title");
while($row = mysql_fetch_array($sql)) {
$curr_ID = $row[0];
$curr_category = $row[1];
echo "<tr class='sub-heading' style='background: rgba(76, 178, 255, 0.1)'><td colspan='".$count."'>".$curr_category."</td></tr>";
$sql1 = mysql_query("select Sub_Category_ID, Title from Sub_Category where Category_ID = '$curr_ID' order by Title");
while($row1 = mysql_fetch_array($sql1)) {
$curr_sub_cat_id = $row1[0];
$curr_sub_cate = $row1[1];
echo "<tr style='background: rgba(149, 255, 145, 0.10'><td colspan='".$count."'><b>".$curr_sub_cate."</b></td></tr>";
$sql2 = mysql_query("select Book_ID, title from Book where sub_category_id = '$curr_sub_cat_id' Order by title");
while($row2 = mysql_fetch_array($sql2)) {
$curr_book = $row2[0];
echo "<tr><td>".$row2[1]."</td>";
$sql4 = mysql_query("select OfficeID, OfficeTitle from Office where OfficeTitle IN ('$locations')");
while($row3 = mysql_fetch_array($sql4)) {
$curr_location = $row3[0];
$sql3 = mysql_query("select Quantity from Stock_Management where Book_ID = '$curr_book' and Location_ID = '$curr_location'");
while($row3 = mysql_fetch_array($sql3)) {
echo "<td>".$row3[0]."</td>";
}
}
echo "</tr>";
}
}
}
这样的东西怎么样-
<?php
$locations = array('Location 1', 'Location 2', 'Location 3');
$locationCols = array();
$locationList = array();
foreach ($locations as $location) {
$locationColName = preg_replace('[^a-z0-9]', '_', strtolower($location));
$location = mysql_real_escape_string($location);
$locationCols[] = "SUM(IF(Office.OfficeTitle = '$location', Stock_Management.Quantity, 0)) AS `$locationColName`";
$locationList[] = "'$location'";
}
$locationCols = implode(', ', $locationCols);
$locationList = implode(',', $locationList);
$sql = "SELECT Board.Title AS BoardTitle, Sub_Category.Title AS SubCatTitle, Book.title AS BookTitle, $locationCols, SUM(Stock_Management.Quantity) AS Total
FROM Board
INNER JOIN Sub_Category
ON Board.Board_ID = Sub_Category.Category_ID
INNER JOIN Book
ON Sub_Category.Sub_Category_ID = Book.sub_category_id
INNER JOIN Office
LEFT JOIN Stock_Management
ON Book.Book_ID = Stock_Management.Book_ID
AND Office.OfficeID = Stock_Management.Location_ID
WHERE Board.Company_ID = 2
AND Office.OfficeTitle IN ($locationList)
GROUP BY Board.Title, Sub_Category.Title, Book.title";
$result = mysql_query($sql);
$prevBoard = '';
$prevSubCat = '';
echo '<table>';
while ($row = mysql_fetch_object($result)) {
// if new board print board
if ($prevBoard != $row->BoardTitle) {
echo '<tr class="sub-heading" style="background: rgba(76, 178, 255, 0.1)"><td colspan="">' . $row->BoardTitle . '</td></tr>';
}
$prevBoard = $row->BoardTitle;
if ($prevSubCat != $row->SubCatTitle) {
echo '<tr style="background: rgba(149, 255, 145, 0.10)"><td colspan=""><b>' . $row->SubCatTitle . '</b></td></tr>';
}
$prevSubCat = $row->SubCatTitle;
// print product row
echo '<tr>';
echo "<td>{$row->BookTitle}</td>";
foreach ($locations as $location) {
$locationColName = preg_replace('[^a-z0-9]', '_', strtolower($location));
echo "<td>{$row->$locationColName}</td>";
}
echo "<td>{$row->Total}</td>";
echo '</tr>';
}
echo '<table>';