Sanjoy Roy

[MCM, MCP, SCJP] – Senior PHP Programmer

Category Archives: MySQL

Update and create timestamps with MySQL


A lot of relational tables need created and update timestamps columns. I prefer having them for all tables with no exception. However, most of applications I am working on are running MySQL. MySQL has minor limitation on timestamps. Unfortunately you can create only one time stamp column that has DEFAULT NOW() value. Read more to see how to avoid this limitation. The simplest way to do this is create the following columns in the table:

stamp_created timestamp default now(), stamp_updated timestamp default now() on update now()

But MySQL will return the following error:

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_ in DEFAULT or ON UPDATE clause
Example:
CREATE TABLE `test`.`test` (
`id` TINYINT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 200 ) NOT NULL ,
`created` TIMESTAMP NULL ,
`modified` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB ;

INSERT INTO `test`.`test` (
`id` ,
`name` ,
`created` ,
`modified`
)
VALUES (
NULL , ‘sanjoy roy’, NOW( ) ,
CURRENT_TIMESTAMP
);

id name created modified
1 sanjoy roy 2010-02-09 10:22:18 2010-02-09 10:22:18

After I modified my name

id name created modified
1 sanjoy kumar roy 2010-02-09 10:24:03 2010-02-09 10:25:36

WampServer


WampServer is a Windows web development environment. It allows you to create web applications with Apache, PHP and the MySQL database. It also comes with PHPMyAdmin to easily manage your databases.

https://i0.wp.com/www.wampserver.com/en/data/image_menu_wamp.gif
WampServer installs automatically (installer), and its usage is very intuitive. You will be able to tune your server without even touching the setting files.
WampServer is the only packaged solution that will allow you to reproduce your production server. Once WampServer is installed, you have the possibility to add as many Apache, MySQL and PHP releases as you want.
WampServer also has a trayicon to manage your server and its settings.

WampServer’s functionalities are very complete and easy to use so we won’t explain here how to use them.
With a left click on WampServer’s icon, you will be able to:

  • manage your Apache and MySQL services
  • switch online/offline (give access to everyone or only localhost)
  • install and switch Apache, MySQL and PHP releases
  • manage your servers settings
  • access your logs
  • access your settings files
  • create alias

With a right click:

  • change WampServer’s menu language
  • access this page

Download Link

Create and Update Timestamp with MySQL


A lot of relational tables need created and update timestamps columns. I prefer having them for all tables with no exception. However, most of applications I am working on are running MySQL. MySQL has minor limitation on timestamps. Unfortunately you can create only one time stamp column that has DEFAULT NOW() value. Read more to see how to avoid this limitation. The simplest way to do this is create the following columns in the table:

stamp_created timestamp default now(), stamp_updated timestamp default now() on update now()

But MySQL will return the following error:

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_ in DEFAULT or ON UPDATE clause
Example:
CREATE TABLE `test`.`test` (
`id` TINYINT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 200 ) NOT NULL ,
`created` TIMESTAMP NULL ,
`modified` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB ;

INSERT INTO `test`.`test` (
`id` ,
`name` ,
`created` ,
`modified`
)
VALUES (
NULL , ’sanjoy roy’, NOW( ) ,
CURRENT_TIMESTAMP
);

id name created modified
1 sanjoy roy 2010-02-09 10:22:18 2010-02-09 10:22:18

After I modified my name

id name created modified
1 sanjoy kumar roy 2010-02-09 10:24:03 2010-02-09 10:25:36

%d bloggers like this: