Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

1064 - You have an error in your SQL syntax


Simplyeasier

Recommended Posts

I get this:

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

 

select customers_name, customers_email_address, orders_status, date_purchased ,ipaddy, ipisporders where orders_id = '1'

 

how I fix this I already did that fix in that catalog/includes/classes/split_page_results.php and catalog/admin/includes/classes/split_page_results.php

 

still getting that error

Link to comment
Share on other sites

  • Replies 354
  • Created
  • Last Reply
I get this:

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

 

select customers_name, customers_email_address, orders_status, date_purchased ,ipaddy, ipisporders where orders_id = '1'

 

how I fix this I already did that fix in that catalog/includes/classes/split_page_results.php and catalog/admin/includes/classes/split_page_results.php

 

still getting that error

Just a thought, might it not be nice to let MySQL know which tables it is supposed to get this information from? Something like ...ipisorders from table " . TABLE_ORDERS . " ... ?

Link to comment
Share on other sites

Now I got this error under my products:

 

Warning: Division by zero in /home/content/k/u/s/kustomjs/html/catalog/includes/classes/split_page_results.php on line 63

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

 

select p.products_image, pd.products_name, 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 left join specials s on p.products_id = s.products_id, products_to_categories p2c 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 = '4' order by pd.products_name limit 0,

 

[TEP STOP]

Link to comment
Share on other sites

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 :D

 

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.

 

:o 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

 

Hi Im new to oscommerce, i already did by updating the admin/includes/classes find split_page_results.php.

But my problem is that in product listing will not view with the error. I need it to work for my first project.

Below are the erros;

 

=================

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 ' p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, ' at line 1

 

select p.products_image, pd.products_name, pd.products_description, , 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 = '4' order by pd.products_name limit 0, 20

 

[TEP STOP]

===================

 

Thanks,

mcramz

Link to comment
Share on other sites

Now I got 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 'MAX_ROW_LISTS_OPTIONS' at line 1

 

select * from products_options where language_id = '1' order by products_options_id LIMIT 0, MAX_ROW_LISTS_OPTIONS

 

[TEP STOP]

 

HEADING_TITLE_OPT

 

Warning: Division by zero in html/catalog/admin/products_attributes.php on line 454

 

here is the code for that line 454

 

$num_rows = tep_db_num_rows($option_query);

 

if ($num_rows <= $per_page) {

$num_pages = 1;

} else if (($num_rows % $per_page) == +1) {

$num_pages = ($num_rows / $per_page);

} else {

$num_pages = ($num_rows / $per_page) -1; <------ that is the one is throwing the code?

}

$num_pages = (int) $num_pages;

 

$options = $options . " LIMIT $option_page_start, $per_page";

 

// Previous

Link to comment
Share on other sites

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 :D

 

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.

 

:o 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

 

 

Hello,

 

I was getting that exact error message when I tried to undate my prices in the Quick Prices Update module (you can find it here )

 

This only happens when I try to sort by manufacturer. I applied the split_page_result fix but nothing seemed to change. Does anyone have any idea what the problem is (and how I can fix it?).

THanks Lionel

 

P.S. This issues started happening after I installed Easy Populate 2.76f r1 (here) ) and imported some new products.

Link to comment
Share on other sites

:o 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.

 

The fix you used for the catalog side, any way you can share it. I'm having an issue on my specials.php page. we took the link off for now as we don't have any specials at this time.. heck we're not even open.

 

I started a thread over in general support "MYSQL Syntax Error" and someone mentioned a possible issue in split_page_results.php but no one has come back with any suggestions. I'm wondering if I need the catalog side fix you mention?

 

the page in question:

http://www.nohasslejewelry.com/specials.php.

 

can you please post the fix here or direct me to where I can locate it as I haven't been able to find it.

 

Thanx,

 

SR

Link to comment
Share on other sites

  • 2 weeks later...

Hi, I am trying to install an new template and I keep on getting the following error msg:

 

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

 

select template_id, template_name from template where template_id =

 

[TEP STOP]

 

Does anyone have an idea how I could fix this.

Link to comment
Share on other sites

I had this "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" for all "empty" modules like Clients, Newsletter,etc..

 

 

The solution presented by Charles Kangethe worked just fine, it is showing 0 entries now as it should be

 

Thank you so much

 

Regards,

 

Fernando

Link to comment
Share on other sites

I got this error on address_book.php (where are the fields with name,address,phone...),I did the modifications to catalog/includes/classes/split_page_results.php and catalog/admin/includes/classes/split_page_results.php but still the same 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 ' address_book where customers_id = '16' order by firstname, lastname' at line 1

 

select address_book_id, entry_firstname as firstname, entry_lastname as lastname, entry_company as company, entry_street_address as street_address, entry_street_address_2 as street_address_2, entry_cui as cui, entry_reg_com as reg_com, entry_iban as iban, entry_banca as banca, entry_suburb as suburb, entry_city as city, entry_postcode as postcode, entry_state as state, entry_zone_id as zone_id, entry_country_id as country_id from, address_book where customers_id = '16' order by firstname, lastname

 

Please help,it's a live store .....

Thanks

Cumparaturi4all

Link to comment
Share on other sites

i have this problem and the solutions mentioned here have fixed part of the problem

i have edited both files the one in admin and the one in the catalogue

but there is still a small problem

I can now see the new products but the the listing is still in minus figures and i get the error

Warning: Division by zero in /home/thegrssc/public_html/includes/classes/split_page_results.php on line 59

 

at the top of the page

 

it can be viewed Here

 

any help would be greatly appreciated

 

Mark

Link to comment
Share on other sites

Thank you for posting this amazing fix. I followed your instructions and now the app is working properly.

 

My problem was there were customers who never placed any orders, so when in the CUSTOMERS listing of the admin panel, when you click on the ORDERS button, the SQL error would throw if there were no orders placed by the customer. Customers who had placed orders worked ok, but not if the customer never placed an order.

 

Thank you!

Kudos for your efforts!

 

 

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 :D

 

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.

 

:o 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

Link to comment
Share on other sites

i have this problem and the solutions mentioned here have fixed part of the problem

i have edited both files the one in admin and the one in the catalogue

but there is still a small problem

I can now see the new products but the the listing is still in minus figures and i get the error

Warning: Division by zero in /home/thegrssc/public_html/includes/classes/split_page_results.php on line 59

 

at the top of the page

 

any help would be greatly appreciated

 

Mark

Problem Fixed Here

http://www.oscommerce.com/forums/index.php?s=&...t&p=1154558

Link to comment
Share on other sites

  • 2 weeks later...

Well I'm hoping that there is still some support through this thread (as I see lots of questions and not so many answers)

 

I tried the initial code fix as noted at the very beginning of this thread by Charles (hope I have the name right. . lots of reading over the past little while here. . brain is on the fritz)

 

Here is the error I'm getting with modded RC1 on mysql 4.1.20 --I have not gotten any errors as of yet in the admin side, but when I try to view a product I get a full screen (not even using my STS at all) with product info etc and this error at the bottom:

 

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

 

select p.products_id, p.products_image, p.products_price, p.products_model, pd.products_name, p.products_tax_class_id from products p, products_description pd, products_to_categories pc where p.products_status = '1' and p.products_id = pc.products_id and p.products_id = pd.products_id and p.products_id != '29' and pc.categories_id = '22' and pd.language_id = '1' and p.products_price >= '0' and p.products_price <= '0' order by RAND() asc limit MAX_ALSO_CONSIDER

 

[TEP STOP]

 

No clue what's going on here. . .I'm thinking it would be better if the mysql could some how be upgraded. . . i dunno. . .any help at all would be appreciated. ..i'm not a skilled mysql or php programmer by any means, but I can follow good directions :)

 

Thanks,

Kyla

Rangeline Design

Michigan USA

Link to comment
Share on other sites

:blush:

 

I'm such a WINNER :blush:

 

I forgot to run the sql query for the Also Consider contrib. . . .sorry for the time anyone took to read this. . .OK I'll go back to leaving you all alone now!

 

Wow :blush:

 

Have I gotten across the point that I'm embarrassed yet? LOL

 

 

Well I'm hoping that there is still some support through this thread (as I see lots of questions and not so many answers)

 

I tried the initial code fix as noted at the very beginning of this thread by Charles (hope I have the name right. . lots of reading over the past little while here. . brain is on the fritz)

 

