Jump to content


Database building


  • Please log in to reply
4 replies to this topic

#1 Guest_Colin Hanke_*

Guest_Colin Hanke_*
  • Guests

Posted 01 July 2006 - 02:37 AM

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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 01 July 2006 - 12:31 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 July 2006 - 05:15 AM

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. 
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 Shaudh

Shaudh
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 22 July 2006 - 10:41 PM

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

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 23 July 2006 - 12:32 PM

Again, that only works in the most recent version of MySQL... and I hate relying on that.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users