php - MySQL query code using the OR for two different fields with Web Service

280

Normally I can figure this out, but the format looks a bit different then what I am used to. I have a web service using php with CodeIgnighter. The function looks like this:

    function getCurrentSales($office_id)
    {
        $CI =& get_instance();
        $CI->load->model("properties");

        //Get the properties
        $where = array('field_SaleOfficeCode'=>$office_id);
        $where = array('field_ListOfficeCode'=>$office_id);
        $result = $CI->properties->getCurrentSales($where); 
        $properties = $result->result_array();

        foreach($properties as $p){
            //Get property images
            $where = array('ListingKey'=>$p['UniqueKey']);
            $property_arr[] = $p;
        }   

            return  $property_arr;
    }

    $this->nusoap_server->service(file_get_contents("php://input"));
} 

What I am trying to do is return rows wherefield_ListOfficeCode orfield_ListOfficeCode have the value of$office_id. As it is now, only whenfield_ListOfficeCode=>$office_id, not when one or the other equals the value.

Models Method Code:

function getCurrentSales($where = null) {


    $this->db->select('field_ListingKey as UniqueKey,
                        field_LocaleListingStatus as Status,
                        field_CloseDate as ClosingDate,
                        field_ContractDate as ContractDate,
                        field_ListingID as MLS,
                        TRIM("#VARIES" from field_FullStreetAddress) as StreetAddress,
                        field_ListPrice as Price,
                        field_ListOfficeCode as BrokerID,
                        if(field_Beds != "", field_Beds, 0) as NumBeds,
                        if(field_BathsFull != "", field_BathsFull, 0) as NumFullBaths,
                        if(field_BathsHalf != "", field_BathsHalf, 0) as NumHalfBaths,
                        field_InternetRemarks as PropertyRemarks,
                        field_ListOfficeName as ListingOfficeName,
                        field_ListPicture3URL as MainPhoto,
                        CONCAT(field_ListAgentNickname, " ", field_ListAgentLastName, " ", (if(field_ListAgentNameSuffix = "NULL", field_ListAgentNameSuffix, ""))) as ListingAgent,
                        CONCAT(field_SaleAgentNickname, " ", field_SaleAgentLastName, " ", (if(field_SaleAgentNameSuffix = "NULL", field_SaleAgentNameSuffix, ""))) as SalesAgent,
                        CONCAT(field_City, ", ", field_State, " ", field_PostalCode) as AreaAddress', false);


    $this->db->from('TABLE_2');



    if($where != null){
        $this->db->where($where, false);
        $this->db->where('YEAR(field_ContractDate) = YEAR(NOW())', NULL, false);
        $this->db->where('MONTH(field_ContractDate) = MONTH(NOW())', NULL, false);
    }


    $this->db->order_by('ContractDate', 'desc'); //get random row
    //$this->db->limit($limit);


    $query = $this->db->get();
    return $query;     
} 
427

Answer

Solution:

I'm not familiar with CodeIgniter, but I would suspect your problem is with these two lines

$where = array('field_SaleOfficeCode'=>$office_id);
$where = array('field_ListOfficeCode'=>$office_id);

You aren't actually building up an array of where clauses, but rather overriding the first assignment with the second, which explains why you are only getting results that matchfield_ListOfficeCode == $office_id

Depending on how CodeIgniter expects these where clauses to be formed, I would assume it should be something like

$where = array(
    array('field_SaleOfficeCode'=>$office_id),
    array('field_ListOfficeCode'=>$office_id)
)

Or from a quick look at the documentation

$where = array(
    'field_SaleOfficeCode'=>$office_id,
    'field_ListOfficeCode'=>$office_id
)

And then in yourgetCurrentSales function, it should be

$this->db->or_where($where)

People are also looking for solutions to the problem: jquery - get content back to javascript from PHP file in JSON format

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.