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.

图片转代码服务由CSDN问答提供 功能建议

我正在努力解决这个问题,希望能够从PID列以及其他两列中进行选择 列出。

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

基本上代码执行正常,我没有尝试获取'PID'列。

在不破坏代码的情况下,我无法弄清楚包含它的位置。

感谢任何帮助。

我已经尝试过移动它 甚至将PID AS设置为新列,但似乎没有任何工作。

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

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条)

相关推荐 更多相似问题