Join + Order By!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
snarkiest
Forum Commoner
Posts: 30
Joined: Mon May 04, 2009 10:06 am
Location: Latvia
Contact:

Join + Order By!

Post by snarkiest »

Code: Select all

$result = mysql_query("SELECT * FROM `characters` ORDER BY `character_id` ASC",$connect);
              while($myrow = mysql_fetch_assoc($result)) {  
                      
                echo "<table width='500' class='toutborder' cellspacing='2' cellpadding='2'><tr width='100%' class='titlegreen'>";
                
                echo "<div align='center'><td class='titlered' align='center' width='25%'><b>Character:</b> <a href='/vpn/lookup.php?account_id=" . $myrow['account_id'] . "' class='link'>";
                echo $myrow['character_name'];
                echo "</a></td></div>";
                
                echo "<div align='center'><td class='titlered' align='center' width='25%'><b>Last login:</b> ";
                
                $result_acc = mysql_query("SELECT * FROM `accounts` WHERE `account_id`='" . $myrow['account_id'] . "' ",$connect);
                $myrow_acc = mysql_fetch_assoc($result_acc);
                if($myrow_acc['lastlogin']=='0000-00-00 00:00:00') {
                    echo "No data.";
                }
                else {
                echo $myrow_acc['lastlogin'];
                }
                echo "</td></div>";
                
                $get_p = mysql_query("SELECT SUM(ach_points) AS ach_points FROM `vpn_have` WHERE `character_id`='" . $myrow['character_id'] . "'",$connect);
                $total = mysql_fetch_assoc($get_p);
                $ach_points = $total['ach_points'];
                
                echo "<div align='center'><td class='titlered' align='center' width='25%'><b>Points: </b>";
                if($ach_points==0) {
                    echo "No data.";
                }
                else {
                    echo $ach_points;
                }
                echo "</td></div>";
                
                echo "<div align='center'><td class='titlered' align='center' width='25%'><b>Achievements:</b> ";
                
                $get_count = mysql_query("SELECT `achievement_id` FROM `vpn_have` WHERE `character_id`='" . $myrow['character_id'] . "' ",$connect);
                $ach_count = mysql_num_rows($get_count);
                echo $ach_count;
                echo "</td></div>";
                
                echo "</tr></table>";
              }
Result: here.
Question: How to order results by points? At the begin I tough I could use join function and join result $result with $get_p, but I failed joining them even looking from manual. Or maybe there is some other way how to order results by points?
Last edited by snarkiest on Fri Jun 19, 2009 1:42 pm, edited 1 time in total.
snarkiest
Forum Commoner
Posts: 30
Joined: Mon May 04, 2009 10:06 am
Location: Latvia
Contact:

Re: Join + Order By!

Post by snarkiest »

Anyone to help with my problem?
:P
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Join + Order By!

Post by jayshields »

Code: Select all

SELECT 
  *, 
  SUM(`ach_points`) AS `total_points` 
FROM 
  `characters` 
INNER JOIN 
  `vpn_have` 
ON 
  `characters`.`character_id` = `vpn_have`.`character_id` 
GROUP BY 
  `characters`.`character_id` 
ORDER BY 
  `total_points`
Something like that might work, try it.
snarkiest
Forum Commoner
Posts: 30
Joined: Mon May 04, 2009 10:06 am
Location: Latvia
Contact:

Re: Join + Order By!

Post by snarkiest »

Oh my God! You saved my day!
Thank you soooo much!
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Join + Order By!

Post by jayshields »

No problem - but please don't bump posts after only 40 minutes! :)
snarkiest
Forum Commoner
Posts: 30
Joined: Mon May 04, 2009 10:06 am
Location: Latvia
Contact:

Re: Join + Order By!

Post by snarkiest »

Oh sorry for that. Won't happen again.

However, I need a bit more help.

Code: Select all

SELECT
  *,
  SUM(`ach_points`) AS `total_points`
FROM
  `characters`
INNER JOIN
`vpn_have` ON `characters`.`character_id` = `vpn_have`.`character_id`
 
INNER JOIN
`accounts` ON `characters`.`account_id` = `accounts`.`account_id`
 
GROUP BY
  `characters`.`character_id`
ORDER BY
  `total_points`
  DESC
How to properly add: COUNT(`achievement_id`) AS achievement_total ? (I want to count how much achievements user has from table vpn_have)
Post Reply