• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Users and Multiple Categories

I have a users, cats (id, name) and users_cats table (user_id, cat_id) which I join together like so:

$this->db->select('users.*, users_cats.cat_id, cats.name AS cat');
$this->db->join('users_cats', 'users.id = users_cats.user_id','left');
$this->db->join('cats', 'users_cats.cat_id = cats.id','left');
$users = $this->db->get();

<?php foreach($users->result() as $user) : ?>
    <p>Category(s): &lt;?=$user->cat?&gt;</p>
&lt;?php endforeach; ?&gt;

Looks like this:

Quote:John Smith
Category(s): Plumbing

Tom Brown
Category(s): Roofing

But if a user has multiple categories assigned to him in the "users_cats" table, like:

user_id   cat_id
1         4
1         7

How can I get it to show both of them, or 3 or 4 or however many there are?


Your result set would look something like this:
1 john smith plumbing
1 john smith roofing
2 tom brown roofing

Therefore, you need to modify your view to have an inner loop when the user_id is the same as the previous one (hint, save the user_id to a temp variable and then compare it on the next loop). Your inner loop will display the categories and the outer loop will display the name. The inner loop will only break when the user_id does not match the previous user id.

Ah, you're right. But how will the inner loop get all the category names for that user if the next cat name won't come until the outer loop loops?

Can you show me an example?

Is [url="http://ellislab.com/forums/viewreply/622401/"]this message[/url] kind of what you are looking for?

[eluser]Dark Preacher[/eluser]
Maybe this kind of code will help..
Create function in one of your models, "get_ucats" for example

function get_ucats()
  $data = array();
  $q = $this->db->get('users');
  if($q->num_rows() > 0)
    foreach ($q->result_array() as $user)
      $data[$user['id']]['user'] = $user['name'];
      $qc = $this->db->where('user_id', $user['id'])->get('categorys');
      if($qc->num_rows() > 0)
        foreach ($qc->result_array() as $cat)
          $data[$user['id']]['cats'][$cat['id']] = $cat['name'];
  return $data;
and then in your view use it like this

$ucats = $this->m_common->get_ucats();
foreach($ucats as $user_id => $user)
    echo '<h1>'.anchor('user/'.$user_id, $user['user']).'</h1>';
    if(isset($user['cats']) && is_array($user['cats']))
      echo 'Category(s): ';
      $count = count($user['cats']);
      $i = 2;
      foreach($user['cats'] as $cat_id => $cat)
        echo anchor('category/'.$cat_id, $cat);
        echo ($i++ <= $count)?', ':'';
  echo '<br />';

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.