这是一个php/mysql初学者的问题，我已经花了几个小时处理各种循环，但我还没能达到目标。我在数据库里有两张桌子。一个包含书籍，另一个包含这些书籍的库存信息。像这样：
id title
1 A book
2 Another book
3 A third book
对于股票（book_stock）
id book_id warehouse_id qty
1 1 1 12
2 1 2 45
3 2 3 22
4 3 1 78
5 1 3 15
我想读取这些表，将book表中的id与stock表中的book_id进行匹配，然后在html表中输出一个摘要，显示书名以及不同仓库中每本书的数量。到目前为止，我所尝试的是循环mysql请求，这似乎会崩溃，因为它没有输出任何结果。我觉得解决方案很简单，涉及数组，但我的技能不足。。。
这是我的非工作代码。该函数适用于手动输入的1、2甚至20个结果，但仅在循环时输出空白表格单元格。
//function to get stock based on book id number
function get_stock($book_row_number)
{
//get BOOK from db
$book_query="SELECT * FROM book";
$book_result=mysql_query($book_query);
//determine book info
$book_id=mysql_result($book_result,$book_row_number,"id");
$book_title=mysql_result($book_result,$book_row_number,"title");
//get LONDON stock for this book from db
$stock_query="SELECT qty FROM book_stock WHERE book_id='$book_id' AND warehouse_id='1'";
$stock_result=mysql_query($stock_query);
$stock_london=mysql_result($stock_result,0,"qty");
//get USA stock for this book from db
$stock_query="SELECT qty FROM book_stock WHERE book_id='$book_id' AND warehouse_id='2'";
$stock_result=mysql_query($stock_query);
$stock_usa=mysql_result($stock_result,0,"qty");
//get GERMANY stock for this book from db
$stock_query="SELECT qty FROM book_stock WHERE book_id='$book_id' AND warehouse_id='4'";
$stock_result=mysql_query($stock_query);
$stock_germany=mysql_result($stock_result,0,"qty");
echo "<tr><td>$book_title</td><td>$stock_london</td><td>$stock_usa</td><td>$stock_germany</td></tr>'n";
}
//find number of rows in book list
$all_query="SELECT * FROM book";
$all_result=mysql_query($all_query);
$all_rows=mysql_numrows($all_result);
// run the function on all the rows
$i=0;
while ($i < $all_rows) {
get_stock('$all_rows');
$i++;
}
编辑：现在的代码是
<?php
//set up connection
$user="username";
$password="paswd";
$database="database";
mysql_connect("localhost",$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
//query
$book_query="SELECT
book_id,
title,
SUM(CASE WHEN warehouse_id = 1 THEN book_stock.qty ELSE 0 END) AS warehouse1,
SUM(CASE WHEN warehouse_id = 2 THEN book_stock.qty ELSE 0 END) AS warehouse2,
SUM(CASE WHEN warehouse_id = 3 THEN book_stock.qty ELSE 0 END) AS warehouse3,
SUM(CASE WHEN warehouse_id = 4 THEN book_stock.qty ELSE 0 END) AS warehouse4
FROM
book_stock
JOIN book ON book.id = book_stock.book_id
GROUP BY book_id, title";
//loop
$result = mysql_query($book_query);
if ($result) {
while ($row = mysql_fetch_assoc($row)) {
echo "<tr><td>" . htmlspecialchars($row['title']) . "</td><td>{$row['warehouse1']}</td><td>{$row['warehouse2']}</td><td>{$row['warehouse3']}</td><td>{$row['warehouse4']}</td></tr>";
}
}
else echo mysql_error();
//close database
mysql_close();
?>
如果将其构建为透视表，则可以在单个查询中完成此操作。其思想是，SUM()聚合将匹配的warehouse_id的qty值加在一起，或者在每行不匹配时加0，将所有仓库折叠成一行。
SELECT
SUM(CASE WHEN warehouse_id = 1 THEN qty ELSE 0 END) AS warehouse1,
SUM(CASE WHEN warehouse_id = 2 THEN qty ELSE 0 END) AS warehouse2,
SUM(CASE WHEN warehouse_id = 3 THEN qty ELSE 0 END) AS warehouse3,
SUM(CASE WHEN warehouse_id = 4 THEN qty ELSE 0 END) AS warehouse4
FROM
book_stock
WHERE book_id = $book_id
对于book_id = 1，这会产生如下结果：
warehouse1 warehouse2 warehouse3 warehouse4
12 45 15 0
要获得行中所有书籍的GROUP BY:
SELECT
book_id,
title,
SUM(CASE WHEN warehouse_id = 1 THEN qty ELSE 0 END) AS warehouse1,
SUM(CASE WHEN warehouse_id = 2 THEN qty ELSE 0 END) AS warehouse2,
SUM(CASE WHEN warehouse_id = 3 THEN qty ELSE 0 END) AS warehouse3,
SUM(CASE WHEN warehouse_id = 4 THEN qty ELSE 0 END) AS warehouse4
FROM
book_stock
JOIN book ON book.id = book_stock.book_id
GROUP BY book_id, title
PHP的输出是一个琐碎的while循环：
// Assuming you already opened your <table>
$result = mysql_query($the_big_query_above);
if ($result) {
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td>" . htmlspecialchars($row['title']) . "</td><td>{$row['warehouse1']}</td><td>{$row['warehouse2']}</td><td>{$row['warehouse3']}</td><td>{$row['warehouse4']}</td></tr>";
}
}
else echo mysql_error();
// Then don't forget to close your </table>