Sevenfoldia 29 Posted July 29, 2022 Share Posted July 29, 2022 (edited) Hello everyone. I am using [Replica] Old Metin2 Website. I edited pretty much things, but I need help with query for guild at player ranking page and guild leader at guild ranking page. I tried so many variants like $guild = "SELECT example FROM example WHERE example = " . $array["example"] . ""; etc. But i didnt find the right solution Here my rankings.php <div class="content"> <div class="content-bg"> <div class="content-bg-bottom"> <h2>100 nejlepších hráčů - podle dosažené úrovně a zkušeností </h2> <div id="ranking"> <br /> <table border="1"> <tr> <th width="10">Umístění</th> <th width="100">Jméno</th> <th width="70">Level</th> <th width="70">Zkušeností</th> <th width="80">Cech</th> <th width="70">Rasa</th> <th width="40">Říše</th> </tr> <?php mysqli_select_db($sqlServ, 'player'); $rank = "SELECT * from player WHERE name NOT LIKE '[%]%' order by level desc, exp desc, playtime desc limit 100"; $query = mysqli_query($sqlServ, $rank); $i = 0; while($array = mysqli_fetch_array($query)) { $i = $i + 1; echo "<tr> <th width=\"10\"><font color=\"black\">" . $i . "</font></th> <th width=\"100\"><font color=\"black\">" . $array["name"] . "</font></th> <th width=\"70\"><font color=\"black\">" . $array["level"] . "</font></th> <th width=\"70\"><font color=\"black\">" . $array["exp"] . "</font></th> <th width=\"80\"><font color=\"black\">" . $array["guild"] . "</font></th>"; //placeholder for right guild query $job = "SELECT job from player where job = " . $array["job"] . ""; $query2 = mysqli_query($sqlServ, $job); $array2 = mysqli_fetch_array($query2); if($array2["job"] == 0) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/warrior_m.png\"></th>"; } elseif($array2["job"] == 1) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/assassin_w.png\"></th>"; } elseif($array2["job"] == 2) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/sura_m.png\"></th>"; } elseif($array2["job"] == 3) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/shaman_m.png\"></th>"; } elseif($array2["job"] == 4) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/warrior_w.png\"></th>"; } elseif($array2["job"] == 5) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/assassin_m.png\"></th>"; } elseif($array2["job"] == 6) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/sura_w.png\"></th>"; } else { echo "<th width=\"70\" td align=\"center\"><img src=\"img/shaman_w.png\"></th>"; } $reich = "SELECT empire from player_index where id = " . $array["account_id"] . ""; $query3 = mysqli_query($sqlServ, $reich); $array3 = mysqli_fetch_array($query3); if($array3["empire"] == 1) { echo "<th width=\"40\" td align=\"center\"><img src=\"img/1_s.png\"></th></tr>"; } elseif($array3["empire"] == 2) { echo "<th width=\"40\" td align=\"center\"><img src=\"img/2_s.png\"></th></tr>"; } else { echo "<th width=\"40\" td align=\"center\"><img src=\"img/3_s.png\"></th></tr>"; } } echo "</table>"; ?> <br /> </div> <center><strong><a class="btn" href="?s=guildrank">Žebříček cechů</a></strong><br /></center> <br class="clearfloat" /> </div> </div> </div> <div class="shadow"> </div> and here is my guild rank.php <div class="content"> <div class="content-bg"> <div class="content-bg-bottom"> <h2>100 nejlepších cechů - podle dosažených bodů ve válkách</h2> <div id="ranking"> <br /> <table border="1"> <tr> <th width="10">Umístění</th> <th width="90">Název</th> <th width="50">Level</th> <th width="50">Vítězství</th> <th width="50">Remízy</th> <th width="40">Prohry</th> <th width="50">Body</th> <th width="70">Vůdce</th> <th width="40">Říše </th> </tr> <?php mysqli_select_db($sqlServ, 'player'); $rank = "SELECT * from guild order by ladder_point desc limit 100"; $query = mysqli_query($sqlServ, $rank); $i = 0; while($array = mysqli_fetch_array($query)) { $i = $i + 1; echo "<tr> <th width=\"10\"><font color=\"black\">" . $i . "</font></th> <th width=\"90\"><font color=\"black\">" . $array["name"] . "</font></th> <th width=\"50\"><font color=\"black\">" . $array["level"] . "</font></th> <th width=\"50\"><font color=\"black\">" . $array["win"] . "</font></th> <th width=\"50\"><font color=\"black\">" . $array["draw"] . "</font></th> <th width=\"40\"><font color=\"black\">" . $array["loss"] . "</font></th> <th width=\"50\"><font color=\"black\">" . $array["ladder_point"] . "</font></th> <th width=\"50\"><font color=\"black\">" . $array["guild_leader"] . "</font></th>"; //placeholder for right guild leader query $reich = "SELECT empire from player_index where id = " . $array2["account_id"] . ""; $query2 = mysqli_query($sqlServ, $reich); if($array2["empire"] == 1) { echo "<th width=\"40\" td align=\"center\"><img src=\"img/1_s.png\"></th></tr>"; } elseif($array2["empire"] == 2) { echo "<th width=\"40\" td align=\"center\"><img src=\"img/2_s.png\"></th></tr>"; } else { echo "<th width=\"40\" td align=\"center\"><img src=\"img/3_s.png\"></th></tr>"; } } echo "</table>"; ?> <br /> </div> <center><strong><a class="btn" href="?s=rankings">Žebříček hráčů</a></strong><br /></center> <br class="clearfloat" /> </div> </div> </div> <div class="shadow"> </div> There is screenshot of my ranking page There is screeshot of my guild rank page It is in Czech language so there are some translations if you need: cech = guild, vůdce = leader Can someone please help me to setup right query? Thank you for all advices. Edited February 9, 2023 by Metin2 Dev Core X - External 2 Internal Link to comment Share on other sites More sharing options...
Management Karbust 4910 Posted July 29, 2022 Management Share Posted July 29, 2022 Use this queries instead For player ranking: SELECT p.id, p.`name`, p.job, p.playtime, p.`level`, p.exp, g.`name` AS g_name FROM player AS p INNER JOIN guild_member AS gm ON gm.pid = p.id INNER JOIN guild AS g ON g.id = gm.guild_id WHERE p.`name` NOT LIKE '[%]%' ORDER BY p.`level` DESC, p.exp DESC, playtime DESC LIMIT 100 For guild ranking: SELECT g.`name`, g.`level`, g.exp, g.win, g.draw, g.loss, g.ladder_point, p.`name`, p.id FROM player AS p INNER JOIN guild_member AS gm ON p.id = gm.pid INNER JOIN guild AS g ON g.id = gm.guild_id WHERE p.`name` NOT LIKE '[%]%' ORDER BY ladder_point DESC LIMIT 100 1 Link to comment Share on other sites More sharing options...
Sevenfoldia 29 Posted July 30, 2022 Author Share Posted July 30, 2022 (edited) On 7/29/2022 at 3:27 PM, Karbust said: Use this queries instead For player ranking: SELECT p.id, p.`name`, p.job, p.playtime, p.`level`, p.exp, g.`name` AS g_name FROM player AS p INNER JOIN guild_member AS gm ON gm.pid = p.id INNER JOIN guild AS g ON g.id = gm.guild_id WHERE p.`name` NOT LIKE '[%]%' ORDER BY p.`level` DESC, p.exp DESC, playtime DESC LIMIT 100 For guild ranking: SELECT g.`name`, g.`level`, g.exp, g.win, g.draw, g.loss, g.ladder_point, p.`name`, p.id FROM player AS p INNER JOIN guild_member AS gm ON p.id = gm.pid INNER JOIN guild AS g ON g.id = gm.guild_id WHERE p.`name` NOT LIKE '[%]%' ORDER BY ladder_point DESC LIMIT 100 Thank you for this. I edit some lines and It works pretty well. But there is a problem at player rankings. It shows only player who are in guild. Players without guilds are not in ladder. When i delete the "guild" part from query it shows all players My rankings.php: <div class="content"> <div class="content-bg"> <div class="content-bg-bottom"> <h2>100 nejlepších hráčů - podle dosažené úrovně a zkušeností </h2> <div id="ranking"> <br /> <table border="1"> <tr> <th width="10">Umístění</th> <th width="100">Jméno</th> <th width="70">Level</th> <th width="70">Zkušeností</th> <th width="80">Cech</th> <th width="70">Rasa</th> <th width="40">Říše</th> </tr> <?php mysqli_select_db($sqlServ, 'player'); $rank = "SELECT p.id, p.account_id, p.`name`, p.job, p.playtime, p.`level`, p.exp, g.`name` AS g_name, i.empire AS empire FROM player AS p INNER JOIN guild_member AS gm ON gm.pid = p.id INNER JOIN guild AS g ON g.id = gm.guild_id INNER JOIN player_index AS i ON i.id = p.account_id WHERE p.`name` NOT LIKE '[%]%' ORDER BY p.`level` DESC, p.exp DESC, playtime DESC LIMIT 100"; $query = mysqli_query($sqlServ, $rank); $i = 0; while($array = mysqli_fetch_array($query)) { $i = $i + 1; echo "<tr> <th width=\"10\"><font color=\"black\">" . $i . "</font></th> <th width=\"100\"><font color=\"black\">" . $array["name"] . "</font></th> <th width=\"70\"><font color=\"black\">" . $array["level"] . "</font></th> <th width=\"70\"><font color=\"black\">" . $array["exp"] . "</font></th> <th width=\"80\"><font color=\"black\">" . $array["g_name"] . "</font></th>"; if($array["job"] == 0) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/warrior_m.png\"></th>"; } elseif($array["job"] == 1) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/assassin_w.png\"></th>"; } elseif($array["job"] == 2) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/sura_m.png\"></th>"; } elseif($array["job"] == 3) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/shaman_w.png\"></th>"; } elseif($array["job"] == 4) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/warrior_w.png\"></th>"; } elseif($array["job"] == 5) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/assassin_m.png\"></th>"; } elseif($array["job"] == 6) { echo "<th width=\"70\" td align=\"center\"><img src=\"img/sura_w.png\"></th>"; } else { echo "<th width=\"70\" td align=\"center\"><img src=\"img/shaman_m.png\"></th>"; } if($array["empire"] == 1) { echo "<th width=\"40\" td align=\"center\"><img src=\"img/1_s.png\"></th></tr>"; } elseif($array["empire"] == 2) { echo "<th width=\"40\" td align=\"center\"><img src=\"img/2_s.png\"></th></tr>"; } else { echo "<th width=\"40\" td align=\"center\"><img src=\"img/3_s.png\"></th></tr>"; } } echo "</table>"; ?> <br /> </div> <center><strong><a class="btn" href="?s=guildrank">Žebříček cechů</a></strong><br /></center> <br class="clearfloat" /> </div> </div> </div> <div class="shadow"> </div> Can you help me with this? Here is screenshot of my website Edited February 9, 2023 by Metin2 Dev Core X - External 2 Internal Link to comment Share on other sites More sharing options...
Management Karbust 4910 Posted July 30, 2022 Management Share Posted July 30, 2022 Sorry, my mistake, here's the correct query. Added a check to not select players in which the accounts that are don't have status OK. SELECT p.id, p.`name`, p.job, p.playtime, p.`level`, p.exp, g.`name` AS g_name, pi.empire FROM player.player AS p LEFT JOIN player.guild_member AS gm ON gm.pid = p.id LEFT JOIN player.guild AS g ON g.id = gm.guild_id INNER JOIN account.account AS a ON p.account_id = a.id INNER JOIN player.player_index AS pi ON p.account_id = pi.id WHERE p.`name` NOT LIKE '[%]%' AND a.`status` = 'OK' ORDER BY p.`level` DESC, p.exp DESC, playtime DESC LIMIT 100 Just now, Karbust said: Sorry, my mistake, here's the correct query. Added a check to not select players in which the accounts don't have status OK. SELECT p.id, p.`name`, p.job, p.playtime, p.`level`, p.exp, g.`name` AS g_name, pi.empire FROM player.player AS p LEFT JOIN player.guild_member AS gm ON gm.pid = p.id LEFT JOIN player.guild AS g ON g.id = gm.guild_id INNER JOIN account.account AS a ON p.account_id = a.id INNER JOIN player.player_index AS pi ON p.account_id = pi.id WHERE p.`name` NOT LIKE '[%]%' AND a.`status` = 'OK' ORDER BY p.`level` DESC, p.exp DESC, playtime DESC LIMIT 100 1 Link to comment Share on other sites More sharing options...
Sevenfoldia 29 Posted July 30, 2022 Author Share Posted July 30, 2022 Thak you! It works. Link to comment Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now