Re: Search box MySQL issue
I can see what is causing the error, but I've no explanation as to how or why it is occuring.
Nonetherless, knowing the cause may lead to a solution.
The part of the query sting causing the error is on the very last line, specifically
"order by p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name"
Note the duplication! (easier to see if I wrap the line)
"order by p.products_sort_order, pd.products_name
order by p.products_sort_order, pd.products_name"
If you can figure out why it has this duplicate ordering then your problem will be solved.
Cheers
Rod
Re: Search box MySQL issue
Thank you for your input.
After a short while of searching I have now resolved this issue. It seems that one of my addons had a slight coding error, resulting in this problem. I have a few addons plugged in, however, looking at some of the comments that are in this modified core file I have a strong belief that it may be the ReCaptcha addon (but not 100%).
I can however say that I only have about 8 addons plugged in and I ONLY use the very popular addons, so someone else is bound to have this issue. For that reason Il post my fix.
File is at - (includes\modules\pages\advanced_search_result)
Line 445.
before:
} else {
// sort by products_sort_order when PRODUCT_LISTING_DEFAULT_SORT_ORDER ia left blank
// for reverse, descending order use:
// $listing_sql .= " order by p.products_sort_order desc, pd.products_name";
$order_str .= " order by p.products_sort_order, pd.products_name";
break;
}
}
after:
} else {
// sort by products_sort_order when PRODUCT_LISTING_DEFAULT_SORT_ORDER ia left blank
// for reverse, descending order use:
// $listing_sql .= " order by p.products_sort_order desc, pd.products_name";
// $order_str .= " order by p.products_sort_order, pd.products_name";
break;
}
}
Simply add the // at the start of line 445 and et viola....
I hope that this helps someone in the same issue, donations are accepted :wink:, lol
Re: Search box MySQL issue
Quote:
Originally Posted by
Miff
after:
} else {
// sort by products_sort_order when PRODUCT_LISTING_DEFAULT_SORT_ORDER ia left blank
// for reverse, descending order use:
// $listing_sql .= " order by p.products_sort_order desc, pd.products_name";
// $order_str .= " order by p.products_sort_order, pd.products_name";
break;
}
}
Simply add the // at the start of line 445 and et viola....
I can also see another solution. I can't determine whether it will be a better solution, or just a different solution.
Change this
$order_str .= " order by p.products_sort_order, pd.products_name";
To this.
$order_str = " order by p.products_sort_order, pd.products_name"; // Remove the preiod (.) before the =
Here's what is happening. The ".=" means "append this string to whatever this variable is already set to"
Without the . it means "replace the current variable with this new string"
The symptoms that you were experiencing would have been the result of the $order_str being already set elsewhere and because of the period, it was having the same value appended to it, hense the duplicated 'order' data that I spoke of before.
The solution you provided will use whatever this order string was previously set to. This could be something earlier in the code, It could be something 'useful', it could be an empty string, or it could just be left over from a previous search query (and therefore not relevent to the *current* query. It probably doesn't matter what this string is/was, as long as it isn't the *same* as the string that is being appended to it.
The alternate solution will ignore anything the string was previously set to, and you just what it is being defined at in this line.
pro's and cons:
If the string was previously set to do something 'useful' (such as sort by date, or sort by price) then appending to this string will sort by date/price and then by product_sort_order and name.
By commenting out the line will prevent the sorting by product_sort_order and name, leaving the sorting by date/price only.
If the string was previously unset, by commenting out this line will leave you with no sort order at all, the listing will be in the order that the products are stored in the database.
If the string was set by a *different* search, then the current search may not be displayed in the order expected.
By removing the period will ensure that the sort order is always going to be set to the product_sort_order and name (ignoring any previous/other sort settings).
As I originally stated, both solutions are quite valid in regards to functionality. Which one is 'better' is something that only the code developer would be able to say for sure (but they'd probably need to find a way to prevent the duplication if the appending was intentional).
In essence, you have found a bug, so on that basis, both of our solutions are nothing more than a 'kludge'.
Hmmm, perhaps if I get a few minutes to spare later I'll take a look at the entire file to see if I can get a better idea as to what the coder origianlly intended. :)
Cheers
Rod
Re: Search box MySQL issue
Quote:
Originally Posted by
Miff
File is at - (includes\modules\pages\advanced_search_result)
Line 445.
Was this a typo, or has your file been edited?
What you are referencing as line 445 is actually 415 in the v1.5 distro files. (and different again in the V1.3.8 and V1.3.9 distro files).:unsure:
Cheers
Rod
Re: Search box MySQL issue
No, as mentioned previously, this is not the default core file. I know that because the original is safely tucked away in the same folder with a -bak extension. It is a file added during the plugging in of an add-on. As I said earlier, I really am not sure as to which it is but I don't have too many add ons and they are all very popular add ons, commonly used. Looking at some of the comments in the file it may be the ReCaptcha add-on but without looking at this module I could not say for sure. I can however confirm that the error was indeed on line 445.
Re: Search box MySQL issue
Quote:
Originally Posted by
Miff
No, as mentioned previously, this is not the default core file.
< chomp > I can however confirm that the error was indeed on line 445.
OK, but since your file has been modified, then referencing line 445 along with "so someone else is bound to have this issue. For that reason Il post my fix" , is actually misleading, and possibly even 'dangerous'.
It may be obvious to both you and I that the *actual* line number will be different in the non modified code, there are some people that will take this literally, and end up commenting out a completely different line, which will cause all sorts of additional problems. (Yes, some people are that silly).
Anyway, that wasn't the point I was trying to make. I was looking at the code trying to determine why your problem existed in the first place, and couldn't see any possible cause for the problem. It was during this time that I noted you'd referenced a different line number than the one I was looking at, and *if* it was a typo (445 vs 415 wouldn't be a difficult error to make) I was going to go back and take an even closer look,
Before I asked you about this though I checked this same file in *all* of the different zencart versions I have on my test bed, expecting to find one that would match what you had referenced. I didn't find one, which is why I probed you for more info. :)
It is now quite apparent that the cause of your problem is indeed related to your code modification. This doesn't make the two possible solutions any different, but I/we are now back to square#1 in not knowing which is going to be the best of the two, because that will depend on what the modified code is actually trying to do.
The *correct* solution would actually be in the code that has been added, because as I stated, both your solution and mine are both nothing more than a 'kludge' that is hiding the *real* cause. I *hope* you understand what I'm saying here?
Cheers
Rod
Re: Search box MySQL issue
The file is a complete file included within an add on. The file has not been modified by myself, I have simply placed this during an installation of an add on at some point. I will take another look to see if there are any clues as to where I have picked it up and double check, so there is definitely a popular module with this error on line 445. Il take a look and report when I know. The fix i made previously though is working flawlessly at the moment so I'm happy for now.