sunkim Posted March 13, 2007 Posted March 13, 2007 I recieve the following error when I try to find a product in the quick find, can someone help? 1054 - Unknown column 'p.products_id' in 'on clause' select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%biofreeze%' or p.products_model like '%biofreeze%' or m.manufacturers_name like '%biofreeze%') )
Velveeta Posted March 13, 2007 Posted March 13, 2007 I recieve the following error when I try to find a product in the quick find, can someone help?1054 - Unknown column 'p.products_id' in 'on clause' select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%biofreeze%' or p.products_model like '%biofreeze%' or m.manufacturers_name like '%biofreeze%') ) Try reordering that query so all the regular joins are together and all the left joins are together, like this: select count(distinct p.products_id) as total from products p, products_description pd, categories c, products_to_categories p2c left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%biofreeze%' or p.products_model like '%biofreeze%' or m.manufacturers_name like '%biofreeze%') ) Richard. Richard Lindsey
sunkim Posted March 13, 2007 Author Posted March 13, 2007 Try reordering that query so all the regular joins are together and all the left joins are together, like this: select count(distinct p.products_id) as total from products p, products_description pd, categories c, products_to_categories p2c left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%biofreeze%' or p.products_model like '%biofreeze%' or m.manufacturers_name like '%biofreeze%') ) Richard. Thank you Velveeta, but pardon my stupidity, but how do you do that? Do you modifiy the advanced_search.php? Thank you again for the fast help
sunkim Posted March 13, 2007 Author Posted March 13, 2007 Or if you can show me which php file I need to modify It would help me out great deal? One additional thing is that I get -1054 - Unknown column 'p.products_in' in 'onclause' for everything on my left column. When I click on manufactureeres, or what's new... If anyone can shed some light to this matter it would really help me out alot. or point me in the direction right direction and I can start digging for some answers.
Velveeta Posted March 13, 2007 Posted March 13, 2007 Or if you can show me which php file I need to modify It would help me out great deal?One additional thing is that I get -1054 - Unknown column 'p.products_in' in 'onclause' for everything on my left column. When I click on manufactureeres, or what's new... If anyone can shed some light to this matter it would really help me out alot. or point me in the direction right direction and I can start digging for some answers. Hmmm, I don't think that specific query isn't one that comes with oscommerce by default... It has to be included from some add-on module you've installed from a contribution or something... I just did a grep through all of my code for "select count" and then grepped the results for "distinct" which should have found at least 1 line with a query like that, but it didn't... I also tried finding "select count" and then searching the results for "left join", also with 0 matches... Can you think of any modules you've added? Another way to track down where this is coming from is to drop some random echo's throughout your file to see at which point the error text comes out, and then find the 2 debugging echo lines that it falls between, and drop some more in between those in your code, then repeating this until you've narrowed down the place in the code where this is actually happening... Richard. Richard Lindsey
crash3903 Posted March 14, 2007 Posted March 14, 2007 Its a mysql 5 error normally - are you running mysql 5 database? Regards Mark A Reynolds
sunkim Posted March 14, 2007 Author Posted March 14, 2007 Hmmm, I don't think that specific query isn't one that comes with oscommerce by default... It has to be included from some add-on module you've installed from a contribution or something... I just did a grep through all of my code for "select count" and then grepped the results for "distinct" which should have found at least 1 line with a query like that, but it didn't... I also tried finding "select count" and then searching the results for "left join", also with 0 matches... Can you think of any modules you've added? Another way to track down where this is coming from is to drop some random echo's throughout your file to see at which point the error text comes out, and then find the 2 debugging echo lines that it falls between, and drop some more in between those in your code, then repeating this until you've narrowed down the place in the code where this is actually happening... Richard. Thanks for your reply Richard, I don't think I installed any additional modules. I was trying to add Google checkout. But also I deleted everything and did a fresh install and it was doing the same thing. the site I'm working on is http://thespinesolution.com/osCommerce/catalog/index.php If you don't mind telling me which file I should add those echos to, general.php advance_search_results.php... I can at least start to try to narrow it down. Or maybe try to grab the files from the osCommerce download section. Its a bit confusing trying to find which file I need to be editing. I really approciate your help.
Guest Posted March 14, 2007 Posted March 14, 2007 use the lates osc http://www.oscommerce.com/solutions/downloads to get around the mysql5x issues.
sunkim Posted March 14, 2007 Author Posted March 14, 2007 use the lates oschttp://www.oscommerce.com/solutions/downloads to get around the mysql5x issues. So is this a mysql5x issue?
Recommended Posts
Archived
This topic is now archived and is closed to further replies.