products with attributes. The url in the shopping cart breaks site.

[galey1981]

Hi guys,

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 associated to 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_fi elds_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 related to Ultimate SEO?

I could just never add any products with attributes but that might not help me out.

[galey1981]

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

Home - Test Platform

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 the basket and navigate to the shopping cart page. Try clicking on the Fire down below product, there are no problems. Go back to the matrox product, and click on it in the cart.... you get a funny error.

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

I'm wondering if it's a .htaccess issue affected by the SEO friendly contribution, or if it's something wrong with the product_info page. I found this code in product_info which relates to this problem...

<?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
?> 

[jpf]

First of all turn off SEO and in htacess. Try it out - maybe your contribs are doing this non-strandard.

[galey1981]

Hey - i just gave that a go, but it did not work. It carried on doing the same thing.

So i can rule out the ultimate seo.... I also stripped out the extra fields code from product_info.php but the same error appeared. It's getting that from somewhere else then.

hmmmm........

[michael_s]

?options={4}3{3}6

Those are normal and it is how osC keeps track of attributes in the url.

What is not normal is how your site is handling it, the mysql error. There is a problem in a query somewhere, most likely a mod you made.

See how it is supposed to work here:
osCMax v2.0 RC3.0.3 : Welcome to AABox.com's osCMax v2.0 - change this in /english/index.php

There are only 2 products, add one of each. One has attributes, one doesn't. Note the url for the one with attributes. Click away and all works fine.

[galey1981]

Thanks for the info - is it possible to localise key pages where the error could be stemming from so I can review the code inside of them?

I downloaded a loaded copy of oscommerce which already had a set of contribs on, all of which are v. useful. However this problem occured from the start.

[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 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 doesnt share. So I hope this helps someone who might have the same problem.