Need help with a bridge table.

[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

[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();

[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
      )
   )
)

[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
      )
   )
)

[/quote]

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.

[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.

[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?

[eluser]jcavard[/eluser]
right on my friend.
'shorthand name' = table alias

[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.