My site breaks when I add a product with attributes to basket

[galey1981]

I have a heavily modded oscommerce installed. I have SEO friendly URLS, extra product fields, and the STS package.

 

I've noticed that when I add a product which has attributes on it, when it's added to my basket the link when hovered over reads as this:

 

http://www.mywebsite.co.uk/catalog/product...ts_id=2{4}3{3}6

 

If i try to continue shopping or click this link, i get a blank page with the following error on it:

 

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 '{4}3{3}6 and ptf.products_extra_fields_value<>'' and (pef.languages_id='0' or pe' at line 1

 

SELECT pef.products_extra_fields_status as status, pef.products_extra_fields_name as name, ptf.products_extra_fields_value as value FROM products_extra_fields pef LEFT JOIN products_to_products_extra_fields ptf ON ptf.products_extra_fields_id=pef.products_extra_fields_id WHERE ptf.products_id=2{4}3{3}6 and ptf.products_extra_fields_value<>'' and (pef.languages_id='0' or pef.languages_id='1') ORDER BY products_extra_fields_order

 

[TEP STOP]

 

All other products I add, which do not have any attributes (e.g. a memory size on a drop down), will be fine and when clicked go to the correct place...... Can you help?

 

From looking at the code it seems like a extra fields issue - however, I'm not actively adding extra fields right now. I swear it must be a conflict somehow - could be one of the contributions I have installed? I could just not add any products with attributes but that might not help me out.

[galey1981]

I've tried switching off the seo urls and this doesnt impact - the product still gives itself this strange link address.

 

If i browse to the product the url will be:

http://www.mywebsite.co.uk/catalog/matrox-g400-32mb-p-2.html

 

The product will load, it has multiple attribute options. I add it to my basket.

 

I go to view my basket, and when i hover over the link in the basket it reads as:

http://www.woodleysnapshot.co.uk/catalog/p...ts_id=2{4}3{3}6

 

If i click this, or then continue shopping to return to that page, i get that error message.

 

Very weird.

[galey1981]

If my query is too vague please visit the live site and have a look

 

www.woodleysnapshot.co.uk/catalog

 

If you add a product to the basket which has product attributes, and then a product without them.

 

One without attributes:

http://www.woodleysnapshot.co.uk/catalog/f...below-p-11.html

 

One with attributes:

http://www.woodleysnapshot.co.uk/catalog/m...0-32mb-p-2.html

 

Add them both to basket and then navigate to the shopping cart page. Try clicking on the Fire down below product, no probs. go back to the matrox product, and click on it..... you get the funny error page.

 

It appears the url is appended with funny numbers at the end should the product contain attributes, and the site doesnt like this link.

[galey1981]

I think i've pinned the issue to the code here, found in product_info.php, and some kind of conflict with the seo friendly url contribution.... Can anyone take a look and make a suggestion?

 

<?php

	  // START: Extra Fields Contribution v2.0b - mintpeel display fix

				  $extra_fields_query = tep_db_query("
				  SELECT pef.products_extra_fields_status as status, pef.products_extra_fields_name as name, ptf.products_extra_fields_value as value
				  FROM ". TABLE_PRODUCTS_EXTRA_FIELDS ." pef
		 LEFT JOIN  ". TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS ." ptf
		ON ptf.products_extra_fields_id=pef.products_extra_fields_id
		WHERE ptf.products_id=". (int) $products_id ." and ptf.products_extra_fields_value<>'' and (pef.languages_id='0' or pef.languages_id='".$languages_id."')
		ORDER BY products_extra_fields_order");

 while ($extra_fields = tep_db_fetch_array($extra_fields_query)) {
	if (! $extra_fields['status'])  // show only enabled extra field
	   continue;
	echo '<tr>
  <td>
  <table border="0" width="100%" cellspacing="0" cellpadding="2px"><tr>
  <td align="right" vallign="middle">';
	echo '<img src="http://www.woodleysnapshot.co.uk/catalog/images/' .$extra_fields['value'].'.jpg "></tr>
  </table>
  </td>
  </tr>'; 
 }
   // END: Extra Fields Contribution - mintpeel display fix
?>

 

I'm so stumped on what is causing these issues! :blink:

[galey1981]

I fixed it!!! I'm impressed with myself.

 

I did something that i probably should've done in the first instance. I copied the sql query and did a find of the catalog folder.

 

It popped up the includes/modules/products_extra_fields.php file and i went in there. I was looking in the wrong place.

 

I found that the query on my product_info.php page was different. So i pasted this query over the top of what was in products_extra_fields.php

 

Before:

 

$sql = "SELECT pef.products_extra_fields_status as status, pef.products_extra_fields_name as name, ptf.products_extra_fields_value as value FROM ". TABLE_PRODUCTS_EXTRA_FIELDS ." pef LEFT JOIN  ". TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS ." ptf ON ptf.products_extra_fields_id=pef.products_extra_fields_id WHERE ptf.products_id=".$HTTP_GET_VARS['products_id']." and ptf.products_extra_fields_value<>'' and (pef.languages_id='0' or pef.languages_id='".$languages_id."') ORDER BY products_extra_fields_order";

 

After:

 

$sql = "SELECT pef.products_extra_fields_status as status, pef.products_extra_fields_name as name, ptf.products_extra_fields_value as value FROM ". TABLE_PRODUCTS_EXTRA_FIELDS ." pef LEFT JOIN  ". TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS ." ptf ON ptf.products_extra_fields_id=pef.products_extra_fields_id WHERE ptf.products_id=". (int) $products_id ." and ptf.products_extra_fields_value<>'' and (pef.languages_id='0' or pef.languages_id='".$languages_id."') ORDER BY products_extra_fields_order";

 

It did not like the ptf.products_id as $HTTP_GET_VAR for some reason, and i replaced that with an int..... I don't understand the impact but know it now works.

 

I always hate when someone gets a fix but doesn't share. So I hope this helps someone who might have the same problem.

[burt]

Good work!