+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Right Join Query Problem (/showthread.php?tid=55966)
Right Join Query Problem - El Forum - 11-19-2012
[eluser]rochellecanale[/eluser]
Hello guys actually it is not a CI problem. But I hope you can help me.
My problem is all about query. I tried all my best but i give up. Here's my tables
Code:
mysql> desc member;
+-----------------+-------------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------------------------------------------------------+------+-----+---------+----------------+
| member_id | int(8) unsigned | NO | PRI | NULL | auto_increment |
| account_type | enum('REGULAR','DUPLICATE','CORPORATE','ADVANCE','WALK-IN') | NO | | NULL | |
| username | varchar(125) | NO | UNI | NULL | |
| lastname | varchar(50) | NO | | NULL | |
| firstname | varchar(50) | NO | | NULL | |
| middlename | varchar(50) | NO | | NULL | |
| gender | enum('Male','Female') | NO | | NULL | |
| address | varchar(100) | NO | | NULL | |
| birthday | date | NO | | NULL | |
| email | varchar(50) | NO | UNI | NULL | |
| mobile_number | varchar(50) | NO | UNI | NULL | |
| password | varchar(50) | NO | | NULL | |
| tin | varchar(50) | NO | | NULL | |
| upline | varchar(15) | NO | | NULL | |
| account_created | datetime | YES | | NULL | |
+-----------------+-------------------------------------------------------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)
mysql> desc downline;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| down_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| level | int(10) unsigned | NO | | NULL | |
| fkmember | int(8) unsigned | NO | MUL | NULL | |
| downline | int(8) unsigned | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)My goal is to get all the downline member of a certain user using the fields member_id(from table member), username(from table member) level (from downline member).
Example:
When i click the button Account History it list all the transactions together the earned points(used in virtual wallet) and payouts (income). And if i click the EARNED it drilldown all my member downline. An it redirect to other page showing my member in a specified level.
Right Join Query Problem - El Forum - 11-19-2012
[eluser]solid9[/eluser]
This is not hard, this is a One to Many relationship tables.
You need to convert your DownLine table from this,
Code:
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| down_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| level | int(10) unsigned | NO | | NULL | |
| fkmember | int(8) unsigned | NO | MUL | NULL | |
| downline | int(8) unsigned | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+to this table,
Code:
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| down_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| member_id| int(8) unsigned | NO | IDX | NULL | |
| level | int(10) unsigned | NO | | NULL | |
| downline | int(8) unsigned | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+Noticed the newly added member_id column.
I don't think you need the fkmember column?
All you need is the member_id from member table and copy that into DownLine table.
The rest are easy.
Right Join Query Problem - El Forum - 11-19-2012
[eluser]solid9[/eluser]
The question is how do you compute earned points(used in virtual wallet) and payouts (income)?
Right Join Query Problem - El Forum - 11-19-2012
[eluser]rochellecanale[/eluser]
I have a specifc code for that every level it required an amount of recruited member. Thanks for the answers