• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!
  • 2026 staff recruitment is open! Check it out and consider applying!

PHP znote acc powergamers

shakal1994

shakal1994

Member
Joined
Nov 20, 2020
Messages
79
Reaction score
21
hello my heroes
I'm trying to adjust my powergamers in Znote AAC, but I'm facing a difficulty.

My server is based on Celohe's TFS 1.2, downgrade to 8.0

I'm not sure if it's the globalevents, or the html page.

The problem is the following, when I run the query in the DB

UPDATE znote_players AS z INNER JOIN players AS p ON p.id = z.player_id SET z.exphist_lastexp = p.experience;
Click to expand...
At first it works.

But if a player enters level 8 and dies, it gives a bug.

I already know that the problem is that negative exp, but I really wanted to solve this



Here is the error that appears when someone has negative exp

Powergamers​

string(435) "SELECT a.id, b.player_id, a.name, a.vocation, a.level, a.group_id, a.experience, b.exphist_lastexp, b.exphist1, b.exphist2, b.exphist3, b.exphist4, b.exphist5, b.exphist6, b.exphist7, (a.experience - b.exphist_lastexp) AS expdiff FROM players a JOIN znote_players b ON a.id = b.player_id WHERE a.group_id < 2 ORDER BY expdiff DESC LIMIT 20"
(query - SQL error)
Type: select_multi (select multiple rows from database)

BIGINT UNSIGNED value is out of range in '(old.a.experience - old.b.exphist_lastexp)'
Click to expand...


Globalevents
local function getEternalStorage(key, parser)
local value = result.getDataString(db.storeQuery("SELECT value FROM znote_global_storage WHERE key = ".. key .. ";"), "value")
if not value then
if parser then
return false
else
return -1
end
end
return tonumber(value) or value
end

local function setEternalStorage(key, value)
if getEternalStorage(key, true) then
db.query("UPDATE znote_global_storage SET value = '".. value .. "' WHERE key = ".. key .. ";")
else
db.query("INSERT INTO znote_global_storage (key, value) VALUES (".. key ..", ".. value ..");")
end
return true
end

function onThink(interval, lastExecution, thinkInterval)
if tonumber(os.date("%d")) ~= getEternalStorage(23856) then
setEternalStorage(23856, (tonumber(os.date("%d"))))
db.query("UPDATE znote_players SET onlinetime7=onlinetime6, onlinetime6=onlinetime5, onlinetime5=onlinetime4, onlinetime4=onlinetime3, onlinetime3=onlinetime2, onlinetime2=onlinetime1, onlinetime1=onlinetimetoday, onlinetimetoday=0;")
db.query("UPDATE players SET exphist7 = players.exphist6, exphist6 = players.exphist5, exphist5 = players.exphist4, exphist4 = players.exphist3, exphist3 = players.exphist2, exphist2 = players.exphist1, exphist1 = GREATEST(players.experience - players.exphist_lastexp, 0), exphist_lastexp = players.experience;")
end
db.query("UPDATE znote_players SET onlinetimetoday = onlinetimetoday + 60, onlinetimeall = onlinetimeall + 60 WHERE player_id IN (SELECT player_id FROM players_online WHERE players_online.player_id = znote_players.player_id)")
return true
end
Click to expand...


html
<?php
require_once 'engine/init.php';
include 'layout/overall/header.php';
if (!$config['powergamers']['enabled']) {
echo 'This page has been disabled at config.php.';
include 'layout/overall/footer.php';
exit();
}
?>
<div class="panel">
<div class="page-header"><h3>Powergamers</h3></div>
<?php
$limit = $config['powergamers']['limit'];
$days = isset($_POST['days']);
$today = true;
if ($days) {
$selected = ($_POST['days']);
$days = (int) $selected[1];
$vocation = (int) $selected[0];
if ($days > 0)
$today = false;
} else {
$znotePlayers = mysql_select_multi('SELECT a.id, b.player_id, a.name, a.vocation, a.level, a.group_id, a.experience, b.exphist_lastexp, b.exphist1, b.exphist2, b.exphist3, b.exphist4, b.exphist5, b.exphist6, b.exphist7, (a.experience - b.exphist_lastexp) AS expdiff FROM players a JOIN znote_players b ON a.id = b.player_id WHERE a.group_id < 2 ORDER BY expdiff DESC LIMIT '.$limit);
}
$limit = $config['powergamers']['limit'];

