Configuration of Specials error

[JonathanH]

I have previously deleted all specials. Now I want to add some, but when I choose specials from the left column in admin I get the following error. How can I fix this?

 

Specials

 

Products Products Price Status Action

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 '-20, 20' at line 1

 

select p.products_id, pd.products_name, p.products_price, s.specials_id, s.specials_new_products_price, s.specials_date_added, s.specials_last_modified, s.expires_date, s.date_status_change, s.status from products p, specials s, products_description pd where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = s.products_id order by pd.products_name limit -20, 20

 

[TEP STOP]

[♥Vger]

This is a problem with MySQL 4.1 and osCommerce. In admin/includes/classes/split_page_results.php, around line 37 you'll see these two lines of code:

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

 

Sandwich these lines of code between the two lines above:

 

if ($offset < 0)

{

$offset = 0 ;

}

 

That should resolve the problem.

 

Vger

[JonathanH]

That worked great. Thank you very much.

[aplein]

Hello Vger,

 

I have this problem to.

Look.

 

1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen (diese kann f?r verschiedene Server-Versionen unterschiedlich sein) bei '-20, 20' in Zeile 1

 

select coupon_id, coupon_code, coupon_amount, coupon_type, coupon_start_date,coupon_expire_date,uses_per_user,uses_per_coupon,restrict_to_p

roducts, restrict_to_categories, date_created,date_modified from coupons where coupon_active='Y' and coupon_type != 'G' limit -20, 20

 

can you help me, please.

 

Thanks.

Alfred

[Brykaczek]

Just perform the steps Vger wrote, but for the file:

includes/classes/split_page_results.php

In my script it is around line 66.

 

Cheers,

Brykaczek :)

[Guest]

I have previously deleted all specials. Now I want to add some, but when I choose specials from the left column in admin I get the following error. How can I fix this?

 

Specials

 

Products Products Price Status Action

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 '-20, 20' at line 1

 

select p.products_id, pd.products_name, p.products_price, s.specials_id, s.specials_new_products_price, s.specials_date_added, s.specials_last_modified, s.expires_date, s.date_status_change, s.status from products p, specials s, products_description pd where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = s.products_id order by pd.products_name limit -20, 20

 

[TEP STOP]

[Guest]

I had the same error message, this is it

 

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 '-20, 20' at line 1

 

select p.products_id, pd.products_name, p.products_price, s.specials_id, s.specials_new_products_price, s.specials_date_added, s.specials_last_modified, s.expires_date, s.date_status_change, s.status from products p, specials s, products_description pd where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = s.products_id order by pd.products_name limit -20, 20

 

[TEP STOP]

 

I tryed what you told the other person so my script look like this

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

if($offset<0)

{

$offset=0

}

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

 

and then I got this error when I opened the admin page

 

Parse error: parse error, unexpected '}' in /home/hansenspaintplace.com.au/htdocs/Store/admin/includes/classes/split_page_results.php on line 41

 

can someone please help me

 

Thanks

[Guest]

I had the same error message, this is it

 

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 '-20, 20' at line 1

 

select p.products_id, pd.products_name, p.products_price, s.specials_id, s.specials_new_products_price, s.specials_date_added, s.specials_last_modified, s.expires_date, s.date_status_change, s.status from products p, specials s, products_description pd where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = s.products_id order by pd.products_name limit -20, 20

 

[TEP STOP]

 

I tryed what you told the other person so my script look like this

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

if($offset<0)

{

$offset=0

}

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

 

and then I got this error when I opened the admin page

 

Parse error: parse error, unexpected '}' in /home/htdocs/Store/admin/includes/classes/split_page_results.php on line 41

 

can someone please help me

 

Thanks

[T_love]

I had the same error message, this is it

 

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 '-20, 20' at line 1

 

select p.products_id, pd.products_name, p.products_price, s.specials_id, s.specials_new_products_price, s.specials_date_added, s.specials_last_modified, s.expires_date, s.date_status_change, s.status from products p, specials s, products_description pd where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = s.products_id order by pd.products_name limit -20, 20

 

[TEP STOP]

 

I tryed what you told the other person so my script look like this

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

if($offset<0)

{

$offset=0

}

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

 

and then I got this error when I opened the admin page

 

Parse error: parse error, unexpected '}' in /home/htdocs/Store/admin/includes/classes/split_page_results.php on line 41

 

can someone please help me

 

Thanks

 

$offset=0 needs a semicolon

 

Thanks for this fix guys and girls. I ran into this thread right when i was about to post about the same issue

[Daddyslipdisk]

Phenomonal work Vger :thumbsup: ,

This fixes an issue I've had for weeks now. Thank you so much!

 

Daddyslipdisk

 

This is a problem with MySQL 4.1 and osCommerce. In admin/includes/classes/split_page_results.php, around line 37 you'll see these two lines of code:

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

 

Sandwich these lines of code between the two lines above:

 

if ($offset < 0)

{

$offset = 0 ;

}

 

That should resolve the problem.

 

Vger

[ian 666]

great post vger, that errors been annoyin me for ages :D

[Guest]

This fixes half the problem for me :( i can now view specials on my site but in the admin section i can not add specials and i have deleted all demo specials and admin still says this to me.

 

Reviews:

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 '-20, 20' at line 1

select reviews_id, products_id, date_added, last_modified, reviews_rating from reviews order by date_added DESC limit -20, 20

[TEP STOP]

 

Specials:

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 '-20, 20' at line 1

select p.products_id, pd.products_name, p.products_price, s.specials_id, s.specials_new_products_price, s.specials_date_added, s.specials_last_modified, s.expires_date, s.date_status_change, s.status from products p, specials s, products_description pd where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = s.products_id order by pd.products_name limit -20, 20

[TEP STOP]

 

Products Expected:

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 '-20, 20' at line 1

select pd.products_id, pd.products_name, p.products_date_available from products_description pd, products p where p.products_id = pd.products_id and p.products_date_available != '' and pd.language_id = '1' order by p.products_date_available DESC limit -20, 20

[TEP STOP]

 

Could someone please help me.

[Guest]

ok i found the fix to my problem incase other people have the same problem.

 

Line 67 in catalog/includes/classes/split_page_results.php must be changed from:

$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

to:

$this->sql_query .= " limit " . max($offset, 0) . ", " . $this->number_of_rows_per_page;

Line 38 in catalog/admin/includes/classes/split_page_results.php must be changed from:

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

to:

$sql_query .= " limit " . max($offset, 0) . ", " . $max_rows_per_page;

 

Souce:

http://www.oscommerce.com/community/bugs,1...limit+-20%2C+20

[imadork8317]

This is a problem with MySQL 4.1 and osCommerce. In admin/includes/classes/split_page_results.php, around line 37 you'll see these two lines of code:

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

 

Sandwich these lines of code between the two lines above:

 

if ($offset < 0)

{

$offset = 0 ;

}

 

That should resolve the problem.

 

Vger

 

I had the same problem

 

worked great once again

 

Thanks Vger!

[GuardDog]

Thanks Vger! Your the man. :thumbsup:

[beam752]

I still get this error and have tried all the bug fixes. Nothing seems to work.

 

Displaying 21 to 23 (of 23 products) Result Pages: [<< Prev] 1 2

1064 - You have an error in your SQL syntax near 'limit 20, 20' at line 1

 

select p.products_image, pd.products_name, p.products_sort_order, 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 products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join 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 = '1' and p2c.categories_id = '21' order by limit 20, 20

[lifewell_nutrition]

Worked for me......in my case i had to apply VGER's first solution to both files. The one in the main and the one in admin.

 

Thanks for the solution!! :blush:

[kwalker]

I had the same 1064 error. Problem fixed.

 

Thanks for the tip.

 

 

Kevin

[hobbyfly]

I had a 1064 error message but this solution didn?t work for me.

 

The message is:

"...

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 'EXPECTED_PRODUCTS_SORT limit 5' at line 1

 

select p.products_id, pd.products_name, products_date_available as date_expected from products p, products_description pd where to_days(products_date_available) >= to_days(now()) and p.products_id = pd.products_id and pd.language_id = '4' order by EXPECTED_PRODUCTS_FIELD EXPECTED_PRODUCTS_SORT limit 5

..."

 

 

Maybe its a date problem in the database. But I?m not sure.

[jeffy777]

I tired Vger's solution, but the error was still there.

 

But this worked for me:

 

In admin/includes/classes/split_page_results.php

 

Find:

if ($current_page_number > $num_pages) {
$current_page_number = $num_pages;
}

 

Replace with:

if (($current_page_number > $num_pages) and ($num_pages > 0)) {
$current_page_number = $num_pages;
}

[hobbyfly]

I still have the problem.

The limit ($offset) is not the problem.

 

Someone can help me?

 

Hobbyfly

 

 

 

 

 

 

I had a 1064 error message but this solution didn?t work for me.

 

The message is:

"...

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 'EXPECTED_PRODUCTS_SORT limit 5' at line 1

 

select p.products_id, pd.products_name, products_date_available as date_expected from products p, products_description pd where to_days(products_date_available) >= to_days(now()) and p.products_id = pd.products_id and pd.language_id = '4' order by EXPECTED_PRODUCTS_FIELD EXPECTED_PRODUCTS_SORT limit 5

..."

Maybe its a date problem in the database. But I?m not sure.

[Guest]

I had this problem after changing to MySQL4.1, I worked out pretty quickly that it was the limit that was causing the problem so I used the same code to set $offset to zero if it was negative. Having been looking at the HSBC secure payment topic regularly I should really have noticed that Vger had already sorted this problem.

 

The supplied code should definately resolve this problem, if not check your MySQL version. To prove it further remove the limit part of the sql query and try again, if you get the same error then something else is causing it.

[SilverRayn5]

I have a similar, but different error which keeps coming up on my search. I've tried the code fixes, but nothing is working.

 

Here's my 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 'sing(manufacturers_id) left join specials s on p.products_id =

 

Any help would be greatly appreciated.

 

Kimberly

[SilverRayn5]

Found my problem!

 

Ok...if anyone else out there is having this problem, here's the solution I used.

 

In the oscommerce milestone update, you will see these instructions:

 

------------------------------------------------------------------------------

MySQL 5.0 Compatibility

------------------------------------------------------------------------------

 

Problem:

 

MySQL 5.0 introduces Server SQL modes as part of its SQL 2003 standards support, and uses a more stricter approach to executing SQL queries. This is performed by default with setting STRICT_TRANS_TABLES as a Server SQL mode.

 

Due to this new setting, MySQL fails on certain SQL queries and produces error messages on the screen.

 

Solution:

 

Lines 213-223 in catalog/advanced_search_result.php must be changed from: << POSSIBLY A PROBLEMATIC REPLACEMENT >>

 

$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 

if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

if (!tep_session_is_registered('customer_country_id')) {

$customer_country_id = STORE_COUNTRY;

$customer_zone_id = STORE_ZONE;

}

$from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_ZONES_TO_GEO_ZONES . " gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";

}

 

$where_str = " where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";

 

to:

 

$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";

 

if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

if (!tep_session_is_registered('customer_country_id')) {

$customer_country_id = STORE_COUNTRY;

$customer_zone_id = STORE_ZONE;

}

$from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_ZONES_TO_GEO_ZONES . " gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";

}

 

$from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 

$where_str = " where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";

 

------------------------------------------------------------------------------

 

 

I changed the code I had updated back to the original code and it now my site search works perfectly.

 

Kimberly

[ZarSu]

Thank You, that worked great, solved numerous errors, was pulling my hair our, installing, reinstalling, reconfiguring etc. :thumbsup:

 

ok i found the fix to my problem incase other people have the same problem.

 

Line 67 in catalog/includes/classes/split_page_results.php must be changed from:

$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

to:

$this->sql_query .= " limit " . max($offset, 0) . ", " . $this->number_of_rows_per_page;

Line 38 in catalog/admin/includes/classes/split_page_results.php must be changed from:

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

to:

$sql_query .= " limit " . max($offset, 0) . ", " . $max_rows_per_page;

 

Souce:

http://www.oscommerce.com/community/bugs,1...limit+-20%2C+20