php - PHP 中的 MySQL UNION 运算符错误

Question

我有三个 mysql 表。

1. inbox - ID(int), FileNo(varchar), FolioNo(smallint), Reference(varchar), Received(date), Sent(date), Description(text), Forwarded(varchar),...etc

2. outbox - ID(int), Reference(varchar), Sent(date), FileNo(varchar), FileName(varchar), FolioNo(smallint), Description(text), Receiver(varchar),...etc

3. status - ID(int), SN(int), Date(date), Minutes(varchar), Instructed(varchar), Responsible(varchar), Action(boolean), FileNo(varchar),...etc

我想使用 FileNo 连接这三个表，并按 FolioNo 显示它们的顺序，如下所示。

File No: 123
File Name: abc
Folio No | Date     | Description | Sender/Receiver | Minutes   | Instructed | Responsible | Action
----------------------------------------------------------------------------------------------------
1        | 13-09-08 | Something   | Someone         | Something | Someone    | Someone     | Yes
2        | 13-09-10 | Something   | Someone         | Something | Someone    | Someone     | Yes
......etc

我在php中的SQL查询如下，

$sql="SELECT * FROM 
(SELECT I.FileNo, I.FolioNo, I.Received AS Date, I.Description, I.Forwarded 
FROM Inbox I WHERE I.FileNo='$ID' 

UNION SELECT O.FileName, O.FolioNo, O.Sent AS Date, O.Description, O.Receiver 
FROM Outbox O WHERE O.FileNo='$ID' 

UNION SELECT S.Date, S.Minutes, S.Instructed, S.Responsible, S.Action 
FROM Status S WHERE S.FileNo='$ID') 

AS A ORDER BY A.FolioNo";

输出，

<table>
<tr><th>Folio No</th><th>Date</th><th>Description</th><th>Sender/ Receiver</th><th>Minutes</th><th>Instructed</th><th>Responsible</th><th>Action</th></tr>
<?php                           
while ($list = mysql_fetch_assoc($result)) {
echo "<tr><td>" . $list['FolioNo'] . "</td><td>" . $list['Date'] . "</td><td>" . $list['Description'] . "</td><td>" . $list['Receiver'] . "</td><td>" . $list['Minutes'] . "</td><td>" . $list['Instructed'] . "</td><td>" . $list['Responsible'] . "</td><td>" . ($list['Action']=1?'Yes':'No') . "</td></tr>";
$x++;
}
echo "</table>";
?>

但这显示错误，

注意:未定义索引:接收器位于 C:\xampp\htdocs\sp\viewMinutes.php 第 145 行注意:未定义索引:C:\xampp\htdocs\sp\viewMinutes.php 第 145 行的分钟注意:未定义索引:在 C:\xampp\htdocs\sp\viewMinutes.php 第 145 行中指示注意:未定义索引:在 C:\xampp\htdocs\sp\viewMinutes.php 第 145 行负责注意:未定义索引:接收器位于 C:\xampp\htdocs\sp\viewMinutes.php 第 145 行注意:未定义索引:C:\xampp\htdocs\sp\viewMinutes.php 第 145 行的分钟注意:未定义索引:在 C:\xampp\htdocs\sp\viewMinutes.php 第 145 行中指示注意:未定义索引:在 C:\xampp\htdocs\sp\viewMinutes.php 第 145 行负责注意:未定义索引:接收器位于 C:\xampp\htdocs\sp\viewMinutes.php 第 145 行注意:未定义索引:C:\xampp\htdocs\sp\viewMinutes.php 第 145 行的分钟注意:未定义索引:在 C:\xampp\htdocs\sp\viewMinutes.php 第 145 行中指示注意:未定义索引:在 C:\xampp\htdocs\sp\viewMinutes.php 第 145 行负责

FOLIO NO |  DATE    | DESCRIPTION   | SENDER/ RECEIVER  | MINUTES   | INSTRUCTED    | RESPONSIBLE   | ACTION
-------------------------------------------------------------------------------------------------------------------------------------
1        | 13-09-08 |   Something   |                   |           |               |               | Yes
2        | 13-09-08 |   Something   |                   |           |               |               | Yes
         | 13-09-08 |   Something   |                   |           |               |               | Yes
Something|  Someone |   Anyone  |               Yes

这里出了什么问题？

更新:

$sql = "SELECT 
Inbox.FolioNo, Inbox.Received, Inbox.Description, Inbox.Forwarded, 
Outbox.FileNo, Outbox.FileName, Outbox.FolioNo, Outbox.Sent, Outbox.Description, Outbox.Receiver, 
Status.Date, Status.Minutes, Status.Instructed, Status.Responsible, Status.Action 
FROM Status INNER JOIN Outbox ON Status.FileNo = Outbox.FileNo 
INNER JOIN Inbox ON Status.FileNo = Inbox.FileNo WHERE Status.FileNo = '$ID' ORDER BY Outbox.FolioNo ASC";

Answer 1

您的 SELECT 子句没有提及别名。因此，SQL 引擎不知道要生成什么列名。我假设它采用第一个 SELECT 的列名。因此，您会看到“Receiver”的未定义索引，它是第二个 SELECT 的一部分。

另外，我不明白 UNION 背后的逻辑。您正在尝试对具有完全不同列的 3 个不同表的结果进行 UNION。