Page 1 of 1

problems with sql statement. please help!

Posted: Sun Sep 06, 2009 4:34 pm
by debuitls
Hi all,

I'm developing a reverse auction style site and have the following code.

Code: Select all

 
$result = mysql_query("SELECT proposal.*,SYSDATE(),count(bid.proposalid) AS bids, TIME_FORMAT(TIMEDIFF(tomorrowtime, SYSDATE()), '%H hours, %i minutes') AS timeleft
FROM proposal
JOIN bid ON proposal.proposalid = bid.proposalid
WHERE NOW() < proposal.tomorrowtime AND proposal.username = '$username'
GROUP BY bid.proposalid
");
 
This code returns a series of information about a user proposal which I echo to the screen.

Now the problem is that it only ever prints one record (one user proposal) to the screen, even if there is more than one user proposal.

This problem is rectified when i remove the

Code: Select all

 
count(bid.proposalid) AS bids
 
and

Code: Select all

 
GROUP BY bid.proposalid
 
parts of the statement.

However I'm then left without the critical information of how many bids are made on each proposal.

I was just wondering if anyone can suggest an alternative way of counting the amount of bids made??

Any suggestions would be really apprecaited!!

Many Thanks

Re: problems with sql statement. please help!

Posted: Sun Sep 06, 2009 4:39 pm
by Darhazer
Create a table that contains proposalid / bidid and join it:
[sql]SELECT proposal.*,SYSDATE(), bids, TIME_FORMAT(TIMEDIFF(tomorrowtime, SYSDATE()), '%H hours, %i minutes') AS timeleftFROM proposalJOIN (SELECT proposalid, count(*) AS bids FROM bid GROUP BY proposalid) AS b ON proposal.proposalid = b.proposalidWHERE NOW() < proposal.tomorrowtime AND proposal.username = '$username'[/sql]

Re: problems with sql statement. please help!

Posted: Sun Sep 06, 2009 6:39 pm
by debuitls
Thanks you very much for getting back to me Darhazer!

I ran your suggestion in phpmyadmin but it seems to be returning an empty result when it should be returning four.

I have been trying different variations on your suggestions but doesnt seem to work.

I also altered my original code to this..

Code: Select all

 
$result = mysql_query("SELECT proposal.*,SYSDATE(),GROUP_CONCAT(bid.proposalid) AS 'bids', TIME_FORMAT(TIMEDIFF(tomorrowtime, SYSDATE()), '%H hours, %i minutes') AS timeleft
FROM proposal
LEFT OUTER JOIN bid ON proposal.proposalid = bid.proposalid
WHERE NOW() < proposal.tomorrowtime AND proposal.username = '$username'
GROUP BY bid.proposalid");
 

Code: Select all

 
echo "<td>" . count(",",explode($row['bids'])). "</td>";
 
but still only retrieving the one record!

Just wondering if anybody can spot anything with either of these or can suggest anything else?

Many Thanks