[MOD] how to widen search on my shop

Quote

Post by gclass » Sun Feb 07, 2010 7:52 pm

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
Last edited by i2Paq on Tue Feb 16, 2010 12:50 am, edited 1 time in total.
Reason: Topic moved
gclass
New member
Posts
55
Joined
Sat Oct 03, 2009 1:21 am
Top

Re: how to widen search on my shop

Quote

Post by Miguelito » Mon Feb 08, 2010 2:29 pm

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.

The Finnish OpenCart Forum
"Real programmers don't document. If it was hard to write, it should be hard to understand."

Miguelito
Active Member
Posts
796
Joined
Sun Jan 10, 2010 10:11 pm
Top

Re: how to widen search on my shop

Quote

Post by gclass » Mon Feb 08, 2010 3:15 pm

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
gclass
New member
Posts
55
Joined
Sat Oct 03, 2009 1:21 am
Top

Re: how to widen search on my shop

Quote

Post by asg333 » Mon Feb 08, 2010 11:21 pm

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!
asg333
New member
Posts
30
Joined
Mon Jan 11, 2010 12:33 am
Top

Re: how to widen search on my shop

Quote

Post by Xsecrets » Tue Feb 09, 2010 3:01 am

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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter

Xsecrets
Guru Member
Posts
5017
Joined
Sun Oct 25, 2009 3:51 am
Location - FL US
Top

Re: how to widen search on my shop

Quote

Post by asg333 » Tue Feb 09, 2010 5:31 am

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.
asg333
New member
Posts
30
Joined
Mon Jan 11, 2010 12:33 am
Top

Re: how to widen search on my shop

Quote

Post by Xsecrets » Tue Feb 09, 2010 5:44 am

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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter

Xsecrets
Guru Member
Posts
5017
Joined
Sun Oct 25, 2009 3:51 am
Location - FL US
Top

Re: how to widen search on my shop

Quote

Post by asg333 » Tue Feb 09, 2010 11:33 pm

please share :)
asg333
New member
Posts
30
Joined
Mon Jan 11, 2010 12:33 am
Top

Re: how to widen search on my shop

Quote

Post by Xsecrets » Wed Feb 10, 2010 12:15 am

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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter

Xsecrets
Guru Member
Posts
5017
Joined
Sun Oct 25, 2009 3:51 am
Location - FL US
Top

Re: how to widen search on my shop

Quote

Post by dbstr » Wed Feb 10, 2010 12:58 am

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.

Request Reviews v1.0 released.

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

Re: how to widen search on my shop

Quote

Post by asg333 » Wed Feb 10, 2010 1:26 am

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.
asg333
New member
Posts
30
Joined
Mon Jan 11, 2010 12:33 am
Top

Re: how to widen search on my shop

Quote

Post by dbstr » Wed Feb 10, 2010 4:43 am

You should probably add the model to the part after } else { aswell, if you want it to work if description is checked

Request Reviews v1.0 released.

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

Re: how to widen search on my shop

Quote

Post by asg333 » Wed Feb 10, 2010 5:21 am

Good point. Thank you!
asg333
New member
Posts
30
Joined
Mon Jan 11, 2010 12:33 am
Top

Re: how to widen search on my shop

Quote

Post by smorelli » Mon Feb 15, 2010 11:57 pm

So what would the final code look like?
smorelli
New member
Posts
94
Joined
Wed Jan 27, 2010 6:28 am
Top

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

Quote

Post by The Alchemist » Fri Feb 19, 2010 5:07 am

This is good to have
User avatar
The Alchemist
Active Member
Posts
302
Joined
Sun Nov 22, 2009 11:04 am
Top

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

Quote

Post by ebeing » Fri Mar 26, 2010 10:35 pm

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?
ebeing
New member
Posts
31
Joined
Wed Jan 06, 2010 2:18 pm
Top

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

Quote

Post by navex » Tue Mar 30, 2010 1:45 am

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.
navex
New member
Posts
39
Joined
Sat Feb 06, 2010 4:15 pm
Top

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

Quote

Post by speedingorange » Thu Apr 01, 2010 1:12 am

will either of these work with version 1.4.3?
speedingorange
Active Member
Posts
272
Joined
Tue Feb 23, 2010 7:33 pm
Top

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

Quote

Post by asg333 » Thu Jan 27, 2011 5:57 pm

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!
asg333
New member
Posts
30
Joined
Mon Jan 11, 2010 12:33 am
Top

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

Quote

Post by asg333 » Thu Jan 27, 2011 6:04 pm

Hi,

False alarm ;)

navex code above does work in 1.4.9.3

:D
asg333
New member
Posts
30
Joined
Mon Jan 11, 2010 12:33 am
Top
Post Reply
Return to “Free Contributions”
Who is online

Users browsing this forum: No registered users and 6 guests