2×3 forced matrix mlm php and mysql database design

I am creating a 2×3 forced matrix mlm.

I have two tables. Table1: users. Table2: matrix


user_id   |   username   |   password   |   email

1              user1           *****        user1@admin.com

2              user2           *****        user2@admin.com

3              user3           *****        user3@admin.com

4              user4           *****        user4@admin.com

5              user5           *****        user5@admin.com

6              user6           *****        user6@admin.com

7              user7           *****        user7@admin.com


user_id, sponsor_id, username, filled_positions, position_1, position_2, position_3, position_4, position_5, position_6, position_7, position_8, position_9, position_10, position_11, position_12, position_13, position_14

 1            0       user1           6            user2       user3       user4       user5       user6      user7

 2            1       user2           2            user4       user5

 3            1       user3           2            user6       user7

 4            2       user4

 5            2       user5

 6            3       user6

 7            3       user7

I already have these 7 users added manually in the mysql database. So it starts with a user8 insertion.
Note that I am only using usernames under each position(position_1,position_2…etc) for visual purposes. In actuality, I’m using user_id of said usernames.

So here’s what happens. A user signs up through the website. If the user is already being sponsored by another user, then I can make it work. But If the user doesn’t have a sponsor, then they have no sponsor id and that’s where I have an issue because I can’t insert the user in the matrix table without knowing the correct sponsor_id. The correct sponsor_id would be the next empty position in the row.

So in this scenario, user8 would be the next member. User8 will be placed under position_7 column of user1(row 1). Once this user’s all 14 positions are filled, it will go down to the next user row below it. The next user, which would be User16 and will be placed under position_3 column of user2 row. So it’ll keep looping through the rows from left to right and find empty positions and fill in the users.

Basically I am going to have 3 querys.

Query 1: Insert the user in users table. This is simple. Don’t need help with this.

Query 2: Update a matrix table row with a new user in the next empty position available. THIS IS WHAT I NEED HELP WITH. WHAT WOULD THE QUERY LOOK LIKE TO DO THIS?

Query 3: Insert the same user in the matrix table. This is simple as well. I just need the user_id of said row from Query 2 to complete this.

Here’s a diagram that would help. All the orange icons are already filled in 7 users. It starts with the 8th user.
2×3 forced matrix diagram

Source: stackoverflow-php