Fredi 98 Posted October 14, 2020 (edited) Phoenix 1.0.7.9 Dear Lee, I got to version 1.0.7.9 but the bug has changed only partially. The error is present. 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 'SELECT p.*, pd.*, m.*, IF(s.status, s.specials_new_products_price, NULL) AS spec' at line 1 SELECT COUNT(p.products_id) AS total SELECT p.*, pd.*, m.*, IF(s.status, s.specials_new_products_price, NULL) AS specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) AS final_price, p.products_quantity AS in_stock, IF(s.status, 1, 0) AS is_special FROM products p LEFT JOIN specials s ON p.products_id = s.products_id INNER JOIN products_description pd ON p.products_id = pd.products_idLEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id INNER JOIN products_to_categories p2c 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 = 32 The reason the error appears, I think, is related to this code: p.*, pd.*, m.* --- REASON -- * Probably the same reason and error in Admin - options. Edited October 14, 2020 by Fredi Support forum for osCommerce in russian language - from Ashkelon. Support since 2002.Best regards, Fredi Share this post Link to post Share on other sites
Fredi 98 Posted October 14, 2020 (edited) If you need a special PHP configuration on the server - tell me what you need to fix. I'll fix this easily on my server. Edited October 14, 2020 by Fredi Support forum for osCommerce in russian language - from Ashkelon. Support since 2002.Best regards, Fredi Share this post Link to post Share on other sites
♥ecartz 692 Posted October 14, 2020 1 hour ago, Fredi said: SELECT COUNT(p.products_id) AS total SELECT Whenever I have encountered this problem, it was because the admin/includes/classes/split_page_results.php file was using case sensitive string functions. So I went through and changed them to be case insensitive. So one possibility would be that you aren't using the standard version of that file. That's reinforced by the appearance of p.products_id in the COUNT clause, although that might suggest that this isn't using split_page_results.php at all. At line 21, the standard version has $pos_from = stripos($sql_query, ' from'); The next word after total should be FROM, not SELECT. I would check what version of split_page_results.php is being used. Then check that there isn't an App that uses something else instead. Always back up before making changes. Share this post Link to post Share on other sites
Fredi 98 Posted October 14, 2020 version 1.0.7.9 My file: Copyright (c) 2020 osCommerce Released under the GNU General Public License */ class splitPageResults { private $current_page_number; function __construct(&$current_page_number, $max_rows_per_page, &$sql_query, &$query_num_rows) { $this->current_page_number = empty($current_page_number) ? 1 : (int)$current_page_number; $pos_to = strlen($sql_query); $pos_from = stripos($sql_query, ' from'); $pos_group_by = strripos($sql_query, ' group by', $pos_from); if (($pos_group_by < $pos_to) && ($pos_group_by != false)) $pos_to = $pos_group_by; $pos_having = strripos($sql_query, ' having', $pos_from); if (($pos_having < $pos_to) && ($pos_having != false)) $pos_to = $pos_having; Support forum for osCommerce in russian language - from Ashkelon. Support since 2002.Best regards, Fredi Share this post Link to post Share on other sites
azpro 144 Posted October 14, 2020 2 hours ago, Fredi said: pd.products_idLEFT JOIN manufacturers m May well be the missing ' ' (SPACE) between pd.products_id and LEFT JOIN ... Share this post Link to post Share on other sites
Fredi 98 Posted October 14, 2020 Checked all the files that contain this expression. Added a space in one file. The error remains. 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 'SELECT p.*, pd.*, m.*, IF(s.status, s.specials_new_products_price, NULL) AS spec' at line 1 SELECT COUNT(p.products_id) AS total SELECT p.*, pd.*, m.*, IF(s.status, s.specials_new_products_price, NULL) AS specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) AS final_price, p.products_quantity AS in_stock, IF(s.status, 1, 0) AS is_special FROM products p LEFT JOIN specials s ON p.products_id = s.products_id INNER JOIN products_description pd ON p.products_id = pd.products_id LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id INNER JOIN products_to_categories p2c 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 = 44 Support forum for osCommerce in russian language - from Ashkelon. Support since 2002.Best regards, Fredi Share this post Link to post Share on other sites
ArtcoInc 364 Posted October 14, 2020 SELECT COUNT(p.products_id) AS total SELECT p.*, pd.*, m.*, IF(s.status, s.specials_new_products_price, NULL) AS specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) AS final_price, p.products_quantity AS in_stock, IF(s.status, 1, 0) AS is_special FROM products p LEFT JOIN specials s ON p.products_id = s.products_id INNER JOIN products_description pd ON p.products_id = pd.products_id LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id INNER JOIN products_to_categories p2c 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 = 44 I find it useful to format the statements to better understand what's going on ... 1) you have 2 select statements 2) Aren't you supposed to have commas after each WHERE/AND line (except the last line)? 3) I'm not sure if there are supposed to be commas after the JOIN/ON lines ... HTH Malcolm Get the latest Responsive osCommerce CE (community edition) here . Share this post Link to post Share on other sites
kgtee 67 Posted October 14, 2020 (edited) I have seen many examples of nested SELECT encapsulated with brackets. Perhaps this is what it should be. https://www.w3resource.com/mysql/subqueries/index.php SELECT COUNT(p.products_id) AS total FROM (SELECT ...... Edited October 14, 2020 by kgtee Share this post Link to post Share on other sites
♥ecartz 692 Posted October 15, 2020 3 hours ago, ArtcoInc said: 2) Aren't you supposed to have commas after each WHERE/AND line (except the last line)? 3) I'm not sure if there are supposed to be commas after the JOIN/ON lines ... No. Only commas in the list between SELECT and FROM. This is not supposed to be a subquery. It's just supposed to replace the SELECT list, of, things with SELECT COUNT(*) It works by looking for ' FROM'. For some reason, it is not finding ' FROM'. If it is using the standard split class, go into the calling files and add an extra space before the FROM. Not a tab or anything else. A space. Always back up before making changes. Share this post Link to post Share on other sites
Fredi 98 Posted October 15, 2020 14 hours ago, ecartz said: No. Only commas in the list between SELECT and FROM. This is not supposed to be a subquery. It's just supposed to replace the SELECT list, of, things with SELECT COUNT(*) It works by looking for ' FROM'. For some reason, it is not finding ' FROM'. If it is using the standard split class, go into the calling files and add an extra space before the FROM. Not a tab or anything else. A space. Thank You Matt! Problem resolved. Add axtra space. Thanks, Fred. Support forum for osCommerce in russian language - from Ashkelon. Support since 2002.Best regards, Fredi Share this post Link to post Share on other sites
Fredi 98 Posted October 15, 2020 (edited) Thank You Matt! The problem is missing spaces. Corrected 3 files: product_new.php specials.php includes/modules/content/index_products/cm_ip_product_listing.php😻 Edited October 15, 2020 by Fredi Support forum for osCommerce in russian language - from Ashkelon. Support since 2002.Best regards, Fredi Share this post Link to post Share on other sites