specials.php problem

[reign^]

Hello all,

 

I hope you guys can help me out with my problem. I got the next error:

 

-

 

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 p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '3' and s.status = '1' order by s.specials_date_added desc limit 10

 

[TEP STOP]

 

-

 

select count(*) as total from sessions where sesskey = 'cec34c13178ac1c0faf3695afb907a79'

 

[TEP STOP]

 

 

Warning: Unknown(): A session is active. You cannot change the session module's ini settings at this time. in Unknown on line 0

 

This problem accoured since I switched between webhosters. First an error accoured because the register_globals weren't turned on, now they are finally. But now this error popped onto my screen. Has anyone an idea of the problem?

 

This error is online visible at www.allbad.nl/catalog .

 

Please help me out, thanx in advance!

[namynot]

I am having the same problem with several pages...

 

specials.php

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

 

Get Them While They're Hot!

 

 

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]

 

admin/reviews.php

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

 

Reviews

 

Products Rating Date Added 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 reviews_id, products_id, date_added, last_modified, reviews_rating from reviews order by date_added DESC limit -20, 20

 

[TEP STOP]

 

 

admin/specials.php

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

 

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]

 

 

admin/products_expected.php

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

 

Products Expected

 

Products Date Expected 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 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]

 

 

Any help?

[namynot]

Hey Reign, I've found our answer... it was on another post on this board, referneced at another forum (http://www.websitetemplatedesign.com/forum/index.php?showtopic=123&st=0&p=394entry394)

 

<Quote>

I have actually answered my own question and I though I should share it with any/everyone who may need this question answered in the future...

 

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

 

<End Quote>

 

Thanks Jonathan

 

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

 

The above fixed my Admin pages with errors. I still had the error on the Specials page. Here is what I did to fix the (catalog/specials.php) page error.

 

Backup catalog/includes/split_page_results.php

 

Find

 

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

 

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

 

Replace with

 

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

if ($offset < 0)

{

$offset = 0 ;

}

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

 

 

-------

 

This should get you running...

[reign^]

2bad, it's not working for me, I do not get the error that something is wrong with the SQL, but it just gives that error. Is there something else i can do?

 

thanks in advance

[reign^]

something i forgot to mension:

 

The new webhost:

 

PHP version: PHP Version 4.3.2

MySQL version: Client API version 3.23.58

 

The old webhost:

 

PHP version: PHP Version 4.3.11

MySQL version: Client API version 4.1.11

 

Maybe this can clear some things up.

[reign^]

found the problem, because we are using an older version of MySQL, some things didn't worked out as they should. We inplanted a piece of scripting from ourselfs but it seemed that because we used the command: mysql_close();, the connection for oscommerce was terminated aswell, strange thing is that this didn't happened on our old host, who uses a newer version of MySQL. Well thanks for anything, i now finally know what was wrong <--- STUPID ME