PHP MySQL连接3个表并输出结果 - PHP MySQL join 3 tables and output result

PHP MySQL join 3 tables and output result

本文关键字：输出结果 3个 MySQL 连接 PHP | 更新日期: 2023-09-27

我有三个表如下:

项目:

-----------------------------------------------------
|  itemID  |  itemName  |  categoryID  |  sellerID  |
-----------------------------------------------------
|  1       |  item1     |  c1          |  s1        |
|  2       |  item2     |  c1          |  s2        |
|  3       |  item3     |  c3          |  s2        |
|  4       |  item4     |  c2          |  s3        |
-----------------------------------------------------

类别:

---------------------------------
|  categoryID  |  categoryName  |
---------------------------------
|  c1          |  category1     |
|  c2          |  category2     |
|  c3          |  category3     |
---------------------------------

卖家:

-----------------------------
|  sellerID  |  sellerName  |
-----------------------------
|  s1        |  seller1     |
|  s2        |  seller2     |
|  s3        |  seller3     |
-----------------------------

我想从 Items 表中选择项目，其中categoryID为 c1 并显示如下:

----------------------------------------------
|  itemName  |  categoryName  |  sellerName  |
----------------------------------------------
|  item1     |  category1     |  seller1     |
|  item2     |  category1     |  seller2     |
----------------------------------------------

我不知道该怎么做。

看看JOIN:

SELECT i.itemName, c.categoryName, s.sellerName
FROM Items i
JOIN Categories c
  ON c.id = i.categoryID
JOIN Seller s
  ON s.id = i.sellerID
WHERE i.categoryID = 'c1'

像这样:

select items.itemName, categories.categoryName, seller.sellerName
from items
join categories on items.categoryId = categories.categoryId
join seller on items.sellerId = seller.sellerId
where categories.categoryId = ?

SELECT itemName, categories.categoryName, seller.sellerName 
FROM items 
INNER JOIN categories ON items.categoryID = categories.categoryID 
INNER JOIN seller ON items.sellerID = seller.sellerID 
WHERE items.categoryID = 'c1'

试试这个:

$strSql = 'SELECT a.`itemName`,b.`categoryName`,c.`sellerName` FROM `Items` a INNER JOIN `Categories` b ON a.`categoryID` = b.`categoryID`
         INNER JOIN `Seller` c ON a.`sellerID` = c.`sellerID` WHERE a.`categoryID` = "c1" ';

这只是创建正确连接的问题。在本例中，需要将表附加到两个不同的东西上。语法应该是这样的:

$stmt = 'SELECT a.itemName,b.categoryName,c.sellerName FROM `[itemsTable]`
    inner join a  on b.categoryID  = a.categoryID  
inner join a on c.sellerID = a.sellerID';