从db显示名称而不是id无法正常工作

Question

I am trying to display a name from db instead of id (sittername) but my view it shows the same name in all rows which is wrong. What I want is to get the name from db for every id in $ids array. I hope you can help me. I am using MVC in CodeIgniter framework..

My Controller:

$results = $this->Admin_model->getSitter_Requests($config["per_page"], $page);

        foreach ($results as $item)
        {
            $data['data1'] = [];
            $sitter_job_id = $item['sitter_job_id']; 

My problem is in this part of code, sittername has only one name which come from the last id in my $ids array. what I want is to get the name from db for every id in $ids array.

    $ids = $item['sitter_id']; 

    foreach ((array)$ids as $id)
    {
        if($id != 0 && $id != null){
            $sittername = $this->Admin_model->get_sitter_name($id[0]); 
            $data["sittername"] = $sittername;
        }

    }
    }


        $data["results"] = $results;
        $data["links"] = $this->pagination->create_links();

        $this->load->view('admin/superadmin_sitter_requests',$data);

My Model:

public function getSitter_Requests($limit, $start)
    {
        $this->db->select('*');
        $this->db->limit($limit, $start);
        $query = $this->db->get('ps_sitter_jobs');
        if ($query->num_rows() > 0) {
            foreach ($query->result_array() as $row) {
                $data[] = $row;
            }
            return $data;
        }
        return array();

    }
function get_sitter_name($id) {

        $this->db->select('fname');
        $this->db->where('id', $id);
        $query = $this->db->get('ps_cms_users');
        return $query->row()->fname;

    }
ps_sitter_jobs table columns:
sitter_job_id                  | int(11)   PRI                
sitter_id                      | int(11)   FK from ps_cms_users(id)                
seller_sitter_id               | int(11)                   
token_trans_id                 | varchar(255)              
camp_id                        | int(11)                   
book_id                        | int(11)                   
sitter_status                  | enum('0','1','2','3','4') 
created_date                   | datetime                  
modify_date                    | datetime                  
assign_new_sitter_btn_disabled | int(11)                   
refund_btn_disabled            | int(11)     
===================================================
ps_cms_users table columns:
id           | int(11)       PRI  
email        | varchar(128) 
password     | varchar(128) 
fname        | varchar(128) 
lname        | varchar(128) 
mobile       | varchar(20)  
roleId       | int(4)       
createdBy    | int(11)      
createdDtm   | datetime     
updatedBy    | int(11)      
updatedDtm   | datetime     
partner_type | int(11)      
state        | int(11)      
city         | int(11)      
userDesc     | varchar(255)   
==================================================

My View:

if(count($results) > 0){
    foreach ($results as $result){
        $result_id = $result['sitter_job_id'];
    ?>
        <tr>
            <td>
                <?php echo '#'.$result_id; ?>
            </td>
            <td>
                <?php 
                if($result['sitter_id'] == 0 || $result['sitter_id'] == null)
                {
                    echo "Sitter Not Assigned yet";
                }
                else
                {                   
                    echo $sittername; 

                }
    }   
    ?>

Answer 1

Not very easy to fully understand what you're trying to accomplish here, but I think you could use a JOIN instead of looping trough your first results. Here's a suggestion. (I have left out pagination and HTML formatting for the purpose of clarity)

Your Controller

$data["results"] = $this->Admin_model->getSitter_Requests();
$this->load->view('admin/superadmin_sitter_requests', $data);

Your Model (Admin_model.php)

public function getSitter_Requests()
{
    $this->db->join('ps_cms_users', 'ps_cms_users.id = ps_sitter_jobs.sitter_id');
    return $this->db->get('ps_sitter_jobs')->result();
}

Your View (admin/superadmin_sitter_requests)

foreach ($results as $result)
{
    echo 'SITTER_JOB_ID = ' . $result['sitter_job_id'];
    echo 'SITTER_ID = ' . $result['sitter_id'];
    echo 'SITTER_FIRSTNAME = ' . $result['fname']; 
}

I hope this helps.