+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: DB Query Question (/showthread.php?tid=72324)
DB Query Question - soneax - 12-05-2018
Hi,
I have a MySQL table with: user.id, user.name, user.refer_id
I would like to query the DB once passing the user.id value, but i would like to use the same query to retrieve the refer name based on his ID too. It is possible?
Basically i want to get the user based on his ID and consequently the 'Refer Name' of that user based on the refer_id.
Table Example:
ID Name Refer_id
1 John 2
2 Alex 3
3 Peter 1
RE: DB Query Question - neuron - 12-05-2018
(12-05-2018, 03:15 AM)soneax Wrote: ID Name Refer_id
1 John 2
2 Alex 3
3 Peter 1
PHP Code:
$this->db->select('u.*, u2.name as referrer_name')
->from('user u')
->join('user u2', 'u2.id = u.refer_id', 'left')
->where('u.id', $user_id);
$result = $this->db->get();
RE: DB Query Question - soneax - 12-05-2018
(12-05-2018, 03:54 AM)neuron Wrote:(12-05-2018, 03:15 AM)soneax Wrote: ID Name Refer_id
1 John 2
2 Alex 3
3 Peter 1
PHP Code:$this->db->select('u.*, u2.name as referrer_name')
->from('user u')
->join('user u2', 'u2.id = u.refer_id', 'left')
->where('u.id', $user_id);
$result = $this->db->get();
Got it! Appreciated
RE: DB Query Question - soneax - 12-05-2018
Can you answer me another question?
If i want to query 2 tables, i join the second one based on ID for example, how can i loop the second table results with foreach if there is no child arrays for this table results?
RE: DB Query Question - neuron - 12-05-2018
(12-05-2018, 05:40 AM)soneax Wrote: Can you answer me another question?
If i want to query 2 tables, i join the second one based on ID for example, how can i loop the second table results with foreach if there is no child arrays for this table results?
I did not understand what you mean by "if there is no child arrays for this table results".
In the above query I used LEFT join. If you want to return all the results from second table ('users u2' in this case) use right join.
Also learn Joining tables, that is the advantage of RDBMS
RE: DB Query Question - soneax - 12-06-2018
Thank you!