OpenCart Community

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;   
      }
   }