/** * Search items * * @param string $qStr : Query String * @param string $catStr : Category Name/ Category ID * @param string $locStr : Location / Location ID * @return array */ public function search($qStr, $catStr, $locStr, $userID) { global $db; $tradeCatIns = new BuckysTradeCategory(); $tradeLocationIns = new BuckysCountry(); //Get category data $catData = null; if (is_numeric($catStr)) { $catData = $tradeCatIns->getCategoryByID($catStr); } else { $catData = $tradeCatIns->getCategoryByName($catStr); } //Get Location data $locationData = null; if (is_numeric($locStr)) { $locationData = $tradeLocationIns->getCountryById($locStr); } else { $locationData = $tradeLocationIns->getCountryByName($locStr); } //Make Where condition $whereCondList = array(); if (isset($qStr) && $qStr != '') { $qStr = addslashes($qStr); $whereCondList[] = sprintf(" MATCH (i.title, i.subtitle, i.description) AGAINST ('%s' IN BOOLEAN MODE)", $qStr); } if (isset($catData)) { $whereCondList[] = 'i.catID=' . $catData['catID']; } else { if ($catStr != '') { return null; } } if (isset($locationData)) { $whereCondList[] = 'i.locationID=' . $locationData['countryID']; } if (isset($userID) && is_numeric($userID)) { $whereCondList[] = 'i.userID=' . $userID; } //Valid items $avaiableTime = date('Y-m-d H:i:s', time() - TRADE_ITEM_LIFETIME * 3600 * 24); $whereCondList[] = "i.createdDate >='" . $avaiableTime . "'"; $whereCondList[] = 'i.status=' . BuckysTradeItem::STATUS_ITEM_ACTIVE; $whereCond = ' WHERE ' . implode(' AND ', $whereCondList); $whereCond .= ' GROUP BY i.itemID '; $query = sprintf("SELECT i.*, (SELECT COUNT(*) FROM %s AS o WHERE i.itemID=o.targetItemID AND o.status=%d) AS offer, u.firstName, u.lastName, tu.totalRating, tu.positiveRating \n FROM %s AS i \n LEFT JOIN %s AS tu ON i.userID=tu.userID \n LEFT JOIN %s AS u ON i.userID=u.userID \n ", TABLE_TRADE_OFFERS, BuckysTradeOffer::STATUS_OFFER_ACTIVE, TABLE_TRADE_ITEMS, TABLE_TRADE_USERS, TABLE_USERS); $query = $db->prepare($query . $whereCond); $data = $db->getResultsArray($query); return $data; }
/** * Search products * * @param string $qStr : Query String * @param string $catStr : Category Name/ Category ID * @param string $locStr : Location / Location ID * @return array */ public function search($qStr, $catStr, $locStr, $userID) { global $db; $catIns = new BuckysShopCategory(); $locationIns = new BuckysCountry(); //Get category data $catData = null; if (is_numeric($catStr)) { $catData = $catIns->getCategoryByID($catStr); } else { $catData = $catIns->getCategoryByName($catStr); } //Get Location data $locationData = null; if (is_numeric($locStr)) { $locationData = $locationIns->getCountryById($locStr); } else { $locationData = $locationIns->getCountryByName($locStr); } //Make Where condition $whereCondList = []; if (isset($qStr) && $qStr != '') { $qStr = addslashes($qStr); $whereCondList[] = sprintf(" MATCH (p.title, p.subtitle, p.description) AGAINST ('%s' IN BOOLEAN MODE)", $qStr); } if (isset($catData)) { $whereCondList[] = 'p.catID=' . $catData['catID']; } else { if ($catStr != '') { return null; } } if (isset($locationData)) { $whereCondList[] = 'p.locationID=' . $locationData['countryID']; } if (isset($userID) && is_numeric($userID)) { $whereCondList[] = 'p.userID=' . $userID; } //Valid items $avaiableTime = date('Y-m-d H:i:s'); $whereCondList[] = " (p.expiryDate >='" . $avaiableTime . "' OR p.listingDuration=-1) "; $whereCondList[] = 'p.status=' . BuckysShopProduct::STATUS_ACTIVE; $whereCond = ' WHERE ' . implode(' AND ', $whereCondList); $whereCond .= ' GROUP BY p.productID '; $query = sprintf("SELECT p.*, u.firstName, u.lastName, tu.totalRating, tu.positiveRating \n FROM %s AS p \n LEFT JOIN %s AS tu ON p.userID=tu.userID \n LEFT JOIN %s AS u ON p.userID=u.userID \n ", TABLE_SHOP_PRODUCTS, TABLE_USERS_RATING, TABLE_USERS); $query = $db->prepare($query . $whereCond); $data = $db->getResultsArray($query); return $data; }