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

I agree @JayBlanchard -- however technically the OP is using an implicit join... I think the query could be improved. — mason81
– mason81, Commented Mar 6, 2015 at 18:48
Can you update your question with the table definitions or structure? Perhaps a few rows of sample data? — mason81
– mason81, Commented Mar 6, 2015 at 18:50
Also, run the query in mysql and copy/paste the results in an update to your question. (Don't have to put all data, just enough to get the idea of what your result sets look like) — mason81
– mason81, Commented Mar 6, 2015 at 18:57
Maybe you should exec your second query outside of the while for performance ... =) — everytimeicob
– everytimeicob, Commented Mar 6, 2015 at 19:00

Drew · Accepted Answer · 2015-03-06 22:30:39Z

3

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
}


}

?>

edited Mar 6, 2015 at 22:30

answered Mar 6, 2015 at 18:57

Drew

4,3934 gold badges28 silver badges42 bronze badges

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

2 Comments

jayburg Over a year ago

See my additions above. I appreciate your input.

jayburg Over a year ago

Thank you very much..... I have been literally starring at this for days trying to figure out appropriate logic. You are the man, sir!

jayburg · Accepted Answer · 2015-03-06 22:31:00Z

0

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!

answered Mar 6, 2015 at 22:31

jayburg

531 silver badge8 bronze badges

1 Comment

mason81 Over a year ago

It would be very helpful if you marked Drew's answer as accepted since his solution did the trick for you.

Collectives™ on Stack Overflow

PHP Checking Values in One Query Against Another

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related