Adding to table, then SQL Errors

I am wanting to add 2 lines to an Install table, yet I get a SQL Error,

all help appreciated :slight_smile:

The 2 lines are:

ALTER TABLE gen_setts
ADD enable_proxy_deny TINYINT( 4 ) NOT NULL ,
ADD enable_country_deny TINYINT( 4 ) NOT NULL ;
…

$db_desc = β€œCreating <b>gen_setts</b> table”;
$db_query = β€œCREATE TABLE " . DB_PREFIX . "gen_setts (
id int(11) NOT NULL auto_increment,
sitename varchar(255) NOT NULL default β€˜β€™,
site_path varchar(255) NOT NULL default β€˜β€™,
admin_email varchar(255) NOT NULL default β€˜β€™,
pg_paypal_email varchar(255) NOT NULL default β€˜β€™,
pg_worldpay_id varchar(50) NOT NULL default β€˜β€™,
pg_checkout_id varchar(50) NOT NULL default β€˜β€™,
pg_protx_username varchar(100) NOT NULL default β€˜β€™,
pg_protx_password varchar(100) NOT NULL default β€˜β€™,
pg_authnet_username varchar(100) NOT NULL default β€˜β€™,
pg_authnet_password varchar(100) NOT NULL default β€˜β€™,
language tinyint(4) NOT NULL default β€˜0’,
currency varchar(10) NOT NULL default β€˜β€™,
amount_format tinyint(4) NOT NULL default β€˜0’,
amount_digits tinyint(4) NOT NULL default β€˜0’,
currency_position tinyint(4) NOT NULL default β€˜0’,
max_images tinyint(4) NOT NULL default β€˜0’,
images_max_size int(11) NOT NULL default β€˜0’,
enable_hpfeat tinyint(4) NOT NULL default β€˜0’,
enable_catfeat tinyint(4) NOT NULL default β€˜0’,
enable_bold tinyint(4) NOT NULL default β€˜0’,
enable_hl tinyint(4) NOT NULL default β€˜0’,
enable_swaps tinyint(4) NOT NULL default β€˜0’,
cron_job_type tinyint(4) NOT NULL default β€˜1’,
enable_header_counter tinyint(4) NOT NULL default β€˜0’,
is_ssl tinyint(4) NOT NULL default β€˜0’,
site_path_ssl varchar(255) NOT NULL default β€˜β€™,
account_mode tinyint(4) NOT NULL default β€˜0’,
max_credit double(16,2) NOT NULL default β€˜0.00’,
init_credit double(16,2) NOT NULL default β€˜0.00’,
closed_auction_deletion_days smallint(6) NOT NULL default β€˜0’,
enable_shipping_costs tinyint(4) NOT NULL default β€˜0’,
default_theme varchar(30) NOT NULL default β€˜β€™,
metatags text NOT NULL,
site_lang varchar(30) NOT NULL default β€˜β€™,
admin_lang varchar(30) NOT NULL default β€˜β€™,
always_show_buyout tinyint(4) NOT NULL default β€˜0’,
enable_addl_category tinyint(4) NOT NULL default β€˜0’,
mailer varchar(20) NOT NULL default β€˜β€™,
sendmail_path varchar(200) NOT NULL default β€˜β€™,
user_lang tinyint(4) NOT NULL default β€˜0’,
enable_sniping_feature tinyint(4) NOT NULL default β€˜0’,
sniping_duration int(11) NOT NULL default β€˜20’,
enable_private_site tinyint(4) NOT NULL default β€˜0’,
enable_pref_sellers tinyint(4) NOT NULL default β€˜0’,
pref_sellers_reduction double(16,2) NOT NULL default β€˜0.00’,
enable_bcc tinyint(4) NOT NULL default β€˜0’,
enable_asq tinyint(4) NOT NULL default β€˜0’,
enable_reg_approval tinyint(4) NOT NULL default β€˜0’,
enable_wanted_ads tinyint(4) NOT NULL default β€˜0’,
enable_hpfeat_desc tinyint(4) NOT NULL default β€˜0’,
auto_vat_exempt tinyint(4) NOT NULL default β€˜0’,
invoice_header text,
invoice_footer text,
vat_number varchar(100) NOT NULL default β€˜β€™,
invoice_comments mediumtext NOT NULL,
enable_bid_retraction tinyint(4) NOT NULL default β€˜0’,
pg_mb_email varchar(255) NOT NULL default β€˜β€™,
min_reg_age smallint(6) NOT NULL default β€˜0’,
birthdate_type tinyint(4) NOT NULL default β€˜0’,
nb_other_items_adp tinyint(4) NOT NULL default β€˜0’,
maintenance_mode tinyint(4) NOT NULL default β€˜0’,
enable_stores tinyint(4) NOT NULL default β€˜0’,
account_mode_personal tinyint(4) NOT NULL default β€˜0’,
enable_bulk_lister tinyint(4) NOT NULL default β€˜1’,
suspend_over_bal_users tinyint(4) NOT NULL default β€˜1’,
activation_key varchar(255) NOT NULL default β€˜β€™,
min_invoice_value double(16,2) NOT NULL default β€˜0.00’,
init_acc_type tinyint(4) NOT NULL default β€˜0’,
enable_tax tinyint(4) default β€˜0’,
enable_cat_counters tinyint(4) NOT NULL default β€˜0’,
enable_display_phone tinyint(4) NOT NULL default β€˜0’,
media_max_size int(11) NOT NULL default β€˜0’,
enable_auctions_approval tinyint(4) NOT NULL default β€˜0’,
approval_categories text NOT NULL,
is_mod_rewrite tinyint(4) NOT NULL default β€˜0’,
buyout_process tinyint(4) NOT NULL default β€˜0’,
sell_nav_position tinyint(4) NOT NULL default β€˜1’,
nb_autorelist_max int(11) NOT NULL default β€˜0’,
site_logo_path varchar(255) NOT NULL default β€˜images/probidlogo.gif’,
time_offset tinyint(4) NOT NULL default β€˜0’,
max_media tinyint(4) NOT NULL default β€˜1’,
enable_other_items_adp tinyint(4) NOT NULL default β€˜0’,
debug_load_time tinyint(4) NOT NULL default β€˜1’,
debug_load_memory tinyint(4) NOT NULL default β€˜0’,
pg_nochex_email varchar(255) NOT NULL default β€˜β€™,
signup_settings tinyint(4) NOT NULL default β€˜0’,
mcrypt_enabled tinyint(4) NOT NULL default β€˜0’,
mcrypt_key varchar(255) NOT NULL default β€˜β€™,
makeoffer_process tinyint(4) NOT NULL,
enable_duration_change tinyint(4) NOT NULL,
duration_change_days int(11) NOT NULL,
enable_seller_verification tinyint(4) NOT NULL,
makeoffer_private TINYINT NOT NULL ,
seller_verification_mandatory TINYINT NOT NULL ,
enable_proxy_deny TINYINT( 4 ) NOT NULL ,
enable_country_deny TINYINT( 4 ) NOT NULL ,

PRIMARY KEY (id)
) ENGINE=MyISAM COMMENT=β€˜Table with General Settings’ AUTO_INCREMENT=2 ;”;
…

A Mysql error has occurred while running the script:

The query you are trying to run is invalid
Mysql Error Output: 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 '; PRIMARY KEY (`id`) ) ENGINE=MyISAM COMMENT='Table with General Settings' A' at line 98
SQL Query: CREATE TABLE `gen_setts` ( `id` int(11) NOT NULL auto_increment, `sitename` varchar(255) NOT NULL default '', `site_path` varchar(255) NOT NULL default '', `admin_email` varchar(255) NOT NULL default '', `pg_paypal_email` varchar(255) NOT NULL default '', `pg_worldpay_id` varchar(50) NOT NULL default '', `pg_checkout_id` varchar(50) NOT NULL default '', `pg_protx_username` varchar(100) NOT NULL default '', `pg_protx_password` varchar(100) NOT NULL default '', `pg_authnet_username` varchar(100) NOT NULL default '', `pg_authnet_password` varchar(100) NOT NULL default '', `language` tinyint(4) NOT NULL default '0', `currency` varchar(10) NOT NULL default '', `amount_format` tinyint(4) NOT NULL default '0', `amount_digits` tinyint(4) NOT NULL default '0', `currency_position` tinyint(4) NOT NULL default '0', `max_images` tinyint(4) NOT NULL default '0', `images_max_size` int(11) NOT NULL default '0', `enable_hpfeat` tinyint(4) NOT NULL default '0', `enable_catfeat` tinyint(4) NOT NULL default '0', `enable_bold` tinyint(4) NOT NULL default '0', `enable_hl` tinyint(4) NOT NULL default '0', `enable_swaps` tinyint(4) NOT NULL default '0', `cron_job_type` tinyint(4) NOT NULL default '1', `enable_header_counter` tinyint(4) NOT NULL default '0', `is_ssl` tinyint(4) NOT NULL default '0', `site_path_ssl` varchar(255) NOT NULL default '', `account_mode` tinyint(4) NOT NULL default '0', `max_credit` double(16,2) NOT NULL default '0.00', `init_credit` double(16,2) NOT NULL default '0.00', `closed_auction_deletion_days` smallint(6) NOT NULL default '0', `enable_shipping_costs` tinyint(4) NOT NULL default '0', `default_theme` varchar(30) NOT NULL default '', `metatags` text NOT NULL, `site_lang` varchar(30) NOT NULL default '', `admin_lang` varchar(30) NOT NULL default '', `always_show_buyout` tinyint(4) NOT NULL default '0', `enable_addl_category` tinyint(4) NOT NULL default '0', `mailer` varchar(20) NOT NULL default '', `sendmail_path` varchar(200) NOT NULL default '', `user_lang` tinyint(4) NOT NULL default '0', `enable_sniping_feature` tinyint(4) NOT NULL default '0', `sniping_duration` int(11) NOT NULL default '20', `enable_private_site` tinyint(4) NOT NULL default '0', `enable_pref_sellers` tinyint(4) NOT NULL default '0', `pref_sellers_reduction` double(16,2) NOT NULL default '0.00', `enable_bcc` tinyint(4) NOT NULL default '0', `enable_asq` tinyint(4) NOT NULL default '0', `enable_reg_approval` tinyint(4) NOT NULL default '0', `enable_wanted_ads` tinyint(4) NOT NULL default '0', `enable_hpfeat_desc` tinyint(4) NOT NULL default '0', `auto_vat_exempt` tinyint(4) NOT NULL default '0', `invoice_header` text, `invoice_footer` text, `vat_number` varchar(100) NOT NULL default '', `invoice_comments` mediumtext NOT NULL, `enable_bid_retraction` tinyint(4) NOT NULL default '0', `pg_mb_email` varchar(255) NOT NULL default '', `min_reg_age` smallint(6) NOT NULL default '0', `birthdate_type` tinyint(4) NOT NULL default '0', `nb_other_items_adp` tinyint(4) NOT NULL default '0', `maintenance_mode` tinyint(4) NOT NULL default '0', `enable_stores` tinyint(4) NOT NULL default '0', `account_mode_personal` tinyint(4) NOT NULL default '0', `enable_bulk_lister` tinyint(4) NOT NULL default '1', `suspend_over_bal_users` tinyint(4) NOT NULL default '1', `activation_key` varchar(255) NOT NULL default '', `min_invoice_value` double(16,2) NOT NULL default '0.00', `init_acc_type` tinyint(4) NOT NULL default '0', `enable_tax` tinyint(4) default '0', `enable_cat_counters` tinyint(4) NOT NULL default '0', `enable_display_phone` tinyint(4) NOT NULL default '0', `media_max_size` int(11) NOT NULL default '0', `enable_auctions_approval` tinyint(4) NOT NULL default '0', `approval_categories` text NOT NULL, `is_mod_rewrite` tinyint(4) NOT NULL default '0', `buyout_process` tinyint(4) NOT NULL default '0', `sell_nav_position` tinyint(4) NOT NULL default '1', `nb_autorelist_max` int(11) NOT NULL default '0', `site_logo_path` varchar(255) NOT NULL default 'images/probidlogo.gif', `time_offset` tinyint(4) NOT NULL default '0', `max_media` tinyint(4) NOT NULL default '1', `enable_other_items_adp` tinyint(4) NOT NULL default '0', `debug_load_time` tinyint(4) NOT NULL default '1', `debug_load_memory` tinyint(4) NOT NULL default '0', `pg_nochex_email` varchar(255) NOT NULL default '', `signup_settings` tinyint(4) NOT NULL default '0', `mcrypt_enabled` tinyint(4) NOT NULL default '0', `mcrypt_key` varchar(255) NOT NULL default '', `makeoffer_process` tinyint(4) NOT NULL, `enable_duration_change` tinyint(4) NOT NULL, `duration_change_days` int(11) NOT NULL, `enable_seller_verification` tinyint(4) NOT NULL, `makeoffer_private` TINYINT NOT NULL , `seller_verification_mandatory` TINYINT NOT NULL , `enable_proxy_deny` TINYINT( 4 ) NOT NULL , `enable_country_deny` TINYINT( 4 ) NOT NULL ; PRIMARY KEY (`id`) ) ENGINE=MyISAM COMMENT='Table with General Settings' AUTO_INCREMENT=2 ;

