MySQL select distinct and return all data in row

Hi All

I have this MySQL query:

$sql = “SELECT DISTINCT productID FROM cart WHERE cartID = '” . $_SESSION[“cartID”] . “'”;
$result = $con->query($sql);

while($row = $result->fetch_assoc())
{
CODE
}

Which works fine however I realised that I need to also get other information out of the same row in the WHILE loop.

I have been trying to work it out and have tried:

$sql = “SELECT * FROM cart WHERE productID IN (SELECT DISTINCT productID FROM cart) AND cartID = '” . $_SESSION[“cartID”] . “'”;
$result = $con->query($sql);

while($row = $result->fetch_assoc())
{
CODE
}

This however does not seem to actually select the distinct.

Any idea on how I can select everything in the row while using the distinct option?

Thanks for any help.

mrmbarnes

Well yeah, thats not going to limit anything…

Your subquery is “Select the unique list of all productID’s”, and then you tell your outer query to only pull rows that have a productID that is in the list of…every productID in the table…

What data exactly do you need to extract, and why do you only need to extract it once? For that matter, why do you have duplicate rows in a cart? Sounds like you need to reexamine your insert logic.

What I have made has products and each product has a variety of sizes and colours.

The code above chooses each product that has been added to the cart and pulls out the colours and sizes for the productID.

In the section I have CODE I want to update a table using the uniqueID from the original select but can’t seem to work out how to do it.

Any suggestions?

No, it doesnt. It pulls the items from the cart.

If the colors and sizes are being stored in the cart, you… dont want to make the productID unique at all, you just want to pull everything in the cart.

If they’re NOT being stored in the cart, SELECT’ing from the cart table isnt going to get you the information you need anyway, so… don’t pull from that table?

Perhaps you’re trying to GROUP BY instead of SELECT DISTINCT?

i doubt it, but then…

the issue i have with DISTINCT is this starting point –

SELECT DISTINCT productID FROM cart WHERE cartID = '" . _SESSION[“cartID”] . “’”;

the use of DISTINCT here implies that it’s possible for a single cart (see WHERE clause) to have the same product more than once

i suspect that this is simply an error in query writing, but in case @mrmbarnes actually does have a cart that allows the same product more than once , this should be fixed immediately before further development

My brain postulated when he mentions his code is attempting to update a table, he’s trying to do stock management; but at that point he would want every row in the cart; or alternatively, his stock system is naive and files all instances of productID as a single stock quantity, which would be impossible with color/size as qualifiers to a unique tuple (productID,color,size)…

Yeah. I got nothing. Everything’s pointing to either a misuse of the cart table or a misuse of the product table.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.