Problem with SQL syntax (probably simple)

Question

I'm doing some custom database work for a module for Drupal and I get the following SQL error when trying to create my table:

user warning: 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 'DEFAULT NULL, rooms INT DEFAULT NULL, adults INT DEFAULT NULL, children' at line 14 query: CREATE TABLE dr_enquiry ( eId INT unsigned NOT NULL auto_increment, eKey VARCHAR(16) NOT NULL, dateSent INT NOT NULL DEFAULT 0, status VARCHAR(30) NOT NULL DEFAULT 'Unanswered', custName VARCHAR(50) NOT NULL, custEmail VARCHAR(200) NOT NULL, custPhone VARCHAR(25) NOT NULL, custCountry VARCHAR(40) NOT NULL, custIP VARCHAR(11) DEFAULT NULL, offerName VARCHAR(100) NOT NULL, offerURL VARCHAR(200) NOT NULL, arrival DATETIME DEFAULT NULL, departure DEFAULT NULL, rooms INT DEFAULT NULL, adults INT DEFAULT NULL, children INT DEFAULT NULL, childAges VARCHAR(32) DEFAULT NULL, toddlers INT DEFAULT NULL, toddlerAges VARCHAR(32) DEFAULT NULL, catering VARCHAR(255) DEFAULT NULL, comments VARCHAR(255) DEFAULT NULL, agent VARCHAR(100) DEFAULT NULL, voucher VARCHAR(100) DEFAULT NULL, PRIMARY KEY (eId) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in /home/travelco/public_html/includes/database.inc on line 550.

Answer 1

The 'departure' field doesn't seem to have a type eg varchar, mediumint etc. Try adding one and see if that solves your problem :)

Answer 2

Nullability is not set using a DEFAULT constraint. Thus, the compiler is balking at each instance of DEFAULT NULL. Thus, the create statement should look like:

CREATE TABLE dr_enquiry ( 
      eId INT unsigned NOT NULL auto_increment
      , eKey VARCHAR(16) NOT NULL
      , dateSent  INT NOT NULL DEFAULT 0
      , status VARCHAR(30) NOT NULL DEFAULT 'Unanswered'
      , custName VARCHAR(50) NOT NULL
      , custEmail  VARCHAR(200) NOT NULL
      , custPhone VARCHAR(25) NOT NULL
      , custCountry  VARCHAR(40) NOT NULL
      , custIP VARCHAR(11) NULL
      , offerName  VARCHAR(100) NOT NULL
      , offerURL VARCHAR(200) NOT NULL
      , arrival  DATETIME NULL
      , departure DATETIME NULL
      , rooms  INT NULL
      , adults INT NULL
      , children  INT NULL
      , childAges VARCHAR(32) NULL
      , toddlers  INT NULL
      , toddlerAges VARCHAR(32) NULL
      , catering  VARCHAR(255) NULL
      , comments VARCHAR(255) NULL
      , agent VARCHAR(100) NULL
      , voucher  VARCHAR(100) NULL
      , PRIMARY KEY (eId) 
      ) 

(Oh and departure did not have a datatype as richsage mentioned. I presumed it was DateTime.)