[SOLVED] Counting the number of bids made on a tender

[debuitls]

Hi,

 

I'm trying to develop a site where buyers can make proposals and sellers can bid on the proposals.

 

On one of the pages I have the buyer proposal information IE proposal subject column, proposal category column..etc

 

Id like to add a "number of bids made" column to this page but I'm not sure how to do it.

 

The problem is that one user can make several proposals which are all on the same page. Each one of these proposals will have a different number of bids made so each count bids made has to refer exclusively to that row.

 

At the moment I have this..

 

$result = mysql_query("SELECT proposal.subject, proposal.budget, proposal.category,
count(bid.username) AS bids
FROM proposal
LEFT JOIN bid ON proposal.proposalid = bid.proposalid
WHERE proposal.proposalid = //[b]stuck on this bit here, needs to refer to individual row [/b] 
GROUP BY bid.username");

echo "<table id='myTable' cellpadding='0'>
<thead>
<th axis='string'>Subject</th>
<th axis='string'>Budget</th>
<th axis='string'>Category</th>
<th axis='string'>Bids Made</th>
</thead>";

while($row = mysql_fetch_array($result))
{
echo "<tr onclick=\"window.location='results.php?id=" . $row['proposalid'] . "'\">";
			  echo "<td>" . $row['subject'] . "</td>";
			  echo "<td>" . $row['budget'] . "</td>";
			  echo "<td>" . $row['category'] . "</td>";
			  echo "<td>" . $row['bids'] . "</td>";
			  
			  				  
			echo "</tr>";
			echo "</tbody>";
}
echo "</table>";

 

So basically I'm trying to figure out how to count the number of bids made for each proposal and put it in a column.

 

I'm looking for a similar effect as on tenderme.ie and the "active replies" column.

 

Sorry for the long post.

 

I'd really appreciate it if anyone could make any suggestions.

 

Thanks a million

 

[DavidAM]

Your SQL is close, but if I understand your question correctly it should be something like this:

SELECT proposal.proposalid, proposal.subject, proposal.budget, proposal.category, count(bid.proposalid) AS bids
FROM proposal LEFT JOIN bid ON proposal.proposalid = bid.proposalid
GROUP BY proposal.proposalid");

You might also have to include all of the non-aggregate columns in the GROUP BY:

GROUP BY proposal.proposalid, proposal.subject, proposal.budget, proposal.category

depending on which server and version you are using.

 

This will get you a list by proposal ID (including subject, budget and category) with the total number of bids placed for that proposal. 

[debuitls]

Hi DavidAM

 

Thanks for taking the time to reply to my post.

 

It works perfectly.

 

Great stuff.