Subquery problem(s)

[FRANTIC]

I have set up a database (MySql 4.1.21) with these tables and rows:

players
p_id (= player_salary.players_id)
team_id
div_id
conf_id
fname
lname
position
status


player_salary [1 to many]
player_id (= players.p_id)
yr
amount
type


Basically, the “players” table holds some information for multiple players, and the “player_salary” table holds a player’s salary for each year of their contract.

What I am trying to do is display the players’ names and their total salary across the length of their contracts. So, I decided to throw all the relevant player data from the database into a multidimensional array, and then output it with PHP (5.1.6).

Here’s my query and code:

$query = array();

$query[] = 'SELECT DISTINCT *  ' FROM players, player_salary, (SELECT SUM( amount ) AS salary_total FROM player_salary GROUP BY player_salary.player_id) AS table01  GROUP BY players . p_id  ORDER BY players . lname ASC';

$rowSet = array();
for ($i = 0; $i < count($query); $i++) {
    $result = mysql_query($query[$i],$link) or die(mysql_error());
    while ($row = mysql_fetch_assoc($result)) {
        $rowSet[] = $row;
    }
    mysql_free_result($result);
}

echo '<table>';

foreach ($rowSet as $outer_key=>$single_array){
echo '<tr>';
    echo '<td>'.$rowSet[$outer_key]['lname']. ', '.$rowSet[$outer_key]['fname'].'</td>';
    echo '<td>'.$rowSet[$outer_key]['salary_total'].'</td>';
echo '</tr>';
}
echo '</table>'; 

And here’s where the problems start. While I can calculate the total salary for Johnny Doe, for some reason that same amount appears for John Doe as well (same with the values for ‘year’ and ‘amount’ ). In reality Johnny Doe’s total salary should be (5,000,000+3,000,000+3,000,000=11,000,000) and John Doe’s salary should be (2,000,000) 2,000,000.

Here’s what the array looks like:

Array
(
    [0] => Array
        (
            [p_id] => 1
            [team_id] => 14
            [div_id] => 2
            [conf_id] => 1
            [fname] => John
            [lname] => Doe
            [position] => F
            [status] => active
            [player_id] => 0
            [yr] => 2007
            [amount] => 5000000
            [type] => 
            [salary_total] => 11000000
        )

    [1] => Array
        (
            [p_id] => 0
            [team_id] => 14
            [div_id] => 2
            [conf_id] => 1
            [fname] => Johnny
            [lname] => Doe
            [position] => F
            [status] => active
            [player_id] => 0
            [yr] => 2007
            [amount] => 5000000
            [type] => 
            [salary_total] => 11000000
        )

)

So, I’m wondering if someone can tell me what I’m doing wrong?

I’m pretty sure it’s the query because when I try the subquery (SELECT SUM( amount ) AS salary_total FROM player_salary GROUP BY player_salary.player_id) in phpMyAdmin, it works. When I put it all together I get the aforementioned results.


Any help would be appreciated.




Thank you.