Displaying User Registration Date

Hi folks, just a very quick question.

What would be the best practice to a) store the registration date of a member b) display this on the front end? Would I use the NOW() function?

Thanks for your time

I do this with a trigger in mysql.

1. create a date field as e.g. date_insert ( datetime )
2. create a trigger for that

Code:

CREATE TRIGGER `yourTableName_date_insert` BEFORE INSERT ON `yourTableName`
FOR EACH ROW SET NEW.date_insert = NOW()

On every insert the insert_date field will be set now automaticly.

I do this with a trigger in mysql.

1. create a date field as e.g. date_insert ( datetime )
2. create a trigger for that

Code:

CREATE TRIGGER `yourTableName_date_insert` BEFORE INSERT ON `yourTableName`
FOR EACH ROW SET NEW.date_insert = NOW()

On every insert the insert_date field will be set now automaticly.

Perfect, thank you very much!

Why do you create a trigger when you can just use NOW() or SYSDATE() in your insert statement? In my opinion, a trigger is overkill just to set the date. 

Also, I don't like having "magic" stuff happening, not visible in the PHP code. But that's just my opinion.

a trigger ist not a overkill its done for this kind of operations. in mysql you can have only one automatic value for timestamp update like

Code:

update_date TIMESTAMP NULL DEFAULT NOW() ON UPDATE NOW()

With a trigger you can have more. As e.g. if you need a field date_inerst and date_update you can go with a trigger that he does the job for you. of course you can do this also bei your insert or update statement. but why should i do this maualy for this kind of data if it can be done by msql.

But as you sad its a kind of programming philosophy

now() not works for datetime data type its enter 0000to all

try DEFAULT CURRENT_TIMESTAMP instead of NOW(). Also if you use ON UPDATE CURRENT_TIMESTAMP, that will overwrite the value whenever you update, so it wouldn't be accurate to use that if you only want the signed up date.

My primary concerns with a trigger or using "DEFAULT CURRENT_TIMESTAMP" are transparency and portability. In most cases I can update a trigger to work in a different SQL environment, but I have to check the list of triggers in the database to see what is happening when I insert data. If I use "DEFAULT CURRENT_TIMESTAMP", I have to change the table definition and possibly come up with a different method of doing the same thing when I move to a different SQL environment.

Instead, I would just generate the timestamp in my model as part of the insert method. It's fairly straight-forward to add a method to your base model to generate a timestamp (even in a variety of formats, if needed), then call the method from your insert and/or update methods to generate the values for the fields in question.

While it's still likely that this would end up in a format which isn't portable between SQL environments (because the compatibility of dates between vendors is terrible), it limits the number (and location) of code changes which would be required to migrate to a new environment.

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide