COUNT ()

[Sabyre]

Hey Guys,

Must be "a case of the MONDAYS" =)

I'm trying count the results of a query of two tables. The following gives me:
Column 'id' in field list is ambiguous

Which I can understand as MYSQL is not sure which result I am asking for.

$query = "select count(id) as auctions from DSI_auctions a, DSI_users u WHERE a.current_bid>'0' AND a.closed<>'0' AND a.suspended='0' AND a.shipped<>'1' AND a.current_bid>reserve_price AND u.account<>'9999999'";
      $result = mysql_query($query);
      if(!$result){
      print "$ERR_001<BR>$query<BR>".mysql_error();
      	exit;
      }
      $num_auctions = mysql_result($result,0,"auctions");

The goal here is to pull auctions won by "account" type. The problem is "account" type is in another table. In the users table.

What I am asking MYSQL to do is to find all the auctions that have closed with a winner and then only return results where the winner is of a speciifc account type.

"I need my red stapler back"

[ChrisPadgham]

you need to include the join on the user table, something like

AND a.userId=u.UserId

[Sabyre]

Excellent!, Thank you my friend. Obviously MYSQL is not my forte. Here is how it worked out to get the results:

$query = "select DISTINCT(a.id), a.refund, a.shipped, a.reserve_price, a.current_bid, a.current_high_bidder, a.closed, u.nick, u.bids_remaining, a.title, a.starts, a.description, a.paid, a.suspended from DSI_auctions a CROSS JOIN DSI_users u ON a.current_high_bidder=u.nick WHERE a.current_bid>'0' AND a.closed<>'0' AND a.suspended='0' AND a.shipped<>'1' AND a.current_bid>reserve_price AND  u.account<>'9999999'";
      $result = mysql_query($query);