mySQL Error 1064

[tompus1]

Hi, I'm administrator of a light shopping website.

 

I have little knowledge of PHP and whilst I can generally do the things I need to change the site when necessary, I appear to have messed something up somewhere. The admin section of the site is no longer registering orders so customers pay through WorldPay yet we are not acknowledged via an email and the order doesn't show up in the 'orders' section of the admin.

 

When clicking on customers>orders i receive an 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 '-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]"

 

Can anyone give me a solution to this? I am fine editing the syntax once I know the bit that needs changing.

 

Regards,

 

Tom Hiddleston

[Deivydask]

I have the same problem in my website http://www.alavijai.xz.lt/catalog/

Can anyone help?

[tompus1]

Anyone?

 

I know there are a lot of topics referring to 1064 errors so i'm not being ignorant. I'm just struggling to match those solutions with my problem. Some help would be very very useful.

 

Tom

[winddancer]

Anyone?

 

I know there are a lot of topics referring to 1064 errors so i'm not being ignorant. I'm just struggling to match those solutions with my problem. Some help would be very very useful.

 

Tom

 

 

I am having the same problem. It's due to the Limit using negative values. mysql doesn't seem to like that. (4.1 here)

 

I am working on a fix...

[tompus1]

Sounds good.

 

Keep me informed please.

 

Tom

[winddancer]

ok... that was pretty easy...

 

find the file /admin/includes/classes/split_page_results.php

 

there's a passage like:

 

	  $offset = ($max_rows_per_page * ($current_page_number - 1));
  $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

 

mysql 4.1, doesn't like negative values - as stated before.

 

so exchange that passage with that:

 

	   /*
  $offset = ($max_rows_per_page * ($current_page_number - 1));
  $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
  */

	  $offset = ($max_rows_per_page * ($current_page_number - 1));
	if ($offset < 0) {
		$offset = 0;
	}
	$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

 

I left the old code in, just deactivated it. Just in case there will be future updates.

 

Kind regards

 

Sven

[tompus1]

Thanks, that's got rid of the error screen. Just got to see if it records orders - that was the other problem.

 

Thanks again,

 

Tom

[Deivydask]

I founded this two lines:

      $offset = ($max_rows_per_page * ($current_page_number - 1));
     $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

and replaced to:

       /*
     $offset = ($max_rows_per_page * ($current_page_number - 1));
     $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
     */

         $offset = ($max_rows_per_page * ($current_page_number - 1));
       if ($offset < 0) {
           $offset = 0;
       }
       $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

But nno results.. I get that error like before <_<

[winddancer]

Unfortunately I only get a "not found" when I click your shop address.

 

can you repost the complete error msg please?

[Deivydask]

Unfortunately I only get a "not found" when I click your shop address.

 

can you repost the complete error msg please?

This is new url http://www.parduotuve.xz.lt/catalog/

[Deivydask]

what to do :|

[Jan Zonjee]

what to do :|

How about doing the same change on the catalog side now that you have done it on the admin side?

 

Or better yet, upgrade your shop to at least the 2006 update (use manual instructions because you use a template) before you post again with "I now get a 1054 - Unknown column 'p.products_id' in 'on clause' HELP What can I do" :)

 

You can download RC 1 here. In the folder extras you can find update-20060817.txt

[Deivydask]

thankyou!! its all working :lol: thx you all guys

[Guest]

thankyou!! its all working :lol: thx you all guys

 

 

Hi,

I tried the solution above but still getting the same 1064 error. What did you do to correct?

Thanks

Zali