php - 匹配两个mysql结果

Question

这是一个初学者的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();
?>

Answer 1

如果将其构建为数据透视表，则可以在单个查询中执行此操作。这个想法是 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>