OpenCart Community

OpenCart Community Forum - Discuss shopping cart and e-commerce solutions.
https://forum.opencart.com/

[ 1.4.8b] Problem with internal search engine

https://forum.opencart.com/viewtopic.php?t=19032

Page 1 of 1

[ 1.4.8b] Problem with internal search engine

Posted: Wed Aug 25, 2010 5:43 pm
by Sub7
Hi to all,
i have a problem with my new shop which use opencart 1.4.8b

lets say i have 2 categories and inside thoose categories i have placed 10 products each.
This is one example product: Pure cotton t-shirt brand adidas size xl
So let's imagine a customer land in my shop and he wants to buy an adidas t-shirt he can browse the categories or use the search engine: adidas t-shirt --> no result!
The search engine is working like that on my e-commerce and thats a problem:


Search 1: pure cotton = OK RESULT (Pure cotton t-shirt brand adidas size xl)
Search 2: cotton t-shirt = OK RESULT (Pure cotton t-shirt brand adidas size xl)
Search 3: cotton = OK RESULT (Pure cotton t-shirt brand adidas size xl)
Search 4: t-shirt adidas = NO RESULT! (Pure cotton t-shirt brand adidas size xl)

The internal search engine is looking for 1 word inside the title i guess or for 2 or 3 consecutive words inside the entire title (WRITTEN EXACTLY) but is not working for random searches, for example one can search t-shirt xl or adidas xl or cotton size xl, who knows..
Somebody told me that's possible to modify the source code putting this code inside getProductsByKeyword funciton:


code to insert:

Code: Select all

$keyword = str_replace(" ", "%", $keyword);[/b]
 
the function:

Code: Select all

public function getProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20)
{

if ($keyword) {

$sql = "SELECT *, pd.name AS name, p.image, 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_store p2s ON (p.product_id = p2s.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 p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'";
$keyword = str_replace(" ", "%", $keyword);
if (!$description) {
$sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
} else {
$sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
}

if (!$model) {
$sql .= ")";
} else {
$sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
}
 
but i am not a php expert and i dont know where to put the code.
Also maybe theres other way to fix this without modify the code?
Would some1 be so kind to give an eye on this pls, my customers runs away if i dont fix that

thank you very much and sorry for my low quality english :crazy:

Sub

Re: Problem with internal search engine 1.4.8b

Posted: Wed Aug 25, 2010 6:58 pm
by Sub7
Heres a live example: http://www.kingpinclothingco.com/

you see in the main page an article named Southern Cali Tattooed Babe - Black
try to search only southern black which gives no results.
instead try search southern or southern cali and you will get a result.
We cannot know what customers ask to the search engine.

Re: Problem with internal search engine 1.4.8b

Posted: Wed Aug 25, 2010 8:22 pm
by dbstr
1. Open catalog/model/catalog/product.php

2. Find

Code: Select all

public function getProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20) { 
3. Replace THE ENTIRE FUNCTION by

Code: Select all

    public function getProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20) {
        if ($keyword) {
                        
            $sql = "SELECT *, pd.name AS name, p.image, 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_store p2s ON (p.product_id = p2s.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 p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'";
            
            $keywords = explode(' ', $keyword);
            
            if (!$description) {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
                }
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'"; 
                }
            }
            
            if (!$model) {
                $sql .= "";
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
                }
            }
            
            if ($category_id) {
                $data = array();
                
                $this->load->model('catalog/category');
                
                $string = rtrim($this->getPath($category_id), ',');
                
                foreach (explode(',', $string) as $category_id) {
                    $data[] = "category_id = '" . (int)$category_id . "'";
                }
                
                $sql .= " AND p.product_id IN (SELECT product_id FROM " . DB_PREFIX . "product_to_category WHERE " . implode(" OR ", $data) . ")";
            }
        
            $sql .= " AND p.status = '1' AND p.date_available <= NOW() GROUP BY p.product_id";
        
            $sort_data = array(
                'pd.name',
                'p.sort_order',
                'special',
                'rating',
                'p.price',
                'p.model'
            );
                
            if (in_array($sort, $sort_data)) {
                if ($sort == 'pd.name' || $sort == 'p.model') {
                    $sql .= " ORDER BY LCASE(" . $sort . ")";
                } else {
                    $sql .= " ORDER BY " . $sort;
                }
            } else {
                $sql .= " ORDER BY p.sort_order";    
            }
            
            if ($order == 'DESC') {
                $sql .= " DESC";
            } else {
                $sql .= " ASC";
            }

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

            $query = $this->db->query($sql);
                        
            return $query->rows;
        }
        return 0;    
    } 
