Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Unanswered: problem with 'group by' aspect of sql statement!

    Hi all,

    I'm building a reverse auction style site using mysql and php.

    I have two tables bid table and proposal table.

    I'm trying to print to screen all the proposals made by a particular user!

    Code:
    $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");

    The problem is that only those proposals that have bids made on them are printing to screen.

    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?

    Many Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT proposal.*
         , COUNT(bid.proposalid) AS bids
         , CURRENT_TIMESTAMP
         , TIME_FORMAT(TIMEDIFF(tomorrowtime, CURRENT_TIMESTAMP)
                          , '%H hours, %i minutes') AS timeleft
      FROM proposal
    LEFT OUTER
      JOIN bid 
        ON bid.proposalid = proposal.proposalid 
     WHERE proposal.username = '$username' 
       AND proposal.time > proposal.proposalid  - INTERVAL 1 DAY
    GROUP 
        BY proposal.proposalid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •