DATETIME woes :(

hargi · 2 Jun 2013 at 16:29

OK I have a mySQL table that has a few date columns in, it is set up so that there is a create date and a update date. The UpdateTimeStamp column updates automatically using the on update CURRENT_TIMESTAMP Attributes, Its my understanding that you can only use a one timestamp in this special way, and this is fine.

So on the createdate column I need to manually add the date that the record was created its a datetime column.

I have read that you can use the NOW() command for this. However using it in the following brings me a undefined function error. I have tried it as just 'CreateDate' => NOW(), which gives the same error or using 'CreateDate' => set_value('NOW()'), does not produce an error but no value is entered into the database for this column.

PHP:

form_data = array(
    'ClientID' => set_value('ClientID'),
    'ClientReference' => set_value('ClientReference'),
    'CreateDate' => set_value(NOW()),
    'Gender' => set_value('Gender'),
    'MaritalStatus' => set_value('MaritalStatus'),
    'ContactNumber' => set_value('ContactNumber'),
    'Nationality' => set_value('Nationality'),
    'Ethnicity' => set_value('Ethnicity'),
    'ClientNotes' => set_value('ClientNotes')
);

I have also tried 'CreateDate' => set_value(date('Y-m-d H:i:s')) which also does not produce an error but no value is entered into the database for this column

Poolybit · 2 Jun 2013 at 16:35

I haven't done PHP & mySQL for a very long time so apologies if this is wrong but from memory, the PHP current time method is time() whereas the now() method is for within mySQL. You can either replace the mysql now() with a php method of time() or make the now() a field value within the insert statement so that it gets interpreted database-side on the insert.

hargi · 2 Jun 2013 at 16:37

Cheers Poolybit,

After going round all the things i could think of i tried
'CreateDate' => (date('Y-m-d H:i:s')),

NO SET_VALUE and it worked. I dont know why that should make a difference

Poolybit · 2 Jun 2013 at 16:42

In that case I suspect:

PHP:

'CreateDate' => time()

Would probably work as well!

visibleman · 2 Jun 2013 at 19:50

Poolybit's solution should work.
Although i'm assuming you're using CodeIgnite? In which case i suspect there is a proper method/solution for adding data to a database query that's outside of form data (set_value() is designed to grab input/textarea form data).

hargi · 3 Jun 2013 at 10:02

Yep codeIgniter,

The formdata array collects all the form data

and then is passed to an insert query, Poolybit's solution does work.
But would the ideal solution be to split the inset date from that array and into another function for example?