CodeIgniter/PHP/MySQL: Retrieving data with JOIN

CodeIgniter/PHP/MySQL: Retrieving data with JOIN

睫毛上残留的泪 发布于 2021-11-26 字数 1274 浏览 834 回复 4 原文

I'm new to PHP/MySQL and super-new to CodeIgniter..
I have information in many MySQL tables. I want to retrieve it with JOIN where the tables primary keys are equal to $variable... How can I do it and get all the fields without the primary key field???

What I'm doing now is this (only two tables joined here):

function getAll($id) {

    $this->db->join('posters', '');
    // WHERE id = $id ... goes here somehow...
    $q = $this->db->get();

    if ($q->num_rows() == 1) {
        $row = $q->row();
        $data = array(
                'id' => $row->id,
                'title' => $row->title,
                'year' => $row->year,
                'runtime' => $row->runtime,
                'plotoutline' => $row->plotoutline,
                'poster_url' => $row->poster_url

    return $data;

id (PK), title, year, runtime and plotoutline are columns from the first table and poster_url is a field from the second table. The second table also contains an ID (PK) column that I don't want to Retrieve because I already have.

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。



需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。


∞梦里开花 2022-06-07 4 楼
$this->db->join('comments', ' =');
$query = $this->db->get();
心奴独伤 2022-06-07 3 楼

Simply put with method chaining:

         ->join('posters', '')
         ->where('', $id)
审判长 2022-06-07 2 楼

An asterisk will return all the fields. To return a subset of these, i.e. all fields apart form the repeated id field, simply list the columns which you require rather than use '*'.

It is often a good idea to not use asterisk anyway. In the future of the app, someone may add a large field to the table which will be surplus to your requirements, and will slow your queries.

黯淡〆 2022-06-07 1 楼

Jon is right. Here's an example:

                   movies.runtime as totaltime,  
$this->db->join('posters', '');
$this->db->where('', $id);
$q = $this->db->get();

This will return objects that have ->id, ->title, ->year, ->totaltime, and ->poster_url properties. You won't need the additional code to fetch the data from each row.

Don't forget, if the Active Record syntax gets a little unwieldy, you can use full SQL queries and get the same results:

$sql = "SELECT,
        movies.runtime as totaltime,
        FROM movies
        INNER JOIN posters ON =
        WHERE = ?"

return $this->db->query($sql, array($id))->result();

Both forms will ensure that your data is escaped properly.

CodeIgniter Active Record

Query Binding in CodeIgniter