Need help with marketing database scripts

Kindersoft

Ello,

I've written a set of php scripts that populate and control a marketing matrix (contained in a mysql database).

The problem that I need to fix now is that I need to be able to show each user in a user's downline. My user table is as follows:

m_uid - unique user id, also serves as the sponsor ID for the user

... (details about the user, nothing important)

m_dsponid - sponsor ID of the person that the user signed up under directly.

naturally, each signup (user) is on their own row in the table

Example:

Joe Smith gives his friend Alan his sponsor ID (joe's user id).
Alan goes and signs up under Joe with his ID 123. 123 would be stored under m_dsponid on Alan's row. Alan is assigned a random userID of 124.

Alan gives his sponsor ID of 124 to Kelly, and she signs up with Alan as her direct sponsor.
But, she also has signed up under Joe Smith's matrix, because Joe is Alan's direct sponsor.

What I need is somehow to compare the m_dsponid field to create the full downline of Joe Smith, which would show Alan in position 1 under Joe, then show Kelly in position 1 under Alan, and position 2 under Joe.

out of breath

nnichols

This would give you three levels -

SELECT a.name, b.name, c.name
FROM user_table a
LEFT JOIN user_table b
  ON a.m_uid = b.m_dsponid
LEFT JOIN user_table c
  ON b.m_uid = c.m_dsponid
WHERE a.m_uid = 123;

To extend it to 4 levels you would need -

SELECT a.name, b.name, c.name
FROM user_table a
LEFT JOIN user_table b
  ON a.m_uid = b.m_dsponid
LEFT JOIN user_table c
  ON b.m_uid = c.m_dsponid
LEFT JOIN user_table d
  ON c.m_uid = d.m_dsponid
WHERE a.m_uid = 123;

I hope you can understand what I've done here. It is very important that you have both the "m_uid" and "m_dsponid" indexed, otherwise the query will become very slow as the table gets bigger.

Hope this helps 😉

Kindersoft

thank you! that helped very much so 🙂
is there any feasible limit to how many left joins you should do? Say if the person's matrix has 10 people in it or so, would it be too much of a system strain to keep adding left join statements?

again, thanks a million

nnichols

It should be ok. I have one of these that runs ten deep and there are about 10M records and it's not too bad. Run some tests with some dummy data.

EDIT:
Wouldn't reckon on it being very good with many more joins, but you'll have to experiment