am trying to build a point system which checks how much points a user have and gives a specific title to them.
I have prepared a table which the php script can refer to when checking which title should be given to a member.
MYSQL Table structure as follows:
name: ptb
structure: pts , title
For example , if you have 100 points , you gain the title - "Veteran" , if you have 500 points , you gain the title "Pro". let's say i have pts:100 , title:veteran and pts:500 , title:pro rows in the ptb table.
However i stumble upon a confusing fact.
How can i use php to determine which title to give the user by using the ptb table data?
If a user have equal or more than 100 points will gain Veteran for title BUT 500 is also MORE THAN 100 which means the php script also needs to make sure it is below 500pts .
I still not sure how to use php to do this. as i am confused myself. I hope someone could understand and provide me some guidelines.
THANKS!
You select all records with enought points, sort the one with the highest score to the top and cut out the rest.
SELECT title FROM ptb WHERE pts <= $points ORDER BY pts DESC LIMIT 1
(PiTheNumber's solution doesn't work very well if you want to retrieve titles for multiple users)
Since the points will change over time and the mutliple users can have the same title, it sounds like this should be 2 tables:
CREATE TABLE users (
userid ...whatever type,
points INTEGER NOT NULL DEFAULT 0
PRIMARY KEY(userid)
);
CREATE TABLE titles (
title VARCHAR(50),
minpoints INTEGER NOT NULL DEFAULT 0
PRIMARY KEY (title),
UNIQUE INDEX (minpoints)
);
Then....
SELECT u.userid, u.points, t.title
FROM users u, titles t
WHERE u.points>=t.minpoints
AND ....other criteria for filtering output....
AND NOT EXISTS (
SELECT 1
FROM titles t2
WHERE t2.minpoints>=u.points
AND t2.minpoints<=t.minpoints
);
(there are other ways to write the query)