if(!empty($days) && !empty($vocation))
$znotePlayers = mysql_select_multi('SELECT a.id, b.player_id, a.name, a.vocation, a.level, a.group_id, a.experience, b.exphist_lastexp, b.exphist1, b.exphist2, b.exphist3, b.exphist4, b.exphist5, b.exphist6, b.exphist7, (a.experience - b.exphist_lastexp) AS expdiff FROM players a JOIN znote_players b ON a.id = b.player_id WHERE a.group_id < 2 AND a.vocation='. (int)$vocation .' OR a.vocation='. ((int)$vocation +4) .' ORDER BY exphist' . (int)$days . ' DESC LIMIT '.$limit);
elseif(empty($days) && !empty($vocation)) {
$znotePlayers = mysql_select_multi('SELECT a.id, b.player_id, a.name, a.vocation, a.level, a.group_id, a.experience, b.exphist_lastexp, b.exphist1, b.exphist2, b.exphist3, b.exphist4, b.exphist5, b.exphist6, b.exphist7, (a.experience - b.exphist_lastexp) AS expdiff FROM players a JOIN znote_players b ON a.id = b.player_id WHERE a.group_id < 2 AND a.vocation='. (int)$vocation .' OR a.vocation='. ((int)$vocation +4) .' ORDER BY expdiff DESC LIMIT '.$limit);
}elseif(!empty($days) && empty($vocation))
$znotePlayers = mysql_select_multi('SELECT a.id, b.player_id, a.name, a.vocation, a.level, a.group_id, a.experience, b.exphist_lastexp, b.exphist1, b.exphist2, b.exphist3, b.exphist4, b.exphist5, b.exphist6, b.exphist7, (a.experience - b.exphist_lastexp) AS expdiff FROM players a JOIN znote_players b ON a.id = b.player_id WHERE a.group_id < 2 ORDER BY exphist' . (int)$days . ' DESC LIMIT '.$limit);
else
$znotePlayers = mysql_select_multi('SELECT a.id, b.player_id, a.name, a.vocation, a.level, a.group_id, a.experience, b.exphist_lastexp, b.exphist1, b.exphist2, b.exphist3, b.exphist4, b.exphist5, b.exphist6, b.exphist7, (a.experience - b.exphist_lastexp) AS expdiff FROM players a JOIN znote_players b ON a.id = b.player_id WHERE a.group_id < 2 ORDER BY expdiff DESC LIMIT '.$limit);

$showVoc = (!empty($vocation)) ? $vocation : 0;
?>
<form class="form form-inline" action="" method="post">
<div class="col sm-4">
<center>
<select class="form-control" name="days[]">
<option value="" selected="all">All</option>
<option value="1">Sorcerers</option>
<option value="2">Druids</option>
<option value="3">Paladins</option>
<option value="4">Knights</option>
<option value="none">No vocation</option>
</select>
<select class="form-control" name="days[]">
<option value="" selected="Today">Today</option>
<option value="1">Yesterday</option>
<option value="2">2 days ago</option>
<option value="3">3 days ago</option>
</select>
<input type="submit" class="btn btn-primary"><br>
<?php echo ($showVoc > 0) ? 'Showing only <b>'. strtolower(vocation_id_to_name($vocation)).'s</b> and' : 'Showing <b>all</b> vocations and'; ?>
<?php echo ($days > 0) ? 'sorted by <b>'. $days .'</b> days': 'sorted by <b>today</b>'; ?>.
</center>
</div>
</form>
<table class="table table-striped">
<td width="5%"><center>#</center></td>
<td>Name</td>
<?php
for($i = 3; $i >= 2; $i--)
echo ($days == $i) ? '<td class="pull-right" width="70%"><b>'.$i.' Days Ago</b></td>' : '';
echo ($days == 1) ? '<td class="pull-right" width="70%"><b>Yesterday</b></td>' : '';
echo ($today) ? '<td class="pull-right" width="70%"><b>Today</b></td>' : '';
echo ($days == 4) ? '<td class="pull-right" width="70%"><b>Total</b></td>' : '';
echo '</tr>';

