PHP MySQL How to grab one of each row and not duplicates?

[Stalingrad]

Hey guys! I haven't really coded teh exact query for this yet. But what I want to do is I want to grab multiple results that are the same and only display one BUT also grab DIFFERENT results from the SAME table the same way. Let me give an example here...

 

Lets say this is my table:

id - name - image

1 - apple - apple.gif

2 - grape - grape.gif

3 - orange - orange.gif

4 - apple - apple.gif

5 - apple - apple.gif

6 - orange - orange.gif

 

I want it to diaply like this:

Apple

-apple.gif-

Quantity: 3

 

Orange

-orange.gif-

Quantity: 2

 

Grape

-grape.gif-

Quantity: 1

 

My query for LIMIT 1 would look like this, but it only grabs one result toal.. i think.. I would also be grabbing stuff from two different tables, which wouldn't be an issue. in this case, i want to grab the QUANTITY form one table, )the uitems table) and the image and name from the items table) the uitems query is also grabbing theitemid to use with the items table..

\\ this would be getting the quantity \\
$query = "SELECT * FROM uitems WHERE username='$showusername' AND location='2'";
$thisthat = mysql_query($query);
while($row = mysql_fetch_array($thisthat)) {
$quantity = $row['quantity'];
$getid = $ow['theitemid'];
}

\\ now getting the item info \\

$itemquery = "SELECT * FROM items WHERE itemid='$getid'";
$item = mysql_query($itemquery);
while($thisrow = mysql_fetch_array($item)) {
$name = $thisrow['name'];
$image = $thisrow['image'];
$actualid = $thisrow['itemid'];
}

 

So basically, I want to be able to be able to grab one row of every kind that is there. How can I do that? Thanks so much in advance!! =D

[SergeiSS]

You wrote a lot of words... But the solution is easy. You have to change you request, something like this, if the name of the table is 'items' (that table with structure id-name-image):

SELECT *, count(id) as c 
FROM items
GROUP BY by name

you may also add any WHERE conditions. Read more about GROUP BY in the manual.

[Stalingrad]

Thank you for your reply, but what you wrote is a little confusing. What is the

, count(id) as c

part of it?

[SergeiSS]

If you go to the manual and read about GROUP BY and aggregate functions... Then you will find that count() counts the number of records in the group.

 

BTW, what is the meaning of your nick-name? I know this word only as a former name of one town in Russia.

[scootstah]

I believe the GROUP BY function is what you are looking for.

 

SELECT * FROM SELECT * FROM uitems WHERE username='$showusername' AND location='2' GROUP BY name

 

Also, you shouldn't use mysql_fetch_array() unless you for some reason specifically require what it does. mysql_fetch_array() returns both an associative array and a numerical array, so your array holds the contents twice - and this is entirely unnecessary. You should either use mysql_fetch_row() (if you want a numerical array) or mysql_fetch_assoc() (if you want an associative array - which in your example, you do).

[Stalingrad]

Thanks guys. :_ I am reading about GROUP BY. Yes, it is a reference to the Russian town from a long time ago.