Here is the error I'm getting with modded RC1 on mysql 4.1.20 --I have not gotten any errors as of yet in the admin side, but when I try to view a product I get a full screen (not even using my STS at all) with product info etc and this error at the bottom:

 

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

 

select p.products_id, p.products_image, p.products_price, p.products_model, pd.products_name, p.products_tax_class_id from products p, products_description pd, products_to_categories pc where p.products_status = '1' and p.products_id = pc.products_id and p.products_id = pd.products_id and p.products_id != '29' and pc.categories_id = '22' and pd.language_id = '1' and p.products_price >= '0' and p.products_price <= '0' order by RAND() asc limit MAX_ALSO_CONSIDER

 

[TEP STOP]

 

No clue what's going on here. . .I'm thinking it would be better if the mysql could some how be upgraded. . . i dunno. . .any help at all would be appreciated. ..i'm not a skilled mysql or php programmer by any means, but I can follow good directions :)

 

Thanks,

Kyla

Rangeline Design

Michigan USA

Link to comment
Share on other sites

Can someone help me with this error:

 

CREATE TABLE customers_to_extra_fields(

customers_id int( 11 ) NOT NULL default '0',

fields_id int( 11 ) NOT NULL default '0',

`value` text

) ENGINE = MYISAM DEFAULT CHARSET = utf8;

 

MySQL 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 'DEFAULT CHARSET=utf8' at line 5

Link to comment
Share on other sites

  • 2 weeks later...

This is the error I get when trying to do a search. I did the change you have listed below and it doesn't work. Any help will be appreciated.

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 'id), products_description pd) left join specials s on p.products_id = s.produc t' at line 2

 

select count(distinct p.products_id) as total from ((products p) left join manufacturers m using(manufacturers_ id), products_description pd) left join specials s on p.products_id = s.produc ts_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%peacock%' or p.products_model like '%peacock%' or m.manufacturers_name like '%peacock%') )

 

 

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 :D

 

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.

 

:o 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

Link to comment
Share on other sites

  • 2 weeks later...

Hey. DMX Ready pointed me to you. They said these postings would have a problem I'm having, but all these posts deal with the catalog manager. My problem is with the classified listings manager. I've installed the mysql databases for the mailinglistmanager and pollingbooth manager without a problem - but as soon as I try to import the sql dump for the classifiedlisting manager I get an error:

 

Error

 

SQL query:

 

INSERT INTO `tblCLM_Items` ( `ItemID` , `TemplateIDkey` , `CategoryIDkey` , `StoreIDkey` , `ExtraIDKey1` , `ExtraIDKey2` , `ItemName` , `ItemMemo` , `ItemDesc` , `ItemDescShort` , `ItemCount` , `ItemUOM` , `DateAdded` , `ExpiryDate` , `Activated` , `SortOrder` , `ItemPriceValue1` , `ItemPriceValue2` , `ItemPriceValue3` , `ItemPriceValue4` , `ItemPriceValue5` , `ImageFileValue1` , `ImageFileValue2` , `ImageFileValue3` , `ImageFileValue4` , `ImageFileValue5` , `ImageFileThumbValue1` , `ImageFileThumbValue2` , `ImageFileThumbValue3` , `ImageFileThumbValue4` , `ImageFileThumbValue5` , `ExtraField1` , `ExtraField2` , `ExtraField3` , `ExtraField4` , `ExtraField5` , `ExtraField6` , `ExtraField7` , `ExtraField8` , `ExtraField9` , `ExtraField10` , `ExtraField11` , `ExtraField12` , `ExtraField13` , `ExtraField14` , `ExtraField15` , `ExtraField16` , `ExtraField17` , `ExtraField18` , `ExtraField19` , `ExtraField20` , `LookupItemID1` , `LookupItemID2` , `LookupItemID3` , `LookupItemID4` , `LookupItemID5` , `LookupItemID6` , `LookupItemID7` , `LookupItemID8` , `LookupItemID9` , `LookupItemID10` , `SendToEmailAddress` , `SpecialInstructions` )

VALUES ( 7, 0, 35, 0, 0, 0, '2004 Chevrolet Venture EXT ', '\r\n<TABLE cellPadding=0 width=\"100%\" border=0>\r\n<TBODY>\r\n<TR>\r\n<TD vAlign=top width=\"100%\">\r\n<TABLE cellPadding=0 width=\"100%\" border=0>\r\n<TBODY>\r\n<TR>\r\n<TD vAlign=top align=left><FONT face=\"Verdana, Arial, Helvetica, sans-serif\" size=2><B>2004 Chevrolet Venture EXT </B></FONT></TD>\r\n<TD vAlign=top align=right><IMG height=20 src=\"http://www.torontostarclassifieds.com/NASApp/OLCSApp/imgs/h_tada_small.gif\" width=60></TD></TR></TBODY></TABLE></TD></TR>\r\n<TR>\r\n<TD width=\"100%\">\r\n<TABLE cellPadding=0 width=\"100%\" border=0>\r\n<TBODY>\r\n<TR>\r\n<TR>\r\n<TD align=left width=\"55%\"><FONT face=\"Verdana, Arial, Helvetica, sans-serif\" size=1><B>Engine: </B>6 Cyl</FONT></TD>\r\n<TD align=left width=\"45%\"><FONT face=\"Verdana, Arial, Helvetica, sans-serif\" size=1><B>Price: </B>20988</FONT></TD></TR>\r\n<TR>\r\n<TD align=left width=\"55%\"><FONT face=\"Verdana, Arial, Helvetica, sans-serif\" size=1><B>Transmission: </B>Automatic</FONT></TD>\r\n<TD align=left width=\"45%\"><FONT face=\"Verdana, Arial, Helvetica, sans-serif\" size=1><B>Colour: </B>Teal</FONT></TD></TR>\r\n<TR>\r\n<TD align=left width=\"55%\"><FONT face=\"Verdana, Arial, Helvetica, sans-serif\" size=1><B>Kilometers: </B>14117</FONT></TD>\r\n<TD align=left width=\"45%\"><FONT face=\"Verdana, Arial, Helvetica, sans-serif\" size=1><B>Stock #: </B>04-166632a</FONT></TD></TR>\r\n<TR>\r\n<TD align=left width=\"55%\"><FONT face=\"Verdana, Arial, Helvetica, sans-serif\" size=1><B>Capacity: </B>7 Passenger</FONT></TD><!--td width = \"45%\" align=\"left\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\"><b>Refernece: </b>104</td--></TR></TR></TBODY></TABLE>\r\n<TR>\r\n<TD height=5></TD>\r\n<TR>\r\n<TD class=plaintext align=left width=\"100%\"><B>Details: </B><FONT size=1><B>2004 Chevrolet Venture EXT </B>$20988, 14117 kms., Teal, Automatic, 4 Door, Stock# 04-166632a, Air Conditioning, Alloy Wheels, CD, Power Windows, Power Locks, Tilt, Cruise Control, Loaded<BR><B>HOGAN CHEVROLET LIMITED, Scarborough, 416-291-5054</B> </FONT><BR></TD></TR></TBODY></TABLE>', '2004 Chevrolet Venture EXT $20988, 14117 kms., Teal, Automatic, 4 Door, Stock# 04-166632a, Air Conditioning, Alloy Wheels, CD, Power Windows, Power Locks, Tilt, Cruise Control, Loaded\r\nHOGAN CHEVROLET LIMITED, Scarborough, 416-291-5054', NULL , NULL , '', '2004-10-05 00:00:00', '2007-06-03 00:00:00' , 'True', NULL , NULL , NULL , NULL , NULL , NULL , '/assets/van1.jpg' , NULL , NULL , NULL , NULL , '/assets/van1.jpg', NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , '[email protected] ', NULL ) ;

 

MySQL said: Documentation

#1216 - Cannot add or update a child row: a foreign key constraint fails

 

 

 

It seems to import the data - because I can view it in the admin area - but as soon as I go to the public pages (applications), such as "preview" it errors out with:

 

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

 

[MySQL][ODBC 3.51 Driver][mysqld-4.1.22-max-log]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 '[Count] FROM tblCLM_Items WHERE tblCLM_Items.ExpiryDate >= NOW() AND tblCLM' at line 1

 

/applications/ClassifiedListingsManager/inc_classifiedlistingsmanager.asp, line 133

 

Hoping you can work your magic here.... Thanks!

Link to comment
Share on other sites

  • 2 weeks later...

Thanks for the great tips. My site is up and running again. However, I used this trick to fix the account_history page but if you look at the page number section, it has -4,-3,-2,-1,0. How do I get rid of this? Please see the screen capture for clearer description of the problem. I am eagerly waiting for your reply. Thank you

 

picture2cs4.gif

 

 

split_page_results.php file:

 

<?php
/*
 $Id: split_page_results.php,v 1.15 2003/06/09 22:35:34 hpdl Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/

 class splitPageResults {
var $sql_query, $number_of_rows, $current_page_number, $number_of_pages, $number_of_rows_per_page, $page_name;

/* class constructor */
function splitPageResults($query, $max_rows, $count_key = '*', $page_holder = 'page') {
  global $HTTP_GET_VARS, $HTTP_POST_VARS;

  $this->sql_query = $query;
  $this->page_name = $page_holder;

  if (isset($HTTP_GET_VARS[$page_holder])) {
	$page = $HTTP_GET_VARS[$page_holder];
  } elseif (isset($HTTP_POST_VARS[$page_holder])) {
	$page = $HTTP_POST_VARS[$page_holder];
  } else {
	$page = '';
  }

  if (empty($page) || !is_numeric($page)) $page = 1;
  $this->current_page_number = $page;

  $this->number_of_rows_per_page = $max_rows;

  $pos_to = strlen($this->sql_query);
  $pos_from = strpos($this->sql_query, ' from', 0);

  $pos_group_by = strpos($this->sql_query, ' group by', $pos_from);
  if (($pos_group_by < $pos_to) && ($pos_group_by != false)) $pos_to = $pos_group_by;

  $pos_having = strpos($this->sql_query, ' having', $pos_from);
  if (($pos_having < $pos_to) && ($pos_having != false)) $pos_to = $pos_having;

  $pos_order_by = strpos($this->sql_query, ' order by', $pos_from);
  if (($pos_order_by < $pos_to) && ($pos_order_by != false)) $pos_to = $pos_order_by;

  if (strpos($this->sql_query, 'distinct') || strpos($this->sql_query, 'group by')) {
	$count_string = 'distinct ' . tep_db_input($count_key);
  } else {
	$count_string = tep_db_input($count_key);
  }

  $count_query = tep_db_query("select count(" . $count_string . ") as total " . substr($this->sql_query, $pos_from, ($pos_to - $pos_from)));
  $count = tep_db_fetch_array($count_query);

  $this->number_of_rows = $count['total'];

  $this->number_of_pages = ceil($this->number_of_rows / $this->number_of_rows_per_page);

  if ($this->current_page_number > $this->number_of_pages) {
	$this->current_page_number = $this->number_of_pages;
  }

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

  if ($offset < 0)
{
$offset = 0;
}
$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;	}

/* class functions */

// display split-page-number-links
function display_links($max_page_links, $parameters = '') {
  global $PHP_SELF, $request_type;

  $display_links_string = '';

  $class = 'class="pageResults"';

  if (tep_not_null($parameters) && (substr($parameters, -1) != '&')) $parameters .= '&';

// previous button - not displayed on first page
  if ($this->current_page_number > 1) $display_links_string .= '<a href="' . tep_href_link(basename($PHP_SELF), $parameters . $this->page_name . '=' . ($this->current_page_number - 1), $request_type) . '" class="pageResults" title=" ' . PREVNEXT_TITLE_PREVIOUS_PAGE . ' "><u>' . PREVNEXT_BUTTON_PREV . '</u></a>  ';

// check if number_of_pages > $max_page_links
  $cur_window_num = intval($this->current_page_number / $max_page_links);
  if ($this->current_page_number % $max_page_links) $cur_window_num++;

  $max_window_num = intval($this->number_of_pages / $max_page_links);
  if ($this->number_of_pages % $max_page_links) $max_window_num++;

// previous window of pages
  if ($cur_window_num > 1) $display_links_string .= '<a href="' . tep_href_link(basename($PHP_SELF), $parameters . $this->page_name . '=' . (($cur_window_num - 1) * $max_page_links), $request_type) . '" class="pageResults" title=" ' . sprintf(PREVNEXT_TITLE_PREV_SET_OF_NO_PAGE, $max_page_links) . ' ">...</a>';

// page nn button
  for ($jump_to_page = 1 + (($cur_window_num - 1) * $max_page_links); ($jump_to_page <= ($cur_window_num * $max_page_links)) && ($jump_to_page <= $this->number_of_pages); $jump_to_page++) {
	if ($jump_to_page == $this->current_page_number) {
	  $display_links_string .= ' <b>' . $jump_to_page . '</b> ';
	} else {
	  $display_links_string .= ' <a href="' . tep_href_link(basename($PHP_SELF), $parameters . $this->page_name . '=' . $jump_to_page, $request_type) . '" class="pageResults" title=" ' . sprintf(PREVNEXT_TITLE_PAGE_NO, $jump_to_page) . ' "><u>' . $jump_to_page . '</u></a> ';
	}
  }

// next window of pages
  if ($cur_window_num < $max_window_num) $display_links_string .= '<a href="' . tep_href_link(basename($PHP_SELF), $parameters . $this->page_name . '=' . (($cur_window_num) * $max_page_links + 1), $request_type) . '" class="pageResults" title=" ' . sprintf(PREVNEXT_TITLE_NEXT_SET_OF_NO_PAGE, $max_page_links) . ' ">...</a> ';

// next button
  if (($this->current_page_number < $this->number_of_pages) && ($this->number_of_pages != 1)) $display_links_string .= ' <a href="' . tep_href_link(basename($PHP_SELF), $parameters . 'page=' . ($this->current_page_number + 1), $request_type) . '" class="pageResults" title=" ' . PREVNEXT_TITLE_NEXT_PAGE . ' "><u>' . PREVNEXT_BUTTON_NEXT . '</u></a> ';

  return $display_links_string;
}

// display number of total products found
function display_count($text_output) {
  $to_num = ($this->number_of_rows_per_page * $this->current_page_number);
  if ($to_num > $this->number_of_rows) $to_num = $this->number_of_rows;

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

  if ($to_num == 0) {
	$from_num = 0;
  } else {
	$from_num++;
  }

  return sprintf($text_output, $from_num, $to_num, $this->number_of_rows);
}
 }
?>

Link to comment
Share on other sites

  • 4 weeks later...

Charles

 

Thanks for posting it as a tip. I just found it via a Google search and almost 3 years on it is still saving hair-pulling :D

 

Best wishes for 2008

Danny

Link to comment
Share on other sites

Hello ive spend the last few hours trying all the fixes shown above but most of them are alredy applyed and i still cant figure out where is the problem the error im getting 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 'limit 20, 20' at line 1

select p.products_image, pd.products_name, p.products_weight, 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 left join specials s on p.products_id = s.products_id, products_to_categories p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '4' and p2c.categories_id = '61' order by limit 20, 20

 

any ideas?

Link to comment
Share on other sites

anyone know how to fix it in the catalog side www.richardgoodallgallery.com/rggstore/catalog_products_with_images.php

 

Am using php 4.1.x have latest correct split_page_results in both admin and catalog. Admin side works fine, rest of site works fine. any ideas how to fix it on this side?

 

Hello ive spend the last few hours trying all the fixes shown above but most of them are alredy applyed and i still cant figure out where is the problem the error im getting 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 'limit 20, 20' at line 1

select p.products_image, pd.products_name, p.products_weight, 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 left join specials s on p.products_id = s.products_id, products_to_categories p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '4' and p2c.categories_id = '61' order by limit 20, 20

 

any ideas?

Link to comment
Share on other sites

  • 3 weeks later...

Some of these errors (especially if the error message says "division by zero") are caused by having the Maximum Values settings in the Configuration panel set to zero. In my case, I was getting an error message on the customer's Account History page, and I checked the Maximum Values, and sure enough, the maximum value for Order History was set to "0" - this was resulting in a "division by zero" error on the account_history page. I set that to 10 and that got rid of the error. So one way to try to eliminate these errors, if it involves a specific box or a specifc page, is to check the Maximum Value related to it, and if that is set to 0, change it to something else. This might help eliminate the error, before you try editing code.

Link to comment
Share on other sites

how can I fix it:

 

1054 - Unknown column 'pd.products_shortdescription' in 'field list'

 

select p.products_id, pd.products_name, pd.products_description, pd.products_shortdescription, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from products p left join specials s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id and pd.language_id = '4' left join featured f on p.products_id = f.products_id where p.products_status = '1' and f.status = '1' order by rand() DESC limit 2

 

[TEP STOP]

 

 

??

 

sql server 4.1.xx

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...