php - 连接表并显示 city.name

Question

连接两个表和显示另一个值时出现问题，已设置表并加载模型并以大写字母开头。

我想要的是匹配 jobs.city_id 和 cities.id 并显示 cities.city_name

Jobs_model.php

public function get_city(){
$this->db->select('*');
$this->db->from('cities');
$this->db->join('jobs', 'cities.city_name = jobs.city_id');
$this->db->where('jobs.city_id = cities.id');
$query = $this->db->get();
}

Controller .php

$data['city_name'] = $this->jobs_model->get_city();

查看.php

<?php echo $city_name['city_name'];?>

SQL

CITIES
id
city_name

JOBS
id
city_id 

更新。 sql和拼写错误

Answer 1

You also have not returned any thing in model function

如果您需要特定类型的 JOIN，您可以通过函数的第三个参数指定它。选项有:左、右、外、内、左外和右外。

下面这个函数示例只会一次返回一行，您可能需要使用 result_array();阅读下面的链接如何生成结果 https://www.codeigniter.com/user_guide/database/results.html

public function get_city(){
    $this->db->select('*');
    $this->db->from('cities');
    $this->db->join('jobs', 'jobs.city_id = cities.id', 'LEFT');
    //$this->db->where('jobs.city_id = cities.id');
    $query = $this->db->get();

    // Note only returns one row
    return $query->row_array();
}

在 Controller 上

$info = $this->jobs_model->get_city();

$data['city_name'] = $info['city_name'];

---

多个结果

public function get_city(){
    $this->db->select('*');
    $this->db->from('cities');
    $this->db->join('jobs', 'jobs.city_id = cities.id', 'LEFT');
    $query = $this->db->get();

    return $query->result_array();
}

在 Controller 上

$data['cities'] = $this->jobs_model->get_city();

查看

<?php foreach ($cities as $city) {?>
    <?php echo $city['city_name'];?>
<?php }?>