Pagination with sorting

[eluser]spyro[/eluser]
I started writing a query but it ended up crashing my mysql server.

Here is the original

Code:

function getMechs($cityState,$num,$offset,$category=0,$withReview=1)
    {
        //this function takes a city state pair separated by a space
        $CI =& get_instance();
        $CI->load->model('datamanager_model');
        //echo "getMechs Category: " . $category;
        $CI->load->model('reviews_model');

        $cityState = parseCityState($cityState);

        $city = $cityState['city'];
        $state = $cityState['state'];

        $this->db->select('id,name,billing_address_street,
                billing_address_city,billing_address_state,billing_address_postalcode,
                billing_address_country,website,description,eighthundrednumber_c,
                mechanixlooprating_c,extension_c,petfriendly_c,womenowned_c,providearide_c'
                );
        $this->db->from('accounts');
        $this->db->join('accounts_cstm', 'accounts_cstm.id_c = accounts.id');
        $this->db->order_by('premiummember_c','desc');
        $this->db->where('billing_address_state',$state);

        if(!$category==0)
        {
            //if the category is not set to 0 then use category in where clause
            $this->db->where('category_c',$category);
        }

        $this->db->where('billing_address_city',$city);
        $this->db->order_by('mechanixlooprating_c','desc');
        $this->db->limit($num,$offset);
        $query = $this->db->get();
        
        //push results onto the array
        $queryResults = $query->result();
        
        if(!empty($queryResults))
        {
            //convert the object to an array to stay consistant with other models
            $queryResultsAr = object_2_array($queryResults);
            
            
            foreach($queryResultsAr as $key => $result) {
               $queryResultsAr[$key]['reviewsTotal'] = $CI->reviews_model->getReviewsTotal($result['id']);
            }
            
            //print_r($queryResultsAr[0]);
            //this will return a single review with each mech result
            if($withReview == 1)
            {
                //get a review for each
                foreach($queryResultsAr as $results)
                {
                    $review = $CI->reviews_model->get_reviews($results['id'],1,0);
                    
                    if(isset($review[0]['description']) && $review[0]['description'] != "")
                    {

                        $review = str_split($review[0]['description'],75);
                        $review = $review[0] . "...";

                    }
                    else
                    {
                        $review = "";
                    }

                    $results['reviewDesc'] = $review;
                    
                    $data[] = $results;
                    
                }
                //print_r($data);
                $data = vsort($data,$id="reviewsTotal",false);
                return $data;

            }
            else
            {
                return $queryResultsAr;
            }

        }
        else
        {
            return 0;
        }
    }

Then I added a sub select which caused it to become unresponsive.

the get_reviews function is to get one related review to put in the list view.
the getReviewsTotal call get the total reviews as named.

I added this: (SELECT count(*) from mech_reviews_cstm where userid_c = accounts.id and inactive_c = 1) as review_count'
to the first select statement to replace getReviewsTotal and the server became unresponsive.

Also, you may notice the object_to_array function. I just found the result_array() functionality and haven't substituted yet.