Welcome Guest, Not a member yet? Register   Sign In
Problem with database query
#1

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?
Reply
#2

here you use accept without quotes 'votes.content = accept'
I belive that should be 'votes.content = "accept"'
On the package it said needs Windows 7 or better. So I installed Linux.
Reply
#3

Look at group_by and order_by
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#4

(This post was last modified: 09-22-2017, 04:44 AM by desbest.)

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).
Reply
#5

(This post was last modified: 09-22-2017, 06:46 AM by Wouter60.)

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(); 
Reply
#6

Thank you all for your help! Everything works now.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB