php 使用 CodeIgniter 的 Active Record 将 NOW() 插入数据库

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

提问by Roman

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

我想在 Codeigniter 的活动记录中使用 mySQL 函数 NOW() 在数据库中插入当前时间。以下查询不起作用：

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

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

这是因为 CodeIgniter 的 ActiveRecord 类会自动转义输入。

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

以下工作正常，通过调用 set() 并传递 peratmeter FALSE，以便它不会转义 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
            );

采纳答案by cwallenpoole

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.

在这种情况下的基本问题是，您正在调用 MySQL 函数而实际上并未设置值。CI 对值进行转义，以便您可以进行干净的插入，但它不会测试这些值是否碰巧调用了aes_encrypt, md5, 或 ( 在这种情况下) 之类的函数now()。虽然在大多数情况下这很好，但对于这些情况，原始 sql 是唯一的资源。

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).

一方面，date('Y-m-d');应该作为NOW()MySQL的 PHP 版本工作。（虽然它不适用于所有版本的 SQL）。

回答by Bill H

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);

回答by Bruno Rigolon

aspirinemaga, just replace:

aspirinemaga，只需替换：

$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);

回答by saurabh kamble

This is the easy way to handle timestamp insertion

这是处理时间戳插入的简单方法

$data = array('created_on' => date('Y-m-d H:i:s'));

回答by sandeep kumar

    $data = array(
            'name' => $name ,
            'email' => $email,
            'time' =>date('Y-m-d H:i:s')
            );
            $this->db->insert('mytable', $data);

回答by Steven

you can load the date helper and use the codeigniter interal now() if you want to reference the users GMT time offset

如果您想引用用户的 GMT 时间偏移，您可以加载日期助手并使用 codeigniter interal now()

it woulk look somewhat like this

它看起来有点像这样

$data = array(
'created_on' => date('Y-m-d H:i:s',now())
);

If you don't use the GTM master settings and let your users set their own offsets there is no advantage over using php's time() function.

如果您不使用 GTM 主设置并让您的用户设置他们自己的偏移量，那么使用 php 的 time() 函数没有任何优势。

回答by Junliang Huang

According to the source code of codeigniter, the function setis defined as:

根据codeigniter的源码，函数set定义为：

public function set($key, $value = '', $escape = TRUE)
{
    $key = $this->_object_to_array($key);

    if ( ! is_array($key))
    {
        $key = array($key => $value);
    }

    foreach ($key as $k => $v)
    {
        if ($escape === FALSE)
        {
            $this->ar_set[$this->_protect_identifiers($k)] = $v;
        }
        else
        {
            $this->ar_set[$this->_protect_identifiers($k, FALSE, TRUE)] = $this->escape($v);
        }
    }

    return $this;
}

Apparently, if $keyis an array, codeigniter will simply ignore the second parameter $value, but the third parameter $escapewill still work throughout the iteration of $key, so in this situation, the following codes work (using the chain method):

显然，如果$key是一个数组，codeigniter 将简单地忽略第二个参数$value，但第三个参数$escape在 的整个迭代过程中仍然有效$key，因此在这种情况下，以下代码有效（使用链方法）：

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

However, this will unescape all the data, so you can break your data into two parts:

但是，这将取消所有数据的转义，因此您可以将数据分为两部分：

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

回答by williamli

putting NOW() in quotes won't work as Active Records will put escape the NOW() into a string and tries to push it into the db as a string of "NOW()"... you will need to use

将 NOW() 放在引号中将不起作用，因为 Active Records 会将 NOW() 转义为字符串并尝试将其作为字符串“NOW()”推入数据库中...您将需要使用

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

to set it correctly.

正确设置。

you can always check your sql afterward with

你可以随时检查你的sql

$this->db->last_query();

回答by Brandon Hollenbeck

Using the date helper worked for me

使用日期助手为我工作

$this->load->helper('date');

You can find documentation for date_helperhere.

您可以在date_helper此处找到文档。

$data = array(
  'created' => now(),
  'modified' => now()
);

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

回答by Kiran P.

$this->db->query("update table_name set ts = now() where 1=1")also works for current time stamp!

$this->db->query("update table_name set ts = now() where 1=1")也适用于当前时间戳！