Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Inserting NOW() into Database with CodeIgniter's Active Record

I want to insert current time in database using mySQL function NOW() in Codeigniter's active record. The following query won't work:

$data = array(
        'name' => $name ,
        'email' => $email,
        'time' => NOW()
        );
        $this->db->insert('mytable', $data);

This is because CodeIgniter’s ActiveRecord class automatically escapes the input.

The following works fine, by calling set() and passing peratmeter FALSE, so that it doesn't escape the NOW().

$data = array(
        'name' => $name ,
        'email' => $email,
        );
        $this->db->set('time', 'NOW()', FALSE);
        $this->db->insert('mytable', $data);

However, my question is that is there any other way than this? For example, if i can use somehow use by adding everything in the data array only? For example, something like:

$data = array(
            'name' => $name ,
            'email' => $email,
            'time' => NOW(), FALSE
            );
like image 639
Roman Avatar asked Jun 15 '11 07:06

Roman


People also ask

How to insert current date to database in CodeIgniter?

Show activity on this post. $datau = array( 'reg_no' => $firstname, 'fee_type' => $productinfo, 'transaction_id' => $txnid, 'status' => '0', 'time' => date("Y-m-d H:i:s") ); $this->db->insert('payment_status', $datau); $this->load->view('fail', $data);

How use now function in CodeIgniter?

$data = array( 'name' => $name , 'email' => $email, 'time' => NOW() ); $this->db->insert('mytable', $data); This is because CodeIgniter's ActiveRecord class automatically escapes the input. The following works fine, by calling set() and passing peratmeter FALSE, so that it doesn't escape the NOW().

How do you check data is inserted or not in CodeIgniter?

You can use $this->db->affected_rows() function of codeigniter. Show activity on this post.


3 Answers

I typically use triggers to handle timestamps but I think this may work.

$data = array(
    'name' => $name,
    'email' => $email
);

$this->db->set('time', 'NOW()', FALSE);
$this->db->insert('mytable', $data);
like image 121
Bill H Avatar answered Oct 13 '22 12:10

Bill H


Unless I am greatly mistaken, the answer is, "No, there is no way."

The basic problem in situations like that is the fact that you are calling a MySQL function and you're not actually setting a value. CI escapes values so that you can do a clean insert but it does not test to see if those values happen to be calling functions like aes_encrypt, md5, or (in this case) now(). While in most situations this is wonderful, for those situations raw sql is the only recourse.

On a side, date('Y-m-d'); should work as a PHP version of NOW() for MySQL. (It won't work for all versions of SQL though).

like image 33
cwallenpoole Avatar answered Oct 13 '22 14:10

cwallenpoole


aspirinemaga, just replace:

$this->db->set('time', 'NOW()', FALSE);
$this->db->insert('mytable', $data);

for it:

$this->db->set('time', 'NOW() + INTERVAL 1 DAY', FALSE);
$this->db->insert('mytable', $data);
like image 15
Bruno Rigolon Avatar answered Oct 13 '22 13:10

Bruno Rigolon