+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Problem with database query (/showthread.php?tid=68969)
Problem with database query - desbest - 09-21-2017
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?
RE: Problem with database query - rtenny - 09-22-2017
here you use accept without quotes 'votes.content = accept'
I belive that should be 'votes.content = "accept"'
RE: Problem with database query - InsiteFX - 09-22-2017
Look at group_by and order_by
RE: Problem with database query - desbest - 09-22-2017
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` =0If 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).
RE: Problem with database query - Wouter60 - 09-22-2017
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();
RE: Problem with database query - desbest - 09-22-2017
Thank you all for your help! Everything works now.