Geek asks and answers

Updating one table conditionally based on the values ​​of another

Hi, I have two tables: user_badgesand a badgestable, the following table data user_badges:

id | user_id | badge_id | points_earned | badges_awarded |
 1    2324        0             5               0

If the user meets the minimum required points, i.e. 5 from the table badges, SQL will update the above as:

id | user_id | badge_id | points_earned | badges_awarded |
 1    2324        1             5               1

If in the future a new point is registered for the same user, the icons user_tablewill add a new line as follows:

 id | user_id | badge_id | points_earned | badges_awarded |
  1    2324        1             5               1
  2    2324        0             7               0

Above problem solved

This is the table badges:

badge_id | badge_name | required_points
   1           new          5
   2           adv          10

Below i need

The problem is that I need a query to compare a table user_badgeswith a table badges, provided that the query needs to know whether the icons were checked earlier or not.

I use this for Zend applications, I need a solution for this problem ...

+5
3

, , , correlated subquery user_badges badges_id badges .

UPDATE user_badges a
       INNER JOIN(SELECT DISTINCT user_id, SUM(badges_awarded) AS total_badges_awarded
                  FROM user_badges
                  GROUP BY user_id
                 ) b
             ON a.user_id = b.user_id
                AND b.total_badges_awarded = 0
SET a.badge_id = (@var_badges_awarded_flag:= (IFNULL((
                             SELECT c.badge_id
                             FROM badges c
                             WHERE a.points_earned > c.required_points
                             ORDER BY c.required_points DESC
                             LIMIT 1
                            ), 0))),
    a.badges_awarded = a.badges_awarded + IF(@var_badges_awarded_flag > 0, 1, 0);
+5

SQL, , :

SELECT *
FROM user_badges
JOIN badges
ON badges.badge_id = (user_badges.badge_id + 1)

, PHP:

while ($row = mysqli_fetch_assoc($result)) // where result is the result from running the above query
{
    if ($row["points_earned"] >= $row["required_points"]) // enough points for next badge, update badges
    {
        mysqli_query("UPDATE user_badges SET badge_id = badge_id + 1, badges_awarded = badges_awarded + 1 WHERE user_id = ".$row['user_id']);
    }
}

SQL/PHP, . , PHP .

0

, user_badges, , users, user_points. UPDATE ... SET earned_points = earned_points + :points.

user_badges:

user_id | badge_id

Use the spanning index UNIQUEto make sure that a user cannot have more than one icon.

Finally, updating the table user_badgescan be performed as a background task (cron, etc.) using two steps:

  • Determine which badges were earned with points_earned >= required_points.
  • Insert new icons in user_badgeswith INSERT IGNORE INTO user_badges ...; this ensures that only new icons are added and existing ones are not overwritten.
0
source

More articles:


All Articles