Page 1 of 1

selecting min and max bid for each item

Posted: Thu Oct 23, 2003 4:14 pm
by mccommunity
I am doing a little piece for an auction company. What i am trying to do is query a bid table and pull all the items for a particular auction facility out of the bid table so item #1 might have 20 entries which are 20 different bids. So I am doing a select distinct just to grab the items that have bids on them. now I want to loop through each item grabbing all the records (bid information) and trying to grab the starting bid and the final maximum bid on each item. Here what I have so far and I am stuck, can anyone help me with this? Thanks a lot.


$strSQL = "select distinct item from bidlog where auction='$auction' and code='B' order by item ";
$result = pg_exec($con, $strSQL);
if(!$result)
{
print("<SCRIPT>alert('An unknown error occurred. The bidder information could not be retrieved, please try again. If it does not work contact AMS.')</SCRIPT>\n");
return "false";
}

$bid_rows = pg_numrows($result);
for($z=0;$z<$bid_rows;$z++)
{
$data = pg_fetch_row($result, $z);
$strSQL = "select * from bidlog where auction='$auction' and code='K' and item='$data[0]' order by item ";
$result = pg_exec($con, $strSQL);
IN THIS AREA I THINK I NEED TO DO AN ARRAY AND DO A MIN($ARRAY) AND MAX($ARRAY) BUT NOT REALLY SURE THE BEST WAY

}

Posted: Fri Oct 24, 2003 1:54 am
by volka
you can get all the infos with one query
something like

Code: Select all

SELECT item, min(bid), max(bid), auction FROM bidlog WHERE code='K' GROUP BY item ORDER BY item
important is the GROUP BY clause. It allows you to use the aggregate functions max(<field>) and min(<field>) in that query

????

Posted: Fri Oct 24, 2003 2:17 am
by itsmani1
wel me think volka is right.......................