为什么我的查询多次返回同一行？

Question

我想连接两个共享一个公共ID的表，并且它不断返回多行单个行。

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; 
} 

是我的错查询：我用笨

这是从模型的功能吗？

Answer 1

您正在为您的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"; 

缺少GROUP BY现在，因为你正在使用SELECT *，可能会有更多的列与COUNT()返回。为了获得准确的结果，您还必须在GROUP BY中列出它们：

GROUP BY pheed_id, pheed, othercol1, othercol2, othercol3