/** * Build a sql query - quick limits, quick order by * * @param int $ThisService Service/Module * @param string $Table sql table * @param string $CustomWhere sql where * @param string $OrderBy sql orderby * @param mixed $arVar misc vars * @return string query put together */ function fun_SQLQuery_Builder($ThisService, $Table, $ID_Name, $ID, $CustomWhere, $OrderBy, $arVar) { //init vars global $WhereQuery; global $LayID; //Comes From view.php,edit.php,... is ServiceID - makes for unique var for each service that is laid out on page //Fix up order by so something can't be snuck in there //Fix up Limit ints to, so something can't be snuck in there. /************************************************************************/ /************************************************************************/ //WhereQuery Setup - By Account - or do I need to skip if (preg_match("/listing/i", $Table)) { //skip on purpose - b/c its not narrowed down by hostid } elseif (preg_match("/gv_todo/i", $Table)) { } else { //Always narrow down by HostID - or error $arQueryAnd[] = $WhereQuery; } //ID - Narrow down by ID if ($ID) { $arQueryAnd[] = "({$ID_Name}='{$ID}')"; } //CustomWhere - drilldown by if ($CustomWhere) { $arQueryAnd[] = $CustomWhere; } /////////////////////////////////////// //Put them together //And Queries if ($arQueryAnd) { $arQueryCombine[] = fun_MySQL_Query_CombineWhere($Type = "AND", $arQueryAnd); } //Or Queries if ($arQueryOr) { $arQueryCombine[] = fun_MySQL_Query_CombineWhere($Type = "OR", $arQueryOr); } //Combine Where Queries if ($arQueryCombine) { $strQueryWhereCombine = fun_MySQL_Query_CombineWhere($Type = "AND", $arQueryCombine); } /************************************************************************/ /************************************************************************/ /************************************************************************/ /************************************************************************/ //LIMIT Query - Each service/query can be limited differently $strS1 = "S{$LayID}"; $strL1 = "L{$LayID}"; if ($_SESSION[$strS1]) { $S1 = $_SESSION[$strS1]; } if ($_SESSION[$strL1]) { $L1 = $_SESSION[$strL1]; } if ($_REQUEST[$strS1] == 0 or $_REQUEST[$strS1] != "") { $S1 = $_REQUEST[$strS1]; $_SESSION[$strS1] = $S1; } if ($_REQUEST[$strL1]) { $L1 = $_REQUEST[$strL1]; $_SESSION[$strL1] = $L1; } //LIMIT: set up the query part -> LIMIT RowStart, HowMany if ($S1 or $L1) { if (!$S1) { $S1 = 0; } if (!$L1) { $L1 = 25; } //make sure they are ints settype($L1, "integer"); settype($S1, "integer"); $HowMany = $L1 - $S1; $strLimit = " LIMIT {$S1},{$HowMany}; "; } /************************************************************************/ /************************************************************************/ /************************************************************************/ /************************************************************************/ //Build Entire Query $strQuery = "SELECT * FROM {$Table}"; //Where if ($strQueryWhereCombine) { $strQuery .= " WHERE {$strQueryWhereCombine} "; } //Order By if ($OrderBy) { $strQuery .= " {$OrderBy} "; } //Limit if ($strLimit) { $strQuery .= " {$strLimit} "; } /************************************************************************/ /************************************************************************/ unset($arVar); //debug //echo $rtnQuery; return $strQuery; }
/** * Build NWMLS listing Query * * * @return string where query */ public function fun_NWMLS_Search_Query_Listing($arVar) { $Count = $this->CleanVar($arVar['Count']); //count listings instead of show them $Status = $this->CleanVar($arVar['Status']); $PropertyType = $this->CleanVar($arVar['PropertyType']); //array $LNs = $this->CleanVar($arVar['LNs']); //array $Street = $this->CleanVar($arVar['Street']); $City = $this->CleanVar($arVar['City']); $State = $this->CleanVar($arVar['State']); $Zip = $this->CleanVar($arVar['Zip']); $PriceFrom = $this->CleanVar($arVar['PriceFrom']); $PriceTo = $this->CleanVar($arVar['PriceTo']); $HomeSizeFrom = $this->CleanVar($arVar['HomeSizeFrom']); $HomeSizeTo = $this->CleanVar($arVar['HomeSizeTo']); $LandSizeFrom = $this->CleanVar($arVar['LandSizeFrom']); $LandSizeTo = $this->CleanVar($arVar['LandSizeTo']); $Bed = $this->CleanVar($arVar['Bed']); $Bath = $this->CleanVar($arVar['Bath']); $AgentID = $this->CleanVar($arVar['AgentID']); $SearchID = $this->CleanVar($arVar['SearchID']); //used for saved queries, 1=featured $query = ""; /* debug foreach ($arVar as $Key => $Value) { if ($Value) { echo "Key($Key) Val($Value)<br>\n"; } }*/ //print_r($arVar); //ST if (is_array($Status)) { foreach ($Status as $strStatus) { if ($strStatus) { $arST[] = "(ST='{$strStatus}')"; } } $strStatus = fun_MySQL_Query_CombineWhere($Type = "OR", $arST); if ($strStatus) { $arQueryA[] = "({$strStatus})"; } } elseif ($Status) { $arQueryA[] = "(ST='{$Status}')"; } //LN if (is_array($LNs)) { foreach ($LNs as $LN) { if ($LN) { $arLN[] = "(LN='{$LN}')"; } } $strLN = fun_MySQL_Query_CombineWhere($Type = "OR", $arLN); if ($strLN) { $arQueryA[] = "({$strLN})"; } } elseif ($LNs) { $arQueryA[] = "(LN='{$LNs}')"; } //PTYP if (is_array($PropertyType)) { foreach ($PropertyType as $pType) { $arPTYP[] = "(PTYP='{$pType}')"; } $strPTYP = fun_MySQL_Query_CombineWhere($Type = "OR", $arPTYP); if ($strPTYP) { $arQueryA[] = "({$strPTYP})"; } } elseif ($PropertyType) { $arQueryA[] = "(PTYP='{$PropertyType}')"; } //ADDRESS if ($Street) { $arQueryA[] = "(STR like '%{$Street}%')"; } if ($City) { $arQueryA[] = "(CIT like '{$City}')"; } if ($Zip) { $arQueryA[] = "(ZIP like '{$Zip}%')"; } if ($PriceFrom or $PriceTo) { if ($PriceFrom) { $arPrice[] = "(LP >= '{$PriceFrom}')"; } if ($PriceTo) { $arPrice[] = "(LP <= '{$PriceTo}')"; } if ($arPrice) { $strPrice = implode(" AND ", $arPrice); } $arQueryA[] = "({$strPrice})"; } //ASF if ($HomeSizeFrom or $HomeSizeTo) { if ($HomeSizeFrom) { $arHS[] = "(ASF >= '{$HomeSizeFrom}')"; } if ($HomeSizeTo) { $arHS[] = "(ASF <= '{$HomeSizeTo}')"; } if ($arHS) { $strHS = implode(" AND ", $arHS); } $arQueryA[] = "({$strHS})"; } //LSF if ($LandSizeFrom or $LandSizeTo) { //acres $LandSizeFrom = $LandSizeFrom * 43560; $LandSizeTo = $LandSizeTo * 43560; if ($LandSizeFrom) { $arLS[] = "(LSF >= '{$LandSizeFrom}')"; } if ($LandSizeTo) { $arLS[] = "(LSF <= '{$LandSizeTo}')"; } if ($arLS) { $strLS = implode(" AND ", $arLS); } $arQueryA[] = "({$strLS})"; } //BR if ($Bed) { $arQueryA[] = "(BR >= '{$Bed}')"; } //BTH if ($Bath) { $arQueryA[] = "(BTH >= '{$Bath}')"; } //LAG, CLA if ($AgentID) { $arQueryA[] = "(LAG IN ({$AgentID}) OR CLA IN ({$AgentID}))"; } //end of logic /////////////////////////////// /////////////////////////////// if ($Count) { $strField = " COUNT(ListingID) AS Total "; //count the listings in query } else { $strField = " * "; //select all } //print_r($arQueryA); //Saved Searches if ($arVar['SearchID']) { $strSearch = $this->fun_NWMLS_Search_Query_SearchID($SearchID); if ($strSearch) { $arQueryA[] = $strSearch; } } //print_r($arQueryA); if ($arQueryA) { $strQueryA = fun_MySQL_Query_CombineWhere($Type = "AND", $arQueryA); $query .= " {$strQueryA}"; } //debug //$query .= " LIMIT 0,1000"; //Just in case //echo "class_nwmls.php $query<br><br>"; return $query; }