[Solved]Search in attributes for OC 2.0

Quote

Post by ITF » Fri Jun 10, 2016 11:41 pm

Hey everyone. I'm trying to update this simple modification for 2.0 taken from this 1.5 forums: http://forum.opencart.com/viewtopic.php ... 56#p470856
I know, there are many search extensions in the marketplace. But, the goal was to use the provided search box to look in attributes and also pull up results. Much like pulling up products with the same tag words.

First, a disclaimer! I'm not entirely familiar with OC2. Though, with that said, I can try to understand the code in bits and pieces. The code for the page (/catalog/model/catalog/product.php) has changed a bit from OC1.5. So, here's what I tried to do to, hopefully, get the same results.

In OC2's catalog/model/catalog/product.php around line 61 (inside getProducts()) and line 406 (inside getTotalProducts())

Code: Select all

		if (!empty($data['filter_category_id'])) {
			if (!empty($data['filter_sub_category'])) {
				$sql .= " FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (cp.category_id = p2c.category_id)";
			} else {
				$sql .= " FROM " . DB_PREFIX . "product_to_category p2c";
			}
Based on the 1.5 post, I'm to add the following after the above code:

Code: Select all

//Add search in attributes
    		$sql .= " LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id)";

Then, search for the following [one inside getProducts() and getTotalProducts()]

Code: Select all

$implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
and add below the above code:

Code: Select all

//Search in Attributes
					$implode[] = "pa.text LIKE '%" . $this->db->escape($word) . "%'";
My understanding is that we are joining the product_attribute table to the default search. Grabbing the "text" column which will show in the results because of the $implode[]. I haven't tested this in 1.5.2.1 build but it's said to be working. Logically (If I understood properly) it makes sense. However, it doesn't work. When I search a word in attributes but not in product/description, no product found.

My questions: Have I misunderstood the logic behind it? What am I missing? and should the code be in both getTotalProducts() and getProducts() or just one of them?
I've tried adding the code to both functions but it still doesn't work. Should I do more? Do I need to put it into arrays and split them? Many questions, not sure what's right/wrong or where to start.

---I know, don't alter core files. When this is tested working, I'll hopefully be able to contribute with an OCMOD for those who might want this function---

Please explain any thought process. I'd like to understand where my understanding went wrong. Thanks for any light that can be shed on the issue! :)


UPDATE: Issue has been solved for anyone looking for a simple mod in the future read the following. OR, just download AlexDW's mod, it's great.
Link: http://www.opencart.com/index.php?route ... n_id=22288
Last edited by ITF on Sat Jun 11, 2016 4:41 am, edited 2 times in total.
ITF
New member
Posts
27
Joined
Tue Apr 20, 2010 7:09 am
Top

Re: Search in attributes for OC 2.0

Quote

Post by AlexDW » Sat Jun 11, 2016 12:20 am

just use this free extension (and look code, if you need):
http://www.opencart.com/index.php?route ... n_id=22288

Useful Opencart extensions

User avatar
AlexDW
New member
Posts
74
Joined
Sun Jun 05, 2016 3:32 am
Top

Re: Search in attributes for OC 2.0

Quote

Post by ITF » Sat Jun 11, 2016 1:40 am

Thanks AlexDW! I tried this before, but received an error when adding it to layout. Perhaps I used it improperly before. I will have another look.

lol Somehow, I think your avatar portrays perfectly your response. :D

AlexDW wrote:just use this free extension (and look code, if you need):
http://www.opencart.com/index.php?route ... n_id=22288
ITF
New member
Posts
27
Joined
Tue Apr 20, 2010 7:09 am
Top

Re: Search in attributes for OC 2.0

Quote

Post by artcore » Sat Jun 11, 2016 2:30 am

I don't know the code by heart and it's not in front of me now but it seems you're joining only the attribute name and not the description which would hold the text you're looking for?

//Add search in attributes
$sql .= " LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id)";
//Add the actual attr desc
$sql .= " LEFT JOIN " . DB_PREFIX . "product_attribute_description pad USING (attribute_id)";
You can check this by inspecting the imploded array contents. Now it might only have product desc and attr IDs ;D

but I could be wrong :)

Attn: I no longer provide OpenCart extensions, nor future support - this includes forum posts.
Reason: OpenCart version 3+ ;D

Thanks!

User avatar
artcore
Active Member
Posts
1349
Joined
Tue Jul 09, 2013 4:13 am
Location - The Netherlands
Top

Re: Search in attributes for OC 2.0

Quote

Post by ITF » Sat Jun 11, 2016 4:13 am

artcore wrote:I don't know the code by heart and it's not in front of me now but it seems you're joining only the attribute name and not the description which would hold the text you're looking for?

//Add search in attributes
$sql .= " LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id)";
//Add the actual attr desc
$sql .= " LEFT JOIN " . DB_PREFIX . "product_attribute_description pad USING (attribute_id)";
You can check this by inspecting the imploded array contents. Now it might only have product desc and attr IDs ;D

