help with 2 queries

[adammc]

Hi folks,

Am stuck again, please help 🙁

I have 2 tables:
'postings' - contains data for each posting.
'bids' - contains the bids for each posting in the postings table.

I am trying to code a page which displays the postings in a html table, I am having trouble during the first while loop to count the number of bids for each posting from the bids table.

This is what I have so far, it is FAR from right as it is echoing the number of bids the first posting has, but then echoing that same number for all other postings 🙁

// make the query to get the postings
$query = "SELECT posting_id, vehicle_type, vehicle_make, year, buyers_city, buyers_state, condition, category, DATE_FORMAT(expiry_date, '%e-%m-%y, %h:%i %p') as e_date FROM postings WHERE buyers_id=$_SESSION[buyers_id]"; 


// run the query
$result = @mysql_query ($query); 


while($row=mysql_fetch_array($result)) 
   {


   // make the 2nd query to get the number of bids
$query2 = "SELECT bid_id, posting_id, sellers_id, price, status, DATE_FORMAT(date_of_bid, '%e-%m-%y, %h:%i %p') as bid_date FROM bids WHERE posting_id='11'"; 


// run the query		
$result2 = @mysql_query ($query2);


// get the number of rows
$num = mysql_num_rows($result2);


echo '<tr>';

	echo '<td class="row1" align="center" valign="middle" width="25%"><span class="gen">' . $row['category'] . '</span></td>';

	echo '<td class="row1" align="center" valign="middle" width="15%"><span class="gen">' . $row['e_date'] . '</span></td>';



if ($num > 0) { // if it ran ok, display the records

echo "<td class=\"row1\" align=\"center\" valign=\"middle\" width=\"10%\"><span class=\"gen\">$num</span></td>";

} else {

echo '<td class="row1" align="center" valign="middle" width="10%"><span class="gen">0 bids</span></td>';
	}


	echo '</tr>';

}

[iNFERiON]

The problem seems to be in your second query.

$query2 = "SELECT bid_id, posting_id, sellers_id, price, status, DATE_FORMAT(date_of_bid, '%e-%m-%y, %h:%i %p') as bid_date FROM bids WHERE posting_id='11'"; 

Since this is in your while clause, and the posting_id is static, it will retrieve the info for posting_id 11 for all returned rows. What you should do is this:

$query2 = "SELECT bid_id, posting_id, sellers_id, price, status, DATE_FORMAT(date_of_bid, '%e-%m-%y, %h:%i %p') as bid_date FROM bids WHERE posting_id='$row[posting_id]'"; 

[adammc]

Thanks for the reply, that worked a treat 🙂

One other question.
IN the bids table there is a column called 'price' which contained the bid amount. How would I extract the lowest bid price for each result during that while loop.

Would I need to do another query $query3 ?

[adammc]

I acived the desired way with this code:

// make the 3rd query to get the lowest bid amount
$query3 = "SELECT MIN(price)as lowest_bid FROM bids WHERE posting_id = '$row[posting_id]'";  

Is there a more efficient way of doing it?

[adammc]

I just put this code together so I dont get empty cells for the postings that dont have any bids. I wanted to add a dash into the cells that have a bidprice lower than 0.00

The result was nothing (empty) in the cells for the results that had no bids. Which I know is obvious looking at my if / else statement. I am just not sure not to construct it?

if ($row3['lowest_bid'] > 0.00) { // if the lowest bid is greater then 0.00 echo this

echo "<td class=\"row1\" align=\"center\" valign=\"middle\" width=\"10%\"><span class=\"gen\">' . $row3['lowest_bid'] . '</span></td>";

} else { // echo a dash  '-'

echo '<td class="row1" align="center" valign="middle" width="10%"><span class="gen"> - </span></td>';
	}

Any advice would be GREATLY appreciated 🙂