CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
Search Posts SEARCH
Who's Online WHO'S
Log in LOG

Home: Perl Programming Help: Intermediate:
mysql and perl - stumped



Jan 12, 2003, 7:53 AM

Post #1 of 8 (2578 views)
mysql and perl - stumped Can't Post


I am working on creating a script for a multi-level-marketing(MLM) company and I ran into a problem I did not even consider.

First off, the MLM is going to be a "forced matrix" 3x4.
It's going to be like a pyramid. The rules of "forced matrix" means there will be spillover if a level is filled. The 3x4 means the pyramid will be 4 levels deep, and each person in the pyramid can have no more than 3 people directly under them.

Spillover - Spillover is what happens when someone goes to the members page and signs up, but the user already has 3 people under him/her, that new signup should be placed in the next available spot (closest to the top) in the pyramid.

Example: Joe signed up. He referred mary, beth, and ann. Another person goes to joe's unique signup page and signs up. Since joe already has 3 people in his first level, the new signup is placed under mary. Now if mary already had 3 people also, then the new member would be under beth. This would keep going until the script found a member without 3 people in there first level. It could in fact go further than 4 levels, because the pyramid is really endless in an admin point of view.

I I I level1 "3"
I I I I I I I I I level2 "9"

If I illustrated a level5 it would have 243 people on it, and everybody on it would be the level1 of someone on level4.

From an admin point of view this pyramid will be of endless levels, because only one person can be at the top. Each "I" is a new person in the pyramid. From the users point of view, (when viewing there downline) they will see themselves at the top of "there" pyramid, but in reality, many people will already be part of the pyramid that is above them(which they can not see). Example: Someone on Level4 refers there first new signup... It would appear to the person who referred them that the signup is in there first level, but to the person at the very top of the pyramid, the person would actually be in level5 of the pyramid, (but that person can only see 4 levels deep, so he would never know)

Now for my problem

I have created all the members area, and admin area. The users can login, view there downline (4 levels down), view there upline(4 levels up). Now I am working on the actual signup part, and I have found it quite hard to imagine a way to keep the server load down yet place a member in the next open spot in the pyramid. The first few levels I could do fine, but let's say this pyramid get's HUGE.

Let's say Joe is the guy at the top of the pyramid.
He has all his levels filled with other people who signed up. Now he signs up yet another person, the script would have to check each person under joe, and keep going through till it finds someone without 3 people. Let's say it starts checking people in level4... that's 81 different users the script would have to go through to see if they each had 3 members in there personal first level.

Let's say everyone on the 4th level already each had 3 people also. Then the script would have to check everybody on the next level (243 people) to see if they all have 3 members.

As you can see, this could become a massive server load if it had to keep checking all these people individually.

Here is how the database is setup now.
username, password, IDNumber, upline1, upline2, upline3, upline4, and other fields that pertain to personal information.

Let's look at joes row in the database:
password=joe's password
upline1=tom321 #the person who referred joe
upline2=mary983 #the person who referred tom
upline3=vince454 #the person who referred mary
upline4=master423 #the person who referred vince

The reason I set it up this way is to easily show Joe how many people are in his downline. Here's how viewing the downline works:

$query = "SELECT COUNT(*) FROM mlm WHERE upline1 = Joe123";

$query = "SELECT COUNT(*) FROM mlm WHERE upline2 = Joe123";



This would show Joe how many people are in each level of his downline.

When I first set this up, I was thinking, ok all I have to do is a SELECT COUNT for joe's id from upline1, if there is less than 3 then the person signing up could go in joes downline... If it was full...I could just store an array of idnumbers of each person in joe's first level, and then do the same check on all them.

Obviously I did not think it all the way through, because I never imagined an array with 243 idnumbers, and checking each one to see if they have 3 people. Heck, depending on how good this company does, the script could be checking 1000's of people just for one simple signup. As you can see that is exactly where my problem is. I don't want a signup to take 20 mins because the user is waiting for a response from the script, I don't want the script bringing the server to it's knees.

So basically what I need are some ideas
(they don't have to be actual code(unless you are really nice(or bored))). I already know I am going to be starting from scratch, but that is ok, because I have not really put much time into the programming aspect of this, except for the time thinking about a solution.

What I need from you are ideas about the format of the database, and ideas on how to keep the server load low, but still keep this a forced matrix, and make sure the person signing up is put in the right spot in the pyramid.

Thanks for your time,


Thaumaturge / Moderator

Jan 12, 2003, 9:10 AM

Post #2 of 8 (2571 views)
Re: [vwiley1] mysql and perl - stumped [In reply to] Can't Post

Two reasons why you may not get an answer from this board.

1/ No-one wants to help you set up another MLM scheme.

2/ Actually your problem is all about the best way to model your data in SQL. Once you've worked that out out the Perl will be simple.

Dave Cross, Perl Hacker, Trainer and Writer
Get more help at Perl Monks


Jan 12, 2003, 9:19 AM

Post #3 of 8 (2570 views)
Re: [davorg] mysql and perl - stumped [In reply to] Can't Post

You were probably expecting a flame back, but actually you made 2 very good points.

I didn't really think about the fact that I didn't actually need perl help, because it's not entirely a perl problem. My fault for posting here, if anyone does have an idea, I still am intrested though.

And yes.... yet another mlm program. If someone was going to pay you very well for a script, you would be doing it too. ;)


Jan 12, 2003, 9:39 AM

Post #4 of 8 (2568 views)
Re: [vwiley1] mysql and perl - stumped [In reply to] Can't Post

It is quite a loaded question which will take a lot of thought. Someone may assist you but as I mentioned it is something that would require quite a detailed answer.

Perhaps you shouldn't be doing paid work for a company until you have a deeper understanding of SQL and Perl.


Jan 13, 2003, 3:08 PM

Post #5 of 8 (2559 views)
Re: [vwiley1] mysql and perl - stumped [In reply to] Can't Post

I tell ya, pyramid schemes should be illegal.

Oh wait... they are! ;-)


Jan 13, 2003, 3:38 PM

Post #6 of 8 (2557 views)
Re: [gregarios] mysql and perl - stumped [In reply to] Can't Post

very true....

luckily I will not have to worry about that.. my customer will.

Second, I did check over her business idea/opportunity. It is a MLM company, and is not a pyramid scheme


Jan 14, 2003, 1:21 AM

Post #7 of 8 (2555 views)
Re: [vwiley1] mysql and perl - stumped [In reply to] Can't Post

luckily I will not have to worry about that.. my customer will.

Dear dear, that is so not the point. If you know it is wrong then you don't do it anyway just to make a bit of cash knowing that "it's not my responsibility". People like you are why these schemes still exist. As long as there are people coding the scripts there will be people using them.

If someone walks in to a gunstore and asks for a gun because they are going to blow someone's head off, you don't say "oh sure, here's a real good gun" just because you know the murderer is the one that will have to deal with the consequences.

(This post was edited by Paul on Jan 14, 2003, 1:22 AM)


Jan 14, 2003, 6:02 AM

Post #8 of 8 (2551 views)
Re: [Paul] mysql and perl - stumped [In reply to] Can't Post

Your point is taken,

but I think you ignored the latter part of my last post which states that I did check her plan over. I found it not to be an illegal pyramid scheme. It is a legal mlm/network marketing company. Signup $ amount is very low, and members do not get commissions for getting new people in the business, they get commissions off of sales only.

If I knew she was doing something illegal, then I would of let her know in the first place, and gave her some pointers on how to make it legal. If it would of happened that way and she would not change her plan, I would of not done it.


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives