Function Help

[Whitey]

Games statistic forum..
gamesplayed list all the games available with the ID number
member is the members profile with the main game selected
i am trying to make a statistic to count all the numbers for each main game.
here is my function so far

PHP Code:

function getGameStats()
{
global $dbprefix;
$query = "SELECT * FROM {$dbprefix}gamesplayed";
$result = mysql_query($query)
     or die(mysql_error());
echo '<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>';
for ($i = 0; $i < mysql_num_rows($result); $i = $i + 1)
{
    $gamepic = mysql_result($result, $i, "gamepic");
    $gamename = mysql_result($result, $i, "gamename");
    $id = mysql_result($result, $i, "ID");
        $query = "SELECT maingame, COUNT('".$id."') FROM {$dbprefix}members GROUP BY maingame";
        $result = mysql_query($query) or die(mysql_error());
            $stat = mysql_result($result);

    echo ("<td><div align='center'>$stat</div></td>");

}
echo '  </tr>
</table>';
} 


[Xav]

So what do you want from us?

[Whitey]

i am trying to make a statistic to count all the numbers for each main game.
here is my function so far

this

[Xav]

What does it do at the mo?

[Whitey]

mo = moment?

Well it dont work at the moment.. so... lol i am trying to count each person who has each game selected.. dono else how to explain it.

[Xav]

Yes, in colloquial English, mo = moment.

[Whitey]

Okay there is a Mysql DATABASE
with 2 Tables
1 is GamesPlayed
another is Members

In games played there are the following fields:
id
gamename
gamepic
statnames
outsidelink

In Members there the following fields:

ID
Username
Password
maingame

The main game field is an ID from the GamesPlayed Table..

So..
there are 5 members lets say
and there are 3 games..
and its filled out like this

ID: 1
Username: Joe
Password: test
maingame: 1

ID: 2
Username: Bob
Password: test
maingame: 1

ID: 3
Username: kiddney
Password: test
maingame: 3

ID: 4
Username: sally
Password: test
maingame: 1

ID: 5
Username: sid
Password: test
maingame: 2

now i am trying to write a function that that pulls out the ID number from the Members page of each members and counts them.
So it should return
Main Game: 1 Members = 3
Main Game: 2 Members = 1
Main Game: 3 Members = 1

[John]

I guess you can try something like this:

PHP Code:

$q1 = "SELECT `id` FROM `GamesPlayed`";
$result = mysql_query($q1);
$row = mysql_fetch_array($result);
$max = count($row);

for($i = 0; $i <= $max; i++)
{
    $q2 = "SELECT `id` FROM `Members` WHERE `maingame` = '".$row[$i]."'";
    $result2 = mysql_query($q2);
    echo $row[$i] . "-->" . mysql_num_rows($result);
} 


[Whitey]

i am trying to count each how many members have it selected.. not how many in gamesplayed.. well look at my function.. it shows what i am trying to do.. but it comes out blank.

Here let me show you visually since you guys dont understand.
Clan Hectic Defiance

See where it Says GAME. on all the member

I am making a statistic where it counts all the people with each game.. so
look at my function..
i know its almost right but it keeps coming up blank..

[John]

This:

"SELECT `id` FROM `Members` WHERE `maingame` = '1'";

Will generate a query of member id's, that have the game with the id 1 as their main game. Is that not what you want?