OpenCart Community

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

[MOD] how to widen search on my shop

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

Page 1 of 2

[MOD] how to widen search on my shop

Posted: Sun Feb 07, 2010 7:52 pm
by gclass
Hello,

I use 1.3.2 and for this example I have a product called:
"finger optical mouse",
when I search for "finger mouse" I dont get any results
("finger optical" and "optical mouse" return the results fine)

how can I widen the search to help people find items easily?

Thx
G

Re: how to widen search on my shop

Posted: Mon Feb 08, 2010 2:29 pm
by Miguelito
Haven't crossed this situation before... have you thought about upgrading to 1.3.4 or 1.4.0? I'm using v1.4.0 and haven't seen this kinda problems.

Re: how to widen search on my shop

Posted: Mon Feb 08, 2010 3:15 pm
by gclass
hi,

I can repeat the problom on the 1.4.0 demo on demo.opencart.com so I dont think I am the only one to have this "bug"..

for example, lets take this demo product : http://demo.opencart.com/index.php?rout ... duct_id=33

title :
Samsung SyncMaster 941BW

desc: Imagine the advantages of going big without slowing down. The big.......

either one of the following search do not end with any result (even in advanced search with "search in description" marked):

search 1: "samsung 941bw"
search2 : "samsung 941"
search 3 : going without"

any idea how to work this out?

thx

Re: how to widen search on my shop

Posted: Mon Feb 08, 2010 11:21 pm
by asg333
Hi,

I tried this on my 1.4 site as well and it is a problem.

In order for the search to return results, the keywords have to be in sequence. If you have a site that sells square pegs and a user searches for:

blue square peg

then no problem. But if they search for:

blue peg

then no results.

I think that this is a big issue as it makes the search far less accurate and useful and will make it much more difficult for customers to find products.

Does anyone know how to modify the search code to correct this?

Thanks!

Re: how to widen search on my shop

Posted: Tue Feb 09, 2010 3:01 am
by Xsecrets
this is not a "bug" it's simply a limitation of the search function. You would have to goto full text searches to do what you are talking about, and it's complicated slow and resource intensive. There's a reason google made billions on search technology it's not easy.

Re: how to widen search on my shop

Posted: Tue Feb 09, 2010 5:31 am
by asg333
I am not calling this a bug, but it certainly is a huge limitation.

I disagree that correcting this would do anything to noticeably slow the search. Prestashop does not have this limitation and search is very fast. This is not a Prestshop endorsement, as I feel that overall, OpenCart is a better product. I do feel that it is an issue that needs to be addressed. Good search is extremely important to the success of any ecommerce site. Customers can't purchase what they can't find.

Comparing this to Google is a bit extreme, because, most ecommerce software does not have this limitation.

Re: how to widen search on my shop

Posted: Tue Feb 09, 2010 5:44 am
by Xsecrets
actually yeah I see where the problem is the entered text is treated as one big string instead of searching each individual word. Shouldn't be too hard to fix.

Re: how to widen search on my shop

Posted: Tue Feb 09, 2010 11:33 pm
by asg333
please share :)

Re: how to widen search on my shop

Posted: Wed Feb 10, 2010 12:15 am
by Xsecrets
well I said shouldn't be too hard I didn't say that I had done it. Basically you'll have to explode your keywords variable with a space then loop through it in the sql query.

Re: how to widen search on my shop

Posted: Wed Feb 10, 2010 12:58 am
by dbstr
Edit: catalog/model/catalog/product.php

Find (in BOTH function getProductsByKeyword() & function getTotalProductsByKeyword())

Code: Select all

            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) . "%')";
            }
Replace with:

Code: Select all

            $keywords = explode(' ', $keyword);
             
			if (!$description) {
                foreach($keywords as $keyword) {
                    $sql .= " AND pd.name LIKE '%" . $this->db->escape($keyword) . "%'";
                }
			} else {
                foreach($keywords as $keyword) {
				    $sql .= " AND (pd.name LIKE '%" . $this->db->escape($keyword) . "%' OR pd.description LIKE '%" . $this->db->escape($keyword) . "%')";
			    }
            }
And yeah, this (or something similar) should be included in the core. The current search function is quite useless.

Re: how to widen search on my shop

Posted: Wed Feb 10, 2010 1:26 am
by asg333
Works great! Thank you so much for posting this!

and if you change it just a bit to:

Code: Select all

	
$keywords = explode(' ', $keyword);
             
         if (!$description) {
                foreach($keywords as $keyword) {
				$sql .= " AND (pd.name LIKE '%" . $this->db->escape($keyword) . "%' OR p.model LIKE '%" . $this->db->escape($keyword) . "%')";;
                }
         } else {
                foreach($keywords as $keyword) {
                $sql .= " AND (pd.name LIKE '%" . $this->db->escape($keyword) . "%' OR pd.description LIKE '%" . $this->db->escape($keyword) . "%')";
             }
         }
It will search the model number as well.

Re: how to widen search on my shop

Posted: Wed Feb 10, 2010 4:43 am
by dbstr
You should probably add the model to the part after } else { aswell, if you want it to work if description is checked

Re: how to widen search on my shop

Posted: Wed Feb 10, 2010 5:21 am
by asg333
Good point. Thank you!

Re: how to widen search on my shop

Posted: Mon Feb 15, 2010 11:57 pm
by smorelli
So what would the final code look like?

Re: [MOD] how to widen search on my shop

Posted: Fri Feb 19, 2010 5:07 am
by The Alchemist
This is good to have

Re: [MOD] how to widen search on my shop

Posted: Fri Mar 26, 2010 10:35 pm
by ebeing
Here is what i did thanks to dbstr contribution

find getTotalProductsByKeyword

Code: Select all

$keywords = explode(' ', $keyword);
             
         if (!$description) {
                foreach($keywords as $keyword) {
            $sql .= " AND (pd.name LIKE '%" . $this->db->escape($keyword) . "%' OR p.model LIKE '%" . $this->db->escape($keyword) . "%')";;
                }
         } else {
                foreach($keywords as $keyword) {
                $sql .= " AND (pd.name LIKE '%" . $this->db->escape($keyword) . "%' OR p.model LIKE '%" . $this->db->escape($keyword) . "%')";
             }
         }
find getProductsByKeyword

Code: Select all

$keywords = explode(' ', $keyword);
             
         if (!$description) {
                foreach($keywords as $keyword) {
            $sql .= " AND (pd.name LIKE '%" . $this->db->escape($keyword) . "%' OR p.model LIKE '%" . $this->db->escape($keyword) . "%')";;
                }
         } else {
                foreach($keywords as $keyword) {
                $sql .= " AND (pd.name LIKE '%" . $this->db->escape($keyword) . "%' OR p.model LIKE '%" . $this->db->escape($keyword) . "%')";
             }
         }

However, since I dont really know PHP I think I have some errors. Basically the checkbox is not functioning as it should. Is it possible to search the model and the description when the checkbox is checked? How would that code look?

Re: [MOD] how to widen search on my shop

Posted: Tue Mar 30, 2010 1:45 am
by navex
Here is my refinement:

edit catalog/model/product/product.php

find public function getTotalProductsByKeyword
replace the whole function:

Code: Select all

	public function getTotalProductsByKeyword($keyword, $category_id = 0, $description = 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') . "'";
			
			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 ($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";
			
			$query = $this->db->query($sql);
		
			if ($query->num_rows) {
				return $query->row['total'];	
			} else {
				return 0;
			}
		} else {
			return 0;	
		}		
	}
with this:

Code: Select all

	public function getTotalProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $start = 0, $limit = 2000) {
		if ($keyword) {
 			$sql = "SELECT pd.name 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)) . "%' OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' ";
                }
            } else {
                foreach($keywords as $keyword) {
                   $sql .= " AND LCASE(pd.description) 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";

			if ($start < 0) {
				$start = 0;
			}

			$sql .= " LIMIT " . (int)$start . "," . (int)$limit;

			$query = $this->db->query($sql);
		
			if ($query->num_rows) {
              return sizeof($query->rows);
			} else {
				return 0;
			}
		} else {
			return 0;	
		}		
	}
find public function getProductsByKeyword(

replace:

Code: Select all

			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)) . "%')";
			}
with this:

Code: Select all

            $keywords = explode(' ', $keyword);

            if (!$description) {
                foreach($keywords as $keyword) {
                    $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' ";
                }
            } else {
                foreach($keywords as $keyword) {
                   $sql .= " AND LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' ";
                }
            }
would be happy to hear some feefback.

Re: [MOD] how to widen search on my shop

Posted: Thu Apr 01, 2010 1:12 am
by speedingorange
will either of these work with version 1.4.3?

Re: [MOD] how to widen search on my shop

Posted: Thu Jan 27, 2011 5:57 pm
by asg333
Hi,

This excellent MOD no longer works with 1.4.9

I have tried to make it work, but keep getting SQL errors.

Any of you SQL/PHP gurus out there know how to fix it? :D

It makes the search function so much better when the search phrase does not have to be consecutive words.

Thank you!

Re: [MOD] how to widen search on my shop

Posted: Thu Jan 27, 2011 6:04 pm
by asg333
Hi,

False alarm ;)

navex code above does work in 1.4.9.3

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