Table join including link creation & sum

[Petrushka]

Good evening

I'm attempting to produce a single table of bets placed by players on my website. I currently have the information across two tables, as per the screenshots below:

comptipsterboard:
[further-flight.co.uk ]

comptipsterselections:
[further-flight.co.uk ]

Ideally, I'd like to pull the player name from comptipsterselections, and sum the selection profits for each player using group by. However, I'd like the final table to provide a link to each players selections, for which I will need the info from comptipsterboard.

My initial attempt can be found here: [further-flight.co.uk ] - it appears to be pulling the correct info from the comptipsterboard table, but the links aren't functioning and the profit isn't showing up correctly.

For info, here is the top scoring player's selections: [further-flight.co.uk...]

  $query = "SELECT SUM(comptipsterselections.profit), comptipsterselections.stable
        FROM comptipsterselections INNER JOIN comptipsterboard 
        ON comptipsterselections.stable=comptipsterboard.stable
        WHERE comptipsterboard.comp = 'aintree 2010' 
        GROUP BY comptipsterselections.stable ORDER BY SUM(comptipsterselections.profit) DESC";
 
 
     $result = mysql_query($query) or die(mysql_error());
 
    // Set-up table
 
     echo "<table class='correctenglish' border='1' cellpadding='4' cellspacing='0' width='75%'>";
     echo "<tr class='toprow'>  <th>Stable</th> <th>Daily Profit</th></tr>";
 
 
    // Print out result
     while($row = mysql_fetch_array($result)){
 
     $link='/site/competitions/tipster'.$row['link'];
 
     echo  "<tr><td>"; 
     echo  "<a href='$link'>";
     echo  $row['stable'];
     echo  "</td><td>"; 
     echo  " £". $row['SUM(comptipsterselections.profit)'];
     echo  "</td></tr>"; 
     }
     echo  "</table>";
    ?>

Any advice on getting the desired effect would be appreciated!

[arms]

You are not returning the link code from your query and maybe (I'm not sure offhand) you need to have an alias for the profit value

Try:

$query = "SELECT SUM(comptipsterselections.profit) as Profit, comptipsterselections.stable, comptipsterboard.link
FROM comptipsterselections INNER JOIN comptipsterboard
ON comptipsterselections.stable=comptipsterboard.stable
WHERE comptipsterboard.comp = 'aintree 2010'
GROUP BY comptipsterselections.stable, comptipsterboard.link
ORDER BY SUM(comptipsterselections.profit) DESC";


$result = mysql_query($query) or die(mysql_error());

// Set-up table

echo "<table class='correctenglish' border='1' cellpadding='4' cellspacing='0' width='75%'>";
echo "<tr class='toprow'> <th>Stable</th> <th>Daily Profit</th></tr>";


// Print out result
while($row = mysql_fetch_array($result)){

$link='/site/competitions/tipster'.$row[link];

echo "<tr><td>";
echo "<a href='$link'>";
echo $row[stable];
echo "</td><td>";
echo " £". $row[Profit];
echo "</td></tr>";
}
echo "</table>";
?>

[Petrushka]

Brilliant, many thanks, links work perfectly. Sums still displaying incorrectly, so any advane much appreciated.

Latest results: [further-flight.co.uk...]