CODEWITHSUNDEEP
phpmysqlcodeigniterdatetime
lumaluis
lumaluis

Reputation: 113

String datetime insert into mysql database with codeigniter

I am having following MySQL table

EventId     ObjectKey   Title           Description     When        Duration        Where           Status
INT         CHAR(36)    VARCHAR(500)    VARCHAR(8000)   DATETIME    VARCHAR(500)    VARCHAR(500)    TINYINT

and my PHP array is

$data = array(
    'Title' => $title,
    'Description' => $description,
    'When' => $when,
    'Duration' => $duration,
    'Where' => $where
);

The variable $when contains 02/21/2013. When I try to insert to the table with CodeIgniter

public function insert_event($guid, $data)
{
    $CI = & get_instance();
    $CI->load->database('default');
    $CI->db->set($data);
    $CI->db->set('ObjectKey', $guid);
    $CI->db->set('Status', 1);
    $vari = $CI->db->insert('Events');
    return $vari;
}

Everything inserted correctly except date. Can you help me on this?

Upvotes: 4

Views: 16966

Answers (3)

Andriy Petrusha
Andriy Petrusha

Reputation: 129

Simplest way to store string date in any format in mysql

$srcFormat = "m/d/Y"; //convert string to php date
$destFormat = "Y-m-d" //convert php date to mysql date string

$data = array(
  'Title' => $title,
  'Description' => $description,
  'When' => DateTime::createFromFormat($srcFormat, $when)->format($destFormat),
  'Duration' => $duration,
  'Where' => $where
);

Upvotes: 1

m4t1t0
m4t1t0

Reputation: 5721

Use the correct MYSQL format for the date YYYY-MM-DD. For example change this in your code:

$data = array(
    'Title' => $title,
    'Description' => $description,
    'When' => date('Y-m-d', strtotime($when)),
    'Duration' => $duration,
    'Where' => $where
);

Upvotes: 14

Laurence
Laurence

Reputation: 60040

Dates in mySQL are YYYY-MM-DD

You are inserting a date MM/DD/YYYY

So try this:

$data = array(
    'Title' => $title,
    'Description' => $description,
    'When' => date('Y-m-d', strtotime($when)),
    'Duration' => $duration,
    'Where' => $where
);

Upvotes: 4

Related Questions