Problem with store feeds

[grunionfab]

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

[grunionfab]

I have searched all over for the answer to this problem and it seems like ALOT of people are having it.... Does anyone have any idea how to fix it?

[Shakti]

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.

[grunionfab]

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?

[Shakti]

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

[grunionfab]

Im not sure what you mean? You want me to add this into my database? Like through phpmyadmin?