PHP - query for guild and guild leader

Sevenfoldia · July 29, 2022

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">&nbsp;</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">&nbsp;</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

Karbust · 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

Sevenfoldia · July 30, 2022

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">&nbsp;</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

Karbust · 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

Sevenfoldia · July 30, 2022

Thak you! It works.

Sign In

PHP - query for guild and guild leader

Recommended Posts

Sevenfoldia 28

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

Karbust

Karbust

Karbust 4816

Link to comment

Share on other sites

Sevenfoldia 28

Link to comment

Share on other sites

Karbust 4816

Link to comment

Share on other sites

Sevenfoldia 28

Link to comment

Share on other sites

Please sign in to comment

Announcements

Similar Content

Activity

Recently Browsing

Important Information