4. Find

Code: Select all

public function getTotalProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE) {  
5. Replace THE ENTIRE FUNCTION by

Code: Select all

    public function getTotalProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE) {
        if ($keyword) {
            $sql = "SELECT COUNT(*) AS total 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_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
            
            $keywords = explode(' ', $keyword); 
            
            if (!$description) {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
                }
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
                }
            }

            if (!$model) {
                $sql .= "";
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
                }
            }

            if ($category_id) {
                $data = array();
                
                $this->load->model('catalog/category');
                
                $string = rtrim($this->getPath($category_id), ',');
                
                foreach (explode(',', $string) as $category_id) {
                    $data[] = "category_id = '" . (int)$category_id . "'";
                }
                
                $sql .= " AND p.product_id IN (SELECT product_id FROM " . DB_PREFIX . "product_to_category WHERE " . implode(" OR ", $data) . ")";
            }
            
            $sql .= " AND p.status = '1' AND p.date_available <= NOW()";
            
            $query = $this->db->query($sql);
        
            if ($query->num_rows) {
                return $query->row['total'];
            } else {
                return 0;
            }
        }
        return 0;
    } 
And let me know if it works..

[SOLVED] Problem with internal search engine 1.4.8b

Posted: Wed Aug 25, 2010 8:25 pm
by Sub7
Thanks dude it was exactly what i was looking for, now engine works like i needed. ;)

Re: Problem with internal search engine 1.4.8b

Posted: Wed Sep 15, 2010 10:06 am
by amobilept
Hi dbstr,

Followed your instructions but cannot get it to work. I could not find the exact code for step 4 in the file (product.php) but found one with the same function name and replaced it. I am on 1.4.8b

Here is my complete product.php file: http://rapidshare.com/files/419110135/product.php

Here is the code replaced my product.php with.... can't figure out what I'm doing wrong....

Code: Select all

public function getProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20) {
        if ($keyword) {
                        
            $sql = "SELECT *, pd.name AS name, p.image, 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_store p2s ON (p.product_id = p2s.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 p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'";
            
            $keywords = explode(' ', $keyword);
            
            if (!$description) {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
                }
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'"; 
                }
            }
            
            if (!$model) {
                $sql .= "";
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
                }
            }
            
            if ($category_id) {
                $data = array();
                
                $this->load->model('catalog/category');
                
                $string = rtrim($this->getPath($category_id), ',');
                
                foreach (explode(',', $string) as $category_id) {
                    $data[] = "category_id = '" . (int)$category_id . "'";
                }
                
                $sql .= " AND p.product_id IN (SELECT product_id FROM " . DB_PREFIX . "product_to_category WHERE " . implode(" OR ", $data) . ")";
            }
        
            $sql .= " AND p.status = '1' AND p.date_available <= NOW() GROUP BY p.product_id";
        
            $sort_data = array(
                'pd.name',
                'p.sort_order',
                'special',
                'rating',
                'p.price',
                'p.model'
            );
                
            if (in_array($sort, $sort_data)) {
                if ($sort == 'pd.name' || $sort == 'p.model') {
                    $sql .= " ORDER BY LCASE(" . $sort . ")";
                } else {
                    $sql .= " ORDER BY " . $sort;
                }
            } else {
                $sql .= " ORDER BY p.sort_order";    
            }
            
            if ($order == 'DESC') {
                $sql .= " DESC";
            } else {
                $sql .= " ASC";
            }

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

            $query = $this->db->query($sql);
                        
            return $query->rows;
        }
        return 0;    
    } 
	
    public function getTotalProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE) {
        if ($keyword) {
            $sql = "SELECT COUNT(*) AS total 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_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
            
            $keywords = explode(' ', $keyword); 
            
            if (!$description) {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
                }
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
                }
            }

            if (!$model) {
                $sql .= "";
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
                }
            }

            if ($category_id) {
                $data = array();
                
                $this->load->model('catalog/category');
                
                $string = rtrim($this->getPath($category_id), ',');
                
                foreach (explode(',', $string) as $category_id) {
                    $data[] = "category_id = '" . (int)$category_id . "'";
                }
                
                $sql .= " AND p.product_id IN (SELECT product_id FROM " . DB_PREFIX . "product_to_category WHERE " . implode(" OR ", $data) . ")";
            }
            
            $sql .= " AND p.status = '1' AND p.date_available <= NOW()";
            
            $query = $this->db->query($sql);
        
            if ($query->num_rows) {
                return $query->row['total'];
            } else {
                return 0;
            }
        }
        return 0;
    }

