CODEWITHSUNDEEP
phpmysqldatetimecodeigniter
Raptrex
Raptrex

Reputation: 4105

Codeigniter datetime not being inserted

I am trying to enter the date and time into datetime field in MySql. Right now, it is entering the datetime as 0000-00-00 00:00:00. I have 3 tables: article, user, and userarticles.

article: id (int), title (varchar), url (varchar), body (text), date (datetime)
user: id (int) ....
userarticle: uid (int), aid (int) -where uid is user.id and aid is article.id

here is part of my user controller:

function newArticle()
    {
        $this->load->view('newArticleview');
    }

    function insertArticle()
    {
            //insert from form into article table
        $this->db->insert('article', $_POST);

        //get ID of the row just added
        $rowID = $this->db->insert_id();
        $uid = $this->tank_auth->get_user_id();

        $data = array('uid' => $uid, 'aid' => $rowID);

            //insert uid and aid into userarticles table
        $this->db->insert('userarticles', $data);

        redirect('');
    }

my newArticleview

<html>
<head>
    <title>Save Me - Add New Article</title>
</head>
<body>  
<?php
    $date = date("Y-m-d H:i:s");
    echo form_hidden('date', $date);
    echo form_open('user/insertArticle');
?>
<p>Title:<input type="text" name="title"></p>
<p>URL:<input type="text" name="url"></p>
<p><textarea name="body" rows="20"></textarea></p>

<p><input type="submit" value="Submit"></p>

</form>
</body>

</html>

Everything is inserted into the database correctly except for datetime, does anyone know what is wrong? The hidden form is showing up correctly as <input type="hidden" name="date" value="2011-04-08 21:51:10" />

Upvotes: 1

Views: 15056

Answers (3)

Binju Mohan
Binju Mohan

Reputation: 11

Just insert the below code before your insert code in model.

$this->db->set('field name','NOW()',FALSE);

For example:

$this->db->set('posted_date', 'CURDATE()', FALSE);

Upvotes: 1

Ross
Ross

Reputation: 17967

You are outputting a hidden form field, before the form starts. Not sure if that will break CI but it's invalid markup. the form_open helper can take a 3rd argument - an array of hidden fields. So something like this can be done instead/addition:

$hidden=array('item_id'=>$this->uri->segment(3)); 
echo form_open('admin/do_upload', '', $hidden); ?>

You don't need to put the date in the view, really, unless you have specific use for it (i.e for use in javascript or something)

I have something like this in my model for created by/modified by times:

// $data contains everything from the form, I pass this to my model, and then do:

$data['created'] =  date('Y-m-d H:i:s');

$this->db->insert('my_table', $data);

Where created is my column name.

If you aren't using a model, you could put this in your controller instead, but in the interest of MVC it should be in a model really.

Don't forget you can always do:

print_r($this->input->post()) or print_r($_POST) to see what's really being picked up - using the input library helps to sanities everything for you automatically, it's not a requirement, obviously, but the functionality is built in to CI.

Upvotes: 6

Wes
Wes

Reputation: 6585

One of three things are wrong here:

  1. The value of $_POST['date'] = ''

  2. $this->db->insert('article', $_POST); is not picking up the field from your post and treating it as a blank string ''.

  3. you have a column name date date which is a reserved word

Here is the proof you are inserting a blank string. First create a sample table and insert the string you think you entering:

mysql> create table uho ( mine datetime );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into uho values ( '2011-01-12 12:10:00' );
Query OK, 1 row affected (0.00 sec)

It checks out:

mysql> select * from uho;
+---------------------+
| mine                |
+---------------------+
| 2011-01-12 12:10:00 |
+---------------------+
1 row in set (0.00 sec)

Now insert blank string:

mysql> insert into uho values ('');
Query OK, 1 row affected, 1 warning (0.05 sec)

Viola! The date you are seeing:

mysql> select * from uho;
+---------------------+
| mine                |
+---------------------+
| 2011-01-12 12:10:00 |
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

Also I really hope for the sake of your applications security you are not using $_POST directly in that db statement unless the insert method sanitizes your data you are vulnerable to a sql injection attack.

Upvotes: 1

Related Questions