CodeIgniter Forums

Full Version: DB Query Question
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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
(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(); 
(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 Smile
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?
(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
Thank you!