Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2008
    Posts
    7
    Plugin Contributions
    0

    Default [Done v1.3.9a] New Products... results in SQL Error

    Running Zen Cart 1.3.8a
    Database Patch Level: 1.3.8
    PHP 4.4.9

    From the Admin page, in Tool->Layout Boxes Controller, I have "sideboxes/whats_new.php" turned OFF

    From Configuration->New Listing, I have "Mask Upcoming Products from being include as New Products" set to 1.

    The "New Products..." link in my Categories sidebox results in a SQL error. I expected a page with no new products displayed.

    This can be seen at http://chessieshop.tracefork.com/

    Is this a bug, or am I configured illegally?
    Any help would be appreciated. I would be satisfied with hiding the "New Products..." link in the Categories sidebox.
    Lars


    The sql error is:

    "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 '-0, 0' at line 11
    in:
    [SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status, p.master_categories_id FROM products p LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 and (p.products_date_available <=20080925 or p.products_date_available IS NULL) order by p.products_date_added DESC, pd.products_name limit -0, 0]"

  2. #2
    Join Date
    Sep 2003
    Location
    Ohio
    Posts
    66,814
    Plugin Contributions
    6

    Default Re: New Products... results in SQL Error

    Sounds like you have the Maximum Values set to 0 ...
    Linda McGrath
    If you have to think ... you haven't been zenned ...

    Did YOU buy the Zen Cart Team a cup of coffee and a donut today? Just click here to support the Zen Cart Team!!

    Are you using the latest? Perhaps you've a problem that's fixed in the latest version: [Upgrade today: v1.5.1]
    Officially PayPal-Certified! Just click here

    Try our Zen Cart Recommended Services - Hosting, Payment and more ...

  3. #3
    Join Date
    Sep 2008
    Posts
    7
    Plugin Contributions
    0

    Default Re: New Products... results in SQL Error

    Quote Originally Posted by Ajeh View Post
    Sounds like you have the Maximum Values set to 0 ...
    Thank you, that was the problem.
    Lars

  4. #4
    Join Date
    Sep 2003
    Location
    Ohio
    Posts
    66,814
    Plugin Contributions
    6

    Default Re: New Products... results in SQL Error

    Thanks for the update that having the Maximum Value of 0 will cause SQL errors or division by 0 errors ...
    Linda McGrath
    If you have to think ... you haven't been zenned ...

    Did YOU buy the Zen Cart Team a cup of coffee and a donut today? Just click here to support the Zen Cart Team!!

    Are you using the latest? Perhaps you've a problem that's fixed in the latest version: [Upgrade today: v1.5.1]
    Officially PayPal-Certified! Just click here

    Try our Zen Cart Recommended Services - Hosting, Payment and more ...

  5. #5
    Join Date
    Jan 2004
    Posts
    59,767
    Blog Entries
    4
    Plugin Contributions
    133

    Default Re: New Products... results in SQL Error

    Problem:
    "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 '-0, 0' at line 11"

    This can also result in PCI scans failing.

    Cause:
    If you set any of the Admin->Configuration->Maximum Values to zero, the above situation may occur.

    Fix:
    There are a few ways to avert this situation:

    a) Don't set Maximum Value settings to 0.

    b) Change /includes/classes/split_page_results.php to detect 0 values and assign something different if that happens:
    At line 25 find this section, and insert the new line as highlighted:
    Code:
      /* class constructor */
      function splitPageResults($query, $max_rows, $count_key = '*', $page_holder = 'page', $debug = false) {
        global $db;
        $max_rows = ($max_rows == '' || $max_rows == 0) ? 20 : $max_rows;
        $this->sql_query = $query;
        $this->page_name = $page_holder;
    c) Change /includes/classes/split_page_results.php to handle the "-0" situation more gracefully if it ever occurs:
    At line 84, find this:
    Code:
        // fix offset error on some versions
        if ($offset < 0) { $offset = 0; }
    
        $this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;
      }
    and change it to this:
    Code:
          // fix offset error on some versions
        if ($offset <= 0) { $offset = 0; }
    
        $this->sql_query .= " limit " . ($offset > 0 ? $offset . ", " : '') . $this->number_of_rows_per_page;
    }
    I recommend all three
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donations always welcome: www.zen-cart.com/donate

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  6. #6
    Join Date
    Jun 2009
    Posts
    618
    Plugin Contributions
    0

    Default Re: [Done v1.3.9] New Products... results in SQL Error

    Just an FYI, I may be wrong, but...

    I believe this only error only occurs w/ PHP4.x or below. I'm on 5.x and just tried applying this patch and it actually CAUSED the error to occur on my site. I simply took out the code and it was fixed.
    Thank you,
    autoace

  7. #7
    Join Date
    Jan 2004
    Posts
    59,767
    Blog Entries
    4
    Plugin Contributions
    133

    Default Re: [Done v1.3.9] New Products... results in SQL Error

    Actually, the "fix" was initially developed when using MySQL 5, and then tested again on MySQL 4 before posting.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donations always welcome: www.zen-cart.com/donate

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

 

 

Similar Threads

  1. Filter results error
    By webby2009 in forum General Questions
    Replies: 0
    Last Post: 3 Dec 2009, 03:31 PM
  2. PayPal Error - IPNCheck results
    By Jeff_Mash in forum PayPal Express Checkout support
    Replies: 2
    Last Post: 4 Jun 2008, 09:07 PM
  3. Adding image results in this error ...
    By kevinmc3 in forum Templates, Stylesheets, Page Layout
    Replies: 2
    Last Post: 3 Jun 2008, 02:22 PM
  4. help, please! - split-page-results error division by zero
    By vibehappy in forum Customization from the Admin
    Replies: 10
    Last Post: 17 Jul 2007, 11:17 PM
  5. Search Results in Division by Zero Error
    By IronMac in forum Templates, Stylesheets, Page Layout
    Replies: 4
    Last Post: 8 Jul 2007, 05:56 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
  •