Ranking blank

Resolved topplayer
(@topplayer)

4 years, 2 months ago

hello friends
I need a little help, the scores and ranking points are generated correctly (I see the data in the database), but on my ranking page it shows all users with a 0 score.

activating the debug mode, it seems that the score_date variable arrives empty to the query.
I get the following error

Incorrect DATETIME value: ”
—————————————-
query looks like

SELECT s.ranking, u.ID AS user_id, u.display_name AS user_name, u.user_email AS email , s.total_score AS points, s.score AS last_score , lu.league_id FROM pool_wp_scorehistory_s1_t1 AS s JOIN ( SELECT user_id, MAX( score_order ) AS last_row FROM pool_wp_scorehistory_s1_t1 WHERE ranking_id = 1 AND ( 1 = 1 OR score_date <= ” ) GROUP BY user_id ) AS s2 ON ( s2.user_id = s.user_id AND s2.last_row = s.score_order ) JOIN wp_users u ON ( u.ID = s.user_id )INNER JOIN pool_wp_league_users lu ON ( u.ID = lu.user_id AND ( 1 = 1 OR lu.league_id = 0 ) ) INNER JOIN pool_wp_leagues l ON ( lu.league_id = l.id ) WHERE s.ranking_id = 1 AND ( 1 = 1 OR score_date <= ” ) ORDER BY s.ranking ASC

any ideas?

Viewing 3 replies - 1 through 3 (of 3 total)

Plugin Author AntoineH
(@antoineh)

4 years, 2 months ago
My theory:
The code uses a little ‘trick’ when there is no date given for the ranking (read: it should show the latest ranking, not at a specific point in time). It then adds “1=1” into the equation which will always evaluate to true and the date expression will be ignored. Maybe having a more strict setting in the database results in the fact that the query returns an empty data set. The zero points are added by the plugin to adjust the ranking for the fact that users may be added after a calculation was done. And because, in my theory, the ranking is an empty data set, all users will be regarded as ‘new’.

I will do some testing to see if I can reproduce this behavior.

What version of MySQL are you running?
- This reply was modified 4 years, 2 months ago by AntoineH.
Thread Starter topplayer
(@topplayer)

4 years, 2 months ago

Thank you for your response
you gave me an idea whit your aswer about how mysql treats empty values (‘ ‘ ) in the DATETIME columns and I remembered that mysql allows to switch beetwen models to be permisive for certain things like empty values o zero divicion.
So, first check the current mode with this query:
SELECT @@sql_mode;
in my case I got:
sql_mode – ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

For my purpose I had to allow empty values on the date, so I had to disable the restrictions:
NO_ZERO_IN_DATE
NO_ZERO_DATE

to deactivate them you only need to run(root user) the query and remove them:
SET GLOBAL sql_mode = ‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;

Now, mysql acepts zero but no empty ”;
so fill whit zero date in the parts of the plugin you need, in my case just modify this function:

public function get_ranking_from_score_history(
$league,
$ranking_id = FOOTBALLPOOL_RANKING_DEFAULT,
$score_date = ‘0000-00-00 00:00:00’ ) {
global $wpdb;
$prefix = FOOTBALLPOOL_DB_PREFIX;

$date_switch = ( $score_date == ‘0000-00-00 00:00:00’ ) ? ‘1 = 1 OR ‘ : ”;
$league_switch = ( $league <= FOOTBALLPOOL_LEAGUE_ALL ) ? ‘1 = 1 OR’ : ” ;

Thats it.
I hope it helps for those who have the same problem.
I have MySQL version: 8.0.18 – MySQL Community Server – GPL.
recomend check the oficcial Documentation
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-setting

Thanks for amazing code dear @antoineh

Plugin Author AntoineH
(@antoineh)

4 years, 2 months ago

Thanks for confirming that my guess was right. And also for showing a solution/workaround for the ranking page!

I will create a permanent fix (regardless of DB settings) for the next version.

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘Ranking blank’ is closed to new replies.