Error in categories

[Fredi 98]

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

[Fredi 98]

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

[♥ecartz 692]

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. 

[Fredi 98]

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;

 

[azpro 144]

2 hours ago, Fredi said:

pd.products_idLEFT JOIN manufacturers m

May well be the missing '   ' (SPACE) between pd.products_id and LEFT JOIN  ...

[Fredi 98]

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

 

[ArtcoInc 364]

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

[kgtee 67]

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

[♥ecartz 692]

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. 

[Fredi 98]

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.

[Fredi 98]

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