Trouble with list showing counts

[Tunnleram]

I've setup the ability for members to select their favorite games. I probably could have built this better from a database design point of view so I'm open to suggestions there.

Aside from that I'm trying to build a query that provides me with counts of the games so I can determine which is the most popular choice.

I can easily return the values of the first favorite game, but once I start to get all of them I have issues because I need to make a join that is apparently eluding my newness to PHP/MYSQL.

The field information is below.

Any help is much appreciated.

=================================

Persons Table
id
name
favorite_game_01 > (games.id)
favorite_game_02 > (games.id)
favorite_game_03 > (games.id)
favorite_game_04 > (games.id)
favorite_game_05 > (games.id)

Games Table
id > (persons.favorite_game_xx)
name

[urtrivedi]

select a.id, a.name,count(*) total from games a 
left outer join 
(

select favorite_game_01 id from persons
union 
select favorite_game_02 id from persons
union
select favorite_game_03 id from persons
union
select favorite_game_04 id from persons
union
select favorite_game_05 id from persons

) b on a.id=b.id

where b.id is not null
group by a.id,a.name

-- order by total desc

[Tunnleram]

Thank you very much for your fast response. I very much appreciate it.

I applied your suggestion and it's surely better than what I had, but it appears the counts don't work properly.

$query="SELECT g.id, g.name as game, count(*) total 
	FROM games g 
	LEFT OUTER JOIN 
	( 
	 SELECT favorite_game_01 id FROM persons
	 UNION 
	 SELECT favorite_game_02 id FROM persons
	 UNION
         	 SELECT favorite_game_03 id FROM persons
	 UNION
	 SELECT favorite_game_04 id FROM persons
	 UNION
	 SELECT favorite_game_05 id FROM persons
	 ) 
	p on g.id=p.id 
	
	WHERE p.id is NOT NULL and p.id <> '0' and g.name <> 'N/A'
	GROUP BY g.id";

         confirm_query($query);

         $result=mysql_query($query, $connection);
         $num=mysql_numrows($result);

         while($row = mysql_fetch_array($result))
         {	
                  	$game = $row['game'];
                  	$total = $row['total'];
                  echo '<p>' . $game . ': ' . $total . '</p>';
         }

Although I have more than one entry for Call of Duty 7, it shows only 1. Any ideas?

============
Results:
Call of Duty 7: Black Ops: 1
Medal of Honor: 1
Just Cause 2: 1

[smantscheff]

Please submit some test data (as CREATE TABLE and INSERT statements).
Regarding table design, it would be easier for you with normalized tables. Create a table favorite_game with person_id and game_id instead of your favorite_game_xx fields.

[pratik_garg]

Hi Tunnleram,

please see your pasted code carefully.

you missed g.name in group by clouse what you have to put in.
just edit your ccode it should run.

or else you can put following code also.

SELECT g.id, g.name as game, 
   (select count(*) from person
    where   favorite_game_01 =g.id
         or favorite_game_02 =g.id 
         or favorite_game_03 =g.id 
         or favorite_game_04 =g.id 
         or favorite_game_05 =g.id 
    ) total 
from games g

one thing you have to be sure that a person can not choose a game twice or more in favorite list.
else it will not count those duplicate game id as this query is counting only rows.

if you want result like that then tell me we can generate result like this also.... :)