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

[rajan]

I just moved our webshop to a new server with MySQL 5.0.51a and PHP 5.2.5 and I get an error when I add an item to the shopping basket, saying:

 

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

SELECT * FROM free_gifts fg, products p LEFT JOIN products_description pd ON (pd.products_id=fg.products_id) WHERE pd.language_id = '1' AND p.products_id = fg.products_id ORDER BY fg.threshold ASC

[TEP STOP]

 

I tried to understand the solution suggested on this topic, but I don't understand how to apply it on my code...

 

www.humaniversityshop.nl

 

Anyone know what to do..?

[web-project]

create fg column with right settings and your issue will be fixed.

[rajan]

create fg column with right settings and your issue will be fixed.

 

Thanks, but that was slightly greek... How do I creat a fg column, and what would be the right settings...?

[Jan Zonjee]

Thanks, but that was slightly greek...

To me too. The error is really that this doesn't make sense to MySQL: products p LEFT JOIN products_description pd ON (pd.products_id=fg.products_id) . fg.products_id is found neither in products nor in products_description.

Try something else in phpMyAdmin like:

SELECT * FROM products p, products_description pd ON pd.products_id=p.products_id  LEFT JOIN free_gifts fg on p.products_id = fg.products_id WHERE pd.language_id = '1' ORDER BY fg.threshold ASC

Do you really need to select all the fields from those three tables (SELECT *)?

[rajan]

WOW, this is really over my head... I am not experienced in phpMyAdmin (yet). How do I practically do what you suggest and where..?

 

Try something else in phpMyAdmin like:

SELECT * FROM products p, products_description pd ON pd.products_id=p.products_id  LEFT JOIN free_gifts fg on p.products_id = fg.products_id WHERE pd.language_id = '1' ORDER BY fg.threshold ASC

Do you really need to select all the fields from those three tables (SELECT *)?

 

Very grateful for answers... :blush:

[lindsayanng]

She is asking you do run that query.. So copy the text that is in the CODE box and paste it into your MySql area.. just the same way that you ran the original query..

 

HOWEVER, make a back up before you do that.. The weird thing is that you got this error after installing a contribution.. Did you download the most recent package or that contribution?? did you check to see if there were other bug fixes posted after the last complete package..

 

it just seems weird that you are the only one who got this error

[rajan]

I was moving the webshop from one server to another, so I just ftp'ed all the files to the new server. (Not good...?) Before I moved it, it worked fine.

[satish]

1)Chek the field exist in the table.

2)If yes then chek Your sql query by running it thru mysql admin query box.

 

if it was working previoulsy and not now then mysqlversion compatability against sql queries needs to be cheked.

 

Satish

[lindsayanng]

I dont think there was anything wrong with FTP'ing the files to the new host.. i just dont understand why this is the ONLY error.. it seems like a very SPECIFIC error that is just due to code..

 

I wonder if you have a corrupt file - did you try uploading a fresh file just to see??

[rajan]

... i just dont understand why this is the ONLY error.. it seems like a very SPECIFIC error that is just due to code..

Well, it doesn't seem to be the only error...

 

* When I go to checkout, I get an error saying "/shop/checkout_payment.php was not found on this server", although I know it is there, and I checked with the web host that ssl is correctly configured.

 

*When I choose one of the categories in the shop, I get an error:

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

select count(p.products_id) as total 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 = '18'

 

*And I can't login to admin. I get an "Internal Server Error"

 

I did not install this oscommerce in the first place, I just needed to upgrade the host server to prepare the ground for a Joomla installation, but now I feel really up shit creek... Is there still hope to find and correct the errors, or do I soon need to consider to reinstall oscommerce from the beginning on?

www.humaniversityshop.nl

[Guest]

Is the 'Dangling Carrot' contribution installed on the site ? Would you know by chance ? I asked because I have a site I just set up for a customer and after installing the 'dangling carrot' contribution I received the same error. There is a fix listed on the contribution page http://addons.oscommerce.com/info/2990 but it did not correct the problem. I did email the author of the 'fix' but have not received a response yet.

 

 

Chris

[satish]

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

 

This is a mysql 5 error.YOu need to apply mysql 5 patch.

 

Satish

[lindsayanng]

this is what i know about the internal server error

Quite literally an "Internal Server Error" is an error on the web server that you're trying to access. That server is misconfigured in some way that is preventing it from responding properly to what you're asking for.

 

Aside from informing the site owner - which may, or may not, be the "webmaster" email address that's included in the error message - there's absolutely nothing you can do to resolve this problem, because it's not your problem. The person who is responsible for the website needs to fix it.

 

So what if that's you? What if you're the webmaster?

Nine times out of 10, this error results from one of two very common errors:

 

An error in a cgi script that caused it to fail or output an error message before it started producing valid HTML. It happens to me all the time if I have a syntax error in one of my Perl cgi scripts.

 

A permissions issue attempting to access a cgi script. Depending on how your web server is configured, quite often it's not enough for the script to have "execute" permission, but it must also be owned by the correct user, and belong to the correct group. I can't tell you what that should be, since it varies widely from server to server (and even site to site on the same server). My best recommendation is to look at the attributes of a script that's working, and copy that.

 

"Quite literally an 'Internal Server Error' is an error on the web server that you're trying to access."The format of the error described here is common for Apache web servers. That means we can also look for common error logs. Once again, the location of these logs can vary a great deal based on the specific web server configuration.

 

access_log is the log of successful accesses. On a shared or virtual hosting server there may be many of these, one per web site, often with site-specific names, or in site-specific locations on the server.

 

error_log is the log of errors. One some hosts, there is an error log per site. On others, though, even though each site may have its own access_log, there may only be a single error_log for the system.

 

suexec_log is the log most people forget about. This log deals with the permissions used to execute cgi and other scripts. When a cgi fails to execute because its ownership is wrong, the generic "Internal Server Error" shows in the error log, but a more specific error detailing the permissions involved shows in this log.

 

And for the record, the message: "Additionally, a 500 Internal Server Error error was encountered while trying to use an ErrorDocument..." indicates a second configuration error. When the web server encounters an error, the page it displays to report the error is actually just another .html file stored on the server. In all likelihood, the website has been configured to use "custom" error pages (meaning that the website can provide its own custom error messages), but the custom error pages were never created or configured.

 

aske-leo.com

[Guest]

Hey Satish,

 

What patch are you speaking about ? I firmly believe that the problem is in the SQL file that is included in the contribution.

 

 

Chris

[Walter75]

I just moved our webshop to a new server with MySQL 5.0.51a and PHP 5.2.5 and I get an error when I add an item to the shopping basket, saying:

 

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

SELECT * FROM free_gifts fg, products p LEFT JOIN products_description pd ON (pd.products_id=fg.products_id) WHERE pd.language_id = '1' AND p.products_id = fg.products_id ORDER BY fg.threshold ASC

[TEP STOP]

 

I tried to understand the solution suggested on this topic, but I don't understand how to apply it on my code...

 

www.humaniversityshop.nl

 

Anyone know what to do..?

 

I had the same problem it has to do with SQL version

It's only the placement of "(" and ")"

 

Fix 3 files:

shopping_cart.php

Edit the line to:

$gift_query = tep_db_query("SELECT fg.*, p.products_id, p.products_model, p.products_price, p.products_image, p.products_status, pd.products_name FROM (" . TABLE_CARROT . " fg, " . TABLE_PRODUCTS . " p)

LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON (pd.products_id=fg.products_id)

WHERE pd.language_id = '".$languages_id."' AND p.products_id = fg.products_id AND p.products_status = '1' ORDER BY fg.threshold ASC");

 

admin/gift_add.php

Edit the line to:

$gift_query = tep_db_query("SELECT fg.*, p.products_id, pd.products_name FROM (" . TABLE_CARROT . " fg, products p)

LEFT JOIN products_description pd ON (pd.products_id=fg.products_id)

WHERE pd.language_id = '".$languages_id."'

AND p.products_id = fg.products_id

ORDER BY fg.threshold ASC");

 

index.php

its discribted in the contribution : MYSQL 5. fix for v.2.6a

 

Goodluck!!

[rajan]

Is the 'Dangling Carrot' contribution installed on the site ? Would you know by chance ? (...) Chris

 

I don't know, although nothing has been installed since jan 07 (if the package is newer than that)... How can I find out?

 

I'm back on the case again (after a needed day off yesterday) and I will try out the different solution you all posted :-)

[satish]

Just confirm with Your hosting company if they update mysql version and follow what ever comments are here related to mysql 5.

 

Satish

[rajan]

(...)

Fix 3 files:

shopping_cart.php

Edit the line to:

$gift_query = tep_db_query("SELECT fg.*, p.products_id, p.products_model, p.products_price, p.products_image, p.products_status, pd.products_name FROM (" . TABLE_CARROT . " fg, " . TABLE_PRODUCTS . " p)

LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON (pd.products_id=fg.products_id)

WHERE pd.language_id = '".$languages_id."' AND p.products_id = fg.products_id AND p.products_status = '1' ORDER BY fg.threshold ASC");

 

admin/gift_add.php

Edit the line to:

$gift_query = tep_db_query("SELECT fg.*, p.products_id, pd.products_name FROM (" . TABLE_CARROT . " fg, products p)

LEFT JOIN products_description pd ON (pd.products_id=fg.products_id)

WHERE pd.language_id = '".$languages_id."'

AND p.products_id = fg.products_id

ORDER BY fg.threshold ASC");

 

index.php

its discribted in the contribution : MYSQL 5. fix for v.2.6a

 

I tried, but I still get the same errors, now with addition saying:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
p.products_status, pd.products_name FROM (TABLE_CARROT fg, products p) LE' at line 1

 

By the way: Are these errors all about a free gift feature that comes with orders over a certain value? If so, if I eventually manage to log into admin mode and remove the free gift, will the errors at least disappear for now..?

[rajan]

(...) A permissions issue attempting to access a cgi script. Depending on how your web server is configured, quite often it's not enough for the script to have "execute" permission, but it must also be owned by the correct user, and belong to the correct group. I can't tell you what that should be, since it varies widely from server to server (and even site to site on the same server). My best recommendation is to look at the attributes of a script that's working, and copy that. (...)

I have been in contact with the hosting company, and they set the right permission (755 -rwxr-xr-x) for some other scripts that was also not working on our website.

 

But I still get an internal server error trying to login to the admin area. What is the name of the script osCommerce is using?

[rajan]

Oops. I had made an error and for some reason not put the .htpasswd in the public_html folder. But with the .htpasswd in place, I get a new error instead of the internal server error when trying to login to admin:

 

1046 - No database selected
select configuration_key as cfgKey, configuration_value as cfgValue from configuration

According to my web host, they say:

This means there is a configuration error in the shop, you have to check the config file to see where this went wrong.

What kind of configuration am I looking for, and in which of the configuration/configure files? (References to the db on the new server in the 2 configure files are correct)

[rajan]

I am still up shit creek with these errors, and I have no webshop and no possibilities to log in to admin mode. Does anyone know what the above configurations in the config files should be..?

 

Still very grateful for answers

[Guest]

Hi all

 

Just run into a similar problem - installing dangling carrot 2.6a on a modified ms2.2 rc2a. MySQL 5.0.51a-community and PHP 5.2.5

 

Mods installed so far:

STS

http://addons.oscommerce.com/info/1524

 

UK Royal Mail & Overseas Shipping Methods

http://addons.oscommerce.com/info/4473

 

UK Based osCommerce

http://addons.oscommerce.com/info/2816

 

SID Killer

http://addons.oscommerce.com/info/2823

 

Ultimate Seo Urls

http://addons.oscommerce.com/info/2823

 

Ship Rates for UK Postcodes

http://addons.oscommerce.com/info/6165

 

Dynamenu

http://addons.oscommerce.com/info/4228

 

Lightbox

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

 

XSell

http://addons.oscommerce.com/info/1415

 

I get the error

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where p.products_carrot = '0' and p.products_status = '1' and p.products_ordered' at line 1

 

I've tried all the steps I can see on here as well as followed the latest install instructions.

 

when I select a category or product, the rest of my store works as normal.

 

Hope someone can help.

 

Thanks

Rob

[Hotclutch]

Hi all

 

Just run into a similar problem - installing dangling carrot 2.6a on a modified ms2.2 rc2a. MySQL 5.0.51a-community and PHP 5.2.5

 

Mods installed so far:

STS

http://addons.oscommerce.com/info/1524

 

UK Royal Mail & Overseas Shipping Methods

http://addons.oscommerce.com/info/4473

 

UK Based osCommerce

http://addons.oscommerce.com/info/2816

 

SID Killer

http://addons.oscommerce.com/info/2823

 

Ultimate Seo Urls

http://addons.oscommerce.com/info/2823

 

Ship Rates for UK Postcodes

http://addons.oscommerce.com/info/6165

 

Dynamenu

http://addons.oscommerce.com/info/4228

 

Lightbox

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

 

XSell

http://addons.oscommerce.com/info/1415

 

I get the error

 

 

I've tried all the steps I can see on here as well as followed the latest install instructions.

 

when I select a category or product, the rest of my store works as normal.

 

Hope someone can help.

 

Thanks

Rob

 

SEO URLS and XSELL should not be the problem. I have those mods and they work fine with php and mysql V5. On what page exactly do you get the error?

[Guest]

SEO URLS and XSELL should not be the problem. I have those mods and they work fine with php and mysql V5. On what page exactly do you get the error?

 

I get the error on any category or product page only. All others are working fine. Leads me to believe it's to do with the catalog/product_info.php page alteration on dangling carrot. These are the instructions for that page

 

catalog/product_info.php:

 

Find:

require('includes/application_top.php');

 

BENEATH it, ADD:

$check_gift = tep_db_query("select p.products_tax_class_id, p.products_carrot, p.products_price from " . TABLE_PRODUCTS . " p where p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' ");

$gift = tep_db_fetch_array($check_gift);

 

if ($gift['products_carrot'] == 1) {

$product_gifts_query = tep_db_query("select g.gift_id, g.threshold, g.products_id from " . TABLE_CARROT . " g where g.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "'");

$product_gift = tep_db_fetch_array($product_gifts_query);

}

 

However this step is optional and when I take it out I still have the problem!! :angry:

[Hotclutch]

I get the error on any category or product page only. All others are working fine. Leads me to believe it's to do with the catalog/product_info.php page alteration on dangling carrot. These are the instructions for that page

 

 

 

However this step is optional and when I take it out I still have the problem!! :angry:

 

Maybe you should search that contributions' files for SQL statements and see where you can apply this CONTRIBUTION