CodeIgniter/PHP/MySQL：使用 JOIN 检索数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/793807/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CodeIgniter/PHP/MySQL: Retrieving data with JOIN
提问by Jonathan
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???
我是 PHP/MySQL 的新手，也是 CodeIgniter 的新手。我在许多 MySQL 表中都有信息。我想用 JOIN 检索它，其中表的主键等于 $variable ......我该怎么做才能获得没有主键字段的所有字段？？？
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 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.
id (PK)、title、year、runtime 和 plotoutline 是第一个表中的列，poster_url 是第二个表中的一个字段。第二个表还包含一个 ID (PK) 列，我不想检索它，因为我已经有了。
回答by GloryFish
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();
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.
这将返回具有 ->id、->title、->year、->totaltime 和 ->poster_url 属性的对象。您不需要额外的代码来获取每一行的数据。
Don't forget, if the Active Record syntax gets a little unwieldy, you can use full SQL queries and get the same results:
不要忘记，如果 Active Record 语法有点笨拙，您可以使用完整的 SQL 查询并获得相同的结果：
$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.
这两种形式都将确保您的数据正确转义。
回答by Jon Winstanley
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 '*'.
星号将返回所有字段。要返回其中的一个子集，即除重复 id 字段之外的所有字段，只需列出您需要的列而不是使用“*”。
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.
无论如何不要使用星号通常是个好主意。在应用程序的未来，有人可能会向表中添加一个大字段，这将超出您的要求，并且会减慢您的查询速度。
回答by JonathanEyre
Simply put with method chaining:
简单地说就是方法链：
$this->db->select('*')
->from('movies')
->join('posters', 'movies.id= posters.id')
->where('movies.id', $id)
->get();
回答by Ks Sjkjs
$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = blogs.id');
$query = $this->db->get();
