[1.5.1.3] New 1.5.x search design causes new problems

Qphoria wrote:Ok .. so AND may be a better choice here. As it does seem to improve results, but needed some more tweaks.

On my site I have a product called "Migs Payment Integration"

With 1.4.x the search for "Migs Integration" found 0 results because it was exact phrase matching. No good.
With 1.5.x "OR", the search for "Migs Integration" found 4 pages of results, and the correct item was bottom of page 2. No good.
With 1.5.x "AND", the search for "Migs Integration" found just the one correct result.
So that is definitely improved.

However, it could still be limiting if you don't have all the necessary terms in the product name. But using tags correctly should resolve that.

If I searched "Payment Integration" it would find all products with "Payment" AND "Integration"
But wouldn't find ones like "Offline Payment System". However, if you use tags and have tags like "Payment, Module, Extension" and leave the TAG search as "OR", then that combination should match everything you need.

So to make it clear, the search logic becomes:
1. Find all products that match "Payment" AND "Integration", which finds all products with those 2 words in the name/description.
2. Additionally, find products that have tags "Payment" OR "Integration" in the tag area, which finds all products that have matching tags, picking up the few that didn't have payment integration in the name/description, but partially matched the tags.

I think we need to add mysql fulltext searching to the database for a more proper solution.... But for now, the quick solution appears to be replacing OR with AND, which I think a lot of people have already figured out in other threads, but hey.. i'm late to the party. But I bring other bug fixes as well. I found that the 1.5.1.3 tag search is actually bugged because it explodes the words, but searches the full phrase, causing tags to be completely broken in 1.5.1.3 without this fix. This AND/OR combination also requires a parenthesis change to work properly.

I've attached the default 1.5.1.3 catalog model file with all changes
Drop it into catalog/model/catalog folder and replace the existing product.php file
OR
To manually make the change and the bug fix yourself:
1. EDIT: catalog/model/catalog/product.php
2. FIND:

Code: Select all

$sql .= " " . implode(" OR ", $implode) . "";

It should match 4 times.

3. Change ONLY the FIRST and THIRD instances to:

Code: Select all

$sql .= " " . implode(" AND ", $implode) . "";

This leaves the tag search as "OR" but the name/description search as "AND".

4. FIND (TWICE):

Code: Select all

$sql .= " OR ";

5. REPLACE BOTH WITH:

Code: Select all

$sql .= ") OR (";

6. TAG BUG FIX, FIND (TWICE):

Code: Select all

$this->db->escape(utf8_strtolower($data['filter_tag']))

7. REPLACE BOTH WITH:

Code: Select all

$this->db->escape(utf8_strtolower($word))

8. Delete all files in your system/cache folder via FTP

9. Now try your searches and see if they've improved.

Again, you may need to use the right combination of tag and title. For my example
If I have a product called "Offline Payment System"
and I want the search for "Payment Integration" to match, I would need to use tags like:

Offline, Payment, System

To guarantee matching more common words that may not all be in the name/desc


This seems to be a great improvement on my site. Let me know how it works on your site.

DO NOT USE THIS FILE UNLESS YOU ARE USING v1.5.1.3
FUTURE VERSIONS WILL ALREADY HAVE THIS FIX AND OTHER CHANGES.
OLDER VERSIONS MAY HAVE DIFFERENT CHANGES AND MAY NOT BE COMPATIBLE WITH THIS FILE.

Hi. On 1.5.2.1 ur mod working normally.
I installed today 1.5.3.1 - cleary - stock search doesnt work normal (like in first post need).
I made changes in code like u say - but have little issuse.
Search now work normal but pagination says i have search result to 10 pages but search found only 8 products ( about 8 - its ok. search work normally). So how to fix pagination ?

The bug is still there in 1.5.3.1

I corrected it though Qphoria's fix.
It works.

I attached my product.php
Make sure to clear your system/cache/ folder.

Turbo Bob wrote:The bug is still there in 1.5.3.1

I corrected it though Qphoria's fix.
It works.

I attached my product.php
Make sure to clear your system/cache/ folder.

Can u write step-by-step ur fixes? f have modifyed files. So cant rewrite her.

I tried all the steps mentioned above in 1.5.3.1, with no luck. Also tried the file attached by Turbo Bob but still doesn't work. I get a blank page when I change this. Nothing displays, not even the header and footer.

Can someone please help with a fix.

I figured out that the problem is with the product tags. If I have products tags for all my products, I am getting a blank page and in the Error log, I see the below error.

2012-06-28 14:33:10 - PHP Notice: Error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay<br />Error No: 1104<br />SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s.............................

I cleared all the product tags and the search function works now. Can someone take a look on why it is not working when there are product tags?

Q is right. Fulltext should be the right choice. But I think maybe we could also create a new table that stores the search results(works as cache).
Something like this should be a good idea:

CREATE TABLE search_index(
search_id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
key_word varchar DEFAULT '' NOT NULL,
search_string TEXT NOT NULL,
product_id TEXT NOT NULL,
start_time INT UNSIGNED DEFAULT '0' NOT NULL
expiration_time INT UNSIGNED DEFAULT '0' NOT NULL
);

to make the data retrieve from search_index real-time enough, we need to set the time when the store data expires.

if we search "iphone" and use all options provided in the search module, the url is gonna be like:
http://localhost/opencart/index.php?rou ... ption=true

Then a new line is inserted into the search_index table

INSERT INTO search_index (key_word , search_string, product_id , start_time, expiration_time)
VALUES('iphone', '20|sub|desc', '40|12|32|98|90|23', 'current_time_stamp', 'expiration_time_stamp');

Next time, when another one searches something, check the table search_index first. If the used keywords are already there, return the product ids directly(no need to do a fulltext search). If not, do a fulltext search.

But I guess we need to configure out someway to clean the data expires in the search_index table. Maybe a con job?

These fixes do not work correctly on 1.5.1.3.

I keep getting black results in between good results. By blank I mean a 0.00 price, an add to cart button but no product information. There is no ID number associated with the add to cart button.

I'm also finding the pagination is screwed up. One search reported 382 results, but after page 4 there were no products.

Right, I've fixed the pagination thing (that was my fault) but I'm still getting blank results.

After inspecting the sql query and running is via phopMyAdmin it seems that it is pulling records that have the status set to '0' although the query seems to have the WHERE p.status='1'

Here is my query
Can any mySQL geniuses see the problem ??

Edit, It also ignores availability dates.

This is fixed you also need to replace the following

FIND (TWICE) REPLACE WITH FIND (TWICE) REPLACE WITH

What about version 1.5.3. How do you fix that???

ecoleman wrote:This is fixed you also need to replace the following

FIND (TWICE)

Code: Select all

$sql .= " AND (";

REPLACE WITH

Code: Select all

$sql .= " AND ((";

FIND (TWICE)

Code: Select all

$sql .= ")";

REPLACE WITH

Code: Select all

$sql .= "))";

May I ask what the purpose of these changes are?

enk

palynch wrote:What about version 1.5.3. How do you fix that???

