在php中使用带有多个左连接的WHERE子句

Question

I successfully did a LEFT JOIN using PHP, however, I am having a little difficulty adding a WHERE clause to enable me select unique record from all existing details in all the tables where a match is found.

This is my code :

  <?php
$sql = "SELECT staff.*, staff_bio.bio_id, staff_cert.*, staff_edu.*, staff_pos.*, staff_res.*
"
    . " FROM staff staff
"
    . "LEFT JOIN staff_bio 
"
    . " ON staff.nuc_id = staff_bio.bio_id
"
    . "LEFT JOIN staff_cert
"
    . " ON staff.nuc_id = staff_cert.pro_id
"
    . "LEFT JOIN staff_edu
"
    . " ON staff.nuc_id = staff_edu.edu_id
"
    . "LEFT JOIN staff_pos
"
    . " ON staff.nuc_id = staff_pos.rank_id
"
    . "LEFT JOIN staff_res
"
    . " ON staff.nuc_id = staff_res.res_id
"
    . "WHERE staff.nuc_id = $userRow['staff_no'] ";//this is where i'm having issues
?>

My final output should be something like :

<?php echo $userRow['sch_name']; ?>
<?php echo $userRow['fac_name']; ?>
<?php echo $userRow['dep_name']; ?>

All coming from different tables. Any help will be greatly appreciated.

Answer 1

You can do like this:

. " WHERE staff.nuc_id = ".$userRow['staff_no'] ;//this is where i'm having issues

Why do you need to concatenate query, it can be written as if you want formatting :

<?php 

    $sql = "SELECT staff.*, staff_bio.bio_id, staff_cert.*, staff_edu.*, staff_pos.*, staff_res.*
         FROM staff staff
        LEFT JOIN staff_bio 
         ON staff.nuc_id = staff_bio.bio_id
        LEFT JOIN staff_cert
         ON staff.nuc_id = staff_cert.pro_id
        LEFT JOIN staff_edu
         ON staff.nuc_id = staff_edu.edu_id
        LEFT JOIN staff_pos
         ON staff.nuc_id = staff_pos.rank_id
        LEFT JOIN staff_res
         ON staff.nuc_id = staff_res.res_id
        WHERE staff.nuc_id = ".$userRow['staff_no'];//this is where i'm having issues

     ?>