$number_of_rows = 0;
if($znotePlayers) {
foreach($znotePlayers as $player)
{
$number_of_rows++;
echo '<td><center>'. $number_of_rows . '.</center></td>';
echo '<td><a href="characterprofile.php?name=' .$player['name']. '">' .$player['name']. '</a>';
echo '<br> '. ($player['level']. ' '.htmlspecialchars(vocation_id_to_name($player['vocation'])) ).' ';
echo ($days == 3) ? '<td><center>'. number_format($player['exphist3']) .'</center></td>' : '';
echo ($days == 2) ? '<td><center>'. $player['exphist2'] .'</center></td>' : '';
echo ($days == 1) ? '<td><center>'. $player['exphist1'] .'</center></td>' : '';
echo ($today == true) ? '<td><center>'. ($player['experience']-$player['exphist_lastexp']) .'</center></td>' : '';
echo '</tr>';
}
}
?>
</table>
<br>
</div>
<?php
include 'layout/overall/footer.php';
Click to expand...
 
Sort by date Sort by votes
The problem is with column, that allows only positive numbers (being UNSIGNED)

To change it, go to phpmyadmin -> select table -> click "structure" -> click "Change" on desired column
1685617325356.png


And when you are there, remove the UNSIGNED attribute from the dropdown:
1685617398294.png
 
Upvote 0 Downvote
slaw said:
The problem is with column, that allows only positive numbers (being UNSIGNED)

To change it, go to phpmyadmin -> select table -> click "structure" -> click "Change" on desired column
View attachment 75879


And when you are there, remove the UNSIGNED attribute from the dropdown:
View attachment 75880
Click to expand...
Thanks!!

I looked in the exphit table and it does not appear UNSIGNED
Post automatically merged:

slaw said:
O problema é com a coluna, que permite apenas números positivos (sendo UNSIGNED)

Para alterá-lo, vá para phpmyadmin -> selecione a tabela -> clique em "estrutura" -> clique em "Alterar" na coluna desejada
View attachment 75879


E quando você estiver lá, remova o atributo UNSIGNED do menu suspenso:
View attachment 75880
Click to expand...
View attachment 75882
 
Upvote 0 Downvote
Can you try this query in phpmyadmin? what does it produce?

Code: 
SELECT (experience - exphist_lastexp) AS expdiff FROM players ORDER BY expdiff DESC LIMIT 20;
 
Upvote 0 Downvote
slaw said:
Você pode tentar esta consulta no phpmyadmin? o que ela produz?

[código]
SELECT (experiência - exphist_lastexp) AS expdiff FROM players ORDER BY expdiff DESC LIMIT 20;
[/código]
Click to expand...

erro​

Comando SQL: Documentação



SELECT (experiência - exphist_lastexp) COMO expdiff DE jogadores ORDER BY expdiff DESC LIMIT 20



Mensagens do MySQL: Documentação

#1054 - Coluna 'exphist_lastexp' desconhecida em 'field list'
Click to expand...
 
Upvote 0 Downvote
Sorry, wrong query. The data is in other table.

Try this one:
Code: 
SELECT (a.experience - b.exphist_lastexp) AS expdiff FROM players a JOIN znote_players b ON a.id = b.player_id ORDER BY expdiff DESC LIMIT 20;

What does it show?
 
Upvote 0 Downvote
slaw said:
Sorry, wrong query. The data is in other table.

Try this one:
Code: 
SELECT (a.experience - b.exphist_lastexp) AS expdiff FROM players a JOIN znote_players b ON a.id = b.player_id ORDER BY expdiff DESC LIMIT 20;

What does it show?
Click to expand...

Erro​

Comando SQL: Documentação



SELECT (a.experience - b.exphist_lastexp) AS expdiff FROM players a JOIN znote_players b ON a.id = b.player_id ORDER BY expdiff DESC LIMIT 20



Mensagens do MySQL : Documentação

