显示两个表中的数据 - codeigniter中的一对多关系

Question

I have two tables:

Table 1 -- rps_users:

id
membership_number
family_name
first_name
email_address
staff (staff column having values Y and N)

Table 2 -- rps_volunteer_score:
volunteer_id
registration_assessor
interviewer
professional_registration_advisor

The id column in the first table contains the same values as the volunteer_id column in the 2nd table. Each user could have multiple registration_assessor,interviewer and professional_registration_advisor values.The possible values of these columns are 0-4.

What I would like to display in the browser records with users having value N in the staff column with a format something like this:

membership_number:000
family_name:Name1
first_name:Name2
email_address:[e-mail]
Type:Interviwer/Assessor/Registrar/PRA/Moderator(If the user have multuple role display all ie, Interviewer,Assessor)

Type column recored based on follwing condition

    if( $interviewer!="" && $interviewer <= 4 )echo "Interviewer";
    if($registration_assessor !="" && $registration_assessor >= 2 &&     $registration_assessor <= 4 )echo "Assessor";
    if($registration_assessor !="" && $registration_assessor >= 3 && $registration_assessor <= 4)echo "Moderator";
    if($registration_assessor !="" && $registration_assessor == 4 ) echo "Registrar";
    if($professional_registration_advisor !="" && $professional_registration_advisor== 1)echo "PRA";

This is my model function

    public function get_volunteers_list($condition)
{

    $this->db->select("u.id,u.membership_number,u.family_name,u.first_name,u.email_address,vs.volunteer_id,vs.registration_assessor, vs.interviewer,vs.professional_registration_advisor");
    $this->db->from(self::$tbl_name . " as u");
    $this->db->join(Volunteer_score::$tbl_name . " as vs", "u.id = vs.volunteer_id","left");
    $this->db->where($condition);
    $query = $this->db->get(); 
    return $query->result();

}

This is my controller code

    $volunteer_list = $this->Users->get_volunteers_list(array("u.staff"=>'N'));
    $this->data["volunteer_list"] = $volunteer_list;

This is my view code

    <?php
        if($volunteer_list)
        {
        foreach($volunteer_list as $volunteer)
        {

            echo "<td>{$volunteer->membership_number}</td>";
echo "<td>{$volunteer->family_name}, {$first_name}</td>";
echo "<td>{$volunteer->email_address}</td>";
echo "<td>";(Here i want to display the type of the user)
//Following is the condition for "TYPE"
/*if( $interviewer!="" && $interviewer <= 4 )
{
    echo "Interviewer"; 


}

if($registration_assessor !="" && $registration_assessor >= 2 && $registration_assessor <= 4 )
{
    echo "Assessor";


}
if($registration_assessor !="" && $registration_assessor >= 3 && $registration_assessor <= 4)
{
    echo "Moderator";


}
if($registration_assessor !="" && $registration_assessor == 4 )
{
    echo "Registrar";


}
if($professional_registration_advisor !="" && $professional_registration_advisor == 1)
{
    echo "PRA";


}*/
echo"</td>";
echo "</tr>";
        }
        }
        ?>

Answer 1

The assembled sql could look like:

SELECT u.id, u.membership_number, u.family_name, u.first_name, u.email_address, 
GROUP_CONCAT(vs.registration_category) AS type
FROM volunteers_list as u
LEFT JOIN Volunteer_score as vs ON(u.id = vs.volunteer_id)
GROUP BY u.id;

and you would get one row with registration_categorys separated by comma