[Solar-talk] Trying to insert a date field into MySQL database

[Paul M Jones]

On 05 Dec 2007, at 16:30, Paul M Jones wrote:

>
> On 05 Dec 2007, at 15:50, Cruz, Darwin wrote:
>
>> I'm trying to figure out how to take a passed in date formatted as  
>> '2007-12-31' and insert that into a MySQL database.
>>
>> I've tried the following array set ups for my insert into database  
>> with no luck:
>>
>>    $eventInsert = array(
>>     'event_id'   => 'null',
>>     'event_summary' => $values['eventSummary'],
>>     'event_number'  => $values['eventNumber'],
>>     'event_type'  => $values['eventType'],
>>     'date'    => 'ADDDATE(\''.$values['eventDate'].'\', INTERVAL 31  
>> DAY)',
>>     );
>>
>>    $eventInsert = array(
>>     'event_id'   => 'null',
>>     'event_summary' => $values['eventSummary'],
>>     'event_number'  => $values['eventNumber'],
>>     'event_type'  => $values['eventType'],
>>     'date'    => $values['eventDate'],
>>     );
>
> The second one should work OK; i.e. if $values['eventDate'] is the  
> string '2007-12-31' then MySQL should recognize it.
>
> The first one, though ...
>
> Solar_Sql runs the values through PDO as a prepared statement, and  
> so those values get quoted to protect against SQL injections.  That  
> means you'll get "'ADDDATE(...)'" (note the surrounding quotes),  
> which is a string, and doesn't look like a date to MySQL.
>
> If you need to do date or time calculations, here are some options:
>
> 1. Use a PHP5 DateTime object and calculate the 31-day interval with  
> that, and insert the calculated value.  <http://php.net/DateTime>
>
> 2. If you have no other option but using the MySQL native functions,  
> you'll probably need to write the insert statement yourself, and  
> pass the values to Solar_Sql::query.  E.g.:
>
>    $cmd = "INSERT INTO table_name (date)
>            VALUES (ADDDATE(:date, INTERVAL 31 DAY))";
>
>    $result = $sql->query($cmd, array('date' => '2007-12-31'));
>

Hmm, you could also use date() and strtotime() like this:

     $date = date('Y-m-d', strtotime('+12 days',  
strtotime('2007-12-31')));


-- pmj