Yet another 1054 error. In all categories.

[mrmordy]

Please go to http://pcpackages.net/index.php?cPath=29 for an example of the problem I'm having.

 

This is after a fresh install of the latest osCommerce. Running PHP5 and i've put in the featured_products contribution.

 

I'm still very noob at this, but since my right column is gone, I'm thinking the includes/column_right.php file

 

Can i has some help please :(

 

 

1054 - Unknown column 'p.products_id' in 'on clause'

select distinct p.products_id, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c left join featured f on p.products_id = f.products_id where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id in (30,29) and p.products_status = '1' and f.status = '1' order by rand() DESC limit 6

[TEP STOP]

[Guest]

look to me that you have a dbase problem.Did you "mess" with index.php by any chance?

[mrmordy]

look to me that you have a dbase problem.Did you "mess" with index.php by any chance?

 

Yes, I only followed instructions for featured_products though.

[mrmordy]

What seems strange though, is that my main catalog page seems ok :/

[Jan Zonjee]

What seems strange though, is that my main catalog page seems ok :/

That is one weird query:

categories c left join featured f on p.products_id = f.products_id

There is indeed no p.products_id in the table categories.

[mrmordy]

That is one weird query:

categories c left join featured f on p.products_id = f.products_id

There is indeed no p.products_id in the table categories.

 

Hiya Jan! Thankyou for your reply :)

 

where may I be able to insert/replace this code?

 

Thankyou in advance :)

[Jan Zonjee]

where may I be able to insert/replace this code?

Looks like this is in includes/modules/featured.php. Find that query around line 43 and try this one instead (couple of lines above shown for reference):

	} else {
  $featured_products_category_id_list .= ',' . $featured_products_category_id;
}
$featured_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price  from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id = p2c.products_id left join " . TABLE_CATEGORIES . " c using(categories_id) left join " . TABLE_FEATURED . " f on p.products_id = f.products_id where c.parent_id in (" . $featured_products_category_id_list . ") and p.products_status = '1' and f.status = '1' order by rand() DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS);

[mrmordy]

Wow! that worked perfectly! Thankyou for your help.

 

May I ask what the problem with the query was?

[Jan Zonjee]

May I ask what the problem with the query was?

The query used the following tables: from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c left join featured f on p.products_id = f.products_id. As you can see it has comma's in between which means a join. But in the last part it states to left join categories and featured using p.products_id. There is no p.products_id in categories.

 

In the revised queries the tables are all left joined and (apparently ;) ) you can then use left join featured f on p.products_id = f.products_id because products is in the left join'ed tables.

 

If I were you I would add an index on products_id in the table specials. If you start to add a lot of products this query will likely get real slow.

[FixItPete]

Marking this thread for reference.

 

(I still can't get Featured Products to work for me since mySQL 5 upgrade) :(