如何使用foreach在表中使用左连接显示选择查询的记录？

I have two tables (table a and b). I need to show them in one table, but got some problem on how I could display the records on my table. Please see my sample sc

我有两张桌子（表a和b）。我需要在一个表中显示它们，但是如何在我的表上显示记录时遇到了一些问题。请看我的样本sc

table a id name 1 John 2 Mark 3 Nick

表名称1 John 2 Mark 3 Nick

table b id job 1 Encoder 2 3 Programmer

表b id job 1编码器2 3编程器

I made a query to connect the two tables

我做了一个查询来连接这两个表

select a.id, a.name, b.job from table_a a
left join table_b b on b.id=a.id
order by a.id;

using codeigniter,

使用codeigniter，

this is the controller

这是控制器

$this->db->select('a.id, a.name, b.job);
$this->db->from('table a');
$this->db->join('table b', 'b.id=a.id', 'left');
$this->db->order_by('a.id');
$query = $this->db->get();
$data["view_records"]=$query;
$this->load->view("table_name", $query);

this is the view

这是观点

<table id="tablestyle" class="table table-bordered table-hover table-condensed">
    <col width="50">
    <col width="150">
    <col width="150">
    <col width="150">
    <thead>
        <tr>
            <th><strong>ID</strong></th>
            <th><strong>NAME</strong></th>
            <th><strong>JOB</strong></th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        <?php foreach($view_records->result() as $row) {  ?>
                <tr>
                    <td><?php echo $row->a.id; ?></td>
                    <td><?php echo $row->a.name; ?></td>
                    <td><?php echo $row->b.job; ?></td>
                    <td><input type="submit" value="Modify" id="btnModify" class="btn btn-block btn-success btn-xs" onclick="btnModify('<?php echo $row->a.id; ?>');"/></td>
                </tr>
        <?php } ?>
    </tbody>
</table>

1 个解决方案

#1

In Model

在模型中

public function get_user()
{
    $query = $this->db->query("select a.id, a.name, b.job from table_a a
        left join table_b b on b.id=a.id
        order by a.id");
    $result = $query->result_array();
    return $result;
}

In Controller

在控制器中

$data['get_user'] = $this->model_name->get_user();

$this->load->view("view_name", $data);

In View

在视图中

<table id="tablestyle" class="table table-bordered table-hover table-condensed">
    <col width="50">
    <col width="150">
    <col width="150">
    <col width="150">
    <thead>
        <tr>
            <th><strong>ID</strong></th>
            <th><strong>NAME</strong></th>
            <th><strong>JOB</strong></th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        <?php foreach($get_user as $rowItem) {  ?>
                <tr>
                    <td><?php echo $rowItem['a.id'] ?></td>
                    <td><?php echo $rowItem['a.name'] ?></td>
                    <td><?php echo $rowItem['b.job'] ?></td>
                    <td><input type="submit" value="Modify" id="btnModify" class="btn btn-block btn-success btn-xs" onclick="btnModify('<?php echo $rowItem['a.id'] ?>');"/></td>
                </tr>
        <?php } ?>
    </tbody>
</table>

#1