Welcome Guest, Not a member yet? Register   Sign In
help with join query *wrong forum

hello people

i'm having a problam. i have to tabels in my database: one for articles, and another for article's comments.

in the comments table one of my fields is "article_id" which containes the article's id of the comment.
i want to show the whole articles in one page and next to each article the amount of comments that the articles has.
for example:
"5 tips for games - number of comments: 3"

this is what i tried to:
$this->db->from('games, comments');
$this->db->select('games.*, comments.*, COUNT(comments.cmnt_itemid) as votes');
$this->db->orderby('ga_id', 'desc');        
$this->db->limit($config['per_page'], $start_at);
$data['games'] = $this->db->get();

but this is the error that i get:
Quote:Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

SELECT games.*, comments.*, COUNT(comments.cmnt_itemid) as votes FROM games, comments ORDER BY ga_id desc LIMIT 12

what can i do?
thank u

[eluser]Chris Newton[/eluser]
Something like this:

$this->db->select(games.column1 AS column1);
          $this->db->select(games.column2 AS column2);
          $this->db->select(games.column3 AS column3);
          $this->db->select(games.column4 AS column4);
          $this->db->select(COUNT(comments.column1) as votes);
          $this->db->join('comments', 'games.id = comments.game_id');        
          $this->db->orderby('ga_id', 'desc');

          $query = $this->db->get();

[eluser]Cadu de Castro Alves[/eluser]
Just a little tip: before writing your SQL queries in the PHP script, try them using a SQL software like HeidiSQL (for MySQL) or PGAdmin (for PostgreSQL) or their admin command line. Doing that, you can make some tests and get what you really want.

Theme © iAndrew 2016 - Forum software by © MyBB