Mysql – Update Ranking Points Based on Date Range

MySQL

Currently, I am building a personal project.

  1. A site that stores Racing Information and grants point to weekly individual winners.

  2. These racers have groups and points earned by the group will be ranked on a monthly basis.

The query below returns Top racing information of the current week where the current week starts every Tuesday.

SELECT *
FROM timeatk 
WHERE DATE(posted)
    BETWEEN DATE_SUB(DATE(now()), INTERVAL (WEEKDAY(now()) - 1 + 7) % 7 DAY)
        AND DATE_ADD(DATE(now()), INTERVAL 6 - (WEEKDAY(now()) - 1 + 7) % 7 DAY)
ORDER BY timer ASC LIMIT 0, 10

But, I am working on a CRON, where a PHP script should be triggered depending on the date on a for-loop. Coz' every Tuesday, I need to add points for the weekly winners.

For the weekly results:

I would like to UPDATE the weekly top ten racers based on the lowest time on the "timer" column from the last week's Tuesday until Monday of the current week.

Weekly Winners:

  • 1st = 10points
  • 2nd = 9points
  • 3rd = 8points
  • .. until
  • 10th = 1point
for ($score = 10; $score > 0; $score--) {
        $data = [
            'score' => $score
        ];
        $sql = "update ometimeatk.timeatk_has_list set weekly_score = :score where DATE_SUB(DATE(now()), INTERVAL (WEEKDAY(now()) - 1 + 7) % 7 DAY) AND DATE_ADD(DATE(now()), INTERVAL 6 - (WEEKDAY(now()) - 1 + 7) % 7 DAY)";
        $stmt= $conn->prepare($sql);
        $stmt->execute($data);
}

Here's the table structure:

CREATE TABLE `timeatk_has_list` (
  `id` int(11) NOT NULL,
  `group_name` varchar(80) NOT NULL,
  `racer_has_name` varchar(255) NOT NULL,
  `racer_has_mobile` varchar(255) NOT NULL,
  `racer_has_nationality` varchar(255) NOT NULL,
  `racer_has_email` varchar(255) NOT NULL,
  `motor` varchar(255) NOT NULL,
  `gear_ratio` varchar(255) NOT NULL,
  `chassis` varchar(255) NOT NULL,
  `timer` varchar(255) NOT NULL,
  `posted` datetime DEFAULT NULL,
  `weekly_score` smallint(6) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

enter image description here

Here's a simple SELECT query to check the result.

The UPDATE query above updates ALL rows weekly_score with 1.

And here's the working SQL query with the help of J.D:

UPDATE timeatk_has_list
INNER JOIN 
(
        SELECT *, ROW_NUMBER() OVER (order by timer desc) AS RacerRank
        FROM timeatk_has_list
        WHERE DATE(posted)
            BETWEEN DATE_SUB(DATE(now()), INTERVAL (WEEKDAY(now()) - 1 + 7) % 7 DAY)
            AND DATE_ADD(DATE(now()), INTERVAL 6 - (WEEKDAY(now()) - 1 + 7) % 7 DAY)
        ORDER BY timer ASC LIMIT 0, 10
) c
    ON ometimeatk.timeatk_has_list.id = c.id
SET ometimeatk.timeatk_has_list.weekly_score = 11 - c.RacerRank

Best Answer

You should always include your schema and sample before and after data, otherwise it's difficult to conceptualize what we don't know.

You can solve this pretty easily without even using a loop in PHP, rather by using a window function in MySQL like ROW_NUMBER to generate the ranks and then use those ranks to update your scores. Here's a partial example (if you update your question with the missing items I mentioned above, I can update this example):

UPDATE ometimeatk.timeatk_has_list
INNER JOIN 
(
        SELECT *, ROW_NUMBER() OVER (ORDER BY timer DESC) AS RacerRank -- Generates a unique ID (assuming timer is unique) for each racer row ordered by timer
        FROM timeatk 
        WHERE DATE(posted)
            BETWEEN DATE_SUB(DATE(now()), INTERVAL (WEEKDAY(now()) - 1 + 7) % 7 DAY)
            AND DATE_ADD(DATE(now()), INTERVAL 6 - (WEEKDAY(now()) - 1 + 7) % 7 DAY)
        ORDER BY timer ASC LIMIT 0, 10
) c -- Gets the racer rank
    ON ometimeatk.timeatk_has_list.id = c.id -- Fill in your actual key fields here
SET ometimeatk.timeatk_has_list.weekly_score = 11 - c.RacerRank -- Sets the weekly score to 10 for 1st place, 9 for 2nd place, etc
Related Question