Post by aaron1988 » Sat May 19, 2012 7:59 pm

Hi Opencart,

wondering if there is an easy solution in 1.5.1.3 and 1.5.2.1 on how to Search by Product Attributes please as this is something i am seeing alot and no1 seems to be integrating it hope some1 can help me.

Kind Regards,
Aaron

Active Member

Posts

Joined
Thu Jan 27, 2011 10:03 am

Post by florinsith » Mon May 21, 2012 11:34 pm

Dont know if I get you correctly. The below code will return in search , products that have the atributes values like keyword.
Example: In a default opencart install, the demo 'Macbook' product has an atribute group called memory, atribute name: test1 and the atribute value: 8gb . So if you search for 8gb , Macbook will be returned, if you search for 16gb, 'HP LP3065' will be returned.

If thats what you are looking for, edit catalog/model/catalog/product/product.php and find the code:

Code: Select all

if (!$product_data) {
			$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.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)";
and replace it with:

Code: Select all

if (!$product_data) {
			$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.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 . "product_attribute pa ON (p.product_id = pa.product_id)";
Now find the code:

Code: Select all

public function getTotalProducts($data = array()) {
		$sql = "SELECT COUNT(DISTINCT p.product_id) 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)";
and replace it with:

Code: Select all

public function getTotalProducts($data = array()) {
		$sql = "SELECT COUNT(DISTINCT p.product_id) 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) LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id)";
Now , twice , you will find the code:

Code: Select all

$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
and both times, replace it with:

Code: Select all

$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%' OR LCASE(pa.text) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";

Image
My Extensions - SuperTheme - Opencart templates - Opencart modules


User avatar
Expert Member

Posts

Joined
Fri May 14, 2010 2:36 am


Post by aaron1988 » Tue May 22, 2012 5:58 pm

Hi mate,

cheers i will try this now :)

EDIT: how hard would it be to implement this into the SmartSearch module? as i forgot to say i have installed this, and if enabled it wont work for what i need :)

Aaron

Active Member

Posts

Joined
Thu Jan 27, 2011 10:03 am

Post by Johnathan » Wed Jul 11, 2012 11:21 pm

I've added the ability to search by product attributes in Smart Search now.

Image
Image Image Image Image


User avatar
Global Moderator

Posts

Joined
Fri Dec 18, 2009 3:08 am


Post by aaron1988 » Thu Jul 12, 2012 2:00 pm

Hi Jonathan,

I have seen thankyou so much for the quick integration

Aaron

Active Member

Posts

Joined
Thu Jan 27, 2011 10:03 am

Post by ryancalderon » Sun Dec 02, 2012 12:56 pm

This solution works perfectly.. thank you very much

Newbie

Posts

Joined
Wed Oct 24, 2012 9:08 am

Post by designsinspired » Thu May 08, 2014 7:59 pm

florinsith wrote:Dont know if I get you correctly. The below code will return in search , products that have the atributes values like keyword.
Example: In a default opencart install, the demo 'Macbook' product has an atribute group called memory, atribute name: test1 and the atribute value: 8gb . So if you search for 8gb , Macbook will be returned, if you search for 16gb, 'HP LP3065' will be returned.

If thats what you are looking for, edit catalog/model/catalog/product/product.php and find the code:

Code: Select all

if (!$product_data) {
			$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.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)";
and replace it with:

Code: Select all

if (!$product_data) {
			$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.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 . "product_attribute pa ON (p.product_id = pa.product_id)";
Now find the code:

Code: Select all

public function getTotalProducts($data = array()) {
		$sql = "SELECT COUNT(DISTINCT p.product_id) 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)";
and replace it with:

Code: Select all

public function getTotalProducts($data = array()) {
		$sql = "SELECT COUNT(DISTINCT p.product_id) 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) LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id)";
Now , twice , you will find the code:

Code: Select all

$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
and both times, replace it with:

Code: Select all

$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%' OR LCASE(pa.text) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
Hello there

This is exactly what I am looking for, however it seems the code has changed in Opencart 1.5.6.2

Are you possibly able to advise if this is possible in this version of Opencart?

Thank you for your time.


Posts

Joined
Thu May 08, 2014 6:53 pm

Post by awaix » Thu Jun 26, 2014 10:59 pm

can anyone suggest above mentioned method for Opencart 1.5.6? please
thanks.

New member

Posts

Joined
Sat Apr 13, 2013 8:22 pm

Post by florinsith » Sat Jun 28, 2014 8:58 pm

Same file, find:

Code: Select all

$sql .= " 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 p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
youll find it twice, each time replace it with:

Code: Select all

$sql .= " 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 . "product_attribute pa ON (p.product_id = pa.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
next find:

Code: Select all

if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
you will find it twice, each time add before it:

Code: Select all

$sql .= " OR LCASE(pa.text) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";

Image
My Extensions - SuperTheme - Opencart templates - Opencart modules


User avatar
Expert Member

Posts

Joined
Fri May 14, 2010 2:36 am


Post by PaulSketo » Tue May 19, 2015 4:16 pm

Anyone for OC 2.0.0?? Please!!

New member

Posts

Joined
Sat Apr 25, 2015 5:10 pm
Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 7 guests