TOP RITCHEST STATS PROBLEM

[admirolas3]

Hi, maybe someone has this statistics script? Players online time? Does the time in datase at onlinetime cell is writen in miliseconds?

[Solomun]

Online time in database is saved in seconds.

[admirolas3]

One more problem with statistics. Wanna make top ritchest players. But Admin account is added too. How could i change it so that admin wont be shown?

$statement = "SELECT owner_id, count FROM items WHERE item_id = 57 AND count > 10 ORDER by count DESC LIMIT 10 ";
				$statement = $pdo->prepare($statement);
				$statement->execute();
				while($row = $statement->fetch()) {
						$statement2 = "SELECT char_name, accesslevel FROM characters WHERE obj_id = '".$row['owner_id']."' AND accesslevel <= 0";
						$statement2 = $pdo->prepare($statement2);
						$statement2->execute();
						$count++;
						$number = floor($row['count'] / 1000000);
						while($row = $statement2->fetch()) {
								
								$name =	$row['char_name'];
									
						}
							echo
							"
								<tr>
									<td class='number'>".$count."</td>
									<td class='name'>".$name."</td>
									<td class='value'>".$number."kk</td>
								</tr>
							";
						
					}

 

[wongerlt]

item_id = 57 AND count > 10 AND access_level = 0

[admirolas3]

Problem is that acceslevel is in different table. If it would be in same than ok. Those two tables has only one same cell, that is owner_id

[wongerlt]

1 minute ago, admirolas3 said:

Problem is that acceslevel is in different table. If it would be in same than ok. Those two tables has only one same cell, that is owner_id

oh rly, then u can connect two tables or just check by owener id. example:

item_id = 57 AND count > 10 AND owner_id=1484984

[Elfo]

Your query is SQL injectable. Parameterise the parameters properly or else you expose your whole db.

[admirolas3]

3 minutes ago, wongerlt said:

oh rly, then u can connect two tables or just check by owener id. example:

item_id = 57 AND count > 10 AND owner_id=1484984

I dont imagine how to do it. At first statement it gets 10 top numbers with an  player ids. Than with that id it checks for a player name and accesslevel in other statement. So if it founds that admin account was added, the top 10 becomes only 9. Because at first statemnet it takes 10 lines with an admin account..

[admirolas3]

2 minutes ago, .Elfocrash said:

Your query is SQL injectable. Parameterise the parameters properly or else you expose your whole db.

Could you give an example?

[xxdem]

9 minutes ago, admirolas3 said:

Problem is that acceslevel is in different table. If it would be in same than ok. Those two tables has only one same cell, that is owner_id

 

google about sql left join

[Elfo]

SELECT i.owner_id, i.count from items as i
inner join characters c on c.obj_Id = i.owner_id 
WHERE i.item_id = 57 and i.count > 10 and c.accesslevel = 0
ORDER BY count DESC limit 10

 

Edited August 7, 2018 by .Elfocrash

[admirolas3]

3 minutes ago, .Elfocrash said:

LEFT JOIN! HAHAHHAHAHAHAHAHAAA

 

Here is the query you need.

SELECT owner_id, count from items as i
inner join characters c on c.obj_Id = i.owner_id 
WHERE i.item_id = 57 and i.count > 10 and c.accesslevel = 0
ORDER BY count DESC limit 10

However read about prepared statements here: https://www.w3schools.com/php/php_mysql_prepared_statements.asp

 Could you add "char_name" in this query?

[Elfo]

2 minutes ago, admirolas3 said:

 Could you add "char_name" in this query?

 

Ofc you can, because you joined to the characters table so you can do c.* or c.char_name to get any column or a specific column from the table.

SELECT i.owner_id, c.char_name, i.count from items as i
inner join characters c on c.obj_Id = i.owner_id 
WHERE i.item_id = 57 and i.count > 10 and c.accesslevel = 0
ORDER BY count DESC limit 10

Edited August 7, 2018 by .Elfocrash

[admirolas3]

9 minutes ago, .Elfocrash said:

 

Ofc you can, because you joined to the characters table so you can do c.* or c.char_name to get any column or a specific column from the table.

SELECT i.owner_id, c.char_name, i.count from items as i
inner join characters c on c.obj_Id = i.owner_id 
WHERE i.item_id = 57 and i.count > 10 and c.accesslevel = 0
ORDER BY count DESC limit 10

 

Edited August 7, 2018 by admirolas3
emty

[admirolas3]

try {
				$pdo = new PDO("mysql:host=127.0.0.1; dbname=l2j", 'root', '');
				$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
				$pdo->query("SET NAMES 'utf8'");
			}
			catch(PDOException  $e) {
				echo "Connection failed: " . $e->getMessage();
		  }

$statement = "SELECT i.owner_id, c.char_name, i.count from items as i inner join characters c on c.obj_Id = i.owner_id WHERE i.item_id = 57 and i.count > 10 and c.accesslevel = 0 ORDER BY count DESC limit 10";
				$statement = $pdo->prepare($statement);
				$statement->execute();
				
				echo "<table style='width: 100%' cellpadding='2'>
						<tr>
							<td colspan='3' style='height: 24px' valign='top'><a href='e107_plugins/l2top/l2top.php?show=4'>Top ritchest players</a></td>
						</tr>
					";
				
				while($row = $statement->fetch()) {
						$count++;
						$number = floor($row['count'] / 1000000);
							echo
							"
								<tr>
									<td class='number'>".$count."</td>
									<td class='name'>".$row['char_name']."</td>
									<td class='value'>".$number."kk</td>
								</tr>
							";
						
					}

 

[Elfo]

1 minute ago, admirolas3 said:

The whole code looks like that. Works like a charm. Thanks. What you think, everything looks ok?

Yeap, that's all you need.