doushe7934 2019-04-14 14:49
浏览 117
已采纳

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

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.

  • 写回答

2条回答 默认 最新

  • dongtuo6562 2019-04-14 15:09
    关注

    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?

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?