CI Query result giving unexpected result

Question

I am using codeigniter framework's active class to build query, my query is below maintained and output and expected output are different, I this the query result in browser and query result in phpmyadmin should be same but here it's not:

function searchProperty($data=array()){ 

    if(!empty($data['status']) && $data['status']=="Occupied"){  
        $get_prop_ids = $this->getOccupiedProperty($data);    
    }


    extract($data);

    $this->db->select('p.*');
    $this->db->from('properties p');
    $this->db->join('prop_address pa', 'pa.prop_id = p.id','INNER');
    $this->db->join('prop_details pd', 'pd.prop_id = p.id','INNER');
    $this->db->join('property_type ptype', 'ptype.id = p.property_type_id');

    $get_post_codes = array();


    $this->db->where('p.status', 'Available');



    $prop_ids = '0';
    $post_codes_search = array();
    $search_tearms = array();
    if(empty(!$countkey)){

        $postcode_1_condition = "";
        $location_condition = "";
        $display_addr_condition = ""; 

        for($i=0; $i < $countkey; $i++){

            $var_data = trim($data['search_'.$i]);  
            $search_tearms[] = $var_data;

            $is_complete_address = $this->IsDisplayAddress($var_data);
            if(!$is_complete_address){
                $is_postCode = $this->isPostcode($var_data); 
                if($is_postCode){ 
                    $prop_ids_postcode  = $this->getPropFromProVeiw('postcode',$var_data); 
                    $post_codes_search[] = $var_data;
                    if(count($prop_ids_postcode)){
                        $prop_ids .=  ','.implode(',',$prop_ids_postcode);
                    }                    

                }else{  
                    $is_location = $this->isLocation($var_data);   
                    if($is_location){
                        $prop_ids_location  = $this->getPropFromProVeiw('location',$var_data); 
                        $prop_ids .=  ','.implode(',',$prop_ids_location);
                        //var_dump($prop_ids);      
                    }else{

                        $prop_ids_area  = $this->getPropFromProVeiw('area',$var_data);    
                        if(count($prop_ids_area)){
                            $prop_ids .=  ','.implode(',',$prop_ids_area);
                        }else{
                            $prop_ids_street  = $this->getPropFromProVeiw('street',$var_data);
                            $prop_ids .=  ','.implode(',',$prop_ids_street);
                        }
                    }
                } 

           }else{

               $prop_ids .= $is_complete_address;
           }
        } 
        $prop_ids = trim($prop_ids,',');
        $prop_ids = str_replace(",,,",",",$prop_ids);
        $prop_ids = str_replace(",,",",",$prop_ids);
        $prop_ids = explode(",",$prop_ids); //p($prop_ids);
        $prop_ids = array_unique($prop_ids);
        $additional_ids = implode(",",$prop_ids);
        $this->db->where("p.id in ($additional_ids) "); 
    }



    if(!empty($min)){
        $this->db->where('pd.price_pw >=', $min);
    }
    if(!empty($max)){
        $this->db->where('pd.price_pw <=', $max);
    }
    /*
    $bed  = (isset($bed)) ? $bed : ''; 
    if($bed != '' && $bed != 4){
        $this->db->where('pd.bedrooms', $bed);
    } elseif ($bed != '' && $bed == 4) {
        $this->db->where('pd.bedrooms >=', $bed);
    }*/

    if(!empty($bed) || $bed=='0'){ 
        $bed  = (isset($bed)) ? $bed : [];  
        if(!is_array($bed)){
            $bed = explode(',',$bed);
        }

        if(in_array(4,$bed)){
            $bed[] = 5;
            $bed[] = 6;
            $bed[] = 7;
            $bed[] = 8;
            $bed[] = 9;
            $bed[] = 10;
        }

        if(count($bed)){
            $beds = implode(',',$bed); 
            $this->db->where("pd.bedrooms in ($beds)");
        }
    }


    if(!empty($type) &&  $type == 'apartment'){
        $this->db->where('ptype.prop_category', 'Apartment');
    } elseif(!empty($type) && $type == 'house') {
        $this->db->where('ptype.prop_category', 'House');
    }
    $tag = (!empty($tag)) ? $tag : "";
    if($tag == 'furnished'){
        $this->db->where('pd.furnished_type_id in (0,1)');
    } elseif($tag == 'part-furnished') {
        $this->db->where('pd.furnished_type_id in (1)');
    } elseif($tag == 'unfurnished') {
        $this->db->where('pd.furnished_type_id', 2);
    } elseif($tag == 'furnished-unFurnished') {
        $this->db->where('pd.furnished_type_id in (0,1,2,4)');
    }

    $this->db->where('pa.display_address !=', '');         
    //$this->db->where('pa.latitude !=', '');
    //$this->db->where('pa.longitude !=', ''); 
    $this->db->where('pa.postcode_1 !=', ''); 



    if(!empty($get_prop_ids)){
        $this->db->where("pd.price > 0 or p.id in ($get_prop_ids) "); 
    }else{
        $this->db->where('pd.price >', 0); 
    }

    if(!empty($sort) && $sort == 'highest'){
        $this->db->order_by('pd.price_pw', 'desc');
    } else {
        $this->db->order_by('pd.price_pw', 'asc');
    }

    $this->db->group_by('p.agent_ref,p.id');  
    $tempdb = clone $this->db;

    if(!empty($loadMorePage)) {
        if($loadMorePage == 1){
            $this->db->limit(10, 0);
        } else {
            $page_limit = 10*($loadMorePage-1);
            $this->db->limit(10, $page_limit);
        }
    }

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



    $sql = $this->db->last_query();
    $queryCount = $tempdb->get();
    if( $query->num_rows() > 0 ){
        $count = $queryCount->num_rows();
        $result = $query->result();
        $propertyData = $result;
    } else {
        $count = 0;
        $propertyData = array();
    }
    //p($search_tearms);
    $get_post_codes = $this->getAllpostCodes($propertyData,$post_codes_search,$search_tearms);

    return array('count' => $count, 'propertyData' => $propertyData, 'query'=>$sql, 'postcodes'=>$get_post_codes);
}

The controller function is:

    public function search_post(){
    $postData = $this->post(); //p($postData);

    $this->load->model('properties_model', 'properties');
    $this->load->model('prop_details_model', 'prop_details');
    $this->load->model('prop_address_model', 'prop_address');
    $this->load->model('prop_features_model', 'prop_features');
    $this->load->model('prop_media_model', 'prop_media');
    $this->load->model('offices_model', 'office');
    $this->load->model('Main_postcodes_model', 'main_postcode');

    $loadMorePage = !empty($postData['loadMorePage']) ? $postData['loadMorePage'] : 1;

    $properties = $this->properties->searchProperty($postData); //p($properties);
    $property = $properties['propertyData'];
    $count = $properties['count'];
    foreach($property as $index => $prop){
        $property[$index]->detail = $this->prop_details->getByPropid( $prop->id, $this->role_id );
        $property[$index]->address = $this->prop_address->getByPropid( $prop->id );
        $property[$index]->features = $this->prop_features->getByPropid( $prop->id );
        $property[$index]->media = $this->prop_media->getMediaByPropid( $prop->id );
        $property[$index]->mediaEpc = $this->prop_media->getMediaEPCByPropid( $prop->id );
        $property[$index]->floorPlan = $this->prop_media->getMediaFPByPropid( $prop->id );
        $property[$index]->branch = $this->office->getByPropcoCode( $prop->branch_id );
    }

    $similerProperties = array();

    if(!empty($properties['postcodes'])){ 

            $postData['min'] = (!empty($postData['min'])) ? $postData['min'] : '';
            $postData['max'] = (!empty($postData['max'])) ? $postData['max'] : '';
            $postData['type'] = (!empty($postData['type'])) ? $postData['type'] : '';
            $postData['bed'] = (isset($postData['bed'])) ? $postData['bed'] : '';
            $postData['type'] = (!empty($postData['type'])) ? $postData['type'] : '';
            $postData['tag'] = (!empty($postData['tag'])) ? $postData['tag'] : '';
            $postData['count_to_show'] = 10 - $count;
            $postData['postcodes'] = $properties['postcodes']; 
            $postData['query'] = $properties['query']; 

            $similerProperties = $this->properties->similerPropSearch($postData );

            $similerProperties = $similerProperties['propertyData'];

            foreach($similerProperties as $index => $prop){
                $similerProperties[$index]->detail = $this->prop_details->getByPropid( $prop->id, $this->role_id );
                $similerProperties[$index]->address = $this->prop_address->getByPropid( $prop->id );
                $similerProperties[$index]->features = $this->prop_features->getByPropid( $prop->id );
                $similerProperties[$index]->media = $this->prop_media->getMediaByPropid( $prop->id );
                $similerProperties[$index]->mediaEpc = $this->prop_media->getMediaEPCByPropid( $prop->id );
                $similerProperties[$index]->floorPlan = $this->prop_media->getMediaFPByPropid( $prop->id );
                $similerProperties[$index]->branch = $this->office->getByPropcoCode( $prop->branch_id );
            }

    }

    if($loadMorePage > 1){
        $properties = $property;
        $myCarouselIndex = 10*($loadMorePage-1);

        $this->_view_data['properties'] = $properties;
        $this->_view_data['myCarouselIndex'] = $myCarouselIndex;
        $this->_view_data['query'] = $postData['query'];
        $this->_view_data['loadMorePage'] = $loadMorePage;

        $propertyHtml = $this->load->view('frontend/property/loadmore-search', $this->_view_data, true);
        echo $propertyHtml;
    } else {
        $response = array('type' => 'success', 'query'=>$properties['query'], 'message' => "", 'data' => $property, 'count' => $count, 'similerProperties' => $similerProperties); 
        $this->output->set_content_type('application/json')->set_output(json_encode($response));
    }
}

