Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Jun 2009
    Posts
    68
    Thanks
    22
    Thanked 0 Times in 0 Posts

    You have an error in your SQL syntax

    Hello

    Can anyone tell me what I'm doing wrong here? I'm getting the following error:

    #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 'NOT NULL default '', `deposit_date` date NOT NULL default '0000-00-00', `dep' at line 31
    Code:
    CREATE TABLE `bookings` (
    `id` int(6) NOT NULL auto_increment,
    `advert_source` varchar(255) NOT NULL default '',
    		`company` varchar(255) NOT NULL default '',
    		`start_time` datetime NOT NULL default '0000-00-00 00:00:00',
    		`finish_time` datetime NOT NULL default '0000-00-00 00:00:00',
    		`event_occasion` varchar(255) NOT NULL default '',
    		`brides_name` varchar(255) NOT NULL default '',
    		`grooms_name` varchar(255) NOT NULL default '',
    		`bg_surname` varchar(255) NOT NULL default '',
    		`birthday_name` varchar(255) NOT NULL default '',
    		`birthday_age` varchar(255) NOT NULL default '',
    		`venue_name` varchar(255) NOT NULL default '',
    		`venue_address_1` varchar(255) NOT NULL default '',
    		`venue_address_2` varchar(255) NOT NULL default '',
    		`venue_town` varchar(255) NOT NULL default '',
    		`venue_county` varchar(255) NOT NULL default '',
    		`venue_postcode` varchar(255) NOT NULL default '',
    		`client_title` varchar(255) NOT NULL default '',
    		`client_first` varchar(255) NOT NULL default '',
    		`client_last` varchar(255) NOT NULL default '',
    		`client_address_1` varchar(255) NOT NULL default '',
    		`client_address_2` varchar(255) NOT NULL default '',
    		`client_town` varchar(255) NOT NULL default '',
    		`client_county` varchar(255) NOT NULL default '',
    		`client_postcode` varchar(255) NOT NULL default '',
    		`home_telephone` varchar(255) NOT NULL default '',
    		`mobile_telephone` varchar(255) NOT NULL default '',
    		`email_address` varchar(255) NOT NULL default '',
    		`client_notes` text NOT NULL default '',
    		`quotation_amount` enum NOT NULL default '',
    		`deposit_date` date NOT NULL default '0000-00-00',
    		`deposit_amount` enum NOT NULL default '',
    		`deposit_method` varchar(255) NOT NULL default '',
    		`deposit_staff` varchar(255) NOT NULL default '',
    		`balance_date` date NOT NULL default '0000-00-00',
    		`balance_amount` enum NOT NULL default '',
    		`balance_method` varchar(255) NOT NULL default '',
    		`balance_staff` varchar(255) NOT NULL default '',
    		`date_booking_taken` date NOT NULL default '0000-00-00',
    		`dj_requested` varchar(255) NOT NULL default '',
    		`quotation_status` varchar(255) NOT NULL default '',
    		PRIMARY KEY  (`id`),
    UNIQUE KEY `id` (`id`)
    )
    ENGINE=MyISAM AUTO_INCREMENT=1;

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    Well, for starters, none of your lines that use enum are legal.

    You *MUST* give the possible values of the enum right there in the declaration. Further, you can't use a default of ''. Period.

    Code:
    mysql> create table foo ( xyz enum not null default '' );
    ERROR 1064 (42000): You have an error in your SQL syntax...
    
    mysql> create table foo ( xyz enum('a','b','c') not null default '' );
    ERROR 1067 (42000): Invalid default value for 'xyz'
    
    mysql> create table foo ( xyz enum('a','b','c') not null default 'a' );
    Query OK
    See it?

    And I think, then, your date lines will then show up okay:
    Code:
    mysql> create table foo ( xyz date not null default '0000-00-00');
    Query OK
    That is, I think the real error is in your enum lines, not your date lines.

    Oh...and a PRIMARY KEY is *ALREADY* a UNIQUE KEY, so your UNIQUE KEY line there is doing nothing but cause overhead you don't need.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    nickburrett (10-22-2012)

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    I always wonder why people create columns that are NOT NULL and then assign an empty string as the default value. Why bother?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    Because then they don't have to handle the case of a NULL value when they SELECT data. That is, they don't have to check in PHP/ASP/JSP if the DB value is null and convert it to a blank string.

    Of course, you CAN also do that in the SELECT:
    Code:
    SELECT IFNULL(field,'') AS field ...
    BUt if you know the value can't be null you don't need special handling in either the SELECT or the client language.

    Personally, I prefer handling the NULLs. But I do understand why some people don't want to.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Jun 2009
    Posts
    68
    Thanks
    22
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    I always wonder why people create columns that are NOT NULL and then assign an empty string as the default value. Why bother?
    Because people like me haven't got a clue what they are doing!

    The code I pasted was generated from a script I came across here

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    Well, clearly the claim that All major bugs now fixed! is not true.

    I'd go so far as to say that their table generation is a joke and should NOT be depended upon.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New to the CF scene
    Join Date
    Oct 2012
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The double quote should not be part of the SQL query. SQL also does not understand PHP variables.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    Quote Originally Posted by manioxy View Post
    The double quote should not be part of the SQL query. SQL also does not understand PHP variables.
    And what in the world does that comment have to do with ANYTHING in this thread?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

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