Sum problem

[Borderline]

Good evening I'm afraid I'm looking for yet more advice. I run a tipping competition, with data as follows: I'm trying to get the data to calculate the profit for each player over the course of the competition, which can last for up to five days. I have some code, which is producing a profit/loss, but not the correct one! I was hoping someone could advise where I'm going wrong.

    <?php       $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>";	   ?>

The current rseult is displayed here: http://www.further-flight.co.uk/site/competitions/tipster/aintr/sp/2010c.php If you click the individual links, the correct profit should be displayed at the top of the page. Any info gratefully received.

[justsomeguy]

Is the "player" the stable?

[Borderline]

I'm sorry - yes it is.

[justsomeguy]

Are you saying that the result in the table on the first page should be the same as the number on the top of the individual page? What query are you using on the individual pages?

[Borderline]

I've looked again at my code, and made an adjust, so that it now reads:

    <?php       $query = " SELECT SUM(comptipsterselections.profit) as Profit, comptipsterselections.stable, comptipsterboard.link	   FROM comptipsterselections INNER JOIN comptipsterboard	   ON comptipsterselections.stable=comptipsterboard.stable	   WHERE comptipsterselections.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>";	   ?>

The profit for each player is now showing correctly for each player. However, the printed results now show links for each competition the player has taken part in, not just the Aintree 2010 competition: http://www.further-flight.co.uk/site/competitions/tipster/aintr/sp/2010c.php I thought the WHERE clause would filter out anything that didn't have Aintree 2010 in the comp column, but this isn't the case. Is there any advice for getting this added to the code? Many thanks.

[justsomeguy]

What are the structures of the two tables you're joining?

[Borderline]

Please find below: Comptipsterselections Comptipsterboard:

[justsomeguy]

You're only joining on the stable columns, you should also probably join on the comp columns since they show up in both tables.