How to write nested query MySQL PHP

Question

I'm developing a page to edit board meetings and I want to display all board members who did not attend specific meeting as a checkox located below who attend as an edit in case of user want to add more so I did this:

My code:

$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>";
        }

    }

DB:

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`)
)

With my code I got all board members including who attend, How to fix that ??

Don't write nested queries, that's highly inefficient: write a single query using a JOIN between the two tables — Mark Baker
– Mark Baker, Commented Apr 2, 2015 at 7:15
First of all please use mysqli_* because mysql_* is deprecated. and how to use multi-query in php take a look of this nice example:- w3schools.com/php/func_mysqli_multi_query.asp. But correct way to do this is Join. — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Apr 2, 2015 at 7:16
more specifically, an outer join to find values that don't match — pala_
– pala_, Commented Apr 2, 2015 at 7:16
Thank You guys for the rich Information you gave me many lessons ^^ — Learner
– Learner, Commented Apr 2, 2015 at 7:34

pala_ · Accepted Answer · 2015-04-02 07:24:40Z

1

You need to use a LEFT JOIN in order to find people in the boardTeam who were not in a specific meeting. eg:

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

If you want to get ALL board members, and then determine within PHP if they attended the meeting or not, simply remove the m.attendance IS NULL clause, as such:

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

and now when you loop through the response rows in php, you can test as such (assuming you fetch your rows one by one into a $row variable):

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

Also, as mentioned in the comments, use mysqli, or pdo instead of pure mysql_ functions

Example fiddle here: http://sqlfiddle.com/#!9/ba7d4/6

edited Apr 2, 2015 at 7:24

answered Apr 2, 2015 at 7:19

pala_

9,0101 gold badge18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Learner Over a year ago

Thank you so much ,, I will rewrite all my nested queries ^^

Collectives™ on Stack Overflow

How to write nested query MySQL PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related