you’ve got a semi-colon where you should have a comma

the error message specifically points you to where the error is –

You have an error in your SQL syntax… near '; PRIMARY KEY (id) )

[SIZE=2][FONT=verdana]Hi Ray,

It is a matter of style, but I also think it leads to less missing or wrong commas:

Do it this way with the commas precede the next line like:


[COLOR=#464646]CREATE TABLE `" . DB_PREFIX . "gen_setts` ([/COLOR]
[COLOR=#464646]  , [/COLOR][COLOR=#464646]`id` int(11) NOT NULL auto_increment[/COLOR]
[COLOR=#464646]  , [/COLOR][COLOR=#464646]`sitename` varchar(255) NOT NULL default ''[/COLOR]
[COLOR=#464646]  ; `site_path` varchar(255) NOT NULL default ''[/COLOR]
[COLOR=#464646]  ,[/COLOR][COLOR=#464646] `admin_email` varchar(255) NOT NULL default ''[/COLOR]
[COLOR=#464646]  , [/COLOR][COLOR=#464646]`pg_paypal_email` varchar(255) NOT NULL default ''[/COLOR]
[COLOR=#464646]...
[/COLOR]

[COLOR=#464646]

Can you see were the problem I entered in the above code is… pretty clear right?

This style works in any language that requires the use of lists delimited by comma.

Steve[/COLOR][/FONT][/SIZE]

i could kiss you :smiley:

i had a big fight with the sitepoint editor who reviewed my book, Simply SQL

a big fight, and my β€œleading comma” convention survived, while other facets of my coding style did not

(he claimed there was a β€œsitepoint style” that he wanted to adhere to)

anyhow, β€œleading comma” is something i’ve been using for about 30 years or more

any intelligent person who runs across it will immediately adopt it, right?

:slight_smile:

Great to see r937 kissing people now :slight_smile:

Hi Steve :slight_smile:

ok yes interesting to see this style, commas preceding the line,

I am in the stage of doing it/copying the style I see, with out understanding exactly what it is doing/indicating,

keen to learn :slight_smile: