Foх Опубликовано 25 июля, 2014 Жалоба Поделиться Опубликовано 25 июля, 2014 Доброго времени суток)Помогите настроить фильтр с шаблона, установил шаблон adaptiv на симплу 2.3.6.Там есть доработка фильтра. и вот проблема заключается в том,что он выдает ошибки. Если настраиваю фильтр,то почему то сбивается поиск, пишет ошибку Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'vts-24'@'localhost' (using password: NO) in /var/www/vts-24/data/www/vts-24.ru/api/Products.php on line 202 вот кучу таких ошибок. изменял файл в папке api ,products.php взял отрезки из файлов которые отвечают за фильтры Исходник с версии 2.2.* require_once('Simpla.php'); class Products extends Simpla { /** * Функция возвращает товары * Возможные значения фильтра: * id - id товара или их массив * category_id - id категории или их массив * brand_id - id бренда или их массив * page - текущая страница, integer * limit - количество товаров на странице, integer * sort - порядок товаров, возможные значения: position(по умолчанию), name, price * keyword - ключевое слово для поиска * features - фильтр по свойствам товара, массив (id свойства => значение свойства) */ public function get_products($filter = array()) { // По умолчанию $limit = 100; $page = 1; $category_id_filter = ''; $brand_id_filter = ''; $product_id_filter = ''; $features_filter = ''; $keyword_filter = ''; $visible_filter = ''; $visible_filter = ''; $is_featured_filter = ''; $discounted_filter = ''; $in_stock_filter = ''; $group_by = ''; $order = 'p.position DESC'; if(isset($filter['limit'])) $limit = max(1, intval($filter['limit'])); if(isset($filter['page'])) $page = max(1, intval($filter['page'])); $sql_limit = $this->db->placehold(' LIMIT ?, ? ', ($page-1)*$limit, $limit); if(!empty($filter['id'])) $product_id_filter = $this->db->placehold('AND p.id in(?@)', (array)$filter['id']); if(!empty($filter['category_id'])) { $category_id_filter = $this->db->placehold('INNER JOIN __products_categories pc ON pc.product_id = p.id AND pc.category_id in(?@)', (array)$filter['category_id']); $group_by = "GROUP BY p.id"; } if(!empty($filter['brand_id'])) $brand_id_filter = $this->db->placehold('AND p.brand_id in(?@)', (array)$filter['brand_id']); if(!empty($filter['featured'])) $is_featured_filter = $this->db->placehold('AND p.featured=?', intval($filter['featured'])); if(!empty($filter['order'])) $order = $filter['order']; if(!empty($filter['discounted'])) $discounted_filter = $this->db->placehold('AND (SELECT 1 FROM __variants pv WHERE pv.product_id=p.id AND pv.compare_price>0 LIMIT 1) = ?', intval($filter['discounted'])); if(!empty($filter['in_stock'])) $in_stock_filter = $this->db->placehold('AND (SELECT 1 FROM __variants pv WHERE pv.product_id=p.id AND pv.price>0 AND (pv.stock IS NULL OR pv.stock>0) LIMIT 1) = ?', intval($filter['in_stock'])); if(!empty($filter['visible'])) $visible_filter = $this->db->placehold('AND p.visible=?', intval($filter['visible'])); if(!empty($filter['sort'])) switch ($filter['sort']) { // По умолчанию case 'position': $order = 'p.position DESC'; break; // по имени от А до Я case 'name_asc': $order = 'p.name'; break; // по имени от Я до А case 'name_desc': $order = 'p.name DESC'; break; case 'created': $order = 'p.created DESC'; break; // по цене Низкие > Высокие case 'price_asc': $order = '(SELECT pv.price FROM __variants pv WHERE (pv.stock IS NULL OR pv.stock>0) AND p.id = pv.product_id AND pv.position=(SELECT MIN(position) FROM __variants WHERE (stock>0 OR stock IS NULL) AND product_id=p.id LIMIT 1) LIMIT 1)'; break; // по цене Высокие < Низкие case 'price_desc': $order = '(SELECT pv.price FROM __variants pv WHERE (pv.stock IS NULL OR pv.stock>0) AND p.id = pv.product_id AND pv.position=(SELECT MIN(position) FROM __variants WHERE (stock>0 OR stock IS NULL) AND product_id=p.id LIMIT 1) LIMIT 1) DESC'; break; case 'rating': $order = 'p.rating DESC,p.position'; break; } if(!empty($filter['keyword'])) { $keywords = explode(' ', $filter['keyword']); foreach($keywords as $keyword) $keyword_filter .= $this->db->placehold('AND (p.name LIKE "%'.mysql_real_escape_string(trim($keyword)).'%" OR p.meta_keywords LIKE "%'.mysql_real_escape_string(trim($keyword)).'%") '); } if(!empty($filter['features']) && !empty($filter['features'])) foreach($filter['features'] as $feature=>$value) $features_filter .= $this->db->placehold('AND p.id in (SELECT product_id FROM __options WHERE feature_id=? AND value in (?@) ) ', $feature, $value); $query = "SELECT p.id, p.url, p.brand_id, p.name, p.annotation, p.body, p.rating, p.votes, p.position, p.created as created, p.visible, p.featured, p.meta_title, p.meta_keywords, p.meta_description, b.name as brand, b.url as brand_url FROM __products p $category_id_filter LEFT JOIN __brands b ON p.brand_id = b.id WHERE 1 $product_id_filter $brand_id_filter $features_filter $keyword_filter $is_featured_filter $discounted_filter $in_stock_filter $visible_filter ".(isset($filter['minCurr']) ? "AND (SELECT 1 FROM __variants pv WHERE pv.product_id=p.id AND pv.price>='".$filter['minCurr']."' LIMIT 1) = 1" : '')." ".(isset($filter['maxCurr']) ? "AND (SELECT 1 FROM __variants pv WHERE pv.product_id=p.id AND pv.price<='".$filter['maxCurr']."' LIMIT 1) = 1" : '')." $group_by ORDER BY $order $sql_limit"; $query = $this->db->placehold($query); $this->db->query($query); return $this->db->results(); } /** * Функция возвращает количество товаров * Возможные значения фильтра: * category_id - id категории или их массив * brand_id - id бренда или их массив * keyword - ключевое слово для поиска * features - фильтр по свойствам товара, массив (id свойства => значение свойства) */ public function count_products($filter = array(), $type='') { $category_id_filter = ''; $brand_id_filter = ''; $keyword_filter = ''; $visible_filter = ''; $is_featured_filter = ''; $discounted_filter = ''; $features_filter = ''; if(!empty($filter['category_id'])) $category_id_filter = $this->db->placehold('INNER JOIN __products_categories pc ON pc.product_id = p.id AND pc.category_id in(?@)', (array)$filter['category_id']); if(!empty($filter['brand_id'])) $brand_id_filter = $this->db->placehold('AND p.brand_id in(?@)', (array)$filter['brand_id']); if(isset($filter['keyword'])) { $keywords = explode(' ', $filter['keyword']); foreach($keywords as $keyword) $keyword_filter .= $this->db->placehold('AND (p.name LIKE "%'.mysql_real_escape_string(trim($keyword)).'%" OR p.meta_keywords LIKE "%'.mysql_real_escape_string(trim($keyword)).'%") '); } if(!empty($filter['featured'])) $is_featured_filter = $this->db->placehold('AND p.featured=?', intval($filter['featured'])); if(!empty($filter['discounted'])) $discounted_filter = $this->db->placehold('AND (SELECT 1 FROM __variants pv WHERE pv.product_id=p.id AND pv.compare_price>0 LIMIT 1) = ?', intval($filter['discounted'])); if(!empty($filter['visible'])) $visible_filter = $this->db->placehold('AND p.visible=?', intval($filter['visible'])); if(!empty($filter['features']) && !empty($filter['features'])) foreach($filter['features'] as $feature=>$value) $features_filter .= $this->db->placehold('AND p.id in (SELECT product_id FROM __options WHERE feature_id=? AND value in (?@) ) ', $feature, $value); $query = "SELECT count(distinct p.id) as count ".($type=='all' ? ', min(v.price) minCost, max(v.price) maxCost ' : '')." FROM __products AS p ".($type=='all' ? 'INNER JOIN __variants v ON (v.product_id = p.id) ' : '')." $category_id_filter WHERE 1 $brand_id_filter $keyword_filter $is_featured_filter $discounted_filter $visible_filter $features_filter ".(isset($filter['minCurr']) ? "AND (SELECT 1 FROM __variants pv WHERE pv.product_id=p.id AND pv.price>='".$filter['minCurr']."' LIMIT 1) = 1" : '')." ".(isset($filter['maxCurr']) ? "AND (SELECT 1 FROM __variants pv WHERE pv.product_id=p.id AND pv.price<='".$filter['maxCurr']."' LIMIT 1) = 1" : '')." "; $this->db->query($query); if($type=='all') return $this->db->result(); else return $this->db->result('count'); } /** * Функция возвращает товар по id * @param $id * @retval object */ public function get_product($id) { if(is_int($id)) $filter = $this->db->placehold('p.id = ?', $id); else $filter = $this->db->placehold('p.url = ?', $id); $query = "SELECT DISTINCT p.id, p.url, p.brand_id, p.name, p.annotation, p.body, p.rating, p.votes, p.position, p.created as created, p.visible, p.featured, p.meta_title, p.meta_keywords, p.meta_description FROM __products AS p LEFT JOIN __brands b ON p.brand_id = b.id WHERE $filter GROUP BY p.id LIMIT 1"; $this->db->query($query); $product = $this->db->result(); return $product; } public function update_product($id, $product) { $query = $this->db->placehold("UPDATE __products SET ?% WHERE id in (?@) LIMIT ?", $product, (array)$id, count((array)$id)); if($this->db->query($query)) return $id; else return false; } и нынешний файл require_once('Simpla.php'); class Products extends Simpla { /** * Функция возвращает товары * Возможные значения фильтра: * id - id товара или их массив * category_id - id категории или их массив * brand_id - id бренда или их массив * page - текущая страница, integer * limit - количество товаров на странице, integer * sort - порядок товаров, возможные значения: position(по умолчанию), name, price * keyword - ключевое слово для поиска * features - фильтр по свойствам товара, массив (id свойства => значение свойства) */ public function get_products($filter = array()) { // По умолчанию $limit = 100; $page = 1; $category_id_filter = ''; $brand_id_filter = ''; $product_id_filter = ''; $features_filter = ''; $keyword_filter = ''; $visible_filter = ''; $is_featured_filter = ''; $discounted_filter = ''; $in_stock_filter = ''; $group_by = ''; $order = 'p.position DESC'; if(isset($filter['limit'])) $limit = max(1, intval($filter['limit'])); if(isset($filter['page'])) $page = max(1, intval($filter['page'])); $sql_limit = $this->db->placehold(' LIMIT ?, ? ', ($page-1)*$limit, $limit); if(!empty($filter['id'])) $product_id_filter = $this->db->placehold('AND p.id in(?@)', (array)$filter['id']); if(!empty($filter['category_id'])) { $category_id_filter = $this->db->placehold('INNER JOIN __products_categories pc ON pc.product_id = p.id AND pc.category_id in(?@)', (array)$filter['category_id']); $group_by = "GROUP BY p.id"; } if(!empty($filter['brand_id'])) $brand_id_filter = $this->db->placehold('AND p.brand_id in(?@)', (array)$filter['brand_id']); if(isset($filter['featured'])) $is_featured_filter = $this->db->placehold('AND p.featured=?', intval($filter['featured'])); if(isset($filter['discounted'])) $discounted_filter = $this->db->placehold('AND (SELECT 1 FROM __variants pv WHERE pv.product_id=p.id AND pv.compare_price>0 LIMIT 1) = ?', intval($filter['discounted'])); if(isset($filter['in_stock'])) $in_stock_filter = $this->db->placehold('AND (SELECT count(*)>0 FROM __variants pv WHERE pv.product_id=p.id AND pv.price>0 AND (pv.stock IS NULL OR pv.stock>0) LIMIT 1) = ?', intval($filter['in_stock'])); if(isset($filter['visible'])) $visible_filter = $this->db->placehold('AND p.visible=?', intval($filter['visible'])); if(!empty($filter['sort'])) switch ($filter['sort']) { case 'position': $order = 'p.position DESC'; break; case 'name': $order = 'p.name'; break; case 'created': $order = 'p.created DESC'; break; case 'price': //$order = 'pv.price IS NULL, pv.price=0, pv.price'; $order = '(SELECT -pv.price FROM __variants pv WHERE (pv.stock IS NULL OR pv.stock>0) AND p.id = pv.product_id AND pv.position=(SELECT MIN(position) FROM __variants WHERE (stock>0 OR stock IS NULL) AND product_id=p.id LIMIT 1) LIMIT 1) DESC'; break; } if(!empty($filter['keyword'])) { $keywords = explode(' ', $filter['keyword']); foreach($keywords as $keyword) { $kw = $this->db->escape(trim($keyword)); $keyword_filter .= $this->db->placehold("AND (p.name LIKE '%$kw%' OR p.meta_keywords LIKE '%$kw%' OR p.id in (SELECT product_id FROM __variants WHERE sku LIKE '%$kw%'))"); } } if(!empty($filter['features']) && !empty($filter['features'])) foreach($filter['features'] as $feature=>$value) $features_filter .= $this->db->placehold('AND p.id in (SELECT product_id FROM __options WHERE feature_id=? AND value=? ) ', $feature, $value); $query = "SELECT p.id, p.url, p.brand_id, p.name, p.annotation, p.body, p.position, p.created as created, p.visible, p.featured, p.meta_title, p.meta_keywords, p.meta_description, b.name as brand, b.url as brand_url FROM __products p $category_id_filter LEFT JOIN __brands b ON p.brand_id = b.id WHERE 1 $product_id_filter $brand_id_filter $features_filter $keyword_filter $is_featured_filter $discounted_filter $in_stock_filter $visible_filter $group_by ORDER BY $order $sql_limit"; $this->db->query($query); return $this->db->results(); } /** * Функция возвращает количество товаров * Возможные значения фильтра: * category_id - id категории или их массив * brand_id - id бренда или их массив * keyword - ключевое слово для поиска * features - фильтр по свойствам товара, массив (id свойства => значение свойства) */ public function count_products($filter = array()) { $category_id_filter = ''; $brand_id_filter = ''; $product_id_filter = ''; $keyword_filter = ''; $visible_filter = ''; $is_featured_filter = ''; $in_stock_filter = ''; $discounted_filter = ''; $features_filter = ''; if(!empty($filter['category_id'])) $category_id_filter = $this->db->placehold('INNER JOIN __products_categories pc ON pc.product_id = p.id AND pc.category_id in(?@)', (array)$filter['category_id']); if(!empty($filter['brand_id'])) $brand_id_filter = $this->db->placehold('AND p.brand_id in(?@)', (array)$filter['brand_id']); if(!empty($filter['id'])) $product_id_filter = $this->db->placehold('AND p.id in(?@)', (array)$filter['id']); if(isset($filter['keyword'])) { $keywords = explode(' ', $filter['keyword']); foreach($keywords as $keyword) $keyword_filter .= $this->db->placehold('AND (p.name LIKE "%'.$this->db->escape(trim($keyword)).'%" OR p.meta_keywords LIKE "%'.$this->db->escape(trim($keyword)).'%") '); } if(isset($filter['featured'])) $is_featured_filter = $this->db->placehold('AND p.featured=?', intval($filter['featured'])); if(isset($filter['in_stock'])) $in_stock_filter = $this->db->placehold('AND (SELECT count(*)>0 FROM __variants pv WHERE pv.product_id=p.id AND pv.price>0 AND (pv.stock IS NULL OR pv.stock>0) LIMIT 1) = ?', intval($filter['in_stock'])); if(isset($filter['discounted'])) $discounted_filter = $this->db->placehold('AND (SELECT 1 FROM __variants pv WHERE pv.product_id=p.id AND pv.compare_price>0 LIMIT 1) = ?', intval($filter['discounted'])); if(isset($filter['visible'])) $visible_filter = $this->db->placehold('AND p.visible=?', intval($filter['visible'])); if(!empty($filter['features']) && !empty($filter['features'])) foreach($filter['features'] as $feature=>$value) $features_filter .= $this->db->placehold('AND p.id in (SELECT product_id FROM __options WHERE feature_id=? AND value=? ) ', $feature, $value); $query = "SELECT count(distinct p.id) as count FROM __products AS p $category_id_filter WHERE 1 $brand_id_filter $product_id_filter $keyword_filter $is_featured_filter $in_stock_filter $discounted_filter $visible_filter $features_filter "; $this->db->query($query); return $this->db->result('count'); } /** * Функция возвращает товар по id * @param $id * @retval object */ public function get_product($id) { if(is_int($id)) $filter = $this->db->placehold('p.id = ?', $id); else $filter = $this->db->placehold('p.url = ?', $id); $query = "SELECT DISTINCT p.id, p.url, p.brand_id, p.name, p.annotation, p.body, p.position, p.created as created, p.visible, p.featured, p.meta_title, p.meta_keywords, p.meta_description FROM __products AS p LEFT JOIN __brands b ON p.brand_id = b.id WHERE $filter GROUP BY p.id LIMIT 1"; $this->db->query($query); $product = $this->db->result(); return $product; } public function update_product($id, $product) { $query = $this->db->placehold("UPDATE __products SET ?% WHERE id in (?@) LIMIT ?", $product, (array)$id, count((array)$id)); if($this->db->query($query)) return $id; else return false; } Цитата Ссылка на сообщение Поделиться на другие сайты
mart Опубликовано 26 июля, 2014 Жалоба Поделиться Опубликовано 26 июля, 2014 Это все замечательно, что Вы приложили весь код, НО...У Вас проблема аналогичная http://forum.simplacms.ru/topic/6854-%D0%BF%D0%BE%D0%B8%D1%81%D0%BA-%D0%BF%D0%BE-%D0%B0%D1%80%D1%82%D0%B8%D0%BA%D1%83%D0%BB%D1%83-224/?do=findComment&comment=59382Т.е. Вам надо найти и заменить все mysql_real_escape_string на $this->db->escape. Цитата Ссылка на сообщение Поделиться на другие сайты
Foх Опубликовано 26 июля, 2014 Автор Жалоба Поделиться Опубликовано 26 июля, 2014 Это все замечательно, что Вы приложили весь код, НО...У Вас проблема аналогичная http://forum.simplacms.ru/topic/6854-%D0%BF%D0%BE%D0%B8%D1%81%D0%BA-%D0%BF%D0%BE-%D0%B0%D1%80%D1%82%D0%B8%D0%BA%D1%83%D0%BB%D1%83-224/?do=findComment&comment=59382Т.е. Вам надо найти и заменить все mysql_real_escape_string на $this->db->escape. спасибо!решили все мои проблемы Цитата Ссылка на сообщение Поделиться на другие сайты
Рекомендуемые сообщения
Присоединяйтесь к обсуждению
Вы можете написать сейчас и зарегистрироваться позже. Если у вас есть аккаунт, авторизуйтесь, чтобы опубликовать от имени своего аккаунта.