Grafity Grafity - 2 months ago 8
MySQL Question

php foreach, person appearing twice with codeigniter

So this is my database:

TABLE NAME: user_account

Account_no Firstname Lastname Username Password
1 Larry Bird larryB larrylarry
2 Magic Johnson magic magiclakers


TABLE NAME: tbl_items

Item_ID Item_Name Quantity Price Directory
1 gown 5 1000 gown.jpg
2 bridal 3 1500 bridal.png


TABLE NAME: tbl_item_availed

Item_availed_ID Item_ID Account_no Date_reserved Quantity
1 1 2 9/14/2016 2
2 2 1 9/14/2016 1
3 1 2 9/14/2016 1


SO yep this are my tables, and I already joined them 3, my problem is this:

CLIENT RESERVATION VIEW:

CLIENT DETAILS
Magic details
Bird details
Magic details


so I just want it to be like this:

CLIENT DETAILS
Magic details
Bird details


I already got the details area, the only thing is that the CLIENT's name also LOOPS, I want it to be only one, how will I do that? heres my controller code:

<div id="page-wrapper">
<table class="table table-hover">
<tr>
<th>CLIENT</th>
<th>DETAILS</th>
</tr>
<?php foreach($posts as $post) { ?>
<tr>
<td><?php echo $post->Firstname ?> <?php echo $post->Lastname; ?> </td>
<td><a href="<?php echo base_url(); ?>index.php/Pages_Controller/show_receipt/<?php echo $post->Username; ?>">details</a></td>
</tr>
<?php } ?>
</table>




The Query as requested in Model form:

public function getServicesAvailed($id){
$this->db->select('*');
$this->db->from("tbl_service_type");
$this->db->join("tbl_service_availed","tbl_service_availed.Service_ID = tbl_service_type.Service_ID");
$this->db->where('Account_ID',$id);
$this->db->where('active',1);
$query = $this->db->get();
return $query->result();
}

A J A J
Answer

Use group_by and modify the code as follow

public function getServicesAvailed($id){
    $this->db->select('*');
    $this->db->from("tbl_service_type");
    $this->db->join("tbl_service_availed","tbl_service_availed.Service_ID = tbl_service_type.Service_ID");
    $this->db->where('Account_ID',$id);
    $this->db->where('active',1);
    $this->db->group_by('tbl_item_availed.user_id');//add this line 
    $query = $this->db->get();
    return $query->result();        
}