CODEWITHSUNDEEP
phpcodeigniter
Ann
Ann

Reputation: 471

Join two tables and display it in a table

enter image description here

I have two tables "rps_users" contains columns named as id,membership_no, volunteer_name and email , and "rps_volunteer_score" contains volunteer_id,registration_assessor,interviewer,professional_registration_advisor and registration_category.Here the id and volunteer_id are same.The volunteers present in the "rps_users" table have multiple records in "rps_volunteer_score" table under different "registration_category". I want to display a table contains membership No,volunteer name,email and Type. The "Type" records finds on the basis of "rps_volunteer_score" records.ie,

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";

I used this to join two tables

public function get_volunteers_list($condition)
{

    $this->db->select("u.id,u.membership_number,u.family_name,u.first_name,u.email_address, 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(); 
    //var_dump($this->db->last_query());
    return $query->result();
}

But I got multiple records of one volunteer. Please help me to find a solution. This is the controller code.

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

Upvotes: 1

Views: 73

Answers (1)

Niranjan N Raju
Niranjan N Raju

Reputation: 11987

you should group by membership number

add this line before get()

$this->db->group_by("u.membership_number");

Upvotes: 1

Related Questions