Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Problem with store feeds


grunionfab

Recommended Posts

I have this store feeds contribution:

http://www.oscommerce.com/community/contributions,4455

 

I recently transferred my site to a new server and now when i try to run a feed i get this error:

 

: SQL error Unknown column 'products.manufacturers_id' in 'on clause'| sql = SELECT concat( 'http://grunionmoto.com/product_info.php?language=en&currency=USD&products_id=' ,products.products_id) AS product_url, products_model AS prodModel, products_weight, manufacturers.manufacturers_name AS mfgName, manufacturers.manufacturers_id, products.products_id AS id, products_description.products_name AS name, products_description.products_description AS description, products.products_quantity AS quantity, products.products_status AS prodStatus, FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * 1,2) AS price, CONCAT( 'http://grunionmoto.com/images/' ,products.products_image) AS image_url, products_to_categories.categories_id AS prodCatID, categories.parent_id AS catParentID, categories_description.categories_name AS catName FROM categories, categories_description, products, products_description, products_to_categories left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id ) left join specials on ( specials.products_id = products.products_id AND ( ( (specials.expires_date > CURRENT_DATE) OR (specials.expires_date = 0) ) AND ( specials.status = 1 ) ) ) WHERE products.products_id=products_description.products_id AND products.products_id=products_to_categories.products_id AND products_to_categories.categories_id=categories.categories_id AND categories.categories_id=categories_description.categories_id AND products.products_status != 0 AND products.products_price != 0 AND products.products_price != '' AND products_description.language_id = '1' AND products_description.language_id = '1' AND categories_description.language_id = '1' ORDER BY products.products_id ASC

 

Anyone have any ideas? I am at a total loss when it comes to messing with my database....

Link to comment
Share on other sites

What is the version of Mysql in the new server and the old one? I guess it's mysql4 in the old and mysql5 in the new which causes the problem. OSC has a patch for mysql5, you can find that in the extras folder of RC2 download. open the file named update-20060817.txt in extras folder and scroll down to Mysql5 patch at the bottom.

 

After you apply those listed there, there are some areas where you may likely get the error. I got errors in categories.php in admin area. For all those errors like "unknown column" in sql where "join" is used, I followed the guidelines in mysql forum

 

This is a change that was made in 5.0.15 to make MySQL more compliant with the standard.

According to the SQL:2003

 

<from clause> ::= FROM <table reference list>

<table reference list> ::=

<table reference> [ { <comma> <table reference> }... ]

<table reference> ::=

<table factor>

| <joined table>

<joined table> ::=

<cross join>

| <qualified join>

| <natural join>

...

 

Thus when you write

 

... FROM t1 , t2 LEFT JOIN t3 ON (expr)

 

it is parsed as

 

(1) ... FROM t1 , (t2 LEFT JOIN t3 ON (expr))

 

and not as

 

(2) ... FROM (t1 , t2) LEFT JOIN t3 ON (expr)

 

so, from expr you can only refer to columns of t2 and t3 - operands of the join.

Workaround - to put parentheses explicitly as in (2). Then you can refer to t1 columns

from expr.

Link to comment
Share on other sites

Shakti, Thanks for the reply. I did everything the patch said to do and it is still not working. When I transferred my other site to the new server I did not get this error on the feeds so I dont know what is up with this site that I am getting it..... I did not get any errors from installing that patch but I also think I had most of it already installed....

 

Does anyone else have any ideas on what I could do to fix this error?

Link to comment
Share on other sites

Try this? Added '(' and ')' in the FROM part of the sql.

 

SELECT concat( 'http://grunionmoto.com/product_info.php?language=en&currency=USD&products_id=' ,products.products_id) AS product_url, products_model AS prodModel, products_weight, manufacturers.manufacturers_name AS mfgName, manufacturers.manufacturers_id, products.products_id AS id, products_description.products_name AS name, products_description.products_description AS description, products.products_quantity AS quantity, products.products_status AS prodStatus, FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * 1,2) AS price, CONCAT( 'http://grunionmoto.com/images/' ,products.products_image) AS image_url, products_to_categories.categories_id AS prodCatID, categories.parent_id AS catParentID, categories_description.categories_name AS catName FROM (categories, categories_description, products, products_description, products_to_categories ) left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id ) left join specials on ( specials.products_id = products.products_id AND ( ( (specials.expires_date > CURRENT_DATE) OR (specials.expires_date = 0) ) AND ( specials.status = 1 ) ) ) WHERE products.products_id=products_description.products_id AND products.products_id=products_to_categories.products_id AND products_to_categories.categories_id=categories.categories_id AND categories.categories_id=categories_description.categories_id AND products.products_status != 0 AND products.products_price != 0 AND products.products_price != '' AND products_description.language_id = '1' AND products_description.language_id = '1' AND categories_description.language_id = '1' ORDER BY products.products_id ASC

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...