php - CI 查询结果给出意外结果-6ren

php - CI 查询结果给出意外结果

转载作者：行者123 更新时间：2023-11-29 16:55:33

我正在使用codeigniter框架的事件类来构建查询，我的查询低于维护，输出和预期输出不同，我认为浏览器中的查询结果和phpmyadmin中的查询结果应该相同，但这里不是:

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);
}

Controller 功能是:

    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));
    }
}

输出如下:

当我在 phpmyadmin 中执行此查询时，我得到的结果顺序不同。

最佳答案

您需要使用第三个参数指定要使用的 JOIN

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.

关于php - CI 查询结果给出意外结果，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/52585641/

文章推荐： mysql - 在两列上使用 group by 获取计数

文章推荐： android - 如何唯一标识 ContactsContract.Contacts 表上的联系人

文章推荐： android - 我可以使用 URL 更改 firebase 实时数据库吗？

文章推荐： android - 如何调试 cordova 混合应用程序

UBUNTU:安装语法错误: "("意外
关闭。这个问题不符合Stack Overflow guidelines .它目前不接受答案。我们不允许在 Stack Overflow 上提出有关通用计算硬件和软件的问题。您可以编辑问题，使其成为
DB2 - 创建表抛出语法错误 "("意外
当我尝试在 db2 中创建表时，它抛出以下错误 $ db2 CREATE TABLE employee(emp_id INT NOT NULL, emp_name VARCHAR(100)) sh:
javascript - 意外 'continue'
我有: while (i < l) { if (one === two) { continue; } i++; } 但是 JSLint 说: Problem at line 1 chara
c - while 循环工作不正常/意外？
所以我有这个代码: char inputs[10] = ""; int numInputs = 0; while (numInputs < 10){ char c; printf("E
JavaScript - 意外/奇怪的构造函数
var ninja = { name: 'Ninja', say: function () { return 'I am a ' + this.name; }
c++ - 类型名称不允许/意外
我收到一个我不明白的错误，请注意，我是编码新手，所以这可能是一个简单的错误。 #include using namespace std; int main() { //Initialise Fahr
javascript - 意外 token `}`
我正在使用 javascript 和 react，由于某种原因，我收到了一个奇怪的 token 错误。这是发生错误的代码: renderNavBar() { if (!this.us
linux - 我收到错误语法错误:”(“意外”
Closed. This question is off-topic。它当前不接受答案。
c++ - 意外#else
由于某种我无法解释的原因，编译器正在输出一个错误，指出它发现了一个意外的#else 标记。这发生在文件的开头: #if defined( _USING_MFC ) #include "stda
php - 意外 = 错误信息
这个问题不太可能帮助任何 future 的访问者；它只与一个小的地理区域、一个特定的时间点或一个非常狭窄的情况有关，这些情况并不普遍适用于互联网的全局受众。为了帮助使这个问题更广泛地适用，visit
linux - bash 脚本中的语法错误 `("意外
这个问题在这里已经有了答案: Difference between sh and Bash (11 个答案) 关闭 2 年前。我正在编写一个简单的 bash 脚本，我在 XX `(' unexpe
bash - 意外 token 'fi'
关闭。这个问题是not reproducible or was caused by typos .它目前不接受答案。此问题是由拼写错误或无法再重现的问题引起的。虽然类似的问题可能是 on-topic
if-statement - 批处理脚本 if 语句 - 意外(
我在 Windows 7 上编写了一个脚本，它不断给我一个错误“(此时出乎意料。”对于以下代码 if %vardns%=="NODNS" ( netsh interface ipv4 set ad
bash - 意外 token (带有xmlstarlet
我正在尝试使用xmlstarlet（使用xpath）解析XML文件，但是出现语法错误，并且我不知道如何更正我的代码。这是我的脚本： #!/bin/bash if [ $1=="author" ];
shell - 意外 token 附近的语法错误
以下脚本旨在在目录中的所有文件上运行程序“senna”，并将每个文件的输出(保留输入文件名)写入另一个目录 for file in ./Data/in/*; do ./senna -iobta
javascript - 意外 -> userSelect.on 'valuesChanged' ->
我从 challengers.coffee 运行此代码，并收到错误 ActionView::Template::Error (SyntaxError: [stdin]:3:31:unexpected
javascript - NodeJS 意外 token "."
我在 config.db.database; 行中有语法错误(意外的标记“.”)。这是我在文件中的代码 const config = require('../config/config') const
powershell - Powershell:意外 token
这一定很明显，但是我无法使它正常工作。我正在尝试传输应该用于构建$ classKey的对象，这反过来又导致删除所需的软件(amd64或i386)。好吧，这里的代码: $name = @("softwa
grails - 意外 token : mapping
我正在使用 1.3.7 版学习 Grails，但我一直无缘无故地遇到以下语法错误: unexpected token: mapping @ line x, column y. 有一次，我通过运行“gr
syntax - 意外 token 附近的Pascal语法错误
我正在尝试找出这段Pascal代码的问题 function Factorial(n: integer): integer; begin if n = 0 then Result := 1

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

php - CI 查询结果给出意外结果