Page 1 of 2 12 LastLast
Results 1 to 10 of 13
  1. #1
    Join Date
    Jul 2011
    Posts
    106
    Plugin Contributions
    0

    Default 1064 SQL error in product filter addon?

    not sure if this is the right place to post Sql errors, but i have this one if anyone can help?

    while viewing my product listing I click either 'next page' or an option from 'filter results by:' I get this error...

    Code:
    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' at line 7
    in:
    [select pd.products_name, p.products_image, p.products_quantity, p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status from znc_products_description pd, znc_products p left join znc_manufacturers m on p.manufacturers_id = m.manufacturers_id, znc_products_to_categories p2c left join znc_specials s on p2c.products_id = s.products_id where p.products_status = 1 and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '14' order by p.products_date_added DESC order by ]
    I have tried looking back at what I was doing but can't seem to find this anywhere.

    Please could some one point me in some direction?

    Thanks in advance,
    Jay

  2. #2
    Join Date
    Nov 2007
    Location
    Sunny Coast, Australia
    Posts
    3,379
    Plugin Contributions
    9

    Default Re: 1064 SQL error

    What version zc are you using?

    List any add-ons you have installed

    When did this error start to appear?

    Link to your site?

  3. #3
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: 1064 SQL error

    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    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.

  4. #4
    Join Date
    Jul 2011
    Posts
    106
    Plugin Contributions
    0

    Default Re: 1064 SQL error

    @frank18

    ZC Version: 1.3.9h

    Addons: product filter by attributes and price range
    uk postcode shipping
    zones uk counties
    column layout grid
    css horizontal dropdown menu
    (i think thats it so far..)

    I only noticed the error while I was styling the product listing, when I changed the amount to display per page in admin config, that's when it first came up, but not sure how long back it actually could have begun.

    Most of what I have been doing is styling.

    But... Something I did do was make a copy of tpl_columnar_display for my specials and called it tpl_columnar_display_specials and changed the require in tpl_modules_specials_default

    This what and why I changed it to :
    PHP Code:
    <?php
      
    if ($title) {
      
    ?>
    <?php 
    //echo $title; ?>
    <?php
     
    }
    ?>
    <?php 
    if ($this_is_home_page) { ?>
    <?php
    if (is_array($list_box_contents) > ) {
     for(
    $row=0;$row<sizeof($list_box_contents);$row++) {
        
    $params "";
        
    //if (isset($list_box_contents[$row]['params'])) $params .= ' ' . $list_box_contents[$row]['params'];
    ?>
    <?php
        
    for($col=0;$col<sizeof($list_box_contents[$row]);$col++) {
          
    $r_params "";
          if (isset(
    $list_box_contents[$row][$col]['params'])) $r_params .= ' ' . (string)$list_box_contents[$row][$col]['params'];
         if (isset(
    $list_box_contents[$row][$col]['text'])) {
    ?>
        <?php echo '<div class="centerBoxContentsSpecialsHome centeredContent back">' $list_box_contents[$row][$col]['text'] .  '</div>' "\n"?>
    <?php
          
    }
        }
    ?>
    <br class="clearBoth" />
    <?php
      
    }
    }
    ?> 
    <?php } else { ?>
    <?php
    if (is_array($list_box_contents) > ) {
     for(
    $row=0;$row<sizeof($list_box_contents);$row++) {
        
    $params "";
        
    //if (isset($list_box_contents[$row]['params'])) $params .= ' ' . $list_box_contents[$row]['params'];
    ?>
    <?php
        
    for($col=0;$col<sizeof($list_box_contents[$row]);$col++) {
          
    $r_params "";
          if (isset(
    $list_box_contents[$row][$col]['params'])) $r_params .= ' ' . (string)$list_box_contents[$row][$col]['params'];
         if (isset(
    $list_box_contents[$row][$col]['text'])) {
    ?>
        <?php echo '<div' $r_params '>' $list_box_contents[$row][$col]['text'] .  '</div>' "\n"?>
    <?php
          
    }
        }
    ?>
    <br class="clearBoth" />
    <?php
      
    }
    }
    ?> 
    <?php ?>
    not sure if it causes a conflict or not....

    @DrByte
    Thanks for the link, but have gone through the code quite few times... As you know it is normally the smallest thing your eye just won't find.

    If you know of any php syntax validators that would greatly appreciated, I have looked on the net, but not much use.

  5. #5
    Join Date
    Jul 2011
    Posts
    106
    Plugin Contributions
    0

    Default Re: 1064 SQL error


  6. #6
    Join Date
    Jul 2011
    Posts
    106
    Plugin Contributions
    0

    Default Re: 1064 SQL error

    Okay, so I found include/index_filters/MT_TEMPLATE/default_filter.php which is for the 'Product Filter Module' if I revert back to the default php, it does not throw out the error but filtering the product listing does not work now, which is definitely an issue then with the default_filter.php provided by the add on module.

    I will have a look for the support threads for that module but in case I can't find anything, here is the code of the default_filter.php for the module... If any one can spot a mistake based on the error message, please do tell.
    Code:
    <?php
    /**
     * default_filter.php  for index filters
     *
     * index filter for the default product type
     * show the products of a specified manufacturer
     *
     * @package productTypes
     * @copyright Copyright 2003-2007 Zen Cart Development Team
     * @copyright Portions Copyright 2003 osCommerce
     * @todo Need to add/fine-tune ability to override or insert entry-points on a per-product-type basis
     * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
     * @version $Id: default_filter.php 6912 2007-09-02 02:23:45Z drbyte $
     */
    if (!defined('IS_ADMIN_FLAG')) {
      die('Illegal Access');
    }
      if (isset($_GET['alpha_filter_id']) && (int)$_GET['alpha_filter_id'] > 0) {
        //$alpha_sort = " and pd.products_name LIKE '" . chr((int)$_GET['alpha_filter_id']) . "%' ";
    	$filterid = (int)$_GET['alpha_filter_id'];
    	switch($filterid)
    	{				
    			case 1:
    				$alpha_sort = " order by pd.products_viewed DESC";
    				break;
    			case 2:
    				$alpha_sort = " order by p.products_price";
    				break;
    			case 3:
    				$alpha_sort = " order by p.products_price DESC";
    				break;
    			case 4:
    				$alpha_sort = " order by pd.products_name";
    				break;
    			case 5:
    				$alpha_sort = " order by p.products_ordered DESC";
    				break;
    			case 6:
    				$alpha_sort = " order by p.products_date_added DESC";
    				break;
    			case 7:
    				$alpha_sort = " order by p.products_date_added";
    				break;
    	}
      } else {
        $alpha_sort = " order by pd.products_viewed DESC";
    $filterid = 1;  
      }
      if (!isset($select_column_list)) $select_column_list = "";
       // show the products of a specified manufacturer
      if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] != '' ) {
        if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id'])) {
    // We are asked to show only a specific category
          $listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, m.manufacturers_name,  p.products_price, p.products_tax_class_id, pd.products_description, if(s.status = 1, s.specials_new_products_price, NULL) AS specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
           from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id , " .
           TABLE_PRODUCTS_DESCRIPTION . " pd, " .
           TABLE_MANUFACTURERS . " m, " .
           TABLE_PRODUCTS_TO_CATEGORIES . " p2c
           where p.products_status = 1
             and p.manufacturers_id = m.manufacturers_id
             and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'
             and p.products_id = p2c.products_id
             and pd.products_id = p2c.products_id
             and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
             and p2c.categories_id = '" . (int)$_GET['filter_id'] . "'" .
             $alpha_sort;
        } else {
    // We show them all
          $listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
          from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " .
          TABLE_PRODUCTS_DESCRIPTION . " pd, " .
          TABLE_MANUFACTURERS . " m
          where p.products_status = 1
            and pd.products_id = p.products_id
            and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
            and p.manufacturers_id = m.manufacturers_id
            and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'" .
            $alpha_sort;
        }
      } else {
    // show the products in a given category
        if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id'])) {
    // We are asked to show only specific category
          $listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
          from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " .
          TABLE_PRODUCTS_DESCRIPTION . " pd, " .
          TABLE_MANUFACTURERS . " m, " .
          TABLE_PRODUCTS_TO_CATEGORIES . " p2c
          where p.products_status = 1
            and p.manufacturers_id = m.manufacturers_id
            and m.manufacturers_id = '" . (int)$_GET['filter_id'] . "'
            and p.products_id = p2c.products_id
            and pd.products_id = p2c.products_id
            and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
            and p2c.categories_id = '" . (int)$current_category_id . "'" .
            $alpha_sort;
        } else {
    // We show them all
          $listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
           from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
           TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " .
           TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id
           where p.products_status = 1
             and p.products_id = p2c.products_id
             and pd.products_id = p2c.products_id
             and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
             and p2c.categories_id = '" . (int)$current_category_id . "'" .
             $alpha_sort;
        }
      }
    
    // set the default sort order setting from the Admin when not defined by customer
      if (!isset($_GET['sort']) and PRODUCT_LISTING_DEFAULT_SORT_ORDER != ' ') {
        $_GET['sort'] = PRODUCT_LISTING_DEFAULT_SORT_ORDER;
      }
    
      if (isset($column_list)) {
        if ((!isset($_GET['sort'])) || (isset($_GET['sort']) && !ereg('[1-8][ad]', $_GET['sort'])) || (substr($_GET['sort'], 0, 1) > sizeof($column_list)) ) {
          for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
            if (isset($column_list[$i]) && $column_list[$i] == 'PRODUCT_LIST_NAME') {
              $_GET['sort'] = $i+1 . 'a';
             // $listing_sql .= " order by p.products_sort_order, pd.products_name";
              break;
            } else {
    // sort by products_sort_order when PRODUCT_LISTING_DEFAULT_SORT_ORDER is left blank
    // for reverse, descending order use:
         // $listing_sql .= " order by p.products_sort_order desc, pd.products_name";
              //$listing_sql .= " order by p.products_sort_order, pd.products_name";
              break;
            }
          }
    // if set to nothing use products_sort_order and PRODUCTS_LIST_NAME is off
          if (PRODUCT_LISTING_DEFAULT_SORT_ORDER == ' ') {
            $_GET['sort'] = '20a';
          }
        } else {
          $sort_col = substr($_GET['sort'], 0 , 1);
          $sort_order = substr($_GET['sort'], 1);
          $listing_sql .= ' order by ';
          switch ($column_list[$sort_col-1]) {
            case 'PRODUCT_LIST_MODEL':
              //$listing_sql .= "p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
              break;
            case 'PRODUCT_LIST_NAME':
              //$listing_sql .= "pd.products_name " . ($sort_order == 'd' ? 'desc' : '');
              break;
            case 'PRODUCT_LIST_MANUFACTURER':
              //$listing_sql .= "m.manufacturers_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
              break;
            case 'PRODUCT_LIST_QUANTITY':
              //$listing_sql .= "p.products_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
              break;
            case 'PRODUCT_LIST_IMAGE':
              //$listing_sql .= "pd.products_name";
              break;
            case 'PRODUCT_LIST_WEIGHT':
              //$listing_sql .= "p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
              break;
            case 'PRODUCT_LIST_PRICE':
    //          $listing_sql .= "final_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
              //$listing_sql .= "p.products_price_sorter " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
              break;
          }
        }
      }
    // optional Product List Filter
      if (PRODUCT_LIST_FILTER > 0) {
        if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] != '') {
          $filterlist_sql = "select distinct c.categories_id as id, cd.categories_name as name
          from " . TABLE_PRODUCTS . " p, " .
          TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " .
          TABLE_CATEGORIES . " c, " .
          TABLE_CATEGORIES_DESCRIPTION . " cd
          where p.products_status = 1
            and p.products_id = p2c.products_id
            and p2c.categories_id = c.categories_id
            and p2c.categories_id = cd.categories_id
            and cd.language_id = '" . (int)$_SESSION['languages_id'] . "'
            and p.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'
          order by cd.categories_name";
        } else {
          $filterlist_sql= "select distinct m.manufacturers_id as id, m.manufacturers_name as name
          from " . TABLE_PRODUCTS . " p, " .
          TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " .
          TABLE_MANUFACTURERS . " m
          where p.products_status = 1
            and p.manufacturers_id = m.manufacturers_id
            and p.products_id = p2c.products_id
            and p2c.categories_id = '" . (int)$current_category_id . "'
          order by m.manufacturers_name";
        }
        $do_filter_list = false;
        $filterlist = $db->Execute($filterlist_sql);
        if ($filterlist->RecordCount() > 1) {
            $do_filter_list = true;
          if (isset($_GET['manufacturers_id'])) {
            $getoption_set =  true;
            $get_option_variable = 'manufacturers_id';
            $options = array(array('id' => '', 'text' => TEXT_ALL_CATEGORIES));
          } else {
            $options = array(array('id' => '', 'text' => TEXT_ALL_MANUFACTURERS));
          }
          while (!$filterlist->EOF) {
            $options[] = array('id' => $filterlist->fields['id'], 'text' => $filterlist->fields['name']);
            $filterlist->MoveNext();
          }
        }
      }
    
    // Get the right image for the top-right
      $image = DIR_WS_TEMPLATE_IMAGES . 'table_background_list.gif';
      if (isset($_GET['manufacturers_id'])) {
        $sql = "select manufacturers_image
                  from   " . TABLE_MANUFACTURERS . "
                  where      manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'";
    
        $image_name = $db->Execute($sql);
        $image = $image_name->fields['manufacturers_image'];
    
      } elseif ($current_category_id) {
    
        $sql = "select categories_image from " . TABLE_CATEGORIES . "
                where  categories_id = '" . (int)$current_category_id . "'";
    
        $image_name = $db->Execute($sql);
        $image = $image_name->fields['categories_image'];
      }
    ?>
    thanks

  7. #7
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: 1064 SQL error

    If I recall correctly, that addon has always had problems. If it's not working for you, don't use it.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    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.

  8. #8
    Join Date
    Jul 2011
    Posts
    106
    Plugin Contributions
    0

    Default Re: 1064 SQL error

    Oh No.... please don't say that. I have to use a filter as there will be quite a few products and will need options to list them. Do you know of another mod that works?

  9. #9
    Join Date
    Jul 2011
    Posts
    106
    Plugin Contributions
    0

    Default Re: 1064 SQL error

    Just wanted to let you guys know that my problem is fixed.

    Not sure if I'm allowed to post external site links here, but try these files if anyone is experiencing the same problem as me....

    contussupport.com/zencart-product-filter.php

  10. #10
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: 1064 SQL error

    You should encourage them to update the addon here, with the latest version.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    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.

 

 
Page 1 of 2 12 LastLast

Similar Threads

  1. 1064 SQL Syntax Error for Column Product Listing.. HELP!!!
    By mooki in forum Setting Up Categories, Products, Attributes
    Replies: 1
    Last Post: 13 Aug 2010, 06:57 PM
  2. Replies: 0
    Last Post: 13 Aug 2010, 06:24 PM
  3. SQL problem with filter by attributes addon
    By daparky in forum All Other Contributions/Addons
    Replies: 0
    Last Post: 6 Jan 2010, 10:16 PM
  4. Replies: 7
    Last Post: 23 Jul 2009, 06:53 AM
  5. error 1064 using music filter search?
    By ooakllc in forum Setting Up Categories, Products, Attributes
    Replies: 3
    Last Post: 1 Aug 2008, 02:08 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