How to retrieve data from multiple columns SQL in PHP

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.

Sample data and desired results would help. Your query should fail because it uses an aggregation function but has no `GROUP BY`. — Gordon Linoff, Apr 14 '19 at 14:50
The `PID` column is ambitious, you need to specify which table you're selecting it from, even if it has the same value. — Qirel, Apr 14 '19 at 14:55

score 0 · Answer 1 · answered Apr 14 '19 at 14:52

0

Do you want the total for a basket, something like this?

SELECT b.BID AS BAID, SUM(p.Price * b.Quantity) AS Total
FROM product p INNER JOIN
     basket b
     ON p.PID = b.PID
WHERE b.Email = ?  -- I am guessing the `Email` is associated with the basket and not the product
GROUP BY b.BID ;

The ? is a placeholder for a parameter. You should be passing the comparison value in as a parameter, rather than munging the query string.

answered Apr 14 '19 at 14:52

Gordon Linoff

1,242,037
58
646
786

Is it possible to also select the PID column, adding that aswell is whats producing the error. Thanks for the other help though! – sarkyJD Apr 14 '19 at 14:55
I already asked for sample data and desired results. It is not clear what you want. – Gordon Linoff Apr 14 '19 at 17:09

score 0 · Accepted Answer · edited Apr 14 '19 at 15:17

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?

How to retrieve data from multiple columns SQL in PHP

2 Answers2