CodeIgniter/PHP/MySQL:使用 JOIN 检索数据 [英] CodeIgniter/PHP/MySQL: Retrieving data with JOIN

问题描述

我是 PHP/MySQL 的新手，也是 CodeIgniter 的超级新手.我在许多 MySQL 表中有信息.我想用 JOIN 检索它，其中表的主键等于 $variable...我该怎么做才能获得没有主键字段的所有字段???

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->select('*');
    $this->db->from('movies');
    $this->db->join('posters', 'movies.id= posters.id');
    // 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
            );
    }

    $q->free_result();
    return $data;

id (PK)、title、year、runtime 和 plotoutline 是第一个表中的列，poster_url 是第二个表中的一个字段.第二个表还包含一个 ID (PK) 列，我不想检索它，因为我已经有了.

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.

推荐答案

Jon 是对的.举个例子:

Jon is right. Here's an example:

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

这将返回具有 ->id、->title、->year、->totaltime 和 ->poster_url 属性的对象.您不需要额外的代码来获取每一行的数据.

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.

不要忘记，如果 Active Record 语法有点笨拙，您可以使用完整的 SQL 查询并获得相同的结果:

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.id,
        movies.title,
        movies.year,
        movies.runtime as totaltime,
        posters.poster_url
        FROM movies
        INNER JOIN posters ON movies.id = posters.id
        WHERE movies.id = ?"

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

两种形式都将确保您的数据正确转义.

Both forms will ensure that your data is escaped properly.

CodeIgniter 活动记录

CodeIgniter 中的查询绑定

这篇关于CodeIgniter/PHP/MySQL:使用 JOIN 检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！