COUNT number of times word appears in cells & list

[leeandrew]

On my site users can create a dream team of 11 players. Once they select a player for each position the player name is saved in a column with the position of the player e.g. `Centre Half` someone choose Jamie Carragher. So in cell 'Centre Half' "Jamie Carragher" is inserted.

 

Now i want to list how many times each player appears in users top11 players. i.e. Jamie Carragher is in 6 users top11's, so the number 6 appears in the list next to his name. And i want to create the top 10 most popular players.

 

1. Jamie Carragher (6) ... is in 6 users top11

2. Steven Gerrard (4) .. is in 4 users top11

3. Cristiano Ronaldo (4) ...

... etc.

 

I have been having problems count from different columns.

 

COUNT(*) FROM (gk,lb,cb1,cb2,rb,lm,cm1,cm2,rm,st1,st2) WHERE ?????

[cooldude832]

table structure???

[leeandrew]

Thats what i dont know how to do, i was listing every single entry (player) in a massive list with a SQL query next to each one, where i could write the players name in the WHERE part of the query, i dont know how to select each player and make it work for all.

[cooldude832]

I can't work magic without your mysql table structure.

[leeandrew]

tablname is `pluserdata` auto incriment for user ID's is `id`, i have 11 columns which are the positions. named GK, LB, CB1, CB2 etc... each player has a profile where a user can click an option to assign the player to a certain position, so the player name is saved in the column of choice.

 

I am trying to count how many times a player name appears in columns GK, LB, CB1.... etc then create a toplist.

[leeandrew]

Another page on my site uses this to create a toplist of best rank:

 

<?php


updateRanking();
$res = getRankingItems($listtype, $category);

$numrows=mysql_num_rows($res);
$records = array(array());
for ($i=1; $i<$numrows+1; $i++){
$records[$i]=mysql_fetch_array($res);
}
mysql_free_result($res);

if ($toplist > $numrows)	$listto=$numrows+1;
else $listto=$toplist+1;

$color[0]=$td_even;
$color[1]=$td_odd;
if ($toplist>$numrows) $toplist=$numrows;
?>

 

Then:

 

   <? 

for ($i=1; $i<$listto; $i++){
$bla=getmod($i,2);
?>

<P>
<b><? echo $i;?>.</b>
<? echo "<a href='/index.php?forceid=".$records[$i]['id']."'>
<b>".$records[$i]['name']."</b></a>";?>
</p>

<? }?>

 

Could i apply this code to create the toplist i want?

[cooldude832]

tablname is `pluserdata` auto incriment for user ID's is `id`, i have 11 columns which are the positions. named GK, LB, CB1, CB2 etc... each player has a profile where a user can click an option to assign the player to a certain position, so the player name is saved in the column of choice.

 

I am trying to count how many times a player name appears in columns GK, LB, CB1.... etc then create a toplist.

 

So you are storing full text in the position fields?

That is poor method you should have a secondary table called Players that stores player data and use the foreign key of PlayerID in the "team" table to link it to each player

[leeandrew]

I have another table with all player data, its just that i didnt know how to get the ID of the player to be set in the position column so i had to type player names in manually rather than using any code.

[cooldude832]

can u go into phpmyadmin and take screen shots of your table's structure cause what I'm seeing is you need help and I can't help without seeing cause odds are this can be refined to be a ton better.

[leeandrew]

Table `pluserdata` where user info is stored. Top1, top2, etc... are player positions containing names.

 

 

Table `voting_items` where player info is stored.

[cooldude832]

so you want to count the number of times each player shows up in the top1,top2,top3 etc?

or what fields

[leeandrew]

count the number of times each player shows up in top1 - top11