The Output is below:

Where as when I execute this query in phpmyadmin I get different result order is different.

Hello, "The Output is bellow" and Phpmyadmin (image) same out. where is output different? — Praveen Kumar, Oct 01 '18 at 06:44
Ohh yes I add it now sorry it was there but I miss that line — Sanjay Gupta, Oct 01 '18 at 06:57
@SanjayGupta...You can simply debug your code.By doing following things.`1. print last executed query and execute the same query in your phpmyadmin and check if the results are same.2.if the results are same print array of results in Model's function as well as in controller's function just after calling the function.` — sady, Oct 01 '18 at 07:59

Rp9 · Answer 1 · 2018-10-01T07:08:49.137

0

You need to specify what JOIN you want to use, by using third parameter

If you need a specific type of JOIN you can specify it via the third parameter of the function. Options are: left, right, outer, inner, left outer, and right outer.

$this->db->join('prop_address pa', 'pa.prop_id = p.id','INNER');

In MySQL writing JOIN unqualified implies INNER JOIN. In other words the INNER in INNER JOIN is optional.

edited Oct 01 '18 at 07:08

answered Oct 01 '18 at 06:49

Rp9

1,955
2
23
31

It's not join issue. I am getting different result in browser just after execution of my query and same query give different result in phpmyadmin – Sanjay Gupta Oct 01 '18 at 07:15
@SanjayGupta just try inner join in ci active query – Rp9 Oct 01 '18 at 07:17
@SanjayGupta..Have you printed last executed query?.$this->db->last_query(); – sady Oct 01 '18 at 07:18
Yes I do so after that I get the query and that query giving other result which is unexpected, it must be same, u can see that in "The Output is below:" part of my question – Sanjay Gupta Oct 01 '18 at 07:20
@SanjayGupta..try printing the array of result in the model's function itself if you are doing it in controller. – sady Oct 01 '18 at 07:23
@SanjayGupta if possible try with inner / left join for both and check result – Rp9 Oct 01 '18 at 07:24
@sady Yes I get different result in model and controller.. do u know the reason. I this this must be the same? – Sanjay Gupta Oct 01 '18 at 07:31
@SanjayGupta...Can you please paste the code from controller where you are calling this model's function? – sady Oct 01 '18 at 07:32
@sady :$this->load->model('properties_model', 'properties'); $this->load->model('Main_postcodes_model', 'main_postcode'); $properties = $this->properties->searchProperty($postData); echo "
```
"; print_r($properties);
```
– Sanjay Gupta Oct 01 '18 at 07:37
I experienced this before and using a left join solved it for me. Trying this wont hurt: $this->db->join('prop_address pa', 'pa.prop_id = p.id','left'); $this->db->join('prop_details pd', 'pd.prop_id = p.id','left'); $this->db->join('property_type ptype', 'ptype.id = p.property_type_id','left'); – Jaycee Daily Oct 01 '18 at 07:40
@SanjayGupta...in Model's function the queries are hardcoded so i guess there is no need pass $postData to the searchProperty function. – sady Oct 01 '18 at 07:41
@sady but it will not affect the result at all. – Sanjay Gupta Oct 01 '18 at 07:45
@SanjayGupta...please post the code of controller function and model function in your question.As it doesn't show properly in comments. – sady Oct 01 '18 at 07:47
@sady Ok I posted my question in detail now please have a look – Sanjay Gupta Oct 01 '18 at 07:53

score 0 · Answer 2 · answered Oct 01 '18 at 07:51

MySQL did not evaluate the sort order at the time it was building the aggregation(i.e GROUP BY).

In fact lets say id(i.e 1375) came from the very first row that MySQL saw for id.In different circumstances the same query on the same data set could return a different value there.

If you need most recent id from any particular Table use MAX while selection for aggregation.

$this->db->select('MAX(p.id) id');// specify other required columns
$this->db->from('properties p');
$this->db->join('prop_address pa', 'pa.prop_id = p.id');
$this->db->join('prop_details pd', 'pd.prop_id = p.id');
$this->db->join('property_type ptype', 'ptype.id = p.property_type_id');
$this->db->where('p.status', 'Available');
$this->db->where('pa.display_address !=', ''); 
$this->db->where('pa.postcode_1 !=', '');    
$this->db->where('pd.price >', 0); 
$this->db->order_by('pd.price_pw', 'asc');
$this->db->group_by('p.agent_ref,p.id');      
$this->db->limit(10, 10);    
$query = $this->db->get();
$result = $query->result(); 
$sql = $this->db->last_query();
return array('propertyData' => $result, 'query'=>$sql);

CI Query result giving unexpected result

2 Answers2