我在mysql中有两个表格,一个表包含葡萄酒的详细信息,第二个表包含框的详细信息,该列是链接到葡萄酒表的ID,每个瓶子中的葡萄酒表盒子。
例如。:
葡萄酒。ID。姓名。年.Description
盒子.boxid。姓名.bottle1.bottle2 -> ....bottle12
因此,如果我有一个有6瓶的酒箱,则.bottle1 thru .bottle6的田地将包含葡萄酒桌上的瓶子ID。
我想在PHP中运行一个选择查询,该查询与这两个表连接并在我的网页中输出以下内容:
box-xxx"一瓶葡萄酒示例1"一瓶葡萄酒示例2"一瓶葡萄酒示例3"一瓶葡萄酒示例4"一瓶葡萄酒示例5"一瓶葡萄酒示例"
,如果盒子只有一瓶葡萄酒,它将显示
box-xxx"一瓶葡萄酒示例1"
- 编辑 -
我正在尝试以下无效的代码...
<?php
session_start();
include("dbconnect.php");
$box_sql="SELECT a.*
FROM box a left join
WineID b1 on a.bottle1=b1.WineID left join
WineID b2 on a.bottle2=b2.WineID left join
WineID b3 on a.bottle3=b3.WineID left join
WineID b4 on a.bottle1=b4.WineID left join
WineID b5 on a.bottle1=b5.WineID left join
WineID b6 on a.bottle1=b6.WineID left join
WineID b7 on a.bottle1=b7.WineID left join
WineID b8 on a.bottle1=b8.WineID left join
WineID b9 on a.bottle1=b9.WineID left join
WineID b10 on a.bottle1=b10.WineID left join
WineID b11 on a.bottle1=b11.WineID left join
WineID b12 on a.bottle1=b12.WineID left join
where (b1.WineID is not null or
b2.WineID is not null or
b3.WineID is not null or
b4.WineID is not null or
b5.WineID is not null or
b6.WineID is not null or
b7.WineID is not null or
b8.WineID is not null or
b9.WineID is not null or
b10.WineID is not null or
b11.WineID is not null or
b12.WineID is not null)";
if($box_query=mysqli_query($dbconnect, $box_sql)) {
$box_rs=mysqli_fetch_assoc($box_query);
?>
<h1><?php echo $box_rs['boxnumber']; ?></h1>
<?php do { ?>
<div class="item">
<a href="index.php?page=item&stockID=<?php echo $box_rs['WineID']; ?>">
<p><?php echo $box_rs['Name']; ?></p>
</a>
</div>
<?php
} while ($box_rs=mysqli_fetch_assoc($box_query));
?>
<?php
}
?>
-------------编辑-------------
描述下面的葡萄酒输出=
Field Type Null Key Default Extra
WineID int(4) NO PRI NULL auto_increment
categoryID int(1) NO NULL
Name varchar(100) NO NULL
Winery varchar(30) NO NULL
Year varchar(4) NO NULL
Country varchar(20) NO NULL
Region varchar(30) NO NULL
ABV varchar(6) NO NULL
bottlesize varchar(6) NO NULL
Grape varchar(100) NO NULL
Notes varchar(2046) NO NULL
ReadyEst varchar(15) NO NULL
Picture varchar(100) NO NULL
Price varchar(10) NO NULL
描述框输出在=
下面Field Type Null Key Default Extra
boxID int(11) NO PRI NULL auto_increment
boxnumber varchar(10) NO NULL
year varchar(4) NO NULL
size varchar(2) NO NULL
bottle1 varchar(3) NO NULL
bottle2 varchar(3) NO NULL
bottle3 varchar(3) NO NULL
bottle4 varchar(3) NO NULL
bottle5 varchar(3) NO NULL
bottle6 varchar(3) NO NULL
bottle7 varchar(3) NO NULL
bottle8 varchar(3) NO NULL
bottle9 varchar(3) NO NULL
bottle10 varchar(3) NO NULL
bottle11 varchar(3) NO NULL
bottle12 varchar(3) NO NULL
好吧,我有一个解决您的"问题"的解决方案
尝试此结构:
葡萄酒桌:
Field Type Null Key Default Extra
WineID int(4) NO PRI NULL auto_increment
categoryID int(1) NO NULL
Name varchar(100) NO NULL
Winery varchar(30) NO NULL
Year varchar(4) NO NULL
Country varchar(20) NO NULL
Region varchar(30) NO NULL
ABV varchar(6) NO NULL
bottlesize varchar(6) NO NULL
Grape varchar(100) NO NULL
Notes varchar(2046) NO NULL
ReadyEst varchar(15) NO NULL
Picture varchar(100) NO NULL
Price varchar(10) NO NULL
框表:
Field Type Null Key Default Extra
boxID int(11) NO PRI NULL auto_increment
boxnumber varchar(10) NO NULL
year varchar(4) NO NULL
size varchar(2) NO NULL
wine_in_box表:
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
boxID int(11) NO NULL
wineID int(11) NO NULL
现在您要做的就是制作许多葡萄酒,然后创建一个盒子。要填充盒子,您将插入wine_in_box表中的表格:
INSERT INTO Wine_In_Box (boxId, wineID) VALUES (1, 1);
然后,当您要检索盒子的内容时,您可以运行此查询:
SELECT wid.*, w.*, b.* FROM Wine_In_Box wid LEFT JOIN Wine w ON w.WineID= wid.wineId LEFT JOIN Box b on b.boxID = wid.boxID WHERE boxId = ?;
此查询将返回包装盒中所有葡萄酒的所有信息,您可以按框选择。您也可以使用IN
子句一次更改Where子句以一次获取多个框。