PHP Fatal error: 1064:You have an error in your SQL syntax

[TamyA]

What is this error means and how to fix it?

[16-Jul-2018 01:52:16 CST6CDT] Request URI: /featured-products, IP address: 46.229.168.71
#1 trigger_error() called at [/home/username/public_html/includes/classes/db/mysql/query_factory.php:171]
#2 queryFactory->show_error() called at [/home/username/public_html/includes/classes/db/mysql/query_factory.php:143]
#3 queryFactory->set_error() called at [/home/username/public_html/includes/classes/db/mysql/query_factory.php:270]
#4 queryFactory->Execute() called at [/home/username/public_html/includes/templates/responsive_classic/templates/tpl_modules_products_featured_listing.php:23]
#5 require(/home/username/public_html/includes/templates/responsive_classic/templates/tpl_modules_products_featured_listing.php) called at [/home/username/public_html/includes/templates/responsive_classic/templates/tpl_featured_products_default.php:87]
#6 require(/home/username/public_html/includes/templates/responsive_classic/templates/tpl_featured_products_default.php) called at [/home/username/public_html/includes/templates/responsive_classic/common/tpl_main_page.php:178]
#7 require(/home/username/public_html/includes/templates/responsive_classic/common/tpl_main_page.php) called at [/home/username/public_html/index.php:97]

[16-Jul-2018 01:52:16 CST6CDT] PHP Fatal error: 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 'limit 10' at line 1 :: limit 10 ==> (as called by) /home/username/public_html/includes/templates/responsive_classic/templates/tpl_modules_products_featured_listing.php on line 23 <== in /home/username/public_html/includes/classes/db/mysql/query_factory.php on line 171


fresh installation of Zen cart 1.5.5f with the following addons
user tracking
RSS feed
One page checkout
RCS
Ceon url mapping
flex footer menue
cross sell
sitemap xml

Thanks.

[carlwhat]

it means that your probably did not install one of the add-ons correctly.

i would start by looking at this file:

includes/modules/featured_products.php

and compare it to the original, which can be found here:

https://github.com/zencart/zencart/b...d_products.php

for copy and paste and purposes you can use this link:

https://raw.githubusercontent.com/ze...d_products.php

i think the problem is there.

best.

[TamyA]

it means that your probably did not install one of the add-ons correctly.

i would start by looking at this file:

includes/modules/featured_products.php

and compare it to the original, which can be found here:

https://github.com/zencart/zencart/b...d_products.php

for copy and paste and purposes you can use this link:

https://raw.githubusercontent.com/ze...d_products.php

i think the problem is there.

best.

The files are exactly the same, this is a 2 weeks old installation, files are up-to-date, i copied the gethup file to the site and still the same when trying to display the feature products, i get this error
WARNING: An Error occurred, please refresh the page and try again

And the error from the log files is
[16-Jul-2018 13:22:35 CST6CDT] Request URI: /featured-products, IP address: 5.45.139.150
#1 trigger_error() called at [/home/username/public_html/includes/classes/db/mysql/query_factory.php:171]
#2 queryFactory->show_error() called at [/home/username/public_html/includes/classes/db/mysql/query_factory.php:143]
#3 queryFactory->set_error() called at [/home/username/public_html/includes/classes/db/mysql/query_factory.php:270]
#4 queryFactory->Execute() called at [/home/username/public_html/includes/templates/responsive_classic/templates/tpl_modules_products_featured_listing.php:23]
#5 require(/home/username/public_html/includes/templates/responsive_classic/templates/tpl_modules_products_featured_listing.php) called at [/home/username/public_html/includes/templates/responsive_classic/templates/tpl_featured_products_default.php:87]
#6 require(/home/username/public_html/includes/templates/responsive_classic/templates/tpl_featured_products_default.php) called at [/home/username/public_html/includes/templates/responsive_classic/common/tpl_main_page.php:178]
#7 require(/home/username/public_html/includes/templates/responsive_classic/common/tpl_main_page.php) called at [/home/username/public_html/index.php:97]

[16-Jul-2018 13:22:35 CST6CDT] PHP Fatal error: 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 'limit 10' at line 1 :: limit 10 ==> (as called by) /home/username/public_html/includes/templates/responsive_classic/templates/tpl_modules_products_featured_listing.php on line 23 <== in /home/username/public_html/includes/classes/db/mysql/query_factory.php on line 171

[carlwhat]

what about this file:

includes/templates/responsive_classic/templates/tpl_modules_products_featured_listing.php

is it the same as:

https://github.com/zencart/zencart/b...ed_listing.php

your log says the error is at line 23. and yet the execution of the sql on the github version of the files suggests line 21 is when the sql gets executed.

have you modified that file?

best.

[TamyA]

what about this file:

includes/templates/responsive_classic/templates/tpl_modules_products_featured_listing.php

is it the same as:

https://github.com/zencart/zencart/b...ed_listing.php

your log says the error is at line 23. and yet the execution of the sql on the github version of the files suggests line 21 is when the sql gets executed.

have you modified that file?

