Jump to content

Recommended Posts

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 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

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 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
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

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
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

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
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
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
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

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 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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×