如何从数据库中查询两个表，并使用codeigniter将结果显示在一行中

Question

I have two tables One for users and the other for project listing

My problem is that i want to display each project(from project_table)and email belonging to user who listed the project(from user_table) on a single row

The project_table has a row for user_id(i.e id from user_table that identifies the user who posted the project)

here's my view(project_view): I this case im displaying data from project_table but i want to display email for a particular user from user_table

<?php 
 foreach($query as $row)
 { 
?>
    <p> <?echo $row->pro_name ?></p>
<p> <?echo $row->duration ?></p>
<p> <?echo $row->budget ?></p>
<p>User email will be displayed here</p>
<?
 }

my model:

function get_projects()
{
    $query = $this->db->get('project_table');
    return $query->result();
}

my controller:

function show_projects()
{
    $data['query']=$this->project_model->get_projects();
    $this->load->view('project_view', $data);
}

Any ideas on how to implement this will be much appreciated

Answer 1

Try join this -

1) Model

function get_projects()
{
    $this->db->select('*');
    $this->db->from('project_table');
    $this->db->join('user_table', 'project_table.user_id = user_table.id');

    $query = $this->db->get();

    if($query->num_rows() > 0)
    {
        return $query->result();
    }
}

2) Controller

$data['user_project'] = $this->project_model->get_projects();
$this->load->view($view, $data);

3) View

foreach($user_project as $row)
{ 
    echo "<p>" .$row->pro_name. "</p>";
    echo "<p>" .$row->duration. "</p>";
    echo "<p>" .$row->budget. "</p>";
    echo "<p>" .$row->email. "</p>";
}