inserting time into mysql

[eluser]airparkroad[/eluser]
Hi, I'm a newbie and while watching the 20-min blog tutorial, I've come across a question.

So, I wanted to insert a time into the mysql database when I comment.
I created a "date" field with date type into a "comment" table.

And I am using this code at the comment_view.php file.

Code:

<p>&lt;textarea name="body" rows="10"&gt;&lt;/textarea&gt;</p>
<p>&lt;input type="text" name="author" /&gt;</p>
<p>&lt;?php $datestring = "Year: %Y Month: %m Day: %d - %h:%i %a";
    $time = time();
    echo mdate($datestring, $time); ?&gt;</p>
<p>&lt;input type="submit" value="Submit Comment" /&gt;</p>

I want to insert the time of the commnet into a date field of the table.

How do I do this?

Thanks in advance!!

[eluser]tonanbarbarian[/eluser]
I just enter the date in the following format Y-m-d

Code:

$data = array(
  'body' => $this->input->post('body',true),
  'author' => $this->input->post('body',true),
  'date' => date('Y-m-d H:i:s')
);

$this->db->where('id', $id);
$this->db->update('comment', $data);

[eluser]Nick Husher[/eluser]
If the field you're storing that date in is of type timestamp, mySQL will automatically set it to the moment the table row was created. Pretty handy, that.

[eluser]John_Betong[/eluser]
 
What's the time?

Timestamp gives your personal Server time I think. Try searching on "Localised timezones?"
 

[eluser]Nick Husher[/eluser]
Which is what you'd want anyway, right? Then you don't have weird time-travel effects where replies are posted before the post they are replying to. If you want localized time, you can either do time transformations with PHP and an offset parameter or do it on the frontend with Javascript and some intelligent time sensing business.

[eluser]airparkroad[/eluser]
okay so now I changed the date field to timestamp in mysql.
but it's not storing..

my controller::

Code:

function comments() {
        
        $data['title'] = "Minky's Comment Title";
        $data['heading'] = "Minky's Comment Heading";
        $this->db->where('entry_id', $this->uri->segment(3));
        $data['query'] = $this->db->get('comments');
        
        $this->load->view('comment_view', $data);
    }

    function comment_insert() {
        
        $this->db->insert('comments', $_POST);
        
        redirect('blog/comments/'.$_POST['entry_id']);
        
    }

my view::

Code:

<p>&lt;textarea name="body" rows="10"&gt;&lt;/textarea&gt;</p>
<p>&lt;input type="text" name="author" /&gt;</p>
<p>&lt;input type="submit" value="Submit Comment" /&gt;</p>

I tried to do the array data and it got more complex..

[eluser]BoltClock[/eluser]
I create the date field in my tables as type TIMESTAMP and set its default as CURRENT_TIMESTAMP. I'm not sure if the default part is necessary but MySQL does automatically insert the dates for me.

However, if you want the date that's displayed on the view itself to be the date inserted into your database (not preferred actually), you can use a hidden input field. Perhaps something like this:

Code:

<p>&lt;textarea name="body" cols="40" rows="10"&gt;&lt;/textarea&gt;</p>
<p>&lt;input type="text" name="author" value="" /&gt;</p>
<p>&lt;?php $datestring = "Year: %Y Month: %m Day: %d - %h:%i %a";
    $time = time();
    echo mdate($datestring, $time); ?&gt;</p>
<p>&lt;input type="hidden" name="date" value="&lt;?php echo date('Y-m-d H:i:s', $time); ?&gt;" /&gt;
<p>&lt;input type="submit" name="submit" value="Submit Comment" /&gt;</p>

Hope it helped

[eluser]airparkroad[/eluser]
Ah! both works.
I set the TIMESTMP to CURRENT_TIMESTAMP and it worked!

The code also works too.

Thanks a lot!

[eluser]BoltClock[/eluser]
YOu're welcome. Glad to know it works for you