Sql Error

[umandajayo]

hi friends

I am developing a osCommerce website and I am strucking with displaying items.
Ok Ill explain what my issue in my site

ok I am using a sql query to display all the product in database. ok this is my query

$products_new_query_raw = "select * from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";

no problem this query is perfectly working. :)

but when I go the next level of menu it should be short under the category which was added to the particular menu item.

ok for this case I was used a cat_id to select particular data regarding to the menu

I create a another sql query, and it like this

$products_new_query_raw = "select * from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and categories_id = '25' order by p.products_date_added DESC, pd.products_name";

I added few codes for WHERE clause. ( and categories_id = '25') but unfortunately it is given that following error massage :'(
1054 - Unknown column 'categories_id' in 'where clause'

select count as total from products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and categories_id = '25'

[TEP STOP]

hmmm.....ok please help me...

Thank you

Umanda Jayo Bandara

[rajarajan07]

Get some info from the thread
http://forums.oscommerce.com/topic/230304-1054-unknown-column-pproducts-id-in-on-clause/

[urtrivedi]

You just give alias name before column name

p.categories_id

Also check the spelling of column name

[umandajayo]

thank you all your replies

yes I also put a alias name

$products_new_query_raw = "select * from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.categories_id = '25' order by p.products_date_added DESC, pd.products_name";

but still it is giving a error massage
1054 - Unknown column 'p.categories_id' in 'where clause'

select count(*) as total from products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.categories_id = '25'

[TEP STOP]

ok guys I ll put here database table and it is sructure

ok there are 9 tables are using to store the relevant data of submitted products.

1. products
2. products_attributes
3. products_attributes_download
4. products_description
5. products_notifications
6. products_options
7. products_options_values
8. products_options_values_to_products_options
9. products_to_categories

category id s are storing in products_to_categories table under categories_id column

so what should I do...?

[urtrivedi]

I have added some thing to your query

"select * from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd , products_to_categories pc where pc.products_id=p.products_id and p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and pc.categories_id = '25' order by p.products_date_added DESC, pd.products_name";

[Virtualbase]

please check does that 'categories_id' field exist in the Products table or are you using the correct spell for this field?