Configuration of Specials error

[celiaak]

I have the same pro8lem:

 

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

 

select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.customers_id = '51' and o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by orders_id DESC limit -40, 40

 

I tried to sandwich the code 8etween those two lines:

 

$offset = ($this->number_of_rows_per_page * ($this->current_page_number - 1));

 

 

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

}

 

These line are somewhat different rom Vger's, the have a $this.

Should I paste the code somwhere else?

Thank you!

[kikePTY]

THESER ARE MY ACTUAL VERSIONS:

PhpMyAdmin 2.5.3

MySQL 4.1.7

 

 

 

i got the following ERRORS setting in the Admin Area:

 

*****************

order.php

 

 

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 o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '3' and ot.class = 'ot_total' order by o.orders_id DESC limit -20, 20

 

[TEP STOP]

 

 

********************

geo_zones.php

 

 

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 a.association_id, a.zone_country_id, c.countries_name, a.zone_id, a.geo_zone_id, a.last_modified, a.date_added, z.zone_name from zones_to_geo_zones a left join countries c on a.zone_country_id = c.countries_id left join zones z on a.zone_id = z.zone_id where a.geo_zone_id = 2 order by association_id limit -20, 20

 

[TEP STOP]

 

 

************************

 

stats_customers.php

 

 

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 c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum from customers c, orders_products op, orders o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC limit -20, 20

 

 

**************************

 

stats_products_purchased.php

 

 

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, p.products_ordered, pd.products_name from products p, products_description pd where pd.products_id = p.products_id and pd.language_id = '3' and p.products_ordered > 0 group by pd.products_id order by p.products_ordered DESC, pd.products_name limit -20, 20

 

[TEP STOP]

 

 

****************************

 

 

If someone can helpme.

 

Thanks a lot!

 

 

 

:)

[edzle]

Thank you so much for this forum.

I'm new in configuring osCommerce and for the last 3 nights even in my sleep thinking about what did I do wrong, why is this mysql errors kept on showing up, why why why why??????

 

Then the lights at the end of the tunnel suddenly brightened when I saw the same problem as I have.

 

I did exactly what it says, and VOILA, just like magic, works perfectly...whoooaaaa....

 

Thanks guys.... :D

[tec]

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

 

i have tried everything that everyone has said to do, but i cant get the errors to go away..

 

errors on the admin side

 

SALE MAKER

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

 

select sale_id, sale_status, sale_name, sale_deduction_value, sale_deduction_type, sale_pricerange_from, sale_pricerange_to, sale_specials_condition, sale_categories_selected, sale_categories_all, sale_date_start, sale_date_end, sale_date_added, sale_date_last_modified, sale_date_status_change from salemaker_sales order by sale_name limit -10, 10

 

FEATURED

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

 

select p.products_id, pd.products_name, s.featured_id, s.featured_date_added, s.featured_last_modified, s.expires_date, s.date_status_change, s.status from products p, featured 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 -10, 10

 

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

 

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

 

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 '-10, 10' 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 -10, 10

 

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 '-10, 10' 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 -10, 10

 

ADMIN>REPORTS>VISITORS

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

 

SELECT * from visitors WHERE browser_language != 'robot' AND browser_language != 'Google' AND browser_language != 'Yahoo!' AND browser_language != 'MSN' ORDER BY online DESC limit -10, 10

 

there are a couple more but i am assuming that one fix will fix them all??

[tec]

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.

 

 

i have the same errors on the same pages except instead of -20, 20

mine is -10, 10

 

this site is live, this happened after moving it, please, help, it has been broken now for over a week

[lgicraig]

I know this is a old post, but just wanted to thank Vger as a new member to the forum for fixing a major headache for me!

[maan]

got this problem please help

 

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 10' 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 = '1' order by EXPECTED_PRODUCTS_FIELD EXPECTED_PRODUCTS_SORT limit 10

 

i treid every thing you wrote, but it didnt work out

thanks

[madcrazygirl]

Me too I have just tried all of the fixes in this thread and error message disappears but still no specials to be seen on catalog side and in admin they are inactive despite date.

[madcrazygirl]

Ok hope this helps others.

 

Following reading many threads lol and changing lots of code and not being able to work out why no specials appeared thought would have a go at deleting them and then adding again.

 

And hey this desperate measure works and they now appear again. Bit of a newbie but guess old table was corrupt in some way and needs new info after code edit?

[Guest]

got this problem please help

 

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 10' 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 = '1' order by EXPECTED_PRODUCTS_FIELD EXPECTED_PRODUCTS_SORT limit 10

 

i treid every thing you wrote, but it didnt work out

thanks

 

you're missing a coma

order by EXPECTED_PRODUCTS_FIELD, EXPECTED_PRODUCTS_SORT limit 10

 

assuming everything else is installed properly for this module. So locate the script/query that causes it and add the coma as indicated above.

[hakand]

Vger Thanks for the info . It was so easy to repair the configuration and helped me much :thumbsup:

[dmodegirl]

I have the following 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 '-6, 6' at line 1

 

select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added DESC limit -6, 6

 

 

What can I do to fix it? I already tried the fix for the -20, 20 error and that fixed my newsletter problem but now this one remains on my Specials page...

 

Thanks!

[alqamardesigns]

Hi, seems like its working for everyone but me!

 

I get this 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 '-9, 9' at line 1

 

select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added DESC limit -9, 9

 

[TEP STOP]

 

^ I added the lines our friend Vger as:

 

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

 

if ($offset < 0)

{

$offset = 0 ;

}

 

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

 

 

but no good, same error. can someone help

[greatbay]

That worked great. Thank you very much.

JonathanH

 

My brain is not right im still baffeled. :'(

[Guest]

Hello members:

 

I've been looking and looking for a solution to fix this 1064-Syntax error, it is what I get when I try to backup my database:

 

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 'type,length,dorder from ss_products_options' at line 1

 

select id,productid,name,description,default,type,length,dorder from ss_products_options

 

I searched around and tried many different solutions and doesn't help! Any idea what I am missing here! I am using a purchased/modified templatemonster of ocCommerce 2.2 MS2 tho

 

Thank you and regards to all,

 

NATTARIOUS

[tompus1]

Hi, I know everyone has been asking the same question but i'm still struggling with mine

 

Orders.php

 

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 o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.customers_id = '82' and o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by orders_id DESC limit -20, 20

 

[TEP STOP]

 

Sorry to be annoying but it's important i sort it out asap.

 

Regards

 

Tom

[arzooloo]

Folks,

 

I am having the following issue when I try to look at the orders by customer.

I tried all the suggestions above and changed code and the SQL error disappears but instead it will not return anything for my query and shows 0 of 0 records.

 

Pls help. I am new at this and really struggling. :(

 

If you cannot assist even a suggestion for where to go for paid support would be appreciated. MySQL version is 4.1.11 and am running osCommnerce 2.2-MS2

 

My error 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 '-10, 10' at line 1

 

select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by o.orders_id DESC limit -10, 10

 

[TEP STOP]

 

Thanks,

Penny

[shaz]

thanks alot its working :)

 

hip hip hurray

 

thank you very much

[Guest]

I've tried the changes suggested and I am still getting this error when I try to upload the database via myphpadmin:

 

MySQL said: Documentation

#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 'address_book`

--

 

CREATE TABLE `address_book` (

`address_book_id` int(11) NOT ' at line 1

 

It asks for a "SQL compatibility mode" but I have tried them all to no avail?

 

I'm moving from one host to another, the company that built the site for me is gone and I have no experience with any of this so your help is most appreciated!

[grita]

help me!!!... i have done all the fixes.. and now, when a i search a product i dont' have in the db there is no problem, but if i have it, next is the text that appears:

 

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 'limit 0, 10' at line 1

 

select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '3' order by p.products_date_added DESC, pd.products_name limit 0, 10 limit 0, 10

 

 

Please help me.:)

[muhammad_barki]

Can anyone assist please... I have tried all the different fixes offered but to no avail.

 

I am getting the following error on orders, manufacturers, specials, reviews, Products Expected, Customers, all reports, etc:

 

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