Format for date-time field in php sql UPDATE statement?

[Reynolds]

To familiarize myself with inserting and updating various Fabrik plug-in types via a php-generated sql statement, I created a table named 'test_tbl' with fields 'mystring', 'myinteger', 'mydecimal', and 'mydate'.

I have created php code to update each field type, but the date field will not update. I have tried it as a string and as a unix timestamp, but I cannot successfully create the query to update the field. I assume the problem is creating a string for the sql query with the proper format for the mydate field. My latest code iteration is below:

<?php
$db = &JFactory::getDbo();
$query = $db->getQuery(true);
$date= strtotime("1994-12-24");
$update = "UPDATE test_tbl SET mystring='Trying a date using strtotime.', myinteger='654321', mydecimal='14.769', mydate='".$date."' WHERE id=1";
$db->setQuery($update);
$db->query();
?>

This successfully updates the string, integer, and decimal fields but the date field is blank. Can someone help me with the proper syntax?

 

[troester]

Did you try
...mydate='1994-12-24' WHERE

 

[cheesegrits]

What column type is your mydate currently set to?

When you say "uniz timestamp", there is no "unix timestamp" field type in MySQL. UNIX timestamps are just 32 bit integers, so a field type of INT.

If you tried creating a 'TIMESTAMP' column, you can't specify a time when saving that, as it automatically updates according to how you set the column up. A typical declaration for a TIMESTAMP would be something like TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

So ... try just a simple INT field.

-- hugh

 

[Reynolds]

Hugh,

The field 'mydate' that I am trying to update is a Fabrik 'date' plugin type. The same type that you show in the default 'date_time' field.

So if I were trying to update the 'date_time' field rather than the 'mydate' field above, how could I correct that code so that it properly updates the 'date_time' field?

 

[cheesegrits]

If you are modifying a MySQL DATETIME field (which is what we use for a 'date' element), then your data has to be a string in standard MySQL datetime format, which is YYYY-MM-DD HH:MM:SS. The second part (HH:MM:SS) is optional, and will default to all 0's.

Note that if you are using a PHP submission script to modify values, depending which hook you are running it on (like onBeforeProcess, onAfterProcess, etc), Fabrik may overwrite whatever you do in your custom query. Should be OK if you run onAfterProcess, which get run after we've done all our processing, though.

-- hugh