sql error 1054: unknown column

[Guest]

Aloha,

I've been struggling with corrections on database after adding contributions. I reloaded the original oscommerce.sql file, then the .sql files of the two contributions I am using (Header tags & group discount) and now am reading an error code on index page.

 

It reads

1054 - Unknown column 'customers_group_id' in 'where clause'

select products_id, specials_new_products_price from specials where (products_id = '27' or products_id = '26' or products_id = '25' or products_id = '24' or products_id = '23' or products_id = '22' or products_id = '21' or products_id = '20' or products_id = '19' ) and status = '1' and customers_group_id = '0'

 

I tried to run the sql file of the contribution again, and it says the column is already there. So I don't understand why this says unknown column.

 

Please help, the site is down now and I am concerned about reentering products before this is corrected.

Thanks...

Jennifer

[FalseDawn]

Please post the SQL you are trying to run and the exact message you are getting.

[Guest]

Aloha,

Thank you for your help. Please let me know if there is anything else I can provide.

This is the error code I get simply on the first page of my catalog:

1054 - Unknown column 'customers_group_id' in 'where clause'

 

select products_id, specials_new_products_price from specials where (products_id = '25' or products_id = '24' or products_id = '23' or products_id = '22' or products_id = '21' or products_id = '20' or products_id = '19' or products_id = '18' or products_id = '17' ) and status = '1' and customers_group_id = '0'

 

This is the error code if I try to enter a new product:

1054 - Unknown column 'customers_group_id' in 'field list'

 

select customers_group_id, customers_group_name from customers_groups where customers_group_id != '0' order by customers_group_id

 

I ran the original oscommerce file hoping to correct previous errors. Then I ran the following two files from contributions:

ALTER TABLE products_description ADD products_head_title_tag VARCHAR(80) NULL;

ALTER TABLE products_description ADD products_head_desc_tag LONGTEXT NULL;

ALTER TABLE products_description ADD products_head_keywords_tag LONGTEXT NULL;

 

ALTER TABLE categories ADD category_head_title_tag VARCHAR(80) NULL;

ALTER TABLE categories ADD category_head_desc_tag LONGTEXT NULL;

ALTER TABLE categories ADD category_head_keywords_tag LONGTEXT NULL;

 

and...

DROP TABLE IF EXISTS customers_groups;

CREATE TABLE customers_groups (

  customers_groups_id INT(11) NOT NULL AUTO_INCREMENT,

  customers_groups_name VARCHAR(32) NOT NULL DEFAULT '',

  customers_groups_discount DECIMAL(8,2) NOT NULL DEFAULT '-0',

  PRIMARY KEY  (customers_groups_id)

) TYPE=MyISAM;

 

INSERT INTO customers_groups VALUES (1, 'Default', '-0.00');

INSERT INTO customers_groups VALUES (2, 'WholeSale', '-20.00');

 

ALTER TABLE customers ADD customers_groups_id INT( 11 ) DEFAULT '1' NOT NULL;

 

I appreciate your help SO much ...

Blessings,

Jennifer

[FalseDawn]

I don't think you have installed the contribution correctly:

 

This query:

 

select products_id, specials_new_products_price from specials where (products_id = '27' or products_id = '26' or products_id = '25' or products_id = '24' or products_id = '23' or products_id = '22' or products_id = '21' or products_id = '20' or products_id = '19' ) and status = '1' and customers_group_id = '0'

 

 

is looking for the "customers_group_id" column in the "specials" table, yet none of the alter SQL statements you posted alter the specials table in any way.

 

I'm not familiar with this contribution - have you tried the support thread for it?

 

It looks to me like the original query should be linking to the customers and customers_groups table somehow, but this is missing.

[Guest]

have you tried the support thread for it?

 

Unfortunately, it is no longer supported. However, I did have this contribution working fine before, and had no problems. Then I tried to install the ProductAttributes contribution and everything went haywire. I switched all of the files back to their original code, but the database had been altered. Trying to handle it myself (maybe not so smart) I thought if I ran the original oscommerce.sql file it would return the database to its original status. Then I ran the two sql files for the contributions I had already installed that were working (HeaderTags and GroupDiscount) and that is where I am now.

 

I cannot enter products and the index page of the catalog now looks like this: Look Here

 

I am frustrated and desperate for help! If you could assist me with this I would gladly send you a donation. Any information you need, I will provide.

 

Thanking you in advance....

Jennifer

[FalseDawn]

I switched all of the files back to their original code

 

This might be a problem then - you will need to look at the group discount contribution and see what code changes it says you need to do, and reapply those changes- as I said, the query is not correct - you can either remove the

 

"and customers_group_id = '0'" off the end of the query (in which case the group discount feature will not work on your specials), or you can go through the contribution very carefully and make sure all the changes you need to do are reflected in your current files.

[Guest]

Well, I found my mistake and its more ridiculous than one might expect. I ran the sql file of the wrong contribution. So, before I make matters worse, please advise...

This is what I was supposed to run:

ALTER TABLE customers
ADD customers_group_id smallint UNSIGNED NOT NULL default '0',
ADD customers_group_ra enum('0','1') NOT NULL,
ADD customers_payment_allowed varchar(255) NOT NULL default '',
ADD customers_shipment_allowed varchar(255) NOT NULL default '';

DROP TABLE IF EXISTS products_groups;
CREATE TABLE products_groups (
 customers_group_id smallint UNSIGNED NOT NULL default '0',
 customers_group_price decimal(15,4) NOT NULL default '0.0000',
 products_id int(11) NOT NULL default '0',
 PRIMARY KEY  (customers_group_id, products_id)
);

ALTER TABLE specials
ADD customers_group_id smallint UNSIGNED NOT NULL default '0';

DROP TABLE IF EXISTS customers_groups;
CREATE TABLE customers_groups (
customers_group_id smallint UNSIGNED NOT NULL,
customers_group_name varchar(32) NOT NULL default '',
customers_group_show_tax enum('1','0') NOT NULL,
customers_group_tax_exempt enum('0','1') NOT NULL,
group_payment_allowed varchar(255) NOT NULL default '',
group_shipment_allowed varchar(255) NOT NULL default '',
PRIMARY KEY (customers_group_id)
);

INSERT INTO customers_groups VALUES('0','Retail','1','0','','');

ALTER TABLE address_book 
ADD entry_company_tax_id VARCHAR(32) DEFAULT NULL AFTER entry_company;

 

But this is what I ran incorrectly:

DROP TABLE IF EXISTS customers_groups;
CREATE TABLE customers_groups (
 customers_groups_id INT(11) NOT NULL AUTO_INCREMENT,
 customers_groups_name VARCHAR(32) NOT NULL DEFAULT '',
 customers_groups_discount DECIMAL(8,2) NOT NULL DEFAULT '-0',
 PRIMARY KEY  (customers_groups_id)
) TYPE=MyISAM;

INSERT INTO customers_groups VALUES (1, 'Default', '-0.00');
INSERT INTO customers_groups VALUES (2, 'WholeSale', '-20.00');

ALTER TABLE customers ADD customers_groups_id INT( 11 ) DEFAULT '1' NOT NULL;

 

My question, do I need to correct the code above somehow before I run the correct sql file?

And thank you GREATLY for your help.

[FalseDawn]

No, you are good to go - it will drop and recreate the customers_groups table, so just run the correct SQL.

 

Edit:

Oops , just seen it also adds the customers_groups_id column, which is not in the "correct" SQL

 

If you can, go into phpMyAdmin and delete the "customers_groups_id" column from the customers table after running the SQL.

 

Otherwise you will have both customer_group_id and customer_groups_id in this table - the latter being redundant.

[Guest]

I am SO relieved, all is well now. BLESS YOU!