PHP Checking Values in One Query Against Another

Question

I am essentially trying to execute two queries, one to grab the requirements of a given program (or major), and the other to grab the courses a student has completed.

I want to check the returned strings against one another, and if a user has taken a required course, place a "checkbox" next to the returned requirement. If they have not taken the required course, place an empty box next to the requirement.

I've gotten almost there where the checkbox concept works, however when I go with two while loops, it duplicates requirements (courses) returned.

When I use one while loop, it fetches the data on the second query and returns all requirements, but simply stops checking after it has found its first match (e.g. does not proceed to see if course 2 or 3 or 4 have been completed, etc).

I've also tried strcmp function to check the $studentsubject against the $programsubject (ditto for programnumber and studentnumber).

If anyone can provide incite as to how to get this working, or provide an alternative method, I would greatly appreciate it. I can provide more details if need be.

         <table class="table">
    <tr>
            <th>&nbsp;</th>
        <th>Class</th>
    <th>Term</th>
    <th>Credits</th>
    </tr>


<?php 

$q = $db->query("SELECT * FROM `program_courses` a, `programs` b, `user_details` c WHERE a.pid = b.pid AND c.uid = '".$_GET['id']."' AND c.major = b.major");



while($program = $q->fetch()) {

$w = $db->query("SELECT * FROM `user_courses` WHERE uid = '".$_GET['id']."'");

$student = $w->fetch(); { // have also tried using a while loop here



$programsubject=$program['subject'];
$programnumber=$program['number'];


$studentsubject=$student['subject'];
$studentnumber=$student['number'];


?>



<?php 


if ($studentsubject==$programsubject && $studentnumber==$programnumber) {

        $checkbox = 'src="http://www.clipartbest.com/cliparts/ncX/jL6/ncXjL6rcB.png" width="25px" height="25px"';


    } elseif ($studentsubject!=$programsubject || $studentnumber!=$programnumber) {


        $checkbox = 'src="http://www.clker.com/cliparts/e/q/p/N/s/G/checkbox-unchecked.svg" width="25px" height="25px"';

    }

    ?>


    <?php

  //check off the requirement if the student has completed the course



        echo '
    <tr style="background-color:#E9FFD2">
    <td> <img '.$checkbox.'> </td>
            <td>'.$programsubject.' '.$programnumber.'</td>
    <td>&nbsp;</td>
    <td>3</td>


    </tr>';



?>


<?php

//End our conditionals

}

 }

 ?>
</table> 

EDIT: Additional information including table structure for these respective tables provided.

I was hoping to avoid altering the two respective queries, but here's a dump of what each returns. My reason for wanting to keep the data separate is so that more complicated conditions can be introduced in the future (e.g. courses with a minimum grade can count, or only courses with a precise number of credits taken, etc).

For user 1001, the following would dump for...

SELECT * 
FROM `program_courses` a, `programs` b, `user_details` c 
WHERE a.pid = b.pid AND c.uid = '1001' AND c.major = b.major

id, pid, subject, number, credits, pid, major, title, degree, college, catalog, credits_req, id, uid, major, college, degree, catalog_year, credits, gpa, academic_standing, advisor, holds, id
'3','1','IDT','600','3','1','4574','Instructional Design & Technology','PHD','45','201408','72','1','1001','4574','45','Doctor of Philosophy','201408','52','3.44','Good Standing','Terence A','01'
'4','1','IDT','610','3','1','4574','Instructional Design & Technology','PHD','45','201408','72','1','1001','4574','45','Doctor of Philosophy','201408','52','3.44','Good Standing','Terence A','01'
'5','1','IDT','693J','3','1','4574','Instructional Design & Technology','PHD','45','201408','72','1','1001','4574','45','Doctor of Philosophy','201408','52','3.44','Good Standing','Terence A','01'
'6','1','IDT','750','3','1','4574','Instructional Design & Technology','PHD','45','201408','72','1','1001','4574','45','Doctor of Philosophy','201408','52','3.44','Good Standing','Terence A','01'
'7','1','IDT','790','3','1','4574','Instructional Design & Technology','PHD','45','201408','72','1','1001','4574','45','Doctor of Philosophy','201408','52','3.44','Good Standing','Terence A','01'
'8','1','IDT','691','3','1','4574','Instructional Design & Technology','PHD','45','201408','72','1','1001','4574','45','Doctor of Philosophy','201408','52','3.44','Good Standing','Terence A','01'
'21','1','IDT','931','3','1','4574','Instructional Design & Technology','PHD','45','201408','72','1','1001','4574','45','Doctor of Philosophy','201408','52','3.44','Good Standing','Terence A','01'
'22','1','IDT','660','3','1','4574','Instructional Design & Technology','PHD','45','201408','72','1','1001','4574','45','Doctor of Philosophy','201408','52','3.44','Good Standing','Terence A','01'

For the same user with..

SELECT * FROM `user_courses` WHERE id = '1001'

id, cid, uid, subject, number, credits, enroll_status, id, id
'1', '1', '1001', 'IDT', '610', '3', 'complete'
'3', '86903', '1001', 'IDT', '750', '3', 'complete'

Thus, according to my base logic - which is only when subjectname and number match between the user_courses and program_courses tables, IDT 610 and IDT 750 would need to have the checkbox for complete, and all other program requirements would need an empty checkbox. Does this make sense? I really appreciate all of the feedback thus far, as I am not a guru when it comes to best-practices. I would be willing to revisit the query should an appropriate JOIN version be presented with the above structure

Answer 1

Please see Drew's response above. By putting the courses taken into an array in the beginning, and the then looping through the required courses (reverse of what I have been trying), the while loop isn't problematic, and this in fact works like a charm. Kudos to you Sir. Drew, and I appreciate the wonderful incite and the eventual solution that you provided. If I had more reputation i'd give an up-vote!

Answer 2

Edited with your table structure: SELECT a.subject, a.number, a.credits as course_credits, b.major, b.title, b.degree, c.catalog_year, d.credits as credits_earned FROM program_courses a join programs b on a.pid = b.pid join user_details c on c.major = b.major and c.uid = '".$_GET['id']."' left join user_courses d on a.pid = d.cid -- not sure that cid from table d is a match to pid in table a, adjust this as appropriate

subject|number|course_credits| major|title|degree|catalog_year|credits_earned -------|------|--------------|------|-----|------|------------|-------------- IDT |600 |3 |Doctor|ID&T |PHD |2015 |{NULL} IDT |610 |3 |Doctor|ID&T |PHD |2015 |2.75

-- Courses that the student has taken will have a value for credits_earned and you can base your display logic off of that

Then in your logic do something like if($results['credits_earned'] > 1.5){ // or whatever threshhold is considered passing // Yes, this student has completed this course }else{ // Has not completed }

I have run into problems doing a second query in the middle of a fetch loop from a first query. If you really don't want to combine the two queries into one then maybe you could rearrange your logic like so:

// store info on all the courses this student has already taken $completed_courses = array(); $w = $db->query("SELECT * FROM user_courses WHERE uid = '".$_GET['id']."'"); while($student = $w->fetch()){ $completed_courses[]=$student['subject'].$student['number']; }

// loop through all required courses $q = $db->query("SELECT * FROM program_courses a, programs b, user_details c WHERE a.pid = b.pid AND c.uid = '".$_GET['id']."' AND c.major = b.major"); while($program = $q->fetch()) {

if(in_array($program['subject'].$program['number'], $completed_courses)){
    // Yes the student has completed this course
}else{
    // no, the student has not completed this course
}

}

?>