Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
09-07-2009, 09:17 PM #1
- Join Date
- Sep 2009
- Thanked 0 Times in 0 Posts
problem with 'group by' aspect of sql statement!
I'm building a reverse auction style site.
I have two tables bid table and proposal table.
I'm trying to print to screen all the proposals made by a particular user!
$result = mysql_query("SELECT proposal.*, count(bid.proposalid) AS bids,SYSDATE(), TIME_FORMAT(TIMEDIFF(tomorrowtime, SYSDATE()), '%H hours, %i minutes') AS timeleft FROM proposal LEFT JOIN bid ON proposal.proposalid = bid.proposalid WHERE proposal.username = '$username' AND proposal.time > NOW()- INTERVAL 1 DAY GROUP BY bid.proposalid");
So for example if four proposals are made by a user, I want all four to be printed, however at the moment the situation is that if only two of them have bids on those two bids are the only two that get printed!
I'm pretty sure its something to do with the group by statement as when I remove it, it seems to work fine, but I consequently lose the critical "count bids" info.
Does anyone have any ideas how I can fix this?
09-08-2009, 05:33 PM #2
- Join Date
- Dec 2005
- Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
- Thanked 637 Times in 625 Posts
Try grouping by proposal.proposalid instead of bid.proposalid. I believe grouping by anything in the bid table essentially converts your left join into a normal inner join (and thus excludes proposals with no bids).