[ 1.4.8b] Problem with internal search engine

Quote

Post by Sub7 » Wed Aug 25, 2010 5:43 pm

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
Last edited by i2Paq on Sat Jun 11, 2011 3:53 pm, edited 2 times in total.
Reason: Code-tags added
Sub7
Newbie
Posts
8
Joined
Wed Aug 25, 2010 5:23 pm
Top

Re: Problem with internal search engine 1.4.8b

Quote

Post by Sub7 » Wed Aug 25, 2010 6:58 pm

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.
Sub7
Newbie
Posts
8
Joined
Wed Aug 25, 2010 5:23 pm
Top

Re: Problem with internal search engine 1.4.8b

Quote

Post by dbstr » Wed Aug 25, 2010 8:22 pm

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..

Request Reviews v1.0 released.

dbstr
Active Member
Posts
368
Joined
Sun Aug 30, 2009 12:20 am
Top

[SOLVED] Problem with internal search engine 1.4.8b

Quote

Post by Sub7 » Wed Aug 25, 2010 8:25 pm

Thanks dude it was exactly what i was looking for, now engine works like i needed. ;)
Sub7
Newbie
Posts
8
Joined
Wed Aug 25, 2010 5:23 pm
Top

Re: Problem with internal search engine 1.4.8b

Quote

Post by amobilept » Wed Sep 15, 2010 10:06 am

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;
    }
Last edited by i2Paq on Thu Sep 16, 2010 12:38 am, edited 2 times in total.
Reason: Code-tags changed
amobilept
Newbie
Posts
10
Joined
Wed Sep 15, 2010 7:54 am
Top

Re: Problem with internal search engine 1.4.8b

Quote

Post by Moggin » Thu Sep 16, 2010 12:26 am

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?
Moggin
Active Member
Posts
1084
Joined
Wed May 05, 2010 4:56 am
Top

Re: Problem with internal search engine 1.4.8b

Quote

Post by amobilept » Thu Sep 16, 2010 1:07 am

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
amobilept
Newbie
Posts
10
Joined
Wed Sep 15, 2010 7:54 am
Top

Re: Problem with internal search engine 1.4.8b

Quote

Post by jty » Sun Oct 03, 2010 11:11 pm

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.
jty
Active Member
Posts
1225
Joined
Sat Aug 30, 2008 8:19 am
Top

Re: Problem with internal search engine 1.4.8b

Quote

Post by jty » Sun Oct 03, 2010 11:36 pm

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
jty
Active Member
Posts
1225
Joined
Sat Aug 30, 2008 8:19 am
Top

Re: Problem with internal search engine 1.4.8b

Quote

Post by Purebeads » Mon Oct 04, 2010 3:58 am

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).
Purebeads
Active Member
Posts
444
Joined
Fri Apr 30, 2010 12:07 pm
Top

Re: Problem with internal search engine 1.4.8b

Quote

Post by gocreative » Mon Oct 04, 2010 5:54 am

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'
User avatar
gocreative
Active Member
Posts
287
Joined
Tue Jan 12, 2010 5:46 pm
Top

Re: Problem with internal search engine 1.4.8b

Quote

Post by jty » Mon Oct 04, 2010 10:22 am

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.
jty
Active Member
Posts
1225
Joined
Sat Aug 30, 2008 8:19 am
Top

Re: Problem with internal search engine 1.4.8b

Quote

Post by JAY6390 » Mon Oct 04, 2010 6:46 pm

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

Image

User avatar
JAY6390
Guru Member
Posts
5086
Joined
Wed May 26, 2010 11:47 pm
Location - United Kingdom
Top

Re: Problem with internal search engine 1.4.8b

Quote

Post by 900pixels » Wed Nov 03, 2010 11:23 am

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.
900pixels
Active Member
Posts
106
Joined
Tue Dec 08, 2009 1:36 pm
Top

Re: Problem with internal search engine 1.4.8b

Quote

Post by kaylamatthews » Sat Jun 11, 2011 10:44 am

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
kaylamatthews
New member
Posts
37
Joined
Wed Jun 01, 2011 12:30 pm
Top
Post Reply
Return to “General Support”
Who is online

Users browsing this forum: No registered users and 89 guests