Pagination with sorting

[eluser]drewbee[/eluser]
Try this on for size (probably some syntax errors, its been a while since I have done a subquery in mysql

Code:

$query = $this->db->query("
        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,
            ac.eighthundrednumber_c, ac.mechanixlooprating_c, ac.extension_c, ac.petfriendly_c,
            ac.womenowned_c, ac.providearide_c,
            (SELECT
                COUNT(mrc.id_c)
             FROM
                 mech_reviews_cstm mrc
             WHERE
                 mrc.userid_c = a.id AND
                 mrc.inactive_c = 1) mech_reviews_total
        FROM
            accounts a
        JOIN
            accounts_cstm ac ON ac.id_c = a.id
        WHERE
            a.billing_address_state = ? AND
            a.billing_address_city = ?
        ORDER BY
            mech_reviews_total desc, ac.premiummember_c desc, ac.mechanixlooprating_c desc
        LIMIT 6",
        array($address_state, $address_city));

Edit: Crap, I forgot to take out the second join which is now handled by the subquery.