[SOLVED] How to display count of duplicate entries

[msaz87]

Hey all,

 

I was looking for a way in MySQL to count the existences of an entry and then display them. Basically, it's going to be used in a scheduling tool to display how many games for a given week a team will play, so if the DB looks something like:

 

week....team....gametime

 

1....panthers....0530

1....patriots....0530

1....broncos....0530

1....colts....0530

1....broncos....1030

1....bengals....1030

1....patriots....1030

1....broncos....1230

 

I'd want to be able to display something like this:

 

TEAM (GAMES)

Panthers (1)

Patriots (2)

Broncos (3)

Colts (1)

Bengals (1)

Cardinals (0)

 

The setup I have now is just displaying each team in the league in their respective division, I just don't know how to count the occurrences of that team in another table that handles the schedule data.

 

Any advice is greatly appreciated -- thanks!

[Mchl]

SELECT team, COUNT(*) AS playCount FROM table WHERE week = 1 GROUP BY team

[msaz87]

SELECT team, COUNT(*) AS playCount FROM table WHERE week = 1 GROUP BY team

 

Thanks for your help -- the only portion of the code you posted I didn't understand was the "playCount" section. I understood it as:

 

SELECT col_containing_teams

COUNT(*) AS ???

FROM table

WHERE col_containing_weeks

GROUP BY col_containing_teams

 

Thanks again

[Mchl]

It's just giving a name to the result of COUNT(*), so that it is easier to use it on PHP.

Look up SQL aliases.

[msaz87]

Sorry... I'm still new to MySQL so I need my hand held through a lot of things...

 

My code looks as follows.. but it's not outputting anything and I'm not sure if the problem is in the query or just how I'm trying to echo it later?

 

				<?
						$game_count 		= mysql_query("	SELECT team, COUNT(*) AS playCount 
											FROM schedules 
											WHERE league = $league_selection 
											AND week = '$week' 
											GROUP BY team")
											or die(mysql_error());


				?>

				<? while($row = mysql_fetch_array($game_count)){ ?>

					<? echo $row['COUNT(team)'] ?>

				<? } ?>	

 

Thanks again

[Mchl]

First of all: you should not use <? . It's better to use <?php

 

The code should look like this:

 

<?php 
$query = " SELECT team, COUNT(*) AS playCount
  FROM schedules
  WHERE league = $league_selection
  AND week = '$week'
  GROUP BY team";

$game_count  = mysql_query("$query");
  or trigger_error(mysql_error()." :$query",E_USER_WARNING);
                                    
while($row = mysql_fetch_array($game_count)){
               
  echo $row['playCount']; //That's what we used alias for 

}

[msaz87]

The code as you gave me didn't ouput any number whatsoever, so I switched it to look for a specific team's ID and it pulls a 0, when it should be 1.

 

					$query 		= " 	SELECT team, COUNT(*) AS playCount
				  			FROM schedules
				  			WHERE league = $league_selection
				  			AND week = '$week'
				  			AND team = '251' ";

 

Any ideas?

 

Thanks!

 

[Mchl]

echo $query to see if it contains all necessary data. You can even copy and paste it into phpMyAdmin (oother software) to see if it returns results.