Counting Records 1

[SLMHC]

Ive tested the following in PHPMYADIM and it returns each record and how many times it occurrs.

SELECT gm, COUNT(*) FROM league_ws GROUP BY gm ORDER BY 'COUNT(*)' DESC

Yet when I try this on my webpage all i get is the number of records(gm).

<?php

include 'dbinfo.php';

include 'dbconnect.php';

$query="SELECT gm, COUNT(*) FROM league_ws GROUP BY gm ORDER BY 'COUNT(*)' DESC";
$result=mysql_query($query) or die ("The Query has failed");

$num=mysql_numrows($result);

echo "$num\n";

mysql_close();

?>

Am I missing something?

-Dave

 

[KarveR]

you're missing code to actually display the records:

<?php

include 'dbinfo.php';

include 'dbconnect.php';

$query="SELECT gm, COUNT(*) FROM league_ws GROUP BY gm ORDER BY 'COUNT(*)' DESC";
$result=mysql_query($query) or die ("The Query has failed");

$num=mysql_numrows($result);

echo "$num\n";

while ($myrow=mysql_fetch_array($result)){
	
	echo "$myrow[0], $myrow[1]<br>";

}

mysql_close();

?>

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[SLMHC]

KarveR,

Thanks for that.

Can you recommend a good(s) book to learn php+mysql? I have one of this learn in 24 hours books but the learning curve goes way up in about 4 chapters and none of the examples seem to work.

I have built my app from everything I could find on the net and a few kind souls to help smooth out the wrinkles and it seems ive found all the good sites already.

-Dave

 

[KarveR]

http://www.devshed.com

have some very good useable PHP+mySQL scripts and tutorials, and the search here is a great source for solutions to most things.

if its a sams book, they normally have all the source code availble on the site too.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[SLMHC]

thanks KarveR!

check out your handy tip!

http://il30.dave-tek.com/league_ws_totals.php

-Dave

 

[KarveR]

Nice one! glad to have been some help.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[SLMHC]

KarveR,

Can I pick your brain again?

I want to query more than one table. I have found the UNION command. It looks like it will do the job for me.

I would like it to pull the name and team from multiple tables, count the name field, add the number of times it appears in the selected table and display the data.

$query="SELECT  gm, team, COUNT(*) FROM div_alw GROUP BY gm 
UNION
SELECT gm, team, COUNT(*) FROM div_alwc GROUP BY gm ORDER BY 'COUNT(*)' DESC, gm";

Problems:
1.Im not exactly sure if UNION actually adds the COUNT(*) from the UNION. Is JOIN the solution or a more comlexe sql statement?

2. For some reason when I have a gm that is in two tables with the same team that have the same count number, only one of the records is displayed. if i change the team name or add/delete a record both gm/team combos shows up.

-Dave