opencart v1.4.0搜索结果重复问题原因和解决方案

Code: Select all

	public function getTotalProductsByKeyword($keyword, $category_id = 0, $description = FALSE) {
		if ($keyword) {
			$sql = "SELECT DISTINCT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
			
			if (!$description) {
				$sql .= " AND pd.name LIKE '%" . $this->db->escape($keyword) . "%'";
			} else {
				$sql .= " AND (pd.name LIKE '%" . $this->db->escape($keyword) . "%' OR pd.description LIKE '%" . $this->db->escape($keyword) . "%')";
			}

			if ($category_id) {
				$data = array();
				
				$this->load->model('catalog/category');
				
				$string = rtrim($this->getPath($category_id), ',');
				
				foreach (explode(',', $string) as $category_id) {
					$data[] = "p2c.category_id = '" . (int)$category_id . "'";
				}
				
				$sql .= " AND (" . implode(" OR ", $data) . ")";
			}
			
			$sql .= " AND p.status = '1' AND p.date_available <= NOW()";
			$sql = "SELECT COUNT(*) AS total FROM (" . $sql . ") as un";

			$query = $this->db->query($sql);
		
			if ($query->num_rows) {
				return $query->row['total'];	
			} else {
				return 0;
			}
		} else {
			return 0;	
		}		
	}

Code: Select all

	public function getProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $sort = 'pd.name', $order = 'ASC', $start = 0, $limit = 20) {
		if ($keyword) {
		$sql = "SELECT p.*, pd.name AS name, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'";
			
			if (!$description) {
				$sql .= " AND pd.name LIKE '%" . $this->db->escape($keyword) . "%'";
			} else {
				$sql .= " AND (pd.name LIKE '%" . $this->db->escape($keyword) . "%' OR pd.description LIKE '%" . $this->db->escape($keyword) . "%')";
			}
			
			if ($category_id) {
				$data = array();
				
				$this->load->model('catalog/category');
				
				$string = rtrim($this->getPath($category_id), ',');
				
				foreach (explode(',', $string) as $category_id) {
					$data[] = "p2c.category_id = '" . (int)$category_id . "'";
				}
				
				$sql .= " AND (" . implode(" OR ", $data) . ")";
			}
		
			$sql .= " AND p.status = '1' AND p.date_available <= NOW()";
			$sql = "(" . $sql . ") as un";

			$sort = preg_replace('/.*\./' , 'un.' , '.' . $sort);	
			if ($sort) {
				$sql .= " ORDER BY " . $sort;
			} else {
				$sql .= " ORDER BY un.name";	
			}
			
			if ($order == 'DESC') {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}

			if ($start < 0) {
				$start = 0;
			}
		
			$sql .= " LIMIT " . (int)$start . "," . (int)$limit;
			$sql = "SELECT DISTINCT * FROM " . $sql;

			$query = $this->db->query($sql);
		
			return $query->rows;
		} else {
			return 0;	
		}
	}