problems with sql statement. please help!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
debuitls
Forum Newbie
Posts: 2
Joined: Sun Sep 06, 2009 4:24 pm

problems with sql statement. please help!

Post 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
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: problems with sql statement. please help!

Post 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]
debuitls
Forum Newbie
Posts: 2
Joined: Sun Sep 06, 2009 4:24 pm

Re: problems with sql statement. please help!

Post 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
Post Reply