Re: Problem with internal search engine 1.4.8b

Posted: Thu Sep 16, 2010 12:26 am
by Moggin
Sub7 wrote:.....The internal search engine is looking for 1 word inside the title i guess or for 2 or 3 consecutive words inside the entire title (WRITTEN EXACTLY) but is not working for random searches, for example one can search t-shirt xl or adidas xl or cotton size xl, who knows..
I was noticing the same thing. One keyword finds the product, more than one gives 'no results' unless it is a consecutive keyword.

For example, on the demo store, if you search for canon the camera comes up: same for canon eos. If you put in canon 5d, even if you search description - no results.

I tried this fix and it seemed to rectify this issue perfectly. Many thanks dbstr. :good:
Has anyone else implemented this fix? What did you think?

Re: Problem with internal search engine 1.4.8b

Posted: Thu Sep 16, 2010 1:07 am
by amobilept
amobilept wrote:Hi dbstr,

Followed your instructions but cannot get it to work. I could not find the exact code for step 4 in the file (product.php) but found one with the same function name and replaced it. I am on 1.4.8b

Here is my complete product.php file: http://rapidshare.com/files/419110135/product.php

Here is the code replaced my product.php with.... can't figure out what I'm doing wrong....

Code: Select all

public function getProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20) {
        if ($keyword) {
                        
            $sql = "SELECT *, pd.name AS name, p.image, 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_store p2s ON (p.product_id = p2s.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 p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'";
            
            $keywords = explode(' ', $keyword);
            
            if (!$description) {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
                }
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'"; 
                }
            }
            
            if (!$model) {
                $sql .= "";
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
                }
            }
            
            if ($category_id) {
                $data = array();
                
                $this->load->model('catalog/category');
                
                $string = rtrim($this->getPath($category_id), ',');
                
                foreach (explode(',', $string) as $category_id) {
                    $data[] = "category_id = '" . (int)$category_id . "'";
                }
                
                $sql .= " AND p.product_id IN (SELECT product_id FROM " . DB_PREFIX . "product_to_category WHERE " . implode(" OR ", $data) . ")";
            }
        
            $sql .= " AND p.status = '1' AND p.date_available <= NOW() GROUP BY p.product_id";
        
            $sort_data = array(
                'pd.name',
                'p.sort_order',
                'special',
                'rating',
                'p.price',
                'p.model'
            );
                
            if (in_array($sort, $sort_data)) {
                if ($sort == 'pd.name' || $sort == 'p.model') {
                    $sql .= " ORDER BY LCASE(" . $sort . ")";
                } else {
                    $sql .= " ORDER BY " . $sort;
                }
            } else {
                $sql .= " ORDER BY p.sort_order";    
            }
            
            if ($order == 'DESC') {
                $sql .= " DESC";
            } else {
                $sql .= " ASC";
            }

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

            $query = $this->db->query($sql);
                        
            return $query->rows;
        }
        return 0;    
    } 
	
    public function getTotalProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE) {
        if ($keyword) {
            $sql = "SELECT COUNT(*) AS total 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_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
            
            $keywords = explode(' ', $keyword); 
            
            if (!$description) {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
                }
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
                }
            }

            if (!$model) {
                $sql .= "";
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
                }
            }

            if ($category_id) {
                $data = array();
                
                $this->load->model('catalog/category');
                
                $string = rtrim($this->getPath($category_id), ',');
                
                foreach (explode(',', $string) as $category_id) {
                    $data[] = "category_id = '" . (int)$category_id . "'";
                }
                
                $sql .= " AND p.product_id IN (SELECT product_id FROM " . DB_PREFIX . "product_to_category WHERE " . implode(" OR ", $data) . ")";
            }
            
            $sql .= " AND p.status = '1' AND p.date_available <= NOW()";
            
            $query = $this->db->query($sql);
        
            if ($query->num_rows) {
                return $query->row['total'];
            } else {
                return 0;
            }
        }
        return 0;
    }

