如何从PHP中的多列SQL中检索数据

Question

I am struggling with this sql and want to be able to select from the PID column as well as the other two columns listed.

SELECT PID, BID AS BAID, SUM(Price*Quantity) AS Total 
FROM product 
INNER JOIN basket 
  ON product.PID = basket.PID 
WHERE Email = '$email'

Basically the code executes fine without me trying to get the 'PID' column.

I can't figure out where to include it without breaking the code.

Any help is appreciated.

I've tried moving it about and even setting PID AS a new column but nothing seems to be working.

Answer 1

Since PID exists in both tables, it becomes ambiguous (meaning that SQL doesn't know which value it should select from, even if it is exactly the same value). You therefore need to specify which table you want to select it from, by using the syntax table.column. The table can be replaced by an alias (p for the products table, b for the basket table), which makes this easier.

You should also GROUP BY when using aggregate functions such as SUM().

SELECT p.PID, p.BID AS BAID, SUM(Price*Quantity) AS Total 
FROM product AS p
INNER JOIN basket AS b
  ON p.PID = b.PID
WHERE Email = '$email'
GROUP BY b.BID;

You should also use a prepared statement instead of injecting variables directly into your query, see How can I prevent SQL injection in PHP?