• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
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
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
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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.