Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

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


tthorpe

Recommended Posts

  • Replies 86
  • Created
  • Last Reply
Hello I know this topic is kinda old.. anyway I'm still hoping for someone to help me here on this... Im having the same issue as xchido, I've posted in new Topics but no one is replying :( anyway if anyone can really help me send me a PM or anything...I'll really appreciate :'(

 

Thanks

Darp

 

 

Im not sure if all you are having this same problem.. but it seems as though its all centered on the 'on clause' apparently :P .. well can you guys try this..

 

For Example

 

select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '61' order by p.products_date_added DESC limit 2

 

Try using parenthesis

select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from ( products_description pd, products p ) left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '61' order by p.products_date_added DESC limit 2

 

See the two parenthesis

 

(products_description pd, products p ) after the FROM clause and before the JOIN clauses ..

 

try that..

 

insaini

Link to comment
Share on other sites

  • 2 weeks later...

Hey everyone I'm having a similar problem, but ONLY if you go to the "My Account" (/account.php) page... here's the exact error ...

 

1054 - Unknown column 's.public_flag' in 'where clause'

select count(*) as total from orders o, orders_status s where o.customers_id = '3' and o.orders_status = s.orders_status_id and s.language_id = '1' and s.public_flag = '1'

[TEP STOP]

 

Any ideas? we're meant to go live tonight with our update so timing just sucks!

Link to comment
Share on other sites

If you're using or upgraded to the rc version did you do this step?

 

 Database changes
Add indexes to increase performance
Add public and download status flag fields to the orders_status table
Increase payment_method field size on the orders table
Increase the last_page_url field size on the whos_online table
[code]alter table banners add index idx_banners_group (banners_group); alter table banners_history add index idx_banners_history_banners_id (banners_id); alter table currencies add index idx_currencies_code (code); alter table customers add index idx_customers_email_address (customers_email_address); alter table customers_basket add index idx_customers_basket_customers_id (customers_id); alter table customers_basket_attributes add index idx_customers_basket_att_customers_id (customers_id); alter table orders add index idx_orders_customers_id (customers_id); alter table orders_products add index idx_orders_products_orders_id (orders_id); alter table orders_products add index idx_orders_products_products_id (products_id); alter table orders_status_history add index idx_orders_status_history_orders_id (orders_id); alter table orders_products_attributes add index idx_orders_products_att_orders_id (orders_id); alter table orders_products_download add index idx_orders_products_download_orders_id (orders_id); alter table products add index idx_products_model (products_model); alter table products_attributes add index idx_products_attributes_products_id (products_id); alter table reviews add index idx_reviews_products_id (products_id); alter table reviews add index idx_reviews_customers_id (customers_id); alter table specials add index idx_specials_products_id (products_id); alter table zones add index idx_zones_to_geo_zones_country_id (zone_country_id); alter table orders_status add public_flag int DEFAULT '1'; alter table orders_status add downloads_flag int DEFAULT '0'; alter table orders modify payment_method varchar(255) NOT NULL; alter table whos_online modify last_page_url text NOT NULL;[/code]

Link to comment
Share on other sites

  • 3 weeks later...

Tried everything from the above. This is what I'm getting on Search...

 

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 '%search%' or p.products_model like '%search%' or m.manufacturers_name like '%search%') )

 

Everything else on the site is working. Only started noticing this after my host switched to a new platform (presumably using the newest versions of PHP/MySql). Any help appreciated.

Link to comment
Share on other sites

After applying Monika's fix to my site I'm experiencing a wierd glitch...

 

check it out... http://www.outdoorexperience.us/store/index.php?cPath=26

 

the n's under the Catagories heading.

 

can anyone give me an idea of what I need to do to get rid of them.

 

thank you.

 

Where did you find the patch? I have been searching for over an hour now, and no luck. The search function on this board is not very good

Link to comment
Share on other sites

Where did you find the patch? I have been searching for over an hour now, and no luck. The search function on this board is not very good

 

 

I use contribution called star products and this error comes how i can fix it ?

 

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

 

