Error 1064 - not changing after the fix. Experts help needed

[sukarya]

Hi,

 

I have posted a topic in the wrong forum, I apologize for any duplications...

 

The following error occur after trying to login, even though I have MySql 4.0.27 and not the one that the posted fix should repair. the error that i have:

 

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 ' 92) and pd.language_id = '1'' at line 1

 

select pd.products_name, p.products_model, p.products_image, p.products_id, p.manufacturers_id, p.products_price, p.products_weight, p.products_quantity, p.products_price1, p.products_price2, p.products_price3,p.products_price4, p.products_price5, p.products_price6, p.products_price7, p.products_price8, p.products_price1_qty, p.products_price2_qty, p.products_price3_qty, p.products_price4_qty, p.products_price5_qty, p.products_price6_qty, p.products_price7_qty, p.products_price8_qty, p.products_qty_blocks, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price from products p left join specials s on p.products_id = s.products_id, products_description pd where p.products_status = '1' and pd.products_id = p.products_id and p.products_id in (, 92) and pd.language_id = '1'

 

[TEP STOP]

 

 

I did go threw the Tips and Tricks and threw all the threads that I found that have this error (1064). all of them relate to the MySql version, which isnt so in my case. BTW, the admin section works o.k.

 

Wating for the wise ones...Sharon

[Velveeta]

Hi,

 

I have posted a topic in the wrong forum, I apologize for any duplications...

 

The following error occur after trying to login, even though I have MySql 4.0.27 and not the one that the posted fix should repair. the error that i have:

 

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 ' 92) and pd.language_id = '1'' at line 1

 

select pd.products_name, p.products_model, p.products_image, p.products_id, p.manufacturers_id, p.products_price, p.products_weight, p.products_quantity, p.products_price1, p.products_price2, p.products_price3,p.products_price4, p.products_price5, p.products_price6, p.products_price7, p.products_price8, p.products_price1_qty, p.products_price2_qty, p.products_price3_qty, p.products_price4_qty, p.products_price5_qty, p.products_price6_qty, p.products_price7_qty, p.products_price8_qty, p.products_qty_blocks, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price from products p left join specials s on p.products_id = s.products_id, products_description pd where p.products_status = '1' and pd.products_id = p.products_id and p.products_id in (, 92) and pd.language_id = '1'

 

[TEP STOP]

 

 

I did go threw the Tips and Tricks and threw all the threads that I found that have this error (1064). all of them relate to the MySql version, which isnt so in my case. BTW, the admin section works o.k.

 

Wating for the wise ones...Sharon

This part "(, 92)" is the problem itself... If you do a search for a field "in (whatever values)" it expects a comma-separated list of values to search in, you've got a comma with nothing in front of it, only something behind it, so it should either look like this "(92)" or something like this "(73,92)"... Can you post the piece of code that's constructing this query?

 

Richard.

[sukarya]

This part "(, 92)" is the problem itself... If you do a search for a field "in (whatever values)" it expects a comma-separated list of values to search in, you've got a comma with nothing in front of it, only something behind it, so it should either look like this "(92)" or something like this "(73,92)"... Can you post the piece of code that's constructing this query?

 

Richard.

 

 

Thanks,

 

ill look it up, and will post it.

 

Sharon.

[sukarya]

Thanks,

 

ill look it up, and will post it.

 

Sharon.

 

Well, i think i found it: its in the index.php, its part of the code (obviously...)

 

// show the products of a specified manufacturer
if (isset($HTTP_GET_VARS['manufacturers_id'])) {
  if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
			$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_price1, p.products_price2, p.products_price3, p.products_price4, p.products_price5, p.products_price6, p.products_price7, p.products_price8, p.products_price1_qty, p.products_price2_qty, p.products_price3_qty, p.products_price4_qty, p.products_price5_qty, p.products_price6_qty, p.products_price7_qty, p.products_price8_qty, p.products_qty_blocks, 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 " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";
  } else {
// We show them all
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_price1, p.products_price2, p.products_price3, p.products_price4, p.products_price5, p.products_price6, p.products_price7, p.products_price8, p.products_price1_qty, p.products_price2_qty, p.products_price3_qty, p.products_price4_qty, p.products_price5_qty, p.products_price6_qty, p.products_price7_qty, p.products_price8_qty, p.products_qty_blocks, 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 " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
  }
} else {
// show the products in a given categorie
  if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_price1, p.products_price2, p.products_price3, p.products_price4, p.products_price5, p.products_price6, p.products_price7, p.products_price8, p.products_price1_qty, p.products_price2_qty, p.products_price3_qty, p.products_price4_qty, p.products_price5_qty, p.products_price6_qty, p.products_price7_qty, p.products_price8_qty, p.products_qty_blocks, 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 " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
  } else {
// We show them all
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_price1, p.products_price2, p.products_price3, p.products_price4, p.products_price5, p.products_price6, p.products_price7, p.products_price8, p.products_price1_qty, p.products_price2_qty, p.products_price3_qty, p.products_price4_qty, p.products_price5_qty, p.products_price6_qty, p.products_price7_qty, p.products_price8_qty, p.products_qty_blocks, 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 " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_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 = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";	  }
}

 

 

