Database building

[Guest Colin Hanke]

I use phpmyadmin and I currently have a database on my computer for testing and building.  I used the export function and it generated the sql for me to upload my database, table, and its first couple entries.  I copied the sql statement but it gave me an error msg when I tried to query the sql on my hosting for the web.  Here is the query, and the error msg, any help will be greatly appreciated.  Thanks.


MySQL said: Documentation
#1064 - You have an error in your SQL syntax near 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `Zip` char(10) collate latin1_g' at line 20



- phpMyAdmin SQL Dump
-- version 2.8.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 30, 2006 at 07:19 PM
-- Server version: 5.0.21
-- PHP Version: 5.1.4
--
-- Database: `survey`
--
-- --------------------------------------------------------
--
-- Table structure for table `mktsurvey`
--
CREATE TABLE `mktsurvey` (
`UI` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`Zip` char( 10 ) COLLATE latin1_general_ci default NULL ,
`age` enum( '18-24', '25-30', '31-35', '36-40', '41-45', '46-50', '51-55', '56-60', '61-65', '+66' ) COLLATE latin1_general_ci default NULL ,
`Children` enum( 'Yes', 'No' ) COLLATE latin1_general_ci default NULL ,
`Online_hrs` enum( '0-1', '2-5', '6-10', '11-15', '+15' ) COLLATE latin1_general_ci default NULL ,
`site_cool` enum( 'Yes', 'No' ) COLLATE latin1_general_ci default NULL ,
`site_features` varchar( 255 ) COLLATE latin1_general_ci default NULL ,
`buy_child_online` enum( 'Yes', 'No' ) COLLATE latin1_general_ci default NULL ,
`times_buy_chile_online` enum( '0-1', '2-5', '6-10', '11-15', '+15' ) COLLATE latin1_general_ci default NULL ,
`myspace` enum( 'Yes', 'No' ) COLLATE latin1_general_ci default NULL ,
`online_local_resources` enum( 'Never', 'A Little', 'Often', 'All the Time' ) COLLATE latin1_general_ci default NULL ,
`partic_online_network` enum( 'Yes', 'No' ) COLLATE latin1_general_ci default NULL ,
`partic_online_network_why` varchar( 255 ) COLLATE latin1_general_ci default NULL ,
`prize_want` varchar( 255 ) COLLATE latin1_general_ci default NULL ,
`stay_update` enum( 'Yes', 'No' ) COLLATE latin1_general_ci default NULL ,
`lm_partic` enum( 'Yes', 'No' ) COLLATE latin1_general_ci default NULL ,
`lmp_artic_why` varchar( 255 ) COLLATE latin1_general_ci default NULL ,
`email` varchar( 255 ) COLLATE latin1_general_ci default NULL ,
`comments` varchar( 255 ) COLLATE latin1_general_ci default NULL ,
`dtstamp` datetime default NULL ,
`gender` char( 6 ) COLLATE latin1_general_ci default NULL ,
PRIMARY KEY ( `UI` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci

[Barand]

My manual is defunct and needs reinstalling, but I don't think you can use functions as a default value.

first timestamp col in a row will automatically update to current time.

[fenway]

Actually, you can in MySQL 5, but not in earlier versions... and only for timestamp columns.  What a stupid feature -- if only they made it actually useful, and have a different one for modified vs. created, and it worked on datetime column as well. 

[Shaudh]

What if you use CURRENT_TIMESTAMP etc in the create table statement as default values. And then simply insert DEFAULT values?

[fenway]

Again, that only works in the most recent version of MySQL... and I hate relying on that.