Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

sql question


sjs3674

Recommended Posts

Hi everyone, I'd really appreciate a nudge in the right direction please. I'm not looking for the answer par se, as I'm really trying to learn, I cannot at the moment see the wood for the trees, so any assistance would be most welcome.

 

Thank you in advance.

 

I have osC 2.3.1 with MTS (currently no addons installed) with mysql 5.1.41

 

After running this via phpMyAdmin:

 

 

drop table if exists products_attributes;

create table products_attributes (

products_attributes_id int(11) not null auto_increment,

products_id int(11) not null ,

options_id int(11) not null ,

options_values_id int(11) not null ,

price_prefix char(1) not null ,

PRIMARY KEY (products_attributes_id),

KEY idx_products_attributes_products_id (products_id)

);

insert into products_attributes (products_attributes_id, products_id, options_id, options_values_id, options_values_price, price_prefix) values ('78', '34', '16', '0.0000', '+');

 

I then get this:

 

#1136 - Column count doesn't match value count at row 1

from catalog/admin/product_attributes.php (around line 59) I see this:

 

tep_db_query("insert into " . TABLE_PRODUCTS_ATTRIBUTES . " values (null, '" . (int)$products_id . "', '" . (int)$options_id . "', '" . (int)$options_values_id . "', '" . (float)tep_db_input($value_price) . "', '" . tep_db_input($price_prefix) . "')");

Link to comment
Share on other sites

Hello

Your columns are

1. products_attributes_id

2. products_id

3. options_id

4. options_values_id

5. price_prefix char

 

you are attempting to add the values for the following columns

 

1. products_attributes_id

2. products_id

3. options_id

4. options_values_id

5. options_values_price

6. price_prefix

 

You are missing options_values_price from the table structure.

 

Nic

Sometimes you're the dog and sometimes the lamp post

[/url]

My Contributions

Link to comment
Share on other sites

Thanks Nic,

 

I ran

 

 

drop table if exists products_attributes;

create table products_attributes (

products_attributes_id int(11) not null auto_increment,

products_id int(11) not null ,

options_id int(11) not null ,

options_values_id int(11) not null ,

options_values_price decimal(15,4) not null ,

price_prefix char(1) not null ,

PRIMARY KEY (products_attributes_id),

KEY idx_products_attributes_products_id (products_id)

);

 

and got the same

 

#1136 - Column count doesn't match value count at row 1

 

??

Link to comment
Share on other sites

In your query there are 6 fields and 5 values to insert that makes the error.

The number of fields and the values should be same.

Try this:

 

insert into products_attributes (products_attributes_id, products_id, options_id, options_values_id, options_values_price, price_prefix) values ('78', '34', '16', '2', '0.0000', '+');

Link to comment
Share on other sites

Thanks for all you comments.

 

I'm still getting an errror.

 

 

Error

 

SQL query:

INSERT INTO products_attributes( products_attributes_id, products_id, options_id, options_values_id, options_values_price, price_prefix, )

VALUES (

'78'
,
'34'
,
'1'
,
'16'
,
'0.0000'
,
'+'

);

 

 

 

MySQL said:

#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 ') values ('78', '34', '1', '16', '0.0000', '+')' at line 1

Link to comment
Share on other sites

You have an extra comma , after price_prefix in the field list. Note that SQL is not as forgiving as many programming languages, which ignore an extraneous comma after the last item.

 

By the way, if you are inserting a new record, with a new products_attribute_id, you would normally either give a null value for that field (generate the next number) or omit both the field name and the value from the lists (just 5 named fields). On the other hand, if you are trying to rebuild/restore an existing database, say, and want products_attribute_id = 78, your code would be correct. Just understand what you're doing here. In your very first post, was '78' the products_attribute_id or the products_id? You had 6 fields and only 5 values (I suspect that you were missing a null products_attribute_id value).

Link to comment
Share on other sites

Phil - Many thanks for your keen eye! Yes I was restoring a db rather than just entering a new record. I have since altered my product_attributes.php file to give a null value from now on. Thank you very much for your help.

Link to comment
Share on other sites

Uh, if you're restoring a database, you would want to have the same products_attribute_id as before (unchanged), so you would want to explicitly give both the field name and value. That field is probably referenced from another table, and it wouldn't do you any good to have a changed value. Only if you're inserting a record for the first time, and will be propogating that ID value to other tables, would you want to have a new value generated (by using null, or omitting the field entirely).

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...