but I could be wrong :)
There isn't a product_attribute_description table @_@ Perhaps it's my database that's an issue?
But, I'm not searching for attribute descriptions. Just the name of attributes assigned to each item.

UPDATE: Got it working! Thanks for your reply artcore. :) Though, can't say I fully understand arrays still. But, I learnt some other things from it. ^^
Last edited by ITF on Sat Jun 11, 2016 4:49 am, edited 1 time in total.
ITF
New member
Posts
27
Joined
Tue Apr 20, 2010 7:09 am
Top

Re: Search in attributes for OC 2.0

Quote

Post by ITF » Sat Jun 11, 2016 4:18 am

AlexDW wrote:just use this free extension (and look code, if you need):
http://www.opencart.com/index.php?route ... n_id=22288
Awesome, I looked at your code. I've come to realize one of my mistakes!

So, I'm supposed to insert the code after the whole if statement. Where as before I was inserting the code too early within the if statement.

Code: Select all

if (!empty($data['filter_category_id'])) {
			if (!empty($data['filter_sub_category'])) {
				$sql .= " FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (cp.category_id = p2c.category_id)";
			} else {
				$sql .= " FROM " . DB_PREFIX . "product_to_category p2c";
			}

			if (!empty($data['filter_filter'])) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf ON (p2c.product_id = pf.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (pf.product_id = p.product_id)";
			} else {
				$sql .= " LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
			}
		} else {
			$sql .= " FROM " . DB_PREFIX . "product p";
		}
after the above code, I've inserted

Code: Select all

		//Add search in attributes
    		$sql .= " LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id)";
I've commented out this part of the code

Code: Select all

// $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%' OR";
and inserted with this

Code: Select all

$test = " (LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
					$test .= " OR LCASE(pa.text) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
					$test .= ") ";
					$implode[] = $test;
I assigned a product with attribute "Item Type": "Box" and I searched "box" but still, no dice.
Did I miss something?


UPDATE: It works! I had to login as admin and press the refresh in the Modifications page. I changed the code directly, but I figured installing an ocmod is pretty similar. It worked.
My bonus question would be... Is it ok to write the array like this?

Code: Select all

$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
$implode[] = " OR LCASE(pa.text) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
Thanks!
Last edited by ITF on Sat Jun 11, 2016 4:31 am, edited 1 time in total.
ITF
New member
Posts
27
Joined
Tue Apr 20, 2010 7:09 am
Top

Re: Search in attributes for OC 2.0

Quote

Post by AlexDW » Sat Jun 11, 2016 4:19 am

2 artcore
No, I think you confuse the tables `attribute` and `product_attribute` ;)

2 ITF
My avatar looks better on the Russian-speaking Opencart support forum (avatars on left side, message on right) ;D

Useful Opencart extensions

User avatar
AlexDW
New member
Posts
74
Joined
Sun Jun 05, 2016 3:32 am
Top

Re: Search in attributes for OC 2.0

Quote

Post by AlexDW » Sat Jun 11, 2016 4:36 am

ITF wrote: My bonus question would be... Is it ok to write the array like this?

Code: Select all

$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
$implode[] = " OR LCASE(pa.text) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
Thanks!
No, it's wrong, use previous construction

And don't forget refresh modifications cache everytime after editing core files ;)

Useful Opencart extensions

User avatar
AlexDW
New member
Posts
74
Joined
Sun Jun 05, 2016 3:32 am
Top

Re: Search in attributes for OC 2.0

Quote

Post by ITF » Sat Jun 11, 2016 4:38 am

Haha, as I'm sure most answers to questions are deserving of that look. Thankfully, I'm spared the direct glare being in the English forums. :D
AlexDW wrote:2 artcore
No, I think you confuse the tables `attribute` and `product_attribute` ;)

2 ITF
My avatar looks better on the Russian-speaking Opencart support forum (avatars on left side, message on right) ;D
ITF
New member
Posts
27
Joined
Tue Apr 20, 2010 7:09 am
Top

Re: Search in attributes for OC 2.0

Quote

Post by ITF » Sat Jun 11, 2016 4:39 am

AlexDW wrote:
ITF wrote: My bonus question would be... Is it ok to write the array like this?

Code: Select all

$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
$implode[] = " OR LCASE(pa.text) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
Thanks!
No, it's wrong, use previous construction

And don't forget refresh modifications cache everytime after editing core files ;)

Much thanks! I believe I'll study up on arrays.
ITF
New member
Posts
27
Joined
Tue Apr 20, 2010 7:09 am
Top

Re: [Solved]Search in attributes for OC 2.0

Quote

Post by artcore » Sat Jun 11, 2016 2:10 pm

Awesome!
ITF, glad my reply got you a bit further.
Alex, thanks for the rectification!

Cheers

Attn: I no longer provide OpenCart extensions, nor future support - this includes forum posts.
Reason: OpenCart version 3+ ;D

Thanks!

User avatar
artcore
Active Member
Posts
1349
Joined
Tue Jul 09, 2013 4:13 am
Location - The Netherlands
Top
Post Reply
Return to “General Support”
Who is online

Users browsing this forum: No registered users and 48 guests