joseph joseph - 7 months ago 8
SQL Question

How to use php language to control sql query

I have this code in my program

<?php
session_start();
$_SESSION['user_id']=201102887;
$con = mysqli_connect('localhost', 'root', '');
if(!$con)
{
die("not ok");
}
mysqli_select_db($con,"uoh");
$q = "SELECT * FROM courses INNER JOIN transfer_student_courses ON
transfer_student_courses.course_number=courses.course_number INNER
JOIN transfered_courses ON transfer_student_courses.sn=transfered_courses.sn
AND transfer_student_courses.student_ID = " . $_SESSION['user_id'];
$result = mysqli_query($con , $q) ;
if($result){
echo "<table>";
echo "<tr>";
echo "<th>equivalent</th>";
echo "</tr>";
while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row["equivalent"]. "</td>";
echo "</tr>";
}
echo "</table>";
}

mysqli_select_db($con,"uoh");
$q = "SELECT * FROM courses
LEFT JOIN degree_plan ON degree_plan.course_number= courses.course_number
LEFT JOIN student_record ON courses.course_number= student_record.course_number
AND student_record.id= ". $_SESSION['user_id']."
WHERE degree_plan.major='COE' ORDER BY term_no";

$result = mysqli_query($con , $q ) ;
if($result){
echo "<table>";
echo "<tr>";
echo "<th>course</th>";
echo "</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row["code"]. "</td>";
echo "</tr>";
}
}
echo "</table>";
?>


I have two queries in this code each of query give me list of courses .
I want if course appear in the first list do not appear in second list

more

if you see the output of this code in the blew first query give me MATH 101 also in second query give me MATH 101.

I want MATH 101 do not appear in second course list because it appear in the first list. How I write a function in php language to do that.

equivalent
MATH 101
course
PHYS 101
CHEM 101
PE 101
IAS 101
MATH 101
ENGL 101

Answer

First of all, store all the equivalent courses in an array, say $equivalent array. And then in the second while loop use in_array() function to check if the course already got printed in the first table or not.

Here's the reference:

So your code should be like this:

<?php
    session_start();
    $_SESSION['user_id']=201102887;
    $con = mysqli_connect('localhost', 'root', '');
    if(!$con){
        die("not ok");
    }
    mysqli_select_db($con,"uoh");  
    $q = "SELECT * FROM courses INNER JOIN transfer_student_courses ON 
    transfer_student_courses.course_number=courses.course_number INNER
     JOIN transfered_courses ON transfer_student_courses.sn=transfered_courses.sn 
     AND transfer_student_courses.student_ID = " . $_SESSION['user_id'];
    $result = mysqli_query($con , $q) ;

    $equivalent = array();
    if($result){
        echo "<table>";
        echo "<tr>";
        echo "<th>equivalent</th>";
        echo "</tr>";
        while($row = mysqli_fetch_array($result)){
            $equivalent[] = $row["equivalent"];
            echo "<tr>";
            echo "<td>" . $row["equivalent"]. "</td>";
            echo "</tr>";
        } 
        echo "</table>";
    }

    mysqli_select_db($con,"uoh");  
    $q = "SELECT * FROM courses 
    LEFT JOIN degree_plan ON degree_plan.course_number= courses.course_number 
    LEFT JOIN student_record ON courses.course_number= student_record.course_number 
    AND student_record.id= ". $_SESSION['user_id']."
    WHERE degree_plan.major='COE' ORDER BY term_no";

    $result = mysqli_query($con , $q ) ;
    if($result){
       echo "<table>";
       echo "<tr>";
       echo "<th>course</th>";
       echo "</tr>";

        while($row = mysqli_fetch_array($result)){
            if(in_array($row["code"], $equivalent)){
                continue;
            }
            echo "<tr>";
            echo "<td>" . $row["code"]. "</td>";
            echo "</tr>";
        } 
    }
    echo "</table>";   
?>