Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Location
    Stoke-on-Trent, Staffordshire. UK
    Posts
    131
    Plugin Contributions
    0

    red flag 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]
    Visit Andy's Motorcycles
    www.andysmotorcycles.com
    Motorcycle & Scooter Parts, Spares & accessories

  2. #2
    Join Date
    Jan 2007
    Location
    Australia
    Posts
    6,167
    Plugin Contributions
    7

    Default 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

  3. #3
    Join Date
    Sep 2010
    Location
    Stoke-on-Trent, Staffordshire. UK
    Posts
    131
    Plugin Contributions
    0

    Default 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 , lol
    Visit Andy's Motorcycles
    www.andysmotorcycles.com
    Motorcycle & Scooter Parts, Spares & accessories

  4. #4
    Join Date
    Jan 2007
    Location
    Australia
    Posts
    6,167
    Plugin Contributions
    7

    Default Re: Search box MySQL issue

    Quote Originally Posted by Miff View Post
    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

  5. #5
    Join Date
    Jan 2007
    Location
    Australia
    Posts
    6,167
    Plugin Contributions
    7

    Default Re: Search box MySQL issue

    Quote Originally Posted by Miff View Post
    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).

    Cheers
    Rod

  6. #6
    Join Date
    Sep 2010
    Location
    Stoke-on-Trent, Staffordshire. UK
    Posts
    131
    Plugin Contributions
    0

    Default 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.
    Visit Andy's Motorcycles
    www.andysmotorcycles.com
    Motorcycle & Scooter Parts, Spares & accessories

  7. #7
    Join Date
    Jan 2007
    Location
    Australia
    Posts
    6,167
    Plugin Contributions
    7

    Default Re: Search box MySQL issue

    Quote Originally Posted by Miff View Post
    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

  8. #8
    Join Date
    Sep 2010
    Location
    Stoke-on-Trent, Staffordshire. UK
    Posts
    131
    Plugin Contributions
    0

    Default 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.
    Visit Andy's Motorcycles
    www.andysmotorcycles.com
    Motorcycle & Scooter Parts, Spares & accessories

 

 

Similar Threads

  1. v150 Category and Search Box Issue
    By bcolby99 in forum Setting Up Categories, Products, Attributes
    Replies: 1
    Last Post: 23 Sep 2012, 04:19 AM
  2. Delete default search text in search box when clicked
    By jaylyns in forum General Questions
    Replies: 7
    Last Post: 15 Apr 2011, 06:03 AM
  3. custom search box - alignment issue
    By tanyaslogos in forum Templates, Stylesheets, Page Layout
    Replies: 2
    Last Post: 3 Oct 2009, 06:31 PM
  4. Search Box only searches products - can it search EZpages?
    By uiserloh in forum General Questions
    Replies: 1
    Last Post: 29 Jan 2008, 06:56 PM
  5. Search Box Issue
    By PortMan in forum Templates, Stylesheets, Page Layout
    Replies: 2
    Last Post: 30 Nov 2006, 10:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR