1064 Error - known bug fix not working

[Guest]

Hi, I have the very beautiful error 1064 when trying to see products by manufacturer:

 

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.products_image, pd.products_name, m.manufacturers_name, p.products_quan' at line 1

 

select count(p.products_id) as total select p.products_image, pd.products_name, m.manufacturers_name, p.products_quantity, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, p.products_status, 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 (from products p) left join specials s on p.products_id = s.products_id, products_description pd, manufacturers m where pd.products_id = p.products_id and pd.language_id = '4' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '14'

 

The known bug fix(code change in includes/classes/split_page_results.php) is applied in both admin and catalog side and not solving the problem.

 

I had this error on some other pages (I modified the template) and fixed it using the solution below. I'd use it for this one but don't know where and how to apply it...

 

 

1. >>>>First, look at the following code found in index.php around line 170-190. This code may not exactly match your store:

 

 

// We are asked to show only a specific category

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, 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 from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";

 

 

 

2. >>>>Look for the words 'left join' in the query above. Now look a few lines before the 'left join' and you will see the word 'from'. This part of the code is shown below:

 

 

from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join

 

 

 

3. >>>>The way to fix this code so it will work in MySQL 5 is simply to add an open parenthesis after the 'from', and a close parenthesis before the 'left join' as follows:

 

from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) left join

 

 

4. >>>>So the final "fixed" code would look like this:

 

// We are asked to show only a specific category

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, 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 from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";

 

 

5. >>>>>That's it! Now there is just one catch that you should be aware of. Some queries contain two left joins in one query, and in this case we will have to add our parenthesis a little bit differently. For example, in the same index.php a little bit farther down, around lines 180-200 or so, you will find this query (your file code may not exactly match):

 

// We show them all

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, 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 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 p.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)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

}

 

 

6. >>>> Again, look for the words 'left join' in the code above. You will see it twice this time. Because it is in the code twice, we have to change where we put our parenthesis, as follows. First, lets look at the code starting with the 'from':

 

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

 

 

7.>>>>This time we add TWO open parenthesis after the from, and then one close parenthesis before EACH left join, as follows:

 

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

 

Thank you!

[Guest]

I managed to solve it - not knowing what to do, but guessing. it was an extra bracket added in p left join...