www.webdeveloper.com
Results 1 to 4 of 4

Thread: [RESOLVED] Retrieve last user reviewed a post

  1. #1
    Join Date
    Sep 2013
    Posts
    19

    resolved [RESOLVED] Retrieve last user reviewed a post

    I am using codeigniter to build a business review.
    I have two table

    Table biz
    ------------------------------------------------
    id | username | bizname | city_id | created_at
    ------------------------------------------------

    Table review
    ------------------------------------------------
    id | bizid | city_id | username | content

    So, when a user reviews a business, that business shows on home page(homepage shows only businesses with reviews)

    I want to query the last username reviewed a business and username's review next to them.
    For example:

    Sunny Bowl (3 reviews)
    [ Mike ] wrote: Simple, fresh, healthy bi-bim-bap for a very reasonable price.

    Vive Sol (4 reviews)
    [ Dave ] wrote: Great green salsa, great margaritas and such yummy food.

    The Sandwich Spot (1 review)
    [ Liza ] wrote: Dutch crunch bread is freshly baked, not too doughy.

    But unfortunately with my query I get

    Sunny Bowl (3 reviews)
    [ Mike ] wrote: Simple, fresh, healthy bi-bim-bap for a very reasonable price.
    Sunny Bowl (3 reviews)
    [ Steve ] wrote: blah blah blah.

    Vive Sol (2 reviews)
    [ Dave ] wrote: Great green salsa, great margaritas and such yummy food.
    Sunny Bowl (3 reviews)
    [ Jose ] wrote: blah blah blah.
    Vive Sol (2 reviews)
    [ Jon ] wrote: blah blah blah.

    The Sandwich Spot (1 review)
    [ Liza ] wrote: Dutch crunch bread is freshly baked, not too doughy.

    So, as you see the query fetches everything, and it looks ugly. If 10 users review Sunny Bowl, then my homepage becomes a Sunny Bowl page, I don't want that.

    Here is my query:


    public function get_city_reviews($city_id,$limit,$offset)
    {
    $list = array();
    // $this->db->distinct('bizid');
    $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;
    }

    On the model side I have:
    $data['reviews'] = $this->reviews->get_city_reviews($city->id,10,0);
    When I use GROUB BY it get exactly what I am looking for but it returns a username who posted (added) the business and I the first review for that business.

    Lets say Sunny Bowl added by Admin and it was first reviewed by Jose. My expectation is :

    Sunny Bowl (3 reviews)
    [ Jose ] wrote: blah blah blah.

    But instead I getting:

    Sunny Bowl (3 reviews)
    [ Admin ] wrote: blah blah blah.

    I hope someone will give me a clue,

    Thanks

  2. #2
    Join Date
    Sep 2013
    Posts
    19
    I missed a created_at row for review table. And I couldn't find how to edit my post after it was submited

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,176
    Quote Originally Posted by faridafardo View Post
    I missed a created_at row for review table. And I couldn't find how to edit my post after it was submited
    You can only edit a post within a limited time period of posting it. (I think it's something like 30 minutes, but I'm not sure.)

    If that means you have resolved your problem, please use the Thread Tools -> Mark Thread Resolved option at the top of this thread listing.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #4
    Join Date
    Sep 2013
    Posts
    19
    Quote Originally Posted by NogDog View Post
    You can only edit a post within a limited time period of posting it. (I think it's something like 30 minutes, but I'm not sure.)

    If that means you have resolved your problem, please use the Thread Tools -> Mark Thread Resolved option at the top of this thread listing.
    I tried to edit it right the way, I couldn't even see the edit button. I think it's important to have that instead of users poster the edited threat as a new post.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles