adding to sql

[aspenation]

newbie question

 

Their are a few contributions that require adding SQL tables via phpMYADMIN in cpanel and I understand the basic process but I'm not sure how to recognize the text that needs to be added or if I just adjust the tables themselves.

 

The example below is from the registry contribution & I can't add it 'as is' or I get an error. So which information needs to be added? The file says it's updated for RC2a so I'm thinking everything should work with my build but I'm stuck. Their are other contributions that require adding tables like newsletter, purchase without account, etc

 

 

#This one is in the updated store already

#ALTER TABLE `orders` ADD customers_company varchar(32) AFTER `customers_name`, ADD delivery_company varchar(32) AFTER `delivery_name`, ADD `billing_name` varchar(64) NOT NULL AFTER `delivery_address_format_id`, ADD `billing_company` varchar(32) AFTER `billing_name`, ADD `billing_street_address` varchar(64) NOT NULL AFTER `billing_company`, ADD `billing_suburb` varchar(32) AFTER `billing_street_address`, ADD `billing_city` varchar(32) NOT NULL AFTER `billing_suburb`, ADD `billing_postcode` varchar(10) NOT NULL AFTER `billing_city`, ADD `billing_state` varchar(32) AFTER `billing_postcode`, ADD `billing_country` varchar(32) NOT NULL AFTER `billing_state`, ADD `billing_address_format_id` int(5) NOT NULL AFTER `billing_country`;

 

ALTER TABLE `orders` ADD `ip_address` VARCHAR(50) NOT NULL;

 

 

#ALTER TABLE `orders` ADD customers_company varchar(32) AFTER `customers_name`; #included in above

#ALTER TABLE `orders` ADD delivery_company varchar(32) AFTER `delivery_name`; #included in above

 

ALTER TABLE `orders_products` ADD registry_products_id int(11) AFTER `products_quantity`;

ALTER TABLE `orders_products` ADD registry_products_customers_id int(13) AFTER `registry_products_id`;

 

#till here uploaded

 

#

# Tabellenstruktur für Tabelle `registry_products`

#

 

CREATE TABLE registry_products (

registry_products_id int(11) NOT NULL auto_increment,

customers_id int(13) NOT NULL default '0',

products_id tinytext NOT NULL,

registry_products_quantity int(2) NOT NULL default '0',

registry_products_quantity_received int(2) NOT NULL default '0',

final_price decimal(15,4) NOT NULL default '0.0000',

registry_products_date_added varchar(8) default NULL,

registry_id int(11) NOT NULL,

PRIMARY KEY (registry_products_id)

);

 

#ALTER TABLE `registry_products` ADD registry_products_quantity_received int(2) NOT NULL default '0' AFTER `registry_products_quantity`; #included in above

 

 

CREATE TABLE registry_products_attributes (

registry_products_attributes_id int(11) NOT NULL auto_increment,

customers_id int(11) NOT NULL default '0',

products_id tinytext NOT NULL,

products_options_id int(11) NOT NULL default '0',

products_options_value_id int(11) NOT NULL default '0',

registry_id int(11) NOT NULL,

PRIMARY KEY (registry_products_attributes_id)

);

 

 

 

 

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Display Registry After Adding Product', 'DISPLAY_REGISTRY', 'true', 'Display the registry after adding a product (or return back to their origin)', '1', '14', 'tep_cfg_select_option(array(\'true\', \'false\'), ', now());

 

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Remove product from registry, once added to the cart', 'REMOVE_REGISTRY_PRODUCT', 'true', 'If set to true, the product is removed from the registry when you add it to the cart. It is ignored, if you turn the registry functionality on.', '1', '14', 'tep_cfg_select_option(array(\'true\', \'false\'), ', now());

 

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Activate registry functionality (if deactivated, we have a wish list functionality).', 'ACTIVATE_REGISTRY', 'true', 'Activate registry functionality (if deactivated, we have a wish list functionality).', '1', '14', 'tep_cfg_select_option(array(\'true\', \'false\'), ', now());

 

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Allow higher quantity or additional items to be purchased than in registry', 'CHECK_REGISTRY_ITEMS', 'false', 'Allow higher quantity or additional items to be purchased than in registry.', '1', '14', 'tep_cfg_select_option(array(\'true\', \'false\'), ', now());

 

INSERT INTO configuration VALUES ('', 'Registry Entries', 'MAX_REGISTRY_ENTRIES', '5', 'Maximum registry entries a customer is allowed to have', 3, 1, NULL, now(), NULL, NULL);

 

 

CREATE TABLE registry (

registry_id int(11) NOT NULL auto_increment,

customers_id int(13) NOT NULL default '0',

registry_occasion_id int(13) NOT NULL default '0',

registry_occasion_date datetime NOT NULL default '0000-00-00 00:00:00',

co_registrant_first_name varchar(32) NOT NULL default '',

co_registrant_last_name varchar(32) NOT NULL default '',

PRIMARY KEY (registry_id)

);

#Insert this record to have a nice start number for the registry

INSERT INTO registry VALUES (100000, 0, 0, '0000-00-00 00:00:00', 'Stub', 'Stub');

 

 

CREATE TABLE registry_occasion (

registry_occasion_id int(11) NOT NULL auto_increment,

language_id int(11) NOT NULL default '1',

registry_occasion_name varchar(64) NOT NULL,

PRIMARY KEY (registry_occasion_id,language_id)

);

 

INSERT INTO registry_occasion VALUES (1, 1, 'Wedding');

INSERT INTO registry_occasion VALUES (1, 2, 'Hochzeit');

INSERT INTO registry_occasion VALUES (1, 3, 'Wedding');

INSERT INTO registry_occasion VALUES (2, 1, 'Baby');

INSERT INTO registry_occasion VALUES (2, 2, 'Kind');

INSERT INTO registry_occasion VALUES (2, 3, 'Baby');

INSERT INTO registry_occasion VALUES (3, 1, 'Anniversary');

INSERT INTO registry_occasion VALUES (3, 2, 'Jubiläum');

INSERT INTO registry_occasion VALUES (3, 3, 'Anniversary');

INSERT INTO registry_occasion VALUES (4, 1, 'Special Occasions');

INSERT INTO registry_occasion VALUES (4, 2, 'Besonderer Anlass');

INSERT INTO registry_occasion VALUES (4, 3, 'Special Occasions');

[npn2531]

The info you have above looks just a bit garbled and would give you probelms. Specifically the '#' symbol. Everything following a '#' on a line is supposed to be a comment, and should be ignored by the sql insert in phpMyAdmin.

For example in your code above you have:

 

#This one is in the updated store already

That line should be ignored, and to be sure it is, don't paste that line into the sql in phpMyAdmin. It does nothing. You can delete it.

 

However, just below that line you have this:

#ALTER TABLE `orders` ADD customers_company varchar(32) AFTER `customers_name`, ADD delivery_company varchar(32) AFTER `delivery_name`, ADD `billing_name` varchar(64) NOT 

 

That line will also be ignored by the sql program because it begins with the '#'. But without the

ALTER TABLE

in the line above, the command makes no sense and will return an error.

To make a long story short, everywhere you have #ALTER you will most likely get an error.

 

Try changing all instances of '#ALTER' to 'ALTER' and it should work. To keep it clean, also don't paste in the sql the other lines, the comments, which are preceeded by a '#'.