CODEWITHSUNDEEP
phpmysqlcodeigniter
Abaij
Abaij

Reputation: 873

Retrieving data from two tables in CodeIgniter

I want to display some categories and their member data like this:

Fruits

Animals

But what I got is something like this:

Fruits

Fruits

Fruits

Animals

Animals

Animals

Here is what I did. In category_model:

function getCategoryMembers(){
   $this->db->join('members','members.category_id=categories.id','left');
   return $this->db->get('categories')->result();
}

In controller, I pass the member and category data.

$data['members'] = $this->category_model->getCategoryMembers();

And, in my view, I write it like this.

<?php foreach ($members as $m) : // list the members ?>
    <?php echo $m->category_name ?>
   <?php echo $m->member_name ?>
<?php endforeach; ?>

Please tell me how to display the data like I want above. I suspect that I have to modify the database query in the model, and also how I loop it, but I'm not sure how. Thanks for your help.

Upvotes: 0

Views: 73

Answers (3)

reignsly
reignsly

Reputation: 502

I think you need to modify the output of the model. In the controller try this(just modify the columns):

$rec = $this->category_model->getCategoryMembers();
$new_rec = array();
foreach ($rec as $k => $v) {
    $new_rec[$v->category][] = $v->member;
}
$data['members'] = $new_rec;

And for the view : 

<?foreach ($members as $k => $v):?>
  <h3><?php echo $k ?></h3>
    <?if($v):?>
        <?foreach ($v as $m):?>
            <p><?=$m?></p>
        <?endforeach;?>
    <?endif;?>
<?endforeach;?>

Upvotes: 1

Niranjan N Raju
Niranjan N Raju

Reputation: 11987

ON join you will get only one row from database, so change your query like this,

SELECT categories.id, GROUP_CONCAT(members.member_name SEPARATOR ', ') as mem
FROM categories 
LEFT JOIN members on members.category_id = categories.id
GROUP BY categories.id

So members will come as comma (,) separated values,

Now in view

<?php foreach ($members as $m) : // list the members ?>
     <?php echo $m->category_name ?>
     <?php $a = explode(",",$row->mem);
        foreach($a as $b) {
           echo $b;
        }
     ?>
 <?php endforeach; ?>

Hope this helps.(im not sure about column names, make sure you cross check them and use it.)

Upvotes: 1

nestedl00p
nestedl00p

Reputation: 490

<?php foreach ($members as $m) : // list the members ?>
     <?php echo $m->category_name; ?>
     <?php foreach ($m->member_name as $n) : // list the names ?>
         <?php echo $n; ?>
     <?php endforeach; ?>
<?php endforeach; ?>

Something like this?

Upvotes: 0

Related Questions