Counting data?

[7724]

Hi Guys,

Wondering if anyone could help.

I have created a count of 'appearances' everytime the name 'harvey' appears in certain variables in my 'appearances' table:

<?php 

$sql = "SELECT COUNT(*) FROM game WHERE game.shirtone = 'harvey' OR game.shirttwo = 'harvey' OR game.shirtthree = 'harvey' OR game.shirtfour = 'harvey' OR game.shirtfive = 'harvey' OR game.shirtsix = 'harvey' OR game.shirtseven = 'harvey' OR game.shirteight = 'harvey' OR game.shirtnine = 'harvey' OR game.shirtten = 'harvey' OR game.shirteleven OR game.r1 = 'harvey'OR game.r2 = 'harvey' OR game.r3 = 'harvey' OR game.r4 = 'harvey' OR game.r5 = 'harvey' OR game.r6 = 'harvey' OR game.r7 = 'harvey' OR game.r8 = 'harvey' OR game.r9 = 'harvey' OR game.r10 = 'harvey' OR game.r11 = 'harvey'OR game.r12 = 'harvey' OR game.r14 = 'harvey' OR game.r15 = 'harvey' OR game.r16 = 'harvey' OR game.r17 = 'harvey'"; 
$res1 = mysql_query($sql) or die(mysql_error()); 
echo mysql_result($res1, 0); 
?>

I want to modify the code now to look in these variables - ie game.shirtone, game.shirttwo etc in the 'appearances' table, but to look at all the different names that are saved dynamically ie Harvey, Stepford, Higgins and output how many times each name appears in a nice neat table in order of how many times they have appeared = ie:

Can anyone please advise?

Thanks

[planetsim]

Well here is an example using a one field test

SELECT  datefield, COUNT(*) FROM test
GROUP BY `datefield` DESC

You will need to select a certain field which to group by, as for ordering in descending order Im not too sure about it, im sure someone like sxooter may show an example of how you would be able to do it.

[7724]

Thanks Planet Sim - if anyone could post examples that would be grand.... 😃 😃

[planetsim]

Also you may want a mod to move this to the Database Forum, you will be sure to get a better response there.