I have three mysql tables.
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
I want to join these three tables using FileNo and show them order by FolioNo as follows.
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
My SQL query in php is as follows,
$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";
Output,
<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>";
?>
But this shows an error,
Notice: Undefined index: Receiver in C:\xampp\htdocs\sp\viewMinutes.php on line 145 Notice: Undefined index: Minutes in C:\xampp\htdocs\sp\viewMinutes.php on line 145 Notice: Undefined index: Instructed in C:\xampp\htdocs\sp\viewMinutes.php on line 145 Notice: Undefined index: Responsible in C:\xampp\htdocs\sp\viewMinutes.php on line 145 Notice: Undefined index: Receiver in C:\xampp\htdocs\sp\viewMinutes.php on line 145 Notice: Undefined index: Minutes in C:\xampp\htdocs\sp\viewMinutes.php on line 145 Notice: Undefined index: Instructed in C:\xampp\htdocs\sp\viewMinutes.php on line 145 Notice: Undefined index: Responsible in C:\xampp\htdocs\sp\viewMinutes.php on line 145 Notice: Undefined index: Receiver in C:\xampp\htdocs\sp\viewMinutes.php on line 145 Notice: Undefined index: Minutes in C:\xampp\htdocs\sp\viewMinutes.php on line 145 Notice: Undefined index: Instructed in C:\xampp\htdocs\sp\viewMinutes.php on line 145 Notice: Undefined index: Responsible in C:\xampp\htdocs\sp\viewMinutes.php on line 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
Whats the wrong here?
UPDATE:
$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";
Your SELECT clause has no aliases mentioned. As a result, SQL engine does not know what column names to generate. I am assuming it takes the column names of the 1st SELECT. Hence you see undefined index for "Receiver" which is a part of 2nd SELECT.
Also, I do not understand the logic behind the UNION. You are trying to UNION results of 3 different tables that have completely different columns.
I had to make small change in output view. Now everything working fine. My sql query is as follows.
$sql="SELECT I.FileNo, I.FolioNo, I.Received AS Date, I.Sender AS Person, I.Description, I.Forwarded,
S.Minutes, S.Instructed, S.Responsible, S.Action FROM Inbox I
LEFT JOIN Status S ON I.ID=S.SN
WHERE I.FileNo='$ID'
UNION SELECT O.FileName, O.FolioNo, O.Sent AS Date, O.Description, O.Receiver AS Person, O.Reference, O.FileNo, O.Signed, O.Subject, O.ID
FROM Outbox O WHERE O.FileNo='$ID'";
Thanks guys!