| Table join including link creation & sum
|
Petrushka
msg:4444824 | 9:26 pm on Apr 24, 2012 (gmt 0) |
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
msg:4447518 | 3:19 pm on Apr 30, 2012 (gmt 0) |
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
msg:4447672 | 8:31 pm on Apr 30, 2012 (gmt 0) |
Brilliant, many thanks, links work perfectly. Sums still displaying incorrectly, so any advane much appreciated.
Latest results: [further-flight.co.uk...]
|
|
|
