Distinct , Count(*) or else???

[trackz]

Hello.. I have this problem, and just can't figure it out..

I have some fields in my table.

field1 - field2

sep - car
inc - bike
dghd - plane
wej2 - car
arg - car
bla - plane
jhg - bike

What I need is: a query that will give all different entries of field 2, but also counts the number of dublicate field

output would be something like:

car - 3
bike - 2
plane - 2

I've already tried with distinct en count(*), and came to this query:

SELECT distinct field2, count(*) FROM tablename GROUP by field2

This works allright in a mySQL client, but I have no idea to output it with PHP..

Anybody?

Thanx in advance.

Trackz

[steved]

You're on the right track. Just change your query so that you drop the distinct and then assign count(*) to a different name, like this:

SELECT field2, count(*) AS FCount
FROM tablename
GROUP by field2

Then, after you execute the query, you can retrieve the values for field2 and FCount the same as you would from any query.

[trackz]

thanx for the info... nice.. but I won't succeed.. someway it isn't put ou the right way.. this is what I have..

mysql_query("SELECT field2, count() AS FCount FROM table GROUP by field2");
$query = "select from table";
$mysql_result = mysql_query($query);
while($row = mysql_fetch_row($mysql_result)){
$field1 = $row[0];
$field2 = $row[1];
}

print("
$field2 - $FCount<BR>
");

what would be the solution?

Thanx again....

[steved]

I don't use mysql so I'm not familiar with the exact formatting to be used.

However, it looks like your print statement should be within the while loop and based upon what I think you're doing you should print $field1 and $field2.