works now, was uploading my edited files to my backup server.... duh.... but......

UPDATE: was testing a little more and now it gives me too many results

if I type in Nokia 7100 the results include everything that I have listed with nokia or 7100, does anyone know what i have to change in the code so that the search results only include items that have both words searched for?

Thanks,
Paulo

Re: Problem with internal search engine 1.4.8b

Posted: Sun Oct 03, 2010 11:11 pm
by jty
Purebeads, I don't know what I did but I have what I want. I think I simply copied and pasted the code above. I couldn't use the download file as it is somehow corrupted so I copied the code per instructions above. I think. I was confused and frustrated so maybe I did something else but I'm pretty sure I didn't cos I can't think when I have tantrums.

What I have now is a search that -
say I have blue rondels and blue elephants.
If I search blue, I get blue elephants and blue rondels
If I search blue rondels, I only get blue rondels or blue big rondels. I don;t get blue elephants and everything else that's blue and everything rondel like the post above.

I'm not able to share my code because I have other mods in there and it will break in standard open cart.

What you can do is comment out the existing code with the /* and */ and drop in the code above. If it doesn't work, then quickly undo it.

dbstr, thank-you a thousand times for this thread.

Re: Problem with internal search engine 1.4.8b

Posted: Sun Oct 03, 2010 11:36 pm
by jty
amobilept wrote:UPDATE: was testing a little more and now it gives me too many results

if I type in Nokia 7100 the results include everything that I have listed with nokia or 7100, does anyone know what i have to change in the code so that the search results only include items that have both words searched for?
OIC, with a little bit more testing, this happens in a 'description' search but it doesn't happen in an ordinary search and I haven't tested a model search.

Still, I'm happy. Ordinary search is my default search

Re: Problem with internal search engine 1.4.8b

Posted: Mon Oct 04, 2010 3:58 am
by Purebeads
I guess I'll try it. I'll simply back up everything and restore the modified files if it doesn't work.

When you say that you get too many results for a "description" search, you mean that you specifically have to check the "Search in product description" box. Otherwise, it doesn't happen. If that's the case, I can live with that. Most customers don't go into Advanced Search anyway. However, I'm tempted to simply wait for Daniel to fix the whole thing (IF, as I said, Ajax search is imminent).

Re: Problem with internal search engine 1.4.8b

Posted: Mon Oct 04, 2010 5:54 am
by gocreative
For me, the current OC search is ok (not that I use it much), but it needs additional search options. For example:

Search all products added between date X to date Y
Search all products between price X and price Y
Checkboxes to search for products by one or more manufacturers
Checkbox for 'only products with photos'
Checkbox for 'only products in stock'
Checkbox for 'only products with positive (or no) ratings'

Re: Problem with internal search engine 1.4.8b

Posted: Mon Oct 04, 2010 10:22 am
by jty
Purebeads wrote:When you say that you get too many results for a "description" search, you mean that you specifically have to check the "Search in product description" box. Otherwise, it doesn't happen.
yes, the buyer has to specifically check the description box.
If they search without description and model, the mod works the way we want ie searches forward and backwards and skips words
However, I'm tempted to simply wait for Daniel to fix the whole thing (IF, as I said, Ajax search is imminent).
In your instance, this is the best option, just wait, I'm not waiting cos I have already waited 2 years. I'm also not upgrading to 1.5 for at least 6 months, or never as the first release of software (any software) is always buggy. I also have too many mods to port and I don't want to do it again.