I am also using OC1.5.3.1. I just worked it out.
Search implode(' OR ',, replace all the four OR to AND, except the 2 below.

implode(' OR ', $implode_data)

then, clear cache, and you will work it out. This is a headache to me and I finally worked it out tonight. I am so happy as I was about to give up opencart beacuase of this weak search problem.

You can check at our site: www.haowind.com, example, product name is 12MM LIQUID COMPASS, you search 12MM COMPASS, it will return an accurate result now. Not like default search, return all products with titles includes 12mm or compass.

Thank you for this it works great..!
i am looking for search on product model please help needed for that..i am using OC 1.5.3x

Firt, about the problem, please visit: viewtopic.php?f=161&t=49136
Qphoria correction.


Hi everyone!

I'm opening this bug because i'm using opencart 1.5.1.3 (with some mods that i cant update for the newer opencart version)

So i need put to work the search system.

I've tried the Qphoria correction, so now the results are fine, but just one problem, about PAGINATION.

Example:

if i search: "iphone 5"

--> with Qphoria correction --> Total results: 243 products / 17 pages

But the correct result will be: 43 products / 3 Pages

So looking into MODEL -> CATALOG -> PRODUCT.PHP how can locate the LINE with problem?

I'm using product.php that Qphoria attached...

Please help me, for now i cant update for the lastest opencart version... Probably some OR or AND are missing... looking the results, look like pagination showing the search just for "iphone" without "5" word...

Thanks

anyone? please?

haowind wrote:

palynch wrote:What about version 1.5.3. How do you fix that???

I am also using OC1.5.3.1. I just worked it out.
Search implode(' OR ',, replace all the four OR to AND, except the 2 below.

implode(' OR ', $implode_data)

then, clear cache, and you will work it out. This is a headache to me and I finally worked it out tonight. I am so happy as I was about to give up opencart beacuase of this weak search problem.

You can check at our site: http://www.haowind.com, example, product name is 12MM LIQUID COMPASS, you search 12MM COMPASS, it will return an accurate result now. Not like default search, return all products with titles includes 12mm or compass.

I'm having the same problem in 1.5.4.1, cannot find "implode(' OR '," in the product.php of 1.5.4.1 please help

Since this thread pretty well petered out last summer, I was hesitant to say anything here until out of curiosity I looked several minutes ago at Qphoria's own store and ran searches via both the top-right and upper-left search boxes, where I found that the problem is still unresolved this spring, in at least one version of OC in at least one fairly serious OC installation.

(1) In each instance, top-right and upper-left searches are set to "show 100" and upper-left is set to check subcategories and descriptions, and putting any entry into one or the other search box puts it (upon execution) into both (tested also by entering "miggitdy" and "miggitdies" which, of course, return 0 items). One wonders how many visitors would select "show 100" or just look at 12 and move on.

"Mig" top-right returns 1 item, upper-left returns 2 items.

"Migs" top-right returns 1 item, upper-left returns 1 item.

"Mig or Migs" (show 100) top-right returns 37 different items, upper-left returns 148 different items.

"Mig and Migs" top-right returns 10 different items, upper-left returns 12 different items.

(2) Search engines have long since overcome the foremost incompetence of spellcheckers to recognize as well as accommodate singulars and plurals in any fashion remotely similar to what humans accomplish instantly (and for that matter, also dogs, between "Want yummy?" and "Want yummies?"--short answer, "Woof!"). I'm not seeing the ability here.

(3) Boolean searches have long since been known to reduce search engines' datadumps by powers of ten, but have in the recent few years been dropped in the interests of cumulative profit from miniscule incomes per click tracked by all manner of address code that, among all the strings followed everywhere by everyone, actually slow down the internet (they must be processed, and their effect is monumentally worse than minor unpreferred settings in Apache php or the like). Boycotting Google and its proverbial funny friends isn't happening any time soon. Returning reduced results for phrases (by quotation marks or a dedicated box) is helpful, but still is not Boolean. "Abraham Lincoln" as a phrase is reasonably expected to appear uncluttered with either Testament or automotive results that amount to "Abraham or Lincoln". I'm not seeing exactitude or consistency here in "and" as well as "or" tried for exactly that expectation.

(4) Forcibly nested hidden searches are an impediment, as where we must often suffer successive make, model, part, and whatever. In some of those cases the annoying boxes are probably there precisely because somebody expected customers to be able to find (to take an example well above) specific and specifically Toyota headlights. In many of those cases the boxes are all the more annoying precisely because a fuming human already knows that "XMG234" is a specific make and model on the maker's own website (try the major computer makers). Mercifully, I'm not seeing that impediment to sensibly quick searches here.

(4) For the sake of development, as well as for the sake of localized code modification, some attention to singulars and plurals as well as to Boolean expectations that most store visitors will have at least dimly in mind would seem appropriate. Granted, OC itself is not intended to be a premium search engine in its own right even internally, but that part of its core doesn't seem to warrant the foregoing several months of virtual silence in this or any similar thread, when the problem isn't dead even in Qphoria's store.

Recent approaches taken by Qphoria to the house forums and house store offer a likely expedient approach to the foregoing concerns, as well. Compare Booleanesque topic searches tried at http://forum.opencart.com/viewtopic.php ... 54#p443454 (below posts of work done on searching) to typical shopping searches.