+- 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: Need help with a bridge table. (/showthread.php?tid=21239)
Need help with a bridge table. - El Forum - 08-04-2009
[eluser]acreek[/eluser]
Hi all,
I am trying to create a database that will tell me what users have access to specific services. I set my tables up like so
customers:
id name
1 Brian
2 Nate
services:
id name
1 Phone
2 Internet
services_bundles:
customer_id service_id
1 1
1 2
2 1
So right now I have a query set up that will pull a 'name' LIKE. What I am trying to accomplish is when you search a name it will pull the service_bundles information and display what services are available to that customer.
I'm using code igniter for the first time, and having a heck of a time with active record. Could someone help me out please?
Thanks
Need help with a bridge table. - El Forum - 08-04-2009
[eluser]jcavard[/eluser]
[quote author="acreek" date="1249412077"]Hi all,
I am trying to create a database that will tell me what users have access to specific services. I set my tables up like so
customers:
id name
1 Brian
2 Nate
services:
id name
1 Phone
2 Internet
services_bundles:
customer_id service_id
1 1
1 2
2 1
So right now I have a query set up that will pull a 'name' LIKE. What I am trying to accomplish is when you search a name it will pull the service_bundles information and display what services are available to that customer.
I'm using code igniter for the first time, and having a heck of a time with active record. Could someone help me out please?
Thanks[/quote]
something like this?
Code:
$this->db->select('services.name');
$this->db->from('services_bundles');
$this->db->join('customers', 'customers.id = services_bundles.customer_id');
$this->db->join('services', 'services.id = services_bundles.service_id');
$this->db->where('customers.name', 'Brian');
$this->db->get();Need help with a bridge table. - El Forum - 08-04-2009
[eluser]darkhouse[/eluser]
You're on the right track, at least your database looks to be setup correctly. Try this in your model:
Code:
function search($name){
$this->db->select('c.*, s.name service_name');
$this->db->join('services_bundles sb', 'sb.customer_id c.id');
$this->db->join('services s', 's.id = sb.service_id');
$this->db->like('c.name', $name);
$query = $this->db->get('customers c');
return $query->result();
}So, if you run $your_model->search('Brian'), that will give you 2 records:
Code:
Array(
[0] => Object(
id => 1,
name => Brian,
service_name => Phone
),
[1] => Object(
id => 1,
name => Brian,
service_name => Internet
)
)Or, you could do it in 2 queries, like this:
Code:
function search($name){
$this->db->like('name', $name);
$query = $this->db->get('customers', 1);
$row = $query->row();
$this->db->join('services s', 's.id = sb.service_id');
$this->db->where('sb.customer_id', $result->id);
$query = $this->db->get('services_bundles sb');
$row->services = $query->result();
return $row;
}So that would give you just 1 row, but an array of that customer's services also, like this:
Code:
Object(
id => 1,
name => Brian,
services => Array(
[0] => Object(
id => 1,
name => Phone
),
[1] => Object(
id => 2,
name => Internet
)
)
)Need help with a bridge table. - El Forum - 08-05-2009
[eluser]acreek[/eluser]
[quote author="darkhouse" date="1249415611"]You're on the right track, at least your database looks to be setup correctly. Try this in your model:
Or, you could do it in 2 queries, like this:
Code:
function search($name){
$this->db->like('name', $name);
$query = $this->db->get('customers', 1);
$row = $query->row();
$this->db->join('services s', 's.id = sb.service_id');
$this->db->where('sb.customer_id', $result->id);
$query = $this->db->get('services_bundles sb');
$row->services = $query->result();
return $row;
}So that would give you just 1 row, but an array of that customer's services also, like this:
Code:
Object(
id => 1,
name => Brian,
services => Array(
[0] => Object(
id => 1,
name => Phone
),
[1] => Object(
id => 2,
name => Internet
)
)
)Thanks for the responses guys. I got back to working on the project today, and hit one small snag I'm not sure how to fix.
I am getting an error
Undefined variable: result
from the line:
Code:
$this->db->where('sb.customer_id', $result->id);I'm not learned enough in SQL to know why, or even how to fix this. Any thoughts? Thanks.
Need help with a bridge table. - El Forum - 08-05-2009
[eluser]darkhouse[/eluser]
Ah that's a typo on my part, change that line to this:
Code:
$this->db->where('sb.customer_id', $row->id);Sorry about that.
Need help with a bridge table. - El Forum - 08-05-2009
[eluser]acreek[/eluser]
Ah gotcha.
One quick question for you. When you do something like 'services s' in the JOIN is that a way of giving the 'services' table a shorthand name s?
Need help with a bridge table. - El Forum - 08-05-2009
[eluser]jcavard[/eluser]
right on my friend.
'shorthand name' = table alias
Need help with a bridge table. - El Forum - 08-07-2009
[eluser]darkhouse[/eluser]
Yeah it's just a lot easier to write than the full table names, especially when you're joining a lot of different tables and working with a lot of different fields to get a certain result, some table names can get pretty long, aliases just make things faster to work with.