nethken nethken - 2 months ago 9
SQL Question

How to use join statement

How can i do this with

JOIN
statement. I can't think of anymore logic to do this. I'm doing this because the
json data
is not shown because of that queries. Can someone help me to achieve this? First i want to fetched all the
enroll_ref
from
subj_enrolled
WHERE subject is equals to '$subject'. After that, I will now use the fetched
enroll_ref
to fetch the
lastname,firstname,middlename
to the
std_enrolled
where equals to
enroll_ref
. What method will i use to achieve this? Or what JOIN?

here's my code

if (isset($_POST['loadstudent'])) {
$subject = $_POST['subject'];
$section = $_POST['section'];

$sql ="SELECT enroll_ref FROM subj_enrolled WHERE subj_descr = '$subject' AND section = '$section'";
$result = mysqli_query($con, $sql);

while($row = mysqli_fetch_array($result)){
$enroll_ref = $row['enroll_ref'];


$select = mysqli_query($con,"SELECT lastname,firstname,middlename FROM std_enrolled WHERE enroll_ref = '$enroll_ref'");

if(mysqli_num_rows($select)){
$data = array();
while($row = mysqli_fetch_array($select)){
$data[] = array(
'firstname' => $row['firstname'],
'lastname' => $row['lastname'],
'middlename' => $row['middlename']

);
}

}
echo json_encode($data);
}


}

Answer

Use an inner join.

Inner joins return all data where there is a match between the two tables.