CodeIgniter Forums

I have a problem with my database query and would like help getting it to work please.

PHP Code:
$acceptal = $this->db->select(array('posts.content', 'posts.id', 'posts.userid', 'votes.userid'))->join('users', 'posts.userid = users.id')->join('votes', "votes.questionid = $debate[id]", "users.id = votes.userid", 'votes.content = accept')->select(array('users.username','users.avatarpath'))
                ->get_where('posts', array('posts.questionid' => $debate['id'], 'posts.floor' => 0))
                ->get_where('votes', array('votes.content' => "accept"))
; 

PHP Code:
$acceptal = $this->db->select(array('posts.content', 'posts.id', 'posts.userid', 'votes.userid'))->join('users', 'posts.userid = users.id')->join('votes', "votes.questionid = $debate[id]", "users.id = votes.userid", 'votes.content = accept')->select(array('users.username','users.avatarpath'))
                ->get_where('posts', array('posts.questionid' => $debate['id'], 'posts.floor' => 0, 'votes.content' => "accept"))
; 

Both of the code snippets above do not work.

I have a joined the posts table to the votes table, and I would like to only fetch rows where votes.content is "accept".
However each time I try to do so with the code above, that never happens, it fetches rows that say "accept" and don't say accept in the same query.

What is going on and how do I fix it?

here you use accept without quotes 'votes.content = accept'
I belive that should be 'votes.content = "accept"'

Look at group_by and order_by

Hello I now have a new problem.

Here is my code.

PHP Code:
$acceptal = $this->db->select(array('posts.content', 'posts.id', 'posts.userid', 'votes.userid', 'votes.content'))
->join('users', 'posts.userid = users.id')
->join('votes', "votes.questionid = $debate[id]", "votes.content = 'accept'")
->select(array('users.username','users.avatarpath')
)->get_where('posts', array('posts.questionid' => $debate['id'], 'posts.floor' => 0)); 

Here is the SQL it generates.

Code:
SELECT `posts`.`content`, `posts`.`id`, `posts`.`userid`, `votes`.`userid`, `votes`.`content`, `users`.`username`, `users`.`avatarpath` 

FROM `posts` 

JOIN `users` ON `posts`.`userid` = `users`.`id` 

JOIN `votes` ON `votes`.`questionid` = 3 WHERE `posts`.`questionid` = '3' AND `posts`.`floor` =0

If you notice, you'll notice that it is not joined to votes.content = "accept".

How do I do this?

I want to join to another table using 2 criteria, not just 1 criteria (the code is only joining to the table with 1 criteria).

There's a fundamental flaw in your join statements.
Join will ask you for the field in table 1 that corresponds with the field in table 2.
If you just want to join records that have the value "accept" in the field votes.content, simply use a where statement.
E.g.:

PHP Code:
$this->db
->select('p.content, p.id, p.userid, v.userid, v.content')
->from('posts p')
->join('users u','p.userid = u.id')
->join('votes v','v.postid = p.id')
->where('v.questionid', $debate['id'])
->where('v.content','accept')
->where('p.floor',0);
$query = $this->db->get();
echo $this->db->last_query();
$records = $query->result_array(); 

Thank you all for your help! Everything works now.

desbest

rtenny

InsiteFX

desbest

Wouter60

desbest