Login

02-27-2009, 01:54 PM

[eluser]dmorin[/eluser]
This might be pretty off, I obviously haven't tested since I don't have your db schema. I had to guess that you have an id column in the reveiws table, if not, change the count lines. This also doesn't pull back a single review so you'll have to keep that part of your code. Let me know if this doesn't work and you can't figure it out and i'll work on it some more.

Code:
&lt;?php

$this->db->select('a.id, a.name, a.billing_address_street,

        a.billing_address_city, a.billing_address_state, a.billing_address_postalcode,

        a.billing_address_country, a.website, a.description, a.eighthundrednumber_c,

        a.mechanixlooprating_c, a.extension_c, a.petfriendly_c, a.womenowned_c, a.providearide_c, 

        count(mrc.id) as num_reviews'

        );

$this->db->from('accounts a');

$this->db->join('accounts_cstm ac', 'ac.id_c = a.id');

$this->db->join('mech_reviews_cstm mrc', 'mrc.userid_c = a.id AND mrc.inactive_c = 1', 'left');

$this->db->order_by('count(mrc.id)', 'desc')

$this->db->order_by('a.premiummember_c','desc');

$this->db->where('a.billing_address_state',$state);

if(!$category==0)

{

    //if the category is not set to 0 then use category in where clause

    $this->db->where('a.category_c',$category);

}

$this->db->where('a.billing_address_city',$city);

$this->db->order_by('a.mechanixlooprating_c','desc');

$this->db->limit($num,$offset);

$query = $this->db->get();

//push results onto the array

$queryResults = $query->result();

        ?&gt;