Let me first start by saying that if anyone have a better idea for me to do this, then please, your help will be greatly appreciated.
I want to implement a status system based on the amount of referals someone has made or influenced.
I have 5 levels. When someone joins, they are default on Level 1 and i have a field in the table called ‘status’ with an integer value assigned, see list below:
LEVEL NAME INT VALUE Level 1: Commoner 25 Level 2: Labourer 30 Level 3: Apprentice 35 Level 4: Knight 40 Level 5: Lord 50
By default, when a person joins and does not enter a referer, a default referer id is assigned.
Currently my table looks like this:
CREATE TABLE `users` ( `id` int(11) NOT NULL auto_increment, `firstname` varchar(64) NOT NULL, `lastname` varchar(64) NOT NULL, `email` varchar(64) NOT NULL, `status` smallint(5) NOT NULL, `refid1` int(11) NOT NULL, `refid2` int(11) NOT NULL, `refid3` int(11) NOT NULL, `refid4` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I store the first referer in the refid1 field. If the user then invites another person, his id will be stored in the refid1 field and the original referer will be stored in the refid2 field and so on, with the final reference to the original referer disappearing from the referal list when he is replaced in refid4. If it is unneccesary to store the values as i have and only store the referer details in refid1 (nested model), it would be nice to know.
Now I need to run a query that will adjust the status of a user if it meets certain criteria. For the sake of clarity, I will try and simplify what I want to achieve:
USER A = The Website; Default Referer (id = 1)
USER B = Non-refered user signing up (id = 2)
USER B will then have refid1=1 as his referer.
For USER B to become Level 2 Labourer he needs to invite atleast 10 users.
For USER B to become Level 3 Apprentice a minimum of 10 of his invited users need to have each invited atleast 10 users.
For USER B to become Level 4 Knight a minimum of 10 of his invited users need to have invited atleast 10 users whom should have in turn invited atleast 10 users.
Since this only goes to Level 5, you can understand where this is going. The minimum requirement to move up in status is that you need to have atleast 10 users refered at each level basically.
Is there someone that can help me build a query for this that will not take ages to run or do I need to restructure my database. I have been up for a couple of nights now trying to figure out how I am going to implement this and I just cannot seem to get it right.
Thanks for any help.