CodeIgniter / PHP / MySQL:使用JOIN检索数据 [英] CodeIgniter/PHP/MySQL: Retrieving data with JOIN
问题描述
我是新的PHP / MySQL和超级新的CodeIgniter ..
我有许多MySQL表中的信息。我想用JOIN检索它的表主键等于$变量...我如何做,并获得所有的字段没有主键字段
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), 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在第二个表格中包含了一个ID是对的。这里有一个例子:
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 / PHP / MySQL:使用JOIN检索数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!