Stats are not accurately displayed.

[smc87]

Hello, a few people have been complaining recently that the stats dont seem to make sense. When looking at the database, everything is fine, so can only assume its something to do with how the stats are being read and summed up on the front end.

EG we have some players where it is reported that they have broken no stone blocks, but yet still have 250+ iron ore breaks. Looking inthe DB we can see that stone breaks have been recorded.

\ putting this here as a memory jog for me - Another admin on my server has suggested that possibly we should sum up the block break values before grouping them. - I will look into this when i have some spare time **

[smc87]

This query seems to be the issue from getPlayerBlock() in core.php of lolmewnstats: SELECT *, Stats_blocks_broken.name as 'name', Stats_blocks_broken.value as 'broken', Stats_blocks_placed.value as 'placed' FROM Stats_blocks_broken INNER JOIN Stats_blocks_placed on Stats_blocks_broken.uuid = Stats_blocks_placed.uuid and Stats_blocks_broken.name = Stats_blocks_placed.name and Stats_blocks_broken.world = Stats_blocks_placed.world WHERE Stats_blocks_broken.uuid = 'UUID' GROUP BY Stats_blocks_broken.name, Stats_blocks_broken.world;

Comparing above with the below shows missing blocks being returned by the above statement

select * from Stats_blocks_placed where uuid = 'something'

[smc87]

removing the INNERJOIN allows me to see blocks broken with values that i would expect (obviously doesn't bring back any placed blocks though).

SELECT *, Stats_blocks_broken.name as 'name', Stats_blocks_broken.value as 'broken' FROM Stats_blocks_broken WHERE Stats_blocks_broken.uuid = 'UUID' GROUP BY Stats_blocks_broken.name, Stats_blocks_broken.world;

[robinp7720]

This is actually rather difficult as MySQL doesn't support full joins. Due to a player sometimes only placing a block but not breaking it, it's even more difficult as Lolmewnstats only registers stats for blocks which have been broken/placed by that player.

Using two separate tables would be much easier for now but it wouldn't be as nice as having just one table. All help to fix this would be appreciated

[smc87]

LEFT JOIN seems to get what we want, but returns null values instead of 0. I'm not sure if it introduces any other issues though.

[smc87]

I guess the issue with this is that if a block has been placed, but not broken, it is missed by the query.

[smc87]

I think this might be working, but ive been wrong several time tonight already!

$stmt = $this->mysqli->stmt_init();

$sql = "SELECT a.world, a.name as name, ifnull(a.value, 0) as 'broken', ifnull(b.value, 0) as 'placed' FROM {$this->prefix}blocks_broken as a LEFT OUTER JOIN{$this->prefix}blocks_placedas b on a.uuid = b.uuid and a.name = b.name and a.world = b.world WHERE a.uuid = ? GROUP BY a.name, a.world UNION ALL SELECT d.world, d.name as name, ifnull(c.value, 0) as 'broken', ifnull(d.value, 0) as 'placed' FROM {$this->prefix}blocks_broken as c RIGHT OUTER JOIN{$this->prefix}blocks_placedas d on c.uuid = d.uuid and c.name = d.name and c.world = d.world WHERE d.uuid = ? and c.value is null GROUP BY d.name, d.world";

if ($stmt->prepare($sql)) { $stmt->bind_param("ss", $player,$player);

[robinp7720]

Thanks @smc87. That query seamed to work flawlessly!

[smc87]

:-)