I wasn't able to fix the mod for description and i also suspect that searching in model is broken. There might be/is an extra ')' that needs to be removed somewhere. but I didn't check it out thoroughly as I don't use model and it was also already 4am and I needed to sleep.

Re: Problem with internal search engine 1.4.8b

Posted: Mon Oct 04, 2010 6:46 pm
by JAY6390
The reason for the problem in the search when using description/model is due to the way the query is run. You've got it so that it just checks it's got one of the keywords

Re: Problem with internal search engine 1.4.8b

Posted: Wed Nov 03, 2010 11:23 am
by 900pixels
I think the other problem is that it does not sort the results in order of relevence (i.e number of terms matched).

If it listed items that matched both 'nokia' and '7100' first and then listed items that matched one term (say just 'nokia') afterwards, then the customer should find what they are looking for immediately.

Re: Problem with internal search engine 1.4.8b

Posted: Sat Jun 11, 2011 10:44 am
by kaylamatthews
dbstr wrote:1. Open catalog/model/catalog/product.php

2. Find

Code: Select all

public function getProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20) { 
3. Replace THE ENTIRE FUNCTION by

Code: Select all

    public function getProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20) {
        if ($keyword) {
                        
            $sql = "SELECT *, pd.name AS name, p.image, 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_store p2s ON (p.product_id = p2s.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 p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'";
            
            $keywords = explode(' ', $keyword);
            
            if (!$description) {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
                }
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'"; 
                }
            }
            
            if (!$model) {
                $sql .= "";
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
                }
            }
            
            if ($category_id) {
                $data = array();
                
                $this->load->model('catalog/category');
                
                $string = rtrim($this->getPath($category_id), ',');
                
                foreach (explode(',', $string) as $category_id) {
                    $data[] = "category_id = '" . (int)$category_id . "'";
                }
                
                $sql .= " AND p.product_id IN (SELECT product_id FROM " . DB_PREFIX . "product_to_category WHERE " . implode(" OR ", $data) . ")";
            }
        
            $sql .= " AND p.status = '1' AND p.date_available <= NOW() GROUP BY p.product_id";
        
            $sort_data = array(
                'pd.name',
                'p.sort_order',
                'special',
                'rating',
                'p.price',
                'p.model'
            );
                
            if (in_array($sort, $sort_data)) {
                if ($sort == 'pd.name' || $sort == 'p.model') {
                    $sql .= " ORDER BY LCASE(" . $sort . ")";
                } else {
                    $sql .= " ORDER BY " . $sort;
                }
            } else {
                $sql .= " ORDER BY p.sort_order";    
            }
            
            if ($order == 'DESC') {
                $sql .= " DESC";
            } else {
                $sql .= " ASC";
            }

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

            $query = $this->db->query($sql);
                        
            return $query->rows;
        }
        return 0;    
    } 
4. Find

Code: Select all

public function getTotalProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE) {  
5. Replace THE ENTIRE FUNCTION by

Code: Select all

    public function getTotalProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE) {
        if ($keyword) {
            $sql = "SELECT COUNT(*) AS total 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_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
            
            $keywords = explode(' ', $keyword); 
            
            if (!$description) {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
                }
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
                }
            }

            if (!$model) {
                $sql .= "";
            } else {
                foreach($keywords as $keyword) {
                    $sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
                }
            }

            if ($category_id) {
                $data = array();
                
                $this->load->model('catalog/category');
                
                $string = rtrim($this->getPath($category_id), ',');
                
                foreach (explode(',', $string) as $category_id) {
                    $data[] = "category_id = '" . (int)$category_id . "'";
                }
                
                $sql .= " AND p.product_id IN (SELECT product_id FROM " . DB_PREFIX . "product_to_category WHERE " . implode(" OR ", $data) . ")";
            }
            
            $sql .= " AND p.status = '1' AND p.date_available <= NOW()";
            
            $query = $this->db->query($sql);
        
            if ($query->num_rows) {
                return $query->row['total'];
            } else {
                return 0;
            }
        }
        return 0;
    } 
And let me know if it works..
Can somebody please show me how to do this in Version 1.5.0? IN the catalog/model/catalog/product.php file I cannot find the getProductsbyKeyword function at all :(

Many thanks!
Kayla
All times are UTC+08:00
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/