Jump to content

Archived

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

Simplyeasier

1064 - You have an error in your SQL syntax

Recommended Posts

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

Share this post


Link to post
Share on other sites

First I would head over to phpmyadmin and see if the field does exist.

 

Have you installed a contribution and not followed the instruction to run some sql that adds this field?


Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Share this post


Link to post
Share on other sites

your a legend... thanx heaps for that!! works a treat. :D

 

oh i should note that this worked for me when i went to newsletters in admin i got the problem... used the original post fix and it worked for that area for me... so if anyone is having a newsletter problem... this worked for me.

Share this post


Link to post
Share on other sites

Hi! thanks so much for this info. I received this message today after doing an upgrade. Your instructions seemed so easy to me until I read the bottom line and now I am confused. Do I do this in cpanel, admin, ??? How can I edit the text inside the actual document. Sorry I am so very new at this and started with the bare basic computer skills, we are learning hence the screne name. Any help would be so much appreciated. Just speak to me like your teaching a child to walk one foot in front of the other and I promise I will get it.

Share this post


Link to post
Share on other sites

Ok, baby steps....I went to cpaneland then to public html and then to admin and so forth and changed the info by backspacing what was in there and putting what you said to, but I sill get this error:

 

[ HEADING_TITLE

 

TABLE_HEADING_PRODUCTS TABLE_HEADING_RATING TABLE_HEADING_DATE_ADDED TABLE_HEADING_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 'select reviews_id, products_id, date_added, last_modified, reviews_rating from r' at line 1

 

select count(select reviews_id, products_id, date_added, last_modified, reviews_rating from reviews order by date_added DESC) as total

 

TEP_DB_ERRORR

 

Is there another place I should have done this? Please help! Im so confused!

Share this post


Link to post
Share on other sites

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 distinct p.products_id, pd.products_name from products p, products_description pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_ordered desc, pd.products_name limit

 

[TEP STOP]

 

I am getting the this error on the catalog side and it occurs at the top of the right column and is confined to the right column. Never had any errors on the admin side. Am running MS2.2 RC2 with no mods. It was working but now is broken. Have looked at the suggestions but they have already been fixed in this release with (offset,0) in split_page...

 

I can't figure this out.

 

Has anyone else seen this error?

 

Help!

 

Thanks to all of you who help us. We appreciate it greatly.

 

Jeff

Share this post


Link to post
Share on other sites
My error message is slightly different and reads:

 

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

 

delete from products_options where products_id =206'

 

[TEP STOP]

 

I tried your fix to no avail. I really need to get this ironed. Please advise. It only happens when I try to delete a product. The product gets deleted, but the error still pops up every time.

 

I put in contribution : froogle_bizrate_yahoo_feeds_1_12

 

My error is : SQL query:

INSERT INTO `configuration_group`

VALUES ( 62, 'Feed Settings', 'Info for data feeds', 62, 1 ) ;

MySQL said:

#1062 - Duplicate entry '62' for key 1

 

What do I do ?

 

Please help. Most appreciated.


Nina

Share this post


Link to post
Share on other sites
My error message is slightly different and reads:

 

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

 

delete from products_options where products_id =206'

 

[TEP STOP]

 

I tried your fix to no avail. I really need to get this ironed. Please advise. It only happens when I try to delete a product. The product gets deleted, but the error still pops up every time.

 

I put in contribution : froogle_bizrate_yahoo_feeds_1_12

 

My error is : SQL query:

INSERT INTO `configuration_group`

VALUES ( 62, 'Feed Settings', 'Info for data feeds', 62, 1 ) ;

MySQL said:

#1062 - Duplicate entry '62' for key 1

 

What do I do ?

 

Please help. Most appreciated.


Nina

Share this post


Link to post
Share on other sites

Dear everyone

 

i have a little problem similar of this one

but i'm getting errors when i try to install Total B2B contrib.

it's not on the admin or catalog but when i tryed to upload the .sql files to my sql 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 'SELECT configuration_group_id FROM configuration_group WHERE co 

 

i already apply the fix for split_page_result but nothing happen (of course) ....

 

 

Hoster : 1and1

Mysql : 4.0

 

if someone have similar problem and have a fix...

thx for all

Share this post


Link to post
Share on other sites
Dear everyone

 

i have a little problem similar of this one

but i'm getting errors when i try to install Total B2B contrib.

it's not on the admin or catalog but when i tryed to upload the .sql files to my sql 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 'SELECT configuration_group_id FROM configuration_group WHERE co 

 

i already apply the fix for split_page_result but nothing happen (of course) ....

Hoster : 1and1

Mysql : 4.0

 

if someone have similar problem and have a fix...

thx for all

 

 

ok i fixed it !

for those who have the same problem :

just download the first release of Total B2B

and upload the sql file of this package

The fact is that the new package is for Mysql5.xxx++++

so ...

f**k 1and1 ...

and thx SQL :D

Share this post


Link to post
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

 

I am using sql 5. I unfortunately deleted all of my orders using the admin interface and 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 '-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 = '1' and ot.class = 'ot_total' order by o.orders_id DESC limit -20, 20

 

I tried the fix modification above and the error message went away and the orders link returned 0 of 0 orders as expected. However, after placing test orders and clicking on the orders link it still says 0 of 0 orders. I can access the test orders by typing the order ID number into the box and I can see the orders in the database table.

 

Could it be that this fix modification doesn't work with sql5?

Share this post


Link to post
Share on other sites

Hi I am new here. I am getting errors on Catalog when trying to add 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 '-30, 30' 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 -30, 30

 

 

I have no idea what to do :'( Anyone can help me??

Thanx

Share this post


Link to post
Share on other sites

I have this error.

 

 

An Error Was Encountered

Error Number: 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 ') ORDER BY classified_id ASC' at line 1

 

SELECT * FROM classifieds WHERE vehicle_make_id=12 AND vehicle_model LIKE '%%' AND zip IN () ORDER BY classified_id ASC

 

 

Can someone help me please?

Share this post


Link to post
Share on other sites

Hi,

 

I have a similar problem with 1064 Error on the Admin / edit order. I applied Moneris package and I believe located the source of the query in admin/orders_addon_campg.php. I tried to apply suggested fix from previous postings, but it did not work. Following is an error itself:

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 'select moneris_order_id, gateway_url, osc_session, ref_num, f4l

 

select baseTable.moneris_order_id, baseTable.gateway_url, baseTable.osc_session, baseTable.ref_num, baseTable.f4l4, baseTable.resp_code, baseTable.iso_code, baseTable.auth_code, baseTable.trans_date, baseTable.trans_time, baseTable.trans_type, baseTable.message, baseTable.card_type, baseTable.txn_num, baseTable.avs_code, baseTable.cvd_code, baseTable.crypt_type, baseTable.veres, baseTable.pares, txnTable.txn_num orig_txn_num from (select moneris_order_id, gateway_url, osc_session, ref_num, f4l4, iso_code, resp_code, auth_code, trans_date, trans_time, trans_type, message, card_type, txn_num, avs_code, cvd_code, crypt_type, veres, pares from moneris_can_orders where osc_order_id = '11') as baseTable cross join (select moneris_order_id, trans_type, txn_num from moneris_can_orders where trans_type='01' and osc_order_id = '11') as txnTable where (baseTable.trans_type = '01' and baseTable.moneris_order_id = txnTable.moneris_order_id) or (baseTable.trans_type <> txnTable.trans_type) and (baseTable.moneris_order_id = txnTable.moneris_order_id)

 

Please help!

Share this post


Link to post
Share on other sites

Hi, after Extra_images contribution installation...now I got this error in admin panel:

 

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 'select pei.products_extra_image, pei.products_extra_images_id, pei.products_id, ' line 1

 

select count(select pei.products_extra_image, pei.products_extra_images_id, pei.products_id, pd.products_name,p.products_image from products_extra_images pei left join products p ON pei.products_id = p.products_id left join products_description pd on p.products_id = pd.products_id order by pd.products_name) as total 1

 

[TEP STOP]

 

Any Help.

thanks

Share this post


Link to post
Share on other sites

Hi there - I have read through the above listings and am suffering a similar, but slightly different error - I did try the adding of

if ($offset < 0)

{

$offset = 0 ;

}

 

but to no avail - I just get a blank screen on shop and back office!!

 

My error however reads:::::

 

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 ' ipaddy orders where orders_id = '3874'' at line 1

 

select customers_name, customers_email_address, orders_status, date_purchased from, ipaddy orders where orders_id = '3874'

 

[TEP STOP]

 

This occurs whenever I try to update the order progress from pending to anything else.

 

I am desperate for some help!!

 

Thanks All

 

Ben

Share this post


Link to post
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

 

 

 

 

Thanks for this help - when I do change as above - all I get is a completely blank page on the admin side????

Share this post


Link to post
Share on other sites
Thanks for this help - when I do change as above - all I get is a completely blank page on the admin side????

Strange, when you first got an error (so error echo'ing is not turned off apparently). The error in your query is do to the comma (might be more problems with it) in:

from, ipaddy orders

 

Perhaps if you do a view source you see some HTML code from that blank page?

 

You don't use the file_manager in the admin? Use a text editor and ftp to change / upload your files if so.

Share this post


Link to post
Share on other sites
Strange, when you first got an error (so error echo'ing is not turned off apparently). The error in your query is do to the comma (might be more problems with it) in:

from, ipaddy orders

 

Perhaps if you do a view source you see some HTML code from that blank page?

 

You don't use the file_manager in the admin? Use a text editor and ftp to change / upload your files if so.

 

Many thanks for the help - - -

Where would I find the from, ipaddy?? It is not in the split_page_result.php??

 

Thanks again

Share this post


Link to post
Share on other sites
Where would I find the from, ipaddy?? It is not in the split_page_result.php??

I think you yourself gave that answer already:

This occurs whenever I try to update the order progress from pending to anything else.

My guess is in the page admin/orders.php in the part that likely starts with $action == "edit" (haven't checked it).

Share this post


Link to post
Share on other sites
I think you yourself gave that answer already:

 

My guess is in the page admin/orders.php in the part that likely starts with $action == "edit" (haven't checked it).

 

 

 

Jan,

Thanks VERY MUCH for your help - you have sorted it!!!! In orders.php 'from, ipaddy " was changed to , ipaddy from " and we are away. You have saved what little hair I have remaining for the next problem - cheers!!!

Share this post


Link to post
Share on other sites

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 ' products_ordered = products_ordered - where products_id = '0'

 

update products set products_quantity = products_quantity + , products_ordered = products_ordered - where products_id = '0'

 

[TEP STOP]

 

I have been reading the posts up and down, and everytime I think...aah here it is...there is no answer to fix the problem...or the

my english is too poor. Please help me.

 

What do the 1064 error want me to do and where. My brains do not get it (nothing unusural for me :D)

 

Thanx,

-Carola

Share this post


Link to post
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 Charles,

 

I am new to OSCommerce and a "friend" set up the website but will not help anymore. I have no PHP experience and have only played with Frontpage in the past so this is all very new to me.

 

I have the following error when i click on customers and then a customers order

 

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.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 = '26' and o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by orders_id DESC limit -10, 10

 

My hoster has told me they run MySQL version is 5.0.45, and if I have a look on my admin page under server info it says the Client API version is 4.1.11

 

I tried the fix you mentioned in the split page and it stopped the error but no orders would show up at all. I have also spoken to my hoster and they have said I need to upgrade my OSCommerce ( I currently use 2.2 MS2) but I have been through the forums and it looks quite complicated to upgrade the version and I was worried about losing my whole shop if I did so.

 

Can you help or recommend someone who I can page to assist?

 

 

Thanks

Penny :'(

Share this post


Link to post
Share on other sites

I got this error 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 'like 'CA%' or zone_code like '%CA%')' at line 1

 

select distinct zone_id from zones where zone_country_id = '223' and (zone_name =like 'CA%' or zone_code like '%CA%')

 

[TEP STOP]

 

what should i do? please help me... thanks...

Share this post


Link to post
Share on other sites

×