How to show Quantity?

[Stalingrad]

Hello. I'm building a shops script for my website. I want to be able to show all of the items somebody has in their shop, and how many of that item they have. I've run into a little problem. I know how to show how many of the item they have, but how do I make it so that it shows only one row for that item, instead of one row for each item they have? Here's an example:

 

Let's say I have three pineapples.

 

It shows this in the stock table:

 

Item    Quantity    Price  Delete?

Pineapple    3          300        X

Pineapple    3          300        X

Pineapple    3          300        X

 

My question is, how do I get it to show like this:

 

Item      Quantity  Price      Delete?

Pineapple    3          300        X

 

I want it to show only once.

 

If anybody could help me out, and show me how to do this with simple PHP, I would really appreciate it, thank you! =]

[premiso]

Post the SQL statement you use the grab the first set.

[revraz]

For your example, wouldn't it be

 

Pineapple  9  900 ?

 

Since it looks like they actually have 3 valid rows?

 

If so, use the Group By Item in MySQL.

[Stalingrad]

$showitemsquery = mysql_query("SELECT * FROM useritems WHERE location='Shop' AND username='$username'")

[Stalingrad]

It would only be 3, because I only have 3 pineapples. It shows it 3 times because it's showing a row in the table for each pineapple plus the amout I have altogether.

[revraz]

Then use LIMIT 1?

[Stalingrad]

Oh, okay. Thank You. I though LIMIT 1 limited everything to 1. Does it only limit 1 for the certain item?

[revraz]

No, it will only return 1 result.

 

I guess a better question is, why does it show QTY 3 each time, for each row, instead of a QTY of 1 for each row.  Sounds like maybe you are inserting your data incorrectly.

[Stalingrad]

It shows 3 quantity for each row because I can't merge the row into one. Like, I want it to show that I have 3 of the item. In the database, I have 3 different item id's. Each item has a unique item id. I want to show that I have three of the same item name, but three different item ids.