CODEWITHSUNDEEP
phpcodeignitercodeigniter-2codeigniter-datamapper
Che Jug
Che Jug

Reputation: 417

Retrieve the last user only who reviewed a post

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;
}

  }

Upvotes: 0

Views: 45

Answers (2)

Rocks
Rocks

Reputation: 511

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;
}

Upvotes: 1

iamthereplicant
iamthereplicant

Reputation: 232

Try selecting DISTINCT bisid when querying the reviews table

EDIT: Here's an example http://www.w3schools.com/sql/sql_distinct.asp

Upvotes: 0

Related Questions