Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unanswered: best way to handle multiple ranges in UPDATE query

    I'm developing a social game where the user advances in levels according to their points number.

    I want to create an efficient query that changes the user points and then change their level if needed.
    The initial query is this:
    Code:
    "UPDATE users SET points = points + $pointsNum WHERE users.id = $id"
    There is a 'levels' table that includes the points needed per level. My questions are:
    1. Maybe storing the points per level data in a config file would be better, since there are only 10 levels and the required points will probably not change much during the lifetime of the game?
    2. How should I approach the level update situation? should I add it to the existing query or create a separate one?
    3. Since there are 10 levels, I will need 10 "BETWEEN"s in the query, to check the current points number vs each points range. Maybe there is a prettier way to do this?

    Any help would be appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    define your levels in a table using say
    Table: Levels
    ID
    Upperlimit integer // defiens the upper limit which this level applies
    declare your user levels
    then when you need to find the level

    SELECT Levels.ID FROM Levels
    WHERE mypoints >= upper limit
    ORDER BY Levels.UpperLimit
    LIMIT 1
    MySQL :: Re: select top 1 ...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2011
    Posts
    2
    Thanks, it makes sense. but how do I combine this query with the previous one? or should I leave it as a separate query?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why do you need to combine this with an update query?

    you can retrieve the level at the same time as the userdetails by doing a join where the users points are less than or equal to the upperlimit and use the TOP 1 predicate
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •