Retrieve the last user only who reviewed a post

Question

What I am trying to do here is query the last user reviewed a business.

This is biz table

---

id | username | bizname | city_id ----------------------------------.......

This is review table

---

id | bisid | username | content ----------------------------------......

The code is working fine, but at the end I realized of 10 users review one business then my homepage is all about that business.

EX: Let say I have three business' on my website. MC Donald' , Jimmy Johns a KFC

I want to show the last user reviewed that business.

I want it to be like:

Mike reviewed MC Donald

Steve reviewed Jimmy Johns

Dave reviewed KFC

Instead of:

Mike reviewed MC Donald Jaycee reviewed MC Donald Anna reviewed MC Donald John reviewed MC Donald Jose reviewed MC Donald Steve reviewed Jimmy Johns Dave reviewed KFC

 Class Local extends CI_controller {

   public function city() {

      $data['reviews'] = $this->reviews->get_city_reviews($city->id,10,0);

      $this->load->view('local/index',$data);

   }

 }

The model

 Class Reviews extends MY_model {

public function get_city_reviews($city_id,$limit,$offset)
{
    $list = array();
    $this->db->select('review.*');
    $this->db->from('review');
    $this->db->join('biz','biz.id = review.bizid');
    $this->db->where('biz.city_id',$city_id);
    $this->db->order_by('review.created_at','desc');
    $this->db->limit($limit,$offset);
    $query = $this->db->get();
    foreach($query->result() as $row)
    {
        $list[] = $row;
    }
    return $list;
}

  }

Answer 1

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

public function get_city_reviews($city_id,$limit,$offset)
{
    $list = array();

    $this->db->from('review');
    $this->db->join('biz','biz.id = review.bizid');
    $this->db->where('biz.city_id',$city_id);
    $this->db->order_by('review.created_at','desc');
    $this->db->limit($limit,$offset);
            $this->db->group_by('bizid');
    $query = $this->db->get();
    foreach($query->result() as $row)
    {
        $list[] = $row;
    }
    return $list;
}