homepage Welcome to WebmasterWorld Guest from 54.145.183.169
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Table join including link creation & sum
Petrushka



 
Msg#: 4444822 posted 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#: 4444822 posted 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#: 4444822 posted 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...]

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved