osCommerce Community Support Forums > 1054 - Unknown column 's.public_flag'

Full Version: 1054 - Unknown column 's.public_flag' - error

osCommerce Community Support Forums > osCommerce Online Merchant v2.x > Tips and Tricks

Phr33x

Mar 8 2008, 03:02 AM

Seeing this error?

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

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

[TEP STOP]

It seems to be a common one, which many are currently struggling with. From what i can determine, this is nothing to do with any contributions.

Probable cause:

It seems that in at least the v2.2 RC2 version, there is a call to a field in the 'order_status' table, called 'public_flag'. In default installs however, this field is not created. The offending line of code is found around line 97 of accounts.php

The offending line:

CODE

$orders_query = tep_db_query("select o.orders_id, o.date_purchased, o.delivery_name, o.delivery_country, o.billing_name, o.billing_country, ot.text as order_total, s.orders_status_name from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_TOTAL . " ot, " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$customer_id . "' and o.orders_id = ot.orders_id and ot.class = 'ot_total' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.public_flag = '1' order by orders_id desc limit 3");

The quick fix:

if you are able to access your DB is to run the sql statement:

CODE

alter table orders_status add public_flag int DEFAULT '1';

This will create the missing field in the order_status table and the world will be a happy place once more.

Unless anyone has any better ideas... :thumbsup:

bktrain

Mar 8 2008, 04:40 AM

If upgrading to the rc1 or rc2a versions. The updates clearly state

[SQL] 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;

Phr33x

Mar 8 2008, 07:12 AM

QUOTE (bktrain @ Mar 8 2008, 03:40 PM)

Yes, it does. However, many are not 'upgrading' but performing an automated install. For example, I did a fresh install from the 'Fantastico' script, and the required field was not created.

A quick search of these forums suggests others are having this problem - whether by their own error or something else. This is certainly not an isolated problem. At least it's a simple resolution.

Curley61

Mar 8 2008, 06:59 PM

QUOTE (Phr33x @ Mar 8 2008, 01:12 AM)

That was what I did. I did the fresh install when moved to a new server.... So I didn't think I needed to read the upgrade documentation. I ran the SQL and it works great now.

Thanks for all the quick replies. This is a great group and I have learned a lot over the last several months.

Thanks again,
Paul

makotoshoto

Mar 8 2008, 08:25 PM

thanks Phr33x! The error just appeared to customers, and looks to be corrected now.

silver360

Mar 9 2008, 12:58 PM

Can someone help me.........I have this and I think it is a result of switching to a new server?

Any help would be greatly appreciated as this has caused alot of grief.......

http://forums.oscommerce.com/index.php?showtopic=295487

jnbits

Mar 11 2008, 01:19 PM

I have got this problem.

I tried your solution but i hasn't seemed to work i still get this error

QUOTE

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

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

[TEP STOP]

I uploaded the .sql to my DB but it came up with this error.

QUOTE

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0044 sec)
SQL query:
ALTER TABLE orders_status ADD public_flag int DEFAULT '1';

I dnt no if i did it right or not.

wlynch

Apr 21 2008, 09:56 PM

QUOTE

The quick fix:

if you are able to access your DB is to run the sql statement:

CODE

alter table orders_status add public_flag int DEFAULT '1';

This will create the missing field in the order_status table and the world will be a happy place once more.
Unless anyone has any better ideas...

Fantastic - worked first time! Thanks

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.

OSCOMMERCE SUPPORT CALL 702-453-3332