#1690 - BIGINT UNSIGNED value is out of range in '(old.a.experience - old.b.exphist_lastexp)'
Click to expand...
 
Upvote 0 Downvote
@shakal1994
can you print your znote_players structure database and show here?

slaw show you an exemple.

go into znote_players -> structure -> click in change where is exphist_lastexp and remove the UNSIGNED
 
Upvote 0 Downvote
-- Despejo de SQL do phpMyAdmin
-- versão 4.9.5deb2
-- phpMyAdmin (https://www.phpmyadmin.net/)
--
-- Host: localhost:3306
-- Tempo de geração: 01-jun-2023 às 10:42
-- Versão do servidor: 8.0.33-0ubuntu0.20.04.2
-- versão do PHP: 7.4.3-4ubuntu2.18

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
CONFIGURAR AUTOCOMMIT = 0;
INICIAR TRANSAÇÃO;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 DEFINIR NOMES utf8mb4 */;

--
-- Banco de dados: xx
--

-------------------------------------------------- --------

--
-- Estrutura da tabela znote_players
--

CRIAR TABELA znote_players (
id int NÃO NULO,
player_id int NÃO NULO,
criado int NOT NULL,
hide_char tinyint NÃO NULO,
comentário varchar(255) NÃO NULO,
exphist_lastexp bigint NOT NULL DEFAULT '0',
exphist1 bigint NOT NULL DEFAULT '0',
exphist2 bigint NOT NULL DEFAULT '0',
exphist3 bigint NOT NULL DEFAULT '0',
exphist4 bigint NOT NULL DEFAULT '0',
exphist5 bigint NOT NULL DEFAULT '0',
exphist6 bigint NOT NULL DEFAULT '0',
exphist7 bigint NOT NULL DEFAULT '0',
onlinetimetoday mediumint UNSIGNED NOT NULL DEFAULT '0',
onlinetime1 mediumint UNSIGNED NOT NULL DEFAULT '0',
onlinetime2 mediumint UNSIGNED NOT NULL DEFAULT '0',
onlinetime3 mediumint UNSIGNED NOT NULL DEFAULT '0',
onlinetime4 mediumint UNSIGNED NOT NULL DEFAULT '0',
onlinetime5 mediumint UNSIGNED NOT NULL DEFAULT '0',
onlinetime6 mediumint UNSIGNED NOT NULL DEFAULT '0',
onlinetime7 mediumint UNSIGNED NOT NULL DEFAULT '0',
onlinetimeall int UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Extraindo dados da tabela znote_players
--



--
-- Índices para tabelas despejadas
--

--
-- Índices para tabela znote_players
--
ALTER TABLE znote_players
ADICIONAR CHAVE PRIMÁRIA (id);

--
-- AUTO_INCREMENT de placas despejadas
--

--
-- AUTO_INCREMENT de tabela znote_players
--
ALTER TABLE znote_players
MODIFY id int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=528;
COMPROMETER-SE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Click to expand...
 
Upvote 0 Downvote
Right, and how would I do that? I tried the way the friend above showed but here for me it doesn't appear :(

If you want to look at my power gamers

gardemot.online/powergamers.php
 
Upvote 0 Downvote
shakal1994 said:
Right, and how would I do that? I tried the way the friend above showed but here for me it doesn't appear :(

If you want to look at my power gamers

gardemot.online/powergamers.php
Click to expand...
você vai no seu banco de dados, tabela players > estrutura, busca por experience e clica em alterar, na nova janela busca por atributos e marca o campo em branco.

You go to your database, table players > structure, search for Experience and click Change, in the new window Search for attributes and mark the blank field.
 
Upvote 0 Downvote
Upvote 0 Downvote
mano368 said:
why u have both in the two tables? anyway, SEARCH FOR EXPERIENCE IN TABLE PLAYERS E X P E R I E N C E AND CHANGE THE UNSIGNED

also you dont need the powergamers coluns in players table, only in znote_players
Click to expand...


Wonder! It worked out!

I hope this doesn't cause any problems with other things.


Thank you very much.

How do I mark it as best answer?
 
Upvote 0 Downvote
Back
Top