best.

Thanks Carl for the quick reply.
The 2 files are not the same, the one on my site had some cosmitc additions like <table> etc. but nothing to do with sql, i replace my file with gethup file again and the same result with the warning mentioned above and the log file fatal error except one thing different which the line number changed from 23 to 20 and both point to this line of code: $featured_products = $db->Execute($featured_products_split->sql_query);


T

[mc12345678]

What about the file: includes/modules/pages/featured_products/header_php.php

And/or the presence of other header_php related files in that directory with a file extension of php?

[TamyA]

What about the file: includes/modules/pages/featured_products/header_php.php

And/or the presence of other header_php related files in that directory with a file extension of php?

Yes i have header_php.php in that directory and it is the only file with the following code

<?php
/**Single Listing Template mod v1.8
 * Featured Products header_php.php
 *
 * @package page
 * @copyright Copyright 2003-2006 Zen Cart Development Team
 * @copyright Portions Copyright 2003 osCommerce
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: header_php.php 6912 2007-09-02 02:23:45Z drbyte $
 */
require(DIR_WS_MODULES . zen_get_module_directory('require_languages.php'));
$breadcrumb->add(NAVBAR_TITLE);
// display order dropdown
$disp_order_default = PRODUCT_FEATURED_LIST_SORT_DEFAULT;

require(DIR_WS_MODULES . zen_get_module_directory(FILENAME_LISTING_DISPLAY_ORDER));

$featured_products_array = array();

$listing_sql = "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, m.manufacturers_id
                                  FROM (" . TABLE_PRODUCTS . " p
                                  LEFT JOIN " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " .
TABLE_PRODUCTS_DESCRIPTION . " pd
                                  LEFT JOIN " . TABLE_FEATURED . " f on pd.products_id = f.products_id )
                                  WHERE p.products_status = 1 and p.products_id = f.products_id and f.status = 1
                                  AND p.products_id = pd.products_id and pd.language_id = :languagesID " .
$order_by;

$listing_sql = $db->bindVars($listing_sql, ':languagesID', $_SESSION['languages_id'], 'integer');
$featured_products_split = new splitPageResults($featured_products_query_raw, MAX_DISPLAY_PRODUCTS_FEATURED_PRODUCTS);

//check to see if we are in normal mode ... not showcase, not maintenance, etc
  $show_submit = zen_run_normal();

$define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,
  'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,
  'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,
  'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,
  'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,
  'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,
  'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE);

  /*                         ,
  'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW);
  */
  asort($define_list);
  reset($define_list);
  $column_list = array();
  foreach ($define_list as $key => $value)
  {
    if ($value > 0) $column_list[] = $key;
  }
?>

[mc12345678]

Yes i have header_php.php in that directory and it is the only file with the following code

<?php
/**Single Listing Template mod v1.8
 * Featured Products header_php.php
 *
 * @package page
 * @copyright Copyright 2003-2006 Zen Cart Development Team
 * @copyright Portions Copyright 2003 osCommerce
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: header_php.php 6912 2007-09-02 02:23:45Z drbyte $
 */
require(DIR_WS_MODULES . zen_get_module_directory('require_languages.php'));
$breadcrumb->add(NAVBAR_TITLE);
// display order dropdown
$disp_order_default = PRODUCT_FEATURED_LIST_SORT_DEFAULT;

require(DIR_WS_MODULES . zen_get_module_directory(FILENAME_LISTING_DISPLAY_ORDER));

$featured_products_array = array();

$listing_sql = "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, m.manufacturers_id
                                  FROM (" . TABLE_PRODUCTS . " p
                                  LEFT JOIN " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " .
TABLE_PRODUCTS_DESCRIPTION . " pd
                                  LEFT JOIN " . TABLE_FEATURED . " f on pd.products_id = f.products_id )
                                  WHERE p.products_status = 1 and p.products_id = f.products_id and f.status = 1
                                  AND p.products_id = pd.products_id and pd.language_id = :languagesID " .
$order_by;

$listing_sql = $db->bindVars($listing_sql, ':languagesID', $_SESSION['languages_id'], 'integer');
$featured_products_split = new splitPageResults($featured_products_query_raw, MAX_DISPLAY_PRODUCTS_FEATURED_PRODUCTS);

//check to see if we are in normal mode ... not showcase, not maintenance, etc
  $show_submit = zen_run_normal();

$define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,
  'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,
  'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,
  'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,
  'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,
  'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,
  'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE);

  /*                         ,
  'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW);
  */
  asort($define_list);
  reset($define_list);
  $column_list = array();
  foreach ($define_list as $key => $value)
  {
    if ($value > 0) $column_list[] = $key;
  }
?>

Next would be to look at the split_page_results class file in includes/classes.

[TamyA]

Next would be to look at the split_page_results class file in includes/classes.

lt is there.

[mc12345678]

lt is there.

As would be expected, but how does it compare to a vanilla version following the process that has been used over the last several posts?

It is this file that is last to create the sql query that is causing the error to occur. Also, some templates have been known to modify the file which is a core file.