Member Avatar for 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"

  • 2 Contributors
  • 2 Replies
  • 193 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Sabyre

Dani AI

Generated

Nice catch by — the error comes from MySQL not knowing which table’s id you meant. Always qualify ambiguous column names (for example a.id), and prefer explicit JOIN syntax instead of listing tables with commas so your ON clause is unambiguous. A clean pattern that solves the original problem is something like:

SELECT COUNT(*) AS auction_count
FROM auctions AS a
JOIN users  AS u ON a.winner_id = u.id
WHERE a.current_bid > 0
  AND a.closed <> 0
  AND a.suspended = 0
  AND u.account <> '9999999';

This avoids the ambiguous-column error and makes the join intent obvious. (stackoverflow.com)

If joining brings back duplicate rows (e.g., multiple matching rows on the joined side), a plain COUNT(*) can be larger than the number of unique auctions. Use COUNT(DISTINCT a.id) when you need the number of unique auctions after the join. Also remember that COUNT(column) ignores NULL values while COUNT(*) counts rows — pick the form that matches what you want to count. (dev.mysql.com)

Two practical hygiene items: stop using the old mysql_* extension (it was deprecated/removed) and use prepared statements via mysqli or PDO to avoid injection and other issues. Example PDO pattern:

$stmt = $pdo->prepare(
  "SELECT COUNT(*) FROM auctions a
   JOIN users u ON a.winner_id = u.id
   WHERE a.current_bid > 0 AND u.account <> ?"
);
$stmt->execute(['9999999']);
$count = (int) $stmt->fetchColumn();

Migrating to PDO/mysqli and prepared statements makes code safer and future-proof. (wiki.php.net)

Finally, when tuning or debugging, run EXPLAIN on the query to check whether your join uses indexes and to spot scans that cause slowness; ensure join columns are indexed (prefer numeric user IDs over nick strings for joins) and test with small limits first. (cunzaima.cn)

Recommended Answers

All 2 Replies

Member Avatar for ChrisPadgham

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

AND a.userId=u.UserId
Member Avatar for 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);
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.