SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Feb 2012
    Location
    Kiwi in Bangkok
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

    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 ;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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`) )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    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:
    Code:
    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 ''
    ...
    


    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
    ictus==""

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ServerStorm View Post
    This style works in any language that requires the use of lists delimited by comma.
    i could kiss you

    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Feb 2012
    Location
    Kiwi in Bangkok
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great to see r937 kissing people now

    Quote Originally Posted by r937 View Post
    i could kiss you

    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?


  6. #6
    SitePoint Addict
    Join Date
    Feb 2012
    Location
    Kiwi in Bangkok
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Steve

    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


    Quote Originally Posted by ServerStorm View Post
    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:
    Code:
    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 ''
    ...
    


    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •