Updating Dates in a MySQL database

[eluser]Pr0v4[/eluser]
Hi everybody!

I'm working on my first application, and now I've found a little problem that i would like to solve...

Basically it's a register form where i try to retrieve nickname, password and create an id and a date of registration in an automatically way.

For the first 3 (nickname, password, id) I don't have any problems, but i cannot understand how to insert the data of registration.

I'd a look on the user guide and i've discovered that there is a Date Helper that looks like fitted for that! So i've autoloaded by the autoload.php file and then tried to insert the right date in this two ways:

Code:

'date' => now(),

'date' =>now(time()),

The date value is part of an array that will be passed trough the function in charge to insert the new row in the database.

The only problem is that all the dates are set to 0000-00-00 and not at the time of registration...

Where am i wrong???

[eluser]w0bbes[/eluser]

Code:

'data' = time()

[eluser]usmc[/eluser]
Create a new field in your database table with type set as "timestamp" then change the default to NOW()

or with a trigger

CREATE TRIGGER foo
BEFORE UPDATE ON yourtable
SET NEW.time = NOW();
end;

[eluser]cahva[/eluser]
As I see the field is already datetime or timestamp(not unix). Usmc's advice will work. For the original problem you tried to add unix timestamp, which ofcourse will not work with datetime or timestamp field.

You could also use the PHP's own date function. This would do the job:

Code:

'date' => date('Y-m-d'),

[eluser]Pr0v4[/eluser]
With the solution propose from cahva i'm now able to set the registration date in my database.

But to be sure to move the correct steps from the first ones, there is someone that could help me to understand, even linking articles on internet, which will be the best solution to manage dates with better tools from CodeIgniter?

[eluser]n0xie[/eluser]
Take a look at my reply here

Also for future reference, if you want the date/time added whenever a new insert is made you are much better off relying on MySQL build in CURRENT_TIMESTAMP functionality. For example:

Code:

CREATE TABLE `mytable` (
  `id` int(3) unsigned NOT NULL auto_increment,
  `nickname` varchar(100) default NULL,
  `password` char(32) default NULL,
  `created_on` timestamp NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
)