355 replies to this topic

[OceanRanch]

You'll need to apply the fix to both catalog and admin versions of split_page_results.php.

See bug report

http://www.oscommerce.com/community/bugs,1605

HTH
Tom

[toby_uk]

Hi,

Thanks for the reply.

I applied the fix to admin/includes/class/split_page_results.php, however I tried changing the includes/classes/split_page_results.php but the file is different to the admin one and the lines:

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

Do not exsist in that file; changing the admin file did allow me to view the orders page (resolved the 1064 error). However now when I try and delete Manufacturers the following error is displayed:

Warning: Cannot modify header information - headers already sent by (output started at /home/sansal00/public_html/admin/includes/classes/split_page_results.php:109) in /home/sansal00/public_html/admin/includes/functions/general.php on line 18

Any idea's what is causing this problem?

Thanks

Edited by toby_uk, 12 August 2005, 10:07.

[webskate]

Simplyeasier, on Apr 1 2005, 09:10 AM, said:

Hi 

First off - this fix is actually already recorded in the contributions section and many thanks to those who recorded it - but I don't think it is a contribution - rather a tip - Secondly the same problem has arisen for 4 people in a few days and I dare say it will happen to more as hosts migrate their mySQL databases to version 4.1.xx -

Hence I post here 

If you get an error whilst creating \ modifying your database such that thro admin you delete all entries on a particular table you will likely get this message

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

followed by something like

select manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified from manufacturers order by manufacturers_name limit -20, 20

or

select banners_id, banners_title, banners_image, banners_group, status, expires_date, expires_impressions, date_status_change, date_scheduled, date_added from banners order by banners_title, banners_group limit -20, 20

depending on which table you have deleted all records from.

To fix this

In admin/includes/classes find split_page_results.php and - BACK IT UP then find the lines

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

change to

$offset = ($max_rows_per_page * ($current_page_number - 1));
if ($offset < 0)
{
$offset = 0 ;
}
$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
MySQL 4.1.xx handles negatives correctly (by forcing an error) in the code above unlike earlier versions of MySQL.

  NOTE - I have NOT asked you to change catalog/includes/classes/split_page_results.php  - If you have a problem on the catalog side you may want to try the same change in that file - I did and it went wrong so the change to my catalog/includes/classes/split_page_results.php  file is a little more elaborate and I will share that here if it becomes an issue - but for now it seems people will mostly have a problem with empty files on the admin side.

hth

Charles

<{POST_SNAPBACK}>

Thanks for that, my site is now back working

caroline

[toby_uk]

toby_uk, on Aug 12 2005, 10:07 AM, said:

Hi,

Thanks for the reply.

I applied the fix to admin/includes/class/split_page_results.php,  however I tried changing the includes/classes/split_page_results.php but the file is different to the admin one and the lines:

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

Do not exsist in that file; changing the admin file did allow me to view the orders page (resolved the 1064 error). However now when I try and delete Manufacturers the following error is displayed:

Warning: Cannot modify header information - headers already sent by (output started at /home/sansal00/public_html/admin/includes/classes/split_page_results.php:109) in /home/sansal00/public_html/admin/includes/functions/general.php on line 18

Any idea's what is causing this problem?

Thanks

<{POST_SNAPBACK}>

It appears the fix did indeed work, but through my stupidness I created another error, I left a space below ?>

[toby_uk]

Hi,

I'm back again, I thought this problem had been fixed, but unfortunately not. I applied the fix to admin/includes/class/split_page_results.php, however I tried changing the includes/classes/split_page_results.php but the file is different to the admin one so didn't know which bit to change. The change meant I am able to update from the admin side with no problems, now however the 'specials' page results in 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 '-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]

See the problem Here

Can anybody help, I have no idea what to do now

Thanks

[Jan Zonjee]

toby_uk, on Aug 25 2005, 10:43 AM, said:

I have no idea what to do now 

The code is pretty similar. $offset is determined in line 65 and should not be lower than 0.
This should work:

      $offset = ($this->number_of_rows_per_page * ($this->current_page_number - 1));
      // begin make sure that $offset is not lower than 0
      if ($offset < 0 ) {
       $offset = 0;
      }
      // end make sure that $offset is not lower than 0
      $this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

[toby_uk]

Thank you JanZ that seems to have done the trick

[fabr]

I am also getting a similar error, but the fixes don't seem to be working. There are no orders and when you click on customers/orders, you get the following error:

"Orders Order ID:
Status: All OrdersPendingProcessingDelivered

Customers Order Total Date Purchased 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 '-30, 30' 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 -30, 30

[TEP STOP]"

CODE:

admin/includes/classes/split_page_results.php:


$offset = ($max_rows_per_page * ($current_page_number - 1));
// begin make sure that $offset is not lower than 0
if ($offset < 0 ) {
$offset = 0;
}
// end make sure that $offset is not lower than 0
$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
}

catalog/includes/classes/split_page_results.php:

$offset = ($this->number_of_rows_per_page * ($this->current_page_number - 1));
// begin make sure that $offset is not lower than 0
if ($offset < 0 ) {
$offset = 0;
}
// end make sure that $offset is not lower than 0
$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

Any suggestions?

Thank you,

Rebecca

[fabr]

Works OK for now.

Rebecca

[lauren4521]

Just a thank you to Charles for this. Just fixed my problem and I couldn't be happier that it was so easy!!!

Lauren

[Qihun]

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

select distinct p.products_id, p.products_image, p.products_model, p.products_quantity, p.products_weight, pd.products_name, p.manufacturers_id, p.products_price, p.products_date_added, 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, m.manufacturers_name, pd.products_description, cd.categories_name from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join categories_description cd on p2c.categories_id = cd.categories_id 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 cd.language_id = '1' order by cd.categories_name DESC, pd.products_name LIMIT -10, 10

i getting this error on my catalog side when Page Cache is enabled , if i loged in (Page Cache disabled) , so everything fine

i tried all suggestions from this thread and more , but still having trouble.
Please , if somebody have any idea what is causing this problem ...
Thank You !

[noppie]

Ok I am having this problem on the catalog side.

I applied the bug fix from http://www.oscommerce.com/community/bugs,1605
on both my admin/includes/classes/split_page_results.php
the admin works great


and /includes/classes/split_page_results.php
this is from the catalog side

Quote

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

and /includes/classes/split_page_results.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 'where lc.link_categories_id = lcd.link_categories_id and lc.link_categories_stat' at line 1

select lc.link_categories_id, lcd.link_categories_name, lcd.link_categories_description,peacem_link_categories lc, peacem_link_categories_description lcd where lc.link_categories_id = lcd.link_categories_id and lc.link_categories_status = '1' and lcd.language_id = '1' order by lcd.link_categories_name

[TEP STOP]

I even added a link to the database and still get this error.
please help if you can.

I added this to a different program and didn't have thiis problem

[bruno_caramiel]

I got a similar problem which I'm describing here :

http://forums.oscommerce.com/index.php?showtopic=168606&st=

Anyone to help me ?

[axioma]

I have tried the fix proposed here, but I still have error. I' 'using sort order by date on clients, and the problem shows itself when I try to do a search (search box) of any client.

MY VERSION IS 4.0.22-standard

Clientes Buscar:


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 'where c.customers_lastname like '%lero%' or c.customers_firstna

select count(*) as total from customers c, address_book a, customers_info ci where c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id and c.customers_id = ci.customers_info_id where c.customers_lastname like '%lero%' or c.customers_firstname like '%lero%' or c.customers_email_address like '%lero%'

THANKS FOR YOUR HELP

[emon]

rootuser, on Jun 21 2005, 05:41 AM, said:

I fixed it thanks!

The fix for this is:

Edit these two files:
admin/includes/classes/split_page_results.php
/includes/classes/split_page_results.php

Insert:

if ($offset < 0)
{
$offset = 0 ;
}

Just before this line:
$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

<{POST_SNAPBACK}>

Thanks a lot for the fix ....

[MechBun]

Hi, thanks for posting this, unfortunately it did not fix my problem. =(

Here is what I am getting on catalog/account_history.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 '-10, 10' at line 1

select o.orders_id, o.date_purchased, o.delivery_name, o.billing_name, ot.text as order_total, s.orders_status_name from orders o, orders_total ot, orders_status s where o.customers_id = '20' and o.orders_id = ot.orders_id and ot.class = 'ot_total' and o.orders_status = s.orders_status_id and s.language_id = '1' order by orders_id DESC limit -10, 10

I did just change my orders.php but I had to in order to get the Cash/Check orders to show up in my admin section again.

Help!

Here is my /includes/classes/split_page code

  $offset = ($this->number_of_rows_per_page * ($this->current_page_number - 1));
     // begin make sure that $offset is not lower than 0
     if ($offset < 0 ) {
      $offset = 0;
     }
     // end make sure that $offset is not lower than 0
     $this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

[MechBun]

PS! It only shows up when the customer actually has orders, if they have no orders everything looks normal on the page.

[MechBun]

Ah! I am getting closer!! Now the account history page comes up properly, however, no orders are actually show in there. I made some new orders but they just wont show up in the account_history.php.

I'll be working on it too, but any help would be appreciated, thanks!

[mr_biggles]

ok,

did a 'google' and found the post:

http://forums.oscommerce.com/index.php?showtopic=144095&st=0

sorted it.

[richaldridge]

I am having the same problem, but running PHP 4.4.0 - I have changed the code as suggested with no luck.

Can anyone suggest where I need to look to fix this? Is there something I need to alter within the database itself?

Cheers,
Richard