Monday, March 18, 2013

MySQL, add a new column with modify time



I have a table cesei_member_extra:
CREATE TABLE cnsh_lms.`cesei_member_extra` (
  `member_id` mediumint(8) unsigned NOT NULL,
`dept_type` tinyint(4) NOT NULL DEFAULT '0',
`edu_type` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`member_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  Now I want to add a modification time, i.e. new column modify_date
 ALTER   TABLE  cnsh_lms.`cesei_member_extra` ADD COLUMN  modify_date datetime default '0000-00-00 00:00:00';
which equal to:
CREATE TABLE cnsh_lms.`cesei_member_extra` (
  `member_id` mediumint(8) unsigned NOT NULL,
`dept_type` tinyint(4) NOT NULL DEFAULT '0',
`edu_type` tinyint(4) NOT NULL DEFAULT '0',
`modify_date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (`member_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 To get current time stamp with format '0000-00-00 00:00:00' in PHP:
 $modify_date = date('Y-m-d H:i:s');
To insert the new vaules in table cesei_member_extra :
    $sql = "REPLACE INTO cesei_member_extra VALUES ($member_id,$dept,$edu,  '$modify_date')";                                       
    mysql_query($sql, $db);


Note:  using '$modify_date' instead of  $modify_date as it is a string or an error will be produced.

No comments:

Post a Comment