i think that its in the :" We show them all" section

[Velveeta]

Hmmm, this doesn't look to be the right section because it uses category table references also, which the original posted query didn't, it was strictly product data, plus there's no "p.products_id in " portion in any of those queries, which was where the original problem lied... Keep hunting :D

 

Richard.

[sukarya]

Hmmm, this doesn't look to be the right section because it uses category table references also, which the original posted query didn't, it was strictly product data, plus there's no "p.products_id in " portion in any of those queries, which was where the original problem lied... Keep hunting :D

 

Richard.

 

 

o.k.

After going threw all the file that had been modified in the past 7 days, I found nothing.

 

I went threw the shop's work flow again and again, and found that this symptom occurs only when the user has products in his cart.

What i mean is that once I registered as a new customer, i can login and its fine BUT when trying to add a product to my cart i get that error.

 

Tried another thing: entered as a guest, add a product to my cart - all is well, when making checkout - I'm sent to the login.php which i can ether 1. register OR 2. login OR 3. use the quick buy (PWA contribution which worked fine).

in this stage when i have a product in my cart and try to login - i get the error.

 

(isn't it a session problem?)

 

will still try to look for the code, but thought this will help to reach any conclusions.

 

Sharon.

[satish]

pd.language_id = '1''

 

should be

pd.language_id = '1'

 

or

pd.language_id = ''1''

[sukarya]

pd.language_id = '1''

 

should be

pd.language_id = '1'

 

or

pd.language_id = ''1''

 

 

Thanks for your reply, but the last: ' is closing the one that is closing the error's quote, so: pd.language_id='1'

(i wish i was wrong...)

 

By the way, the error continues as long as i have the same session (didn't close the browser etc.) if I end the session it disappears until the next time ill try to login, while my shop cart contains something, if its empty - then i can login with no problem.

 

anyone?

Sharon.

[Velveeta]

Thanks for your reply, but the last: ' is closing the one that is closing the error's quote, so: pd.language_id='1'

(i wish i was wrong...)

 

By the way, the error continues as long as i have the same session (didn't close the browser etc.) if I end the session it disappears until the next time ill try to login, while my shop cart contains something, if its empty - then i can login with no problem.

 

anyone?

Sharon.

Which actual page is this problem coming up on? Whenever you login, is it throwing the error on login.php, or shopping_cart.php, or checkout_shipping.php if you're heading for checkout at the time, or what? Wherever the url is pointing to at the time the script dies with this error, that's the file you need to check in, and you may have to check the program flow back through that file... i.e. if it dies in shopping_cart.php, then is it dying in the actual file, or is it dying in the shopping_cart class file that's first loaded and assigned, or is it dying somewhere else that's being called, like function get_products when the products list is pulled down...

 

Richard.

[sukarya]

Which actual page is this problem coming up on? Whenever you login, is it throwing the error on login.php, or shopping_cart.php, or checkout_shipping.php if you're heading for checkout at the time, or what? Wherever the url is pointing to at the time the script dies with this error, that's the file you need to check in, and you may have to check the program flow back through that file... i.e. if it dies in shopping_cart.php, then is it dying in the actual file, or is it dying in the shopping_cart class file that's first loaded and assigned, or is it dying somewhere else that's being called, like function get_products when the products list is pulled down...

 

Richard.

 

 

Well, here are the details:

 

1. if i am NOT logged in, i can add products to basket cart(shopping_cart.php) after clicking checkout, I am referred to the login page, when inserting my login name and pass and click continue then i get the ERROR (address shows checkout_shipping.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 ' 125, 190, 143, 161) and pd.language_id = '1'' at line 1

 

select pd.products_name, p.products_model, p.products_image, p.products_id, p.manufacturers_id, p.products_price, p.products_weight, p.products_quantity, p.products_price1, p.products_price2, p.products_price3,p.products_price4, p.products_price5, p.products_price6, p.products_price7, p.products_price8, p.products_price1_qty, p.products_price2_qty, p.products_price3_qty, p.products_price4_qty, p.products_price5_qty, p.products_price6_qty, p.products_price7_qty, p.products_price8_qty, p.products_qty_blocks, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price from products p left join specials s on p.products_id = s.products_id, products_description pd where p.products_status = '1' and pd.products_id = p.products_id and p.products_id in (, 125, 190, 143, 161) and pd.language_id = '1'

 

[TEP STOP]

 

i have noticed that the numbers shown in the error (125, 190, 143, 161) are the product ID numbers that i have click-test this login for the error.

 

2. if i try to login as a customer that have products in the basket i will get the same error. (lets say try to login from index.php, ill be referred to login.php and the error will show on the index.php in the address bar.

 

3. if i register as a new customer and add products , its o.k. but when trying to checkout - ill get this error.

 

This is why i think it has some thing to do with the sessions (that i dont know how to deal with), but its just a speculation.

 

i couldnt find any problems with any of the modified files for the past 7 days, ill dig again...

 

Sharon.

[Velveeta]

Well, here are the details:

 

1. if i am NOT logged in, i can add products to basket cart(shopping_cart.php) after clicking checkout, I am referred to the login page, when inserting my login name and pass and click continue then i get the ERROR (address shows checkout_shipping.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 ' 125, 190, 143, 161) and pd.language_id = '1'' at line 1

 

select pd.products_name, p.products_model, p.products_image, p.products_id, p.manufacturers_id, p.products_price, p.products_weight, p.products_quantity, p.products_price1, p.products_price2, p.products_price3,p.products_price4, p.products_price5, p.products_price6, p.products_price7, p.products_price8, p.products_price1_qty, p.products_price2_qty, p.products_price3_qty, p.products_price4_qty, p.products_price5_qty, p.products_price6_qty, p.products_price7_qty, p.products_price8_qty, p.products_qty_blocks, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price from products p left join specials s on p.products_id = s.products_id, products_description pd where p.products_status = '1' and pd.products_id = p.products_id and p.products_id in (, 125, 190, 143, 161) and pd.language_id = '1'

 

[TEP STOP]

 

i have noticed that the numbers shown in the error (125, 190, 143, 161) are the product ID numbers that i have click-test this login for the error.

 

2. if i try to login as a customer that have products in the basket i will get the same error. (lets say try to login from index.php, ill be referred to login.php and the error will show on the index.php in the address bar.

 

3. if i register as a new customer and add product , its o.k., but when trying to checkout - ill get this error.

 

This is why i think it has some thing to do with the sessions, but maybe im wrong and its just a speculation.

i couldnt find any problems with any of the modified files for the past 7 days, ill dig again... : (

 

Sharon.

Well I can tell you for certain that it's not a problem with the session data, as that doesn't have an effect on the code constructing a query that's malformed in this way... The problem is always that leading comma with nothing in front of it, and you'll need to track down where that's happening... My guess is that it's in includes/classes/shopping_cart.php, most likely in function get_products... Try looking there first, and if you can't find it in that function, search the whole file for this text:

 

p.products_id in

 

Richard.

[sukarya]

Well I can tell you for certain that it's not a problem with the session data, as that doesn't have an effect on the code constructing a query that's malformed in this way... The problem is always that leading comma with nothing in front of it, and you'll need to track down where that's happening... My guess is that it's in includes/classes/shopping_cart.php, most likely in function get_products... Try looking there first, and if you can't find it in that function, search the whole file for this text:

 

p.products_id in

 

Richard.

 

ok, found it: its in the PriceFormatStore.php from the Quantity Break Price contribution

 

Have inquired it (still am...), so far nothing.

 

There are few bugs noted there that I'm checking to see if they are the source of my problems.

 

Any further help will be appreciated.

 

p.s.

thanks Richard,

 

Sharon.

[Velveeta]

ok, found it: its in the PriceFormatStore.php from the Quantity Break Price contribution

 

Have inquired it (still am...), so far nothing.

 

There are few bugs noted there that I'm checking to see if they are the source of my problems.

 

Any further help will be appreciated.

 

p.s.

thanks Richard,

 

Sharon.

Well, if you can post the code here that's constructing that query, I can probably figure out what the problem is :)

 

Richard.