Welcome Guest, Not a member yet? Register   Sign In
DB Query Question
#1

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
Reply
#2

(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(); 
Reply
#3

(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
Reply
#4

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?
Reply
#5

(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
Reply
#6

Thank you!
Reply




Theme © iAndrew 2016 - Forum software by © MyBB