admirolas3 Posted August 5, 2018 Share Posted August 5, 2018 Hi, maybe someone has this statistics script? Players online time? Does the time in datase at onlinetime cell is writen in miliseconds? Quote Link to comment Share on other sites More sharing options...
0 Solomun Posted August 6, 2018 Share Posted August 6, 2018 Online time in database is saved in seconds. Quote Link to comment Share on other sites More sharing options...
0 admirolas3 Posted August 7, 2018 Author Share Posted August 7, 2018 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> "; } Quote Link to comment Share on other sites More sharing options...
0 wongerlt Posted August 7, 2018 Share Posted August 7, 2018 item_id = 57 AND count > 10 AND access_level = 0 Quote Link to comment Share on other sites More sharing options...
0 admirolas3 Posted August 7, 2018 Author Share Posted August 7, 2018 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 Quote Link to comment Share on other sites More sharing options...
0 wongerlt Posted August 7, 2018 Share Posted August 7, 2018 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 Quote Link to comment Share on other sites More sharing options...
0 Elfo Posted August 7, 2018 Share Posted August 7, 2018 Your query is SQL injectable. Parameterise the parameters properly or else you expose your whole db. Quote Link to comment Share on other sites More sharing options...
0 admirolas3 Posted August 7, 2018 Author Share Posted August 7, 2018 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.. Quote Link to comment Share on other sites More sharing options...
0 admirolas3 Posted August 7, 2018 Author Share Posted August 7, 2018 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? Quote Link to comment Share on other sites More sharing options...
0 xxdem Posted August 7, 2018 Share Posted August 7, 2018 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 Quote Link to comment Share on other sites More sharing options...
0 Elfo Posted August 7, 2018 Share Posted August 7, 2018 (edited) 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 Quote Link to comment Share on other sites More sharing options...
0 admirolas3 Posted August 7, 2018 Author Share Posted August 7, 2018 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? Quote Link to comment Share on other sites More sharing options...
0 Elfo Posted August 7, 2018 Share Posted August 7, 2018 (edited) 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 Quote Link to comment Share on other sites More sharing options...
0 admirolas3 Posted August 7, 2018 Author Share Posted August 7, 2018 (edited) 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 Quote Link to comment Share on other sites More sharing options...
0 admirolas3 Posted August 7, 2018 Author Share Posted August 7, 2018 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> "; } Quote Link to comment Share on other sites More sharing options...
0 Elfo Posted August 7, 2018 Share Posted August 7, 2018 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. Quote Link to comment Share on other sites More sharing options...
Question
admirolas3
Hi, maybe someone has this statistics script? Players online time? Does the time in datase at onlinetime cell is writen in miliseconds?
Link to comment
Share on other sites
20 answers to this question
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.