Why does my query return the same row multiple times?
I'm trying to join two tables which share a common id, and it keeps returning a single row multiple times. 开发者_运维技巧Am working with codeigniter
This is the function from the model:
function get_latest_pheeds() {
$data = $this->user_keywords($this->ion_auth->user_id);
$keyword = $data;
$user_id = $this->ion_auth->user_id;
foreach($keyword as $key => $word) {
$q = "SELECT *,COUNT(pheed_comments.comment_id) as comments
FROM pheeds
LEFT JOIN pheed_comments ON pheed_comments.P_id=pheeds.pheed_id
WHERE pheed LIKE '%$word%' OR user_id='$user_id'
ORDER BY datetime DESC";
$result = $this->db->query($q);
$rows[] = $result->result();
}
return $rows;
}
Is my query wrong?
You are missing a GROUP BY
for your COUNT()
$q = "SELECT *,COUNT(pheed_comments.comment_id) as comments
FROM pheeds
LEFT JOIN pheed_comments ON pheed_comments.P_id=pheeds.pheed_id
WHERE pheed LIKE '%$word%' OR user_id='$user_id'
GROUP BY pheed_id, pheed
ORDER BY datetime DESC";
Now, since you are using SELECT *
, there may be more columns returned with the COUNT()
. For accurate results, you also must list them in the GROUP BY
:
GROUP BY pheed_id, pheed, othercol1, othercol2, othercol3
精彩评论