select substring(pd.products_description, 1, 598) as products_description, p.products_id, p.products_image, p.manufacturers_id, p.products_price, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price, p.products_tax_class_id, sp.product_id from products p, products_description pd, ACER01 sp left join specials s on p.products_id = s.products_id where p.products_id = pd.products_id and p.products_status = '1' and pd.products_description != '' and p.products_id=sp.product_id and pd.language_id = '4'

 

[TEP STOP]

 

 

:blink:

 

And code here:

 

<?php

function tep_ACER01_with_attributes($products_id) {

$attributes_query = tep_db_query("select count(*) as count from " . TABLE_PRODUCTS_ATTRIBUTES . " where products_id = '" . (int)$products_id . "'");

$attributes = tep_db_fetch_array($attributes_query);

 

if ($attributes['count'] > 0) {

return true;

}

else {

return false;

}

}

 

$star_products_query = tep_db_query("select substring(pd.products_description, 1, 240) as products_description, p.products_id, p.products_image, p.manufacturers_id, p.products_price, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price, p.products_tax_class_id, sp.product_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_ACER01 . " sp left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_id = pd.products_id and p.products_status = '1' and pd.products_description != '' and p.products_id=sp.product_id and pd.language_id = '" . $languages_id . "'");

$star_products = tep_db_fetch_array($star_products_query);

$star_products['products_name'] = tep_get_products_name($star_products['products_id']);

 

$evita_cortar_palabras = explode( ' ', $star_products["products_description"] );

array_pop( $evita_cortar_palabras );

$star_products["products_description"] = implode( ' ', $evita_cortar_palabras );

 

 

if (!tep_ACER01_with_attributes($star_products['products_id'])) {

$star_products["0"] = array('align' => 'center',

'params' => 'width="100%" height="100%" valign="top"',

'text' => '

 

 

<table border=0 WIDTH="100%">

 

<tr>

<td align="center" valign="top">

<a href="' . tep_href_link(FILENAME_POPUP_IMAGE, 'pID=' . $star_products['products_id']) . '"' . ' target="popup"' . ' onClick="window.open(this.href, this.target, \'toolbar=no, location=no, directories=no, status=no, menubar=no, scrollbars=no, resizable=yes, copyhistory=no, width=' . SMALL_IMAGE_WIDTH . ', height=' . SMALL_IMAGE_HEIGHT . '\').focus(); return false;">' . tep_image(DIR_WS_IMAGES . $star_products['products_image'], $star_products['products_name'], 0,200) . '</a>

</td>

</tr>

 

<tr>

<td width="100%" nowrap class="star-product-title01">' . $star_products['products_name'] . '<br></td>

</tr>

<td width="350" height="100" ALIGN=left VALIGN=TOP nowrap class="star-product01">'. $star_products["products_description"] . '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, "products_id=" . $star_products["products_id"]). '">' . STAR_READ_MORE . '</a>

</td>

</td>

 

 

 

<tr>

<td ALIGN=center VALIGN=TOP class="star-product-price01">

'.$currencies->display_price($star_products['final_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . '

<br>

</td>

 

</tr>

 

<tr>

<td align=center valign=top>

<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, "products_id=" . $star_products["products_id"]). '">' . tep_image(DIR_WS_LANGUAGES . $language . '/images/buttons/more_info.gif',IMAGE_BUTTON_MOREINFO) . '</a>' . tep_draw_separator('pixel_trans.gif', '10', '10') . '<a href="' . tep_href_link(FILENAME_KIRJAUDU, tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $star_products['products_id']) . '">' . tep_image_button('button_buy_now.gif', IMAGE_BUTTON_IN_CART) . '</a>

</td>

 

</tr>

</table>

 

<br>

 

 

');

 

new contentBox($star_products);

}

?>

Link to comment
Share on other sites

  • 4 weeks later...
Thats seems simple to follow, however as using a template and getting these errors, I have found that I dont have the code that they are stating to replace in the index.php

 

Any ideas?

 

 

i just spent the last week digging through my code to figure this out. i had the same problem as you and couldn't find the proper code to swap out. i did finally find it though in this file: templates/content/advanced_search_result.tpl.php it should start around line 80. hope this helps, it worked for me.

Link to comment
Share on other sites

  • 1 month later...

I had this problem that affected my advanced search results and manufacturers sections

 

I downloaded the latest version of oscommerce and uploaded the advanced_search_results.php which fixed the problem. I didnt initially try doing the same with the index.php file as i have the SPPC contribution installed which has some code right in the middle of the manufacturers section. After trying, and failing, to fix it i decided to upload the index.php file to see what would happen and it not only fixed the problem, but the SPPC continues to work with an unmodified index.php file?

 

perhaps the SPPC mod is more complicated than it needs to be? or perhaps i am just lucky? either way it works.

 

If you are going to try this then dont forget to backup your existing files first!!

Link to comment
Share on other sites

  • 1 month later...
I had this problem that affected my advanced search results and manufacturers sections

 

I downloaded the latest version of oscommerce and uploaded the advanced_search_results.php which fixed the problem. I didnt initially try doing the same with the index.php file as i have the SPPC contribution installed which has some code right in the middle of the manufacturers section. After trying, and failing, to fix it i decided to upload the index.php file to see what would happen and it not only fixed the problem, but the SPPC continues to work with an unmodified index.php file?

 

perhaps the SPPC mod is more complicated than it needs to be? or perhaps i am just lucky? either way it works.

 

If you are going to try this then dont forget to backup your existing files first!!

 

Thanks, this idea wound up working like a gem for me. ;)

Link to comment
Share on other sites

  • 3 weeks later...
That's a known problem for those using mysql version 5. Are you sure of the version? Maybe your host updated since last you checked. In any event, take a look at this thread. It might help.

 

Jack

 

hi, i'm new here and found this to be very help forum, thankyou.

this fix worked half way for me, (fixed the subcategory error message) and yes i'm still search and try but i am still stuck on this.

when i search for product by manufacturer this error happens..........

 

1054 - Unknown column 'p2c.manufacturers_id' in 'where clause'

 

select distinct c.categories_id as id, cd.categories_name as name from products p, products_to_categories p2c, categories c, categories_description cd where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id = cd.categories_id and cd.language_id = '1' and p2c.manufacturers_id = '4' order by cd.categories_name

 

[TEP STOP]

 

thanks for any info.

Link to comment
Share on other sites

We've had the same problem with an OsCommerce but we managed to remedy it by replacing the old code with new one in the index.php & advanced_search_resilt.php files.

But we immediatley encoutered a new problem, namely, the CSS is not working. The product pics can be seen, but the template, the design is not working. The fonts, the header the menu's are all fubar.

 

Any ideas on how to fix this problem?

Link to comment
Share on other sites

Any ideas on how to fix this problem?

 

Restore your original files and apply the mysql compatibility patches that you can find by following the link in my signature block.

Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Link to comment
Share on other sites

its a php5 error

 

Try this

 

change....

 

p.products_id

 

to

 

p2c.products_id

 

and see if that works

 

regards

 

Mark

 

Can somebody tell me specifically where this change should be made?

I find the fun in everything.

Link to comment
Share on other sites

  • 4 weeks later...
This can be resolved by editing default.php

 

go to line 174-175 and replace:

 

// We show them all

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . $languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "'";

 

with the following:

 

// We show them all

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

 

worked for me...

 

 

Hi guys/girls/

 

This worked for me (the above) ... sort of...

 

Ok the 1054 error message disapered WHOHOO....

 

HOWEVER!,

 

Now it can't show the products. It's visable in the product category that there are products BUT when i press a category it shows this message...

 

There are no products to list in this category.

 

 

any suggestions? thx

Link to comment
Share on other sites

Thats seems simple to follow, however as using a template and getting these errors, I have found that I dont have the code that they are stating to replace in the index.php

 

Any ideas?

 

 

I'we found the code in the products.php

 

however when I changed the code it fixed the 1054 error mes. but now it dont show any products. It just says There are not products to list or something BUT i see in the categories that there infact is products. it just can show them...

 

weird hue? :S

Link to comment
Share on other sites

Hi guys/girls/

 

This worked for me (the above) ... sort of...

 

Ok the 1054 error message disapered WHOHOO....

 

HOWEVER!,

 

Now it can't show the products. It's visable in the product category that there are products BUT when i press a category it shows this message...

 

There are no products to list in this category.

 

 

any suggestions? thx

 

 

Noo :S

 

It has happened to three different installations of osc all have got different templates, and when I fix them with the fix above that I quoted earlier it's getting...

 

"There are no products to list in this category" - seriously this is bad :blink:

 

PLEASE! help me, what shoudl I do?

_________________ THIS IS WHAT HAPPENED ______________________

 

- The categories display that there are products underneeth but when pressing a category you get the message. "There are no..."

 

- It's possible to wiev the new products = the product must be working somehow... so might it be between the the categories and fetching the information from the products list? :huh:

 

I'we fixed the 1054 error message before and then it gets the message "There are no..."

 

 

 

Anyone :'(

Link to comment
Share on other sites

  • 1 month later...

Hi,

 

I'm having a problem and have please can anyone help me, I have been searching for ages to fix this but without any success. Below is my error, please can someone tell me what it means. I only get this when I click a category that has sub categories.

 

My error is below:

 

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

 

select distinct p.products_id, p.products_image, p.products_model, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, mnf.manufacturers_name, rvw.reviews_rating from (products p) left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c left join manufacturers mnf on p.manufacturers_id = mnf.manufacturers_id left join reviews rvw on p.products_id = rvw.products_id where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '42' and p.products_status = '1' order by p.products_date_added desc limit 2

 

[TEP STOP]

 

I hope someone can help me!

Link to comment
Share on other sites

Hi,

 

I'm having a problem and have please can anyone help me, I have been searching for ages to fix this but without any success. Below is my error, please can someone tell me what it means. I only get this when I click a category that has sub categories.

 

My error is below:

 

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

 

select distinct p.products_id, p.products_image, p.products_model, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, mnf.manufacturers_name, rvw.reviews_rating from (products p) left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c left join manufacturers mnf on p.manufacturers_id = mnf.manufacturers_id left join reviews rvw on p.products_id = rvw.products_id where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '42' and p.products_status = '1' order by p.products_date_added desc limit 2

 

[TEP STOP]

 

I hope someone can help me!

 

See this THREAD for example

Link to comment
Share on other sites

I have a problem now that I fixed the 1054 too. When there are subcatagories to list, I get

n

n

n

where the names of the catagories used to be. I am happy as can be my catagories are back, but can't locate the particular code to stop the n's!

 

Anyone know?

Link to comment
Share on other sites

  • 2 weeks later...

Hi Guys,

 

I had this problem too and it was driving me crazy. The people at the host were no help either even though the problem was happening when they installed it. I tried some of the other suggestions in this post and they the didn't work or even caused other problems. The pc to p2c fix didn't work because I already had a version that included the p2c change.

 

Finally I found Ozstar's method, which is referenced in some other post on this forum.

 

Here's the link to the fix:

 

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

 

It's amazing! By replacing two blocks of code just as he says in his contribution it fixed the problem entirely.

 

Thanks Ozstar!

Link to comment
Share on other sites

Hello,

 

I have a problem, i fix the error 1054 but now when i click to open the produt in the categorie it apear all produtcs it the same descripton, the images are diferent the the descripton it´s the same.

 

A tried to fix this, i tried to search but not anwers.

 

Please help me !

Best regards, Ema

 

Tank you...

Link to comment
Share on other sites

  • 2 months later...
...

Not being stellar at programming, this was a hard fix for me. I finally found the bug fix (in laymen's terms) :D

http://www.oscommerce.com/community/bugs,3171

...

 

Hi tthorpe, thanks for that info. It fixed the problem.

 

Btw anyone had the error 1054 - Unknown column 'p.products_id' in 'on clause' ...

error occuring from index.php in the first of the 2 lines that starts with the comment:"// We show them all" , even though the field "products_id" exists in the table "products" ??.

 

Just changing the order of the table references fixed it for me, I have no idea why it is so though.

I had to change this...

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

 

to this...

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...