If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Search box MySQL issue

I have never encountered an issue like this before but I am currently in the middle of creating another Zen store but whenever I attempt to use the search box, I am presented with an error message, instead of the search results. The site is far from complete and is currently residing on a temporary domain which is www.playstationpower.com

Here is the message I encounter whenever I try a search.

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by p.products_sort_order, pd.products_name limit 10' at line 1
in:
[SELECT DISTINCT p.products_image, p.products_model, p.products_quantity , m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id FROM (zen_products p LEFT JOIN zen_manufacturers m USING(manufacturers_id), zen_products_description pd, zen_categories c, zen_products_to_categories p2c ) LEFT JOIN zen_meta_tags_products_description mtpd ON mtpd.products_id= p2c.products_id AND mtpd.language_id = 1 WHERE (p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 AND p.products_id = p2c.products_id AND p2c.categories_id = c.categories_id AND ((pd.products_name LIKE '%baotian%' OR p.products_model LIKE '%baotian%' OR m.manufacturers_name LIKE '%baotian%' OR (mtpd.metatags_keywords LIKE '%baotian%' AND mtpd.metatags_keywords !='') OR (mtpd.metatags_description LIKE '%baotian%' AND mtpd.metatags_description !='') OR pd.products_description LIKE '%baotian%') and (pd.products_name LIKE '%pads%' OR p.products_model LIKE '%pads%' OR m.manufacturers_name LIKE '%pads%' OR (mtpd.metatags_keywords LIKE '%pads%' AND mtpd.metatags_keywords !='') OR (mtpd.metatags_description LIKE '%pads%' AND mtpd.metatags_description !='') OR pd.products_description LIKE '%pads%') )) order by p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name limit 10]

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.

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. :)

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

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?

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.