Hi All
I have a table for network scheme (multilevel marketing),
mysql> select * from structure;
+----+--------+------+------+------+------+------+
| id | leader | 1st | 2nd | 3rd | 4th | 5th |
+----+--------+------+------+------+------+------+
| 1 | 1 | 2 | 5 | 14 | NULL | NULL |
| 3 | 1 | 3 | 8 | 23 | NULL | NULL |
| 5 | 1 | 4 | 11 | NULL | NULL | NULL |
| 2 | 2 | 5 | 14 | NULL | NULL | NULL |
| 8 | 2 | 6 | 17 | NULL | NULL | NULL |
| 10 | 2 | 7 | 20 | NULL | NULL | NULL |
| 4 | 3 | 8 | 23 | NULL | NULL | NULL |
| 13 | 3 | 9 | 26 | NULL | NULL | NULL |
| 15 | 3 | 10 | NULL | NULL | NULL | NULL |
| 6 | 4 | 11 | NULL | NULL | NULL | NULL |
| 18 | 4 | 12 | NULL | NULL | NULL | NULL |
| 20 | 4 | 13 | NULL | NULL | NULL | NULL |
| 7 | 5 | 14 | NULL | NULL | NULL | NULL |
| 9 | 6 | 17 | NULL | NULL | NULL | NULL |
| 11 | 7 | 20 | NULL | NULL | NULL | NULL |
| 12 | 8 | 23 | NULL | NULL | NULL | NULL |
| 14 | 9 | 26 | NULL | NULL | NULL | NULL |
| 16 | 10 | NULL | NULL | NULL | NULL | NULL |
| 17 | 11 | NULL | NULL | NULL | NULL | NULL |
| 19 | 12 | NULL | NULL | NULL | NULL | NULL |
| 21 | 13 | NULL | NULL | NULL | NULL | NULL |
| 22 | 14 | NULL | NULL | NULL | NULL | NULL |
| 23 | 5 | 15 | NULL | NULL | NULL | NULL |
| 24 | 15 | NULL | NULL | NULL | NULL | NULL |
| 25 | 5 | 16 | NULL | NULL | NULL | NULL |
| 26 | 16 | NULL | NULL | NULL | NULL | NULL |
| 27 | 17 | NULL | NULL | NULL | NULL | NULL |
| 28 | 6 | 19 | NULL | NULL | NULL | NULL |
| 29 | 19 | NULL | NULL | NULL | NULL | NULL |
| 30 | 20 | NULL | NULL | NULL | NULL | NULL |
| 31 | 7 | 21 | NULL | NULL | NULL | NULL |
| 32 | 21 | NULL | NULL | NULL | NULL | NULL |
| 33 | 7 | 22 | NULL | NULL | NULL | NULL |
| 34 | 22 | NULL | NULL | NULL | NULL | NULL |
| 35 | 23 | NULL | NULL | NULL | NULL | NULL |
| 36 | 8 | 24 | NULL | NULL | NULL | NULL |
| 37 | 24 | NULL | NULL | NULL | NULL | NULL |
| 38 | 8 | 25 | NULL | NULL | NULL | NULL |
| 39 | 25 | NULL | NULL | NULL | NULL | NULL |
| 40 | 26 | NULL | NULL | NULL | NULL | NULL |
| 41 | 9 | 27 | NULL | NULL | NULL | NULL |
| 42 | 27 | NULL | NULL | NULL | NULL | NULL |
| 43 | 9 | 28 | NULL | NULL | NULL | NULL |
| 44 | 28 | NULL | NULL | NULL | NULL | NULL |
+----+--------+------+------+------+------+------+
44 rows in set (0.00 sec)
* All those number representing users' IDs
We want to select all records based on the 'leader' column.
Here is an example. The 1st row is
mysql> select * from structure;
+----+--------+------+------+------+------+------+
| id | leader | 1st | 2nd | 3rd | 4th | 5th |
+----+--------+------+------+------+------+------+
| 1 | 1 | 2 | 5 | 14 | NULL | NULL |
we need to do query again based on the 1st, 2nd, 3rd, th and 5th column.
mysql> select * from structure where leader = '2';
+----+--------+------+------+------+------+------+
| id | leader | 1st | 2nd | 3rd | 4th | 5th |
+----+--------+------+------+------+------+------+
| 2 | 2 | 5 | 14 | NULL | NULL | NULL |
| 8 | 2 | 6 | 17 | NULL | NULL | NULL |
| 10 | 2 | 7 | 20 | NULL | NULL | NULL |
+----+--------+------+------+------+------+------+
3 rows in set (0.00 sec)
mysql> select * from structure where leader = '5';
+----+--------+------+------+------+------+------+
| id | leader | 1st | 2nd | 3rd | 4th | 5th |
+----+--------+------+------+------+------+------+
| 7 | 5 | 14 | NULL | NULL | NULL | NULL |
| 23 | 5 | 15 | NULL | NULL | NULL | NULL |
| 25 | 5 | 16 | NULL | NULL | NULL | NULL |
+----+--------+------+------+------+------+------+
3 rows in set (0.00 sec)
mysql> select * from structure where leader = '14';
+----+--------+------+------+------+------+------+
| id | leader | 1st | 2nd | 3rd | 4th | 5th |
+----+--------+------+------+------+------+------+
| 22 | 14 | NULL | NULL | NULL | NULL | NULL |
+----+--------+------+------+------+------+------+
1 row in set (0.00 sec)
All these values should be returned.
What is the best method to do all this using mySQL 3.23
How can I do this in PHP?
Thanks
