如何编写嵌套查询MySQL PHP - How to write nested query MySQL PHP

我正在开发一个编辑董事会会议的页面，我想将没有参加特定会议的所有董事会成员显示为下面的复选框，如果用户想添加更多内容，他们将作为编辑参加，所以我这样做了：

我的代码：

$q = "SELECT * FROM `boardteam`";
    $r = mysql_query($q);
    while ($dbfield = mysql_fetch_assoc($r))
    {
        $member_id =$dbfield['nationalID'];
        $query = "SELECT `attendance` FROM `meetingattendance` WHERE `meetingID` = '$mid' AND `attendance`!= '$member_id'";
    $res = mysql_query($query);
    if ($res)
        {
        $tname ="";
        switch ($dbfield['titleName'])
        {
            case "Dr":
            $tname .= "د.";
            break;
            case "Ms":
            $tname .= "السيدة.";
            break;
            case "Mr":
            $tname .= "السيد.";
            break;
        }
        $At .= "<input type='checkbox' name='moreAttendence[]' dir='rtl' value=".$dbfield['nationalID']."><div class='styled-checkbox'>".$tname." ".$dbfield['fName']." ".$dbfield['sName']." ".$dbfield['lName']."</div><br>";
        }
    }

数据库：

CREATE TABLE `boardteam` (
`nationalID` int(10) NOT NULL,
`titleName` char(2) NOT NULL,
`fName` char(20) NOT NULL,
`sName` char(20) NOT NULL,
`lName` char(20) NOT NULL,
`gender` char(1) NOT NULL,
`birthDate` date DEFAULT NULL,
`materialStatus` char(15) DEFAULT NULL,
`jobTitle` varchar(100) NOT NULL,
`jobLocation` varchar(20) DEFAULT NULL,
`employer` varchar(100) DEFAULT NULL,
`email` varchar(100) NOT NULL,
`photo` varchar(255) DEFAULT NULL,
`academicGrade` char(15) DEFAULT NULL,
`employmentStartDate` date NOT NULL,
`employmentEndDate` date NOT NULL,
`employmentType` char(20) DEFAULT NULL,
`employmentStatus` char(15) DEFAULT NULL,
`jobStartDate` date DEFAULT NULL,
`jobNumber` int(10) DEFAULT NULL,
`cv` varchar(255) DEFAULT NULL,
PRIMARY KEY (`nationalID`)
)

CREATE TABLE `meetingattendance` (
 `meetingID` int(11) NOT NULL,
 `attendance` int(10) DEFAULT NULL,
 `absence` int(10) DEFAULT NULL,
 `reason` varchar(255) DEFAULT NULL,
 `additionalAttendance` varchar(255) DEFAULT NULL,
 KEY `absence` (`absence`),
 KEY `meeingID` (`meetingID`),
 KEY `attendance` (`attendance`),
 CONSTRAINT `meetingattendane_ibfk_1` FOREIGN KEY (`meetingID`) REFERENCES `boardmeetings` (`meetingID`),
 CONSTRAINT `meetingattendane_ibfk_2` FOREIGN KEY (`attendance`) REFERENCES `boardteam` (`nationalID`),
 CONSTRAINT `meetingattendane_ibfk_3` FOREIGN KEY (`absence`) REFERENCES `boardteam` (`nationalID`)
)

用我的代码，我得到了所有董事会成员，包括出席的人，如何解决这个问题？？

您需要使用LEFT JOIN来查找董事会团队中未参加特定会议的人员。例如：

SELECT b.*, m.attendance
  FROM boardTeam b
    LEFT JOIN meetingattendance m
      ON b.nationalID = m.attendance AND m.meetingID = $mid
    WHERE m.meetingID IS NULL

如果你想让所有的董事会成员，然后在PHP中确定他们是否参加了会议，只需删除m.attendance IS NULL条款，例如：

SELECT b.*, m.attendance as attendance
  FROM boardTeam b
    LEFT JOIN meetingattendance m
      ON b.nationalID = m.attendance AND m.meetingID = $mid

现在，当您在php中循环遍历响应行时，您可以这样测试（假设您将行一个接一个地提取到$row变量中）：

if($row['attendance'] != null)
{
  // attended meeting
}
else
{
  // did not attend meeting
}

此外，正如评论中提到的，使用mysqli或pdo而不是纯mysql_函数

小提琴示例：http://sqlfiddle.com/#！9/ba7d4/6