php mysql 插入 now,使用MySQL/PHP在字段中使用now()插入當前日期/時間

Since MySQL evidently cannot automatically insert the function now() in a datetime field in adding new records like some other databases, based on comments, I'm explicitly trying to insert it using an SQL statement. (People seem to think timestamp with curdate() is not the answer due to the various limitations of timestamp.) There are numerous articles on the web suggesting inserting now() using SQL should work.

由於MySQL顯然不能像其他數據庫那樣在datetime字段中根據注釋自動插入函數now()，因此我明確地嘗試使用SQL語句插入它。(由於時間戳的各種限制，人們似乎認為帶有curdate()的時間戳不是答案。)web上有大量的文章建議現在使用SQL插入()。

When I try to insert the date time using the SQL statement, however, the field does not populate with the current time/date, but it only gives me the default 0000-00-, etc. This is probably a syntax error, but it's driving me crazy, so I am posting it.

但是，當我嘗試使用SQL語句插入日期時間時，字段不會填充當前的時間/日期，但是它只會給我默認的000000 -，等等。這可能是語法錯誤，但是它讓我抓狂，所以我把它發布了。

mysql_query("INSERT INTO users (first, last, whenadded) VALUES ('$first', '$last', now())";

It inserts first and last, but nothing for when added, leaving 0000-00-00, etc. in the whenadded field.

它首先插入，最后插入，但是添加時不插入，在when添加字段中留下000000 -00等等。

The field type is datetime, it has no collation, attributes, null default or extra. BTW, I tried putting now() in single quotes... It threw an error.

字段類型是datetime，它沒有排序、屬性、null默認值或額外屬性。順便說一句，我試着把now()放在單引號里……它把一個錯誤。

7 个解决方案

#1

66

NOW() normally works in SQL statements and returns the date and time. Check if your database field has the correct type (datetime). Otherwise, you can always use the PHP date() function and insert:

NOW()通常在SQL語句中工作，並返回日期和時間。檢查您的數據庫字段是否具有正確的類型(datetime)。否則，您可以始終使用PHP date()函數並插入:

date('Y-m-d H:i:s')

But I wouldn't recommend this.

但我不推薦這個。

#2

13

You forgot to close the mysql_query command:

您忘記關閉mysql_query命令:

mysql_query("INSERT INTO users (first, last, whenadded) VALUES ('$first', '$last', now())");

mysql_query(“插入到用戶(首先，最后，當添加)值('$first'， '$last'， now())”);

Note that last parentheses.

注意,最后一個括號。

#3

7

Like Pekka said, it should work this way. I can't reproduce the problem with this self-contained example:

就像Pekka說的，它應該這樣工作。我無法用這個獨立的例子來重現這個問題:

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$pdo->exec('

CREATE TEMPORARY TABLE soFoo (

id int auto_increment,

first int,

last int,

whenadded DATETIME,

primary key(id)

)

');

$pdo->exec('INSERT INTO soFoo (first,last,whenadded) VALUES (0,1,Now())');

$pdo->exec('INSERT INTO soFoo (first,last,whenadded) VALUES (0,2,Now())');

$pdo->exec('INSERT INTO soFoo (first,last,whenadded) VALUES (0,3,Now())');

foreach( $pdo->query('SELECT * FROM soFoo', PDO::FETCH_ASSOC) as $row ) {

echo join(' | ', $row), "\n";

}

Which (currently) prints

(目前)打印嗎

1 | 0 | 1 | 2012-03-23 16:00:18

2 | 0 | 2 | 2012-03-23 16:00:18

3 | 0 | 3 | 2012-03-23 16:00:18

And here's (almost) the same script using a TIMESTAMP field and DEFAULT CURRENT_TIMESTAMP:

這里(幾乎是)使用時間戳字段和默認CURRENT_TIMESTAMP的相同腳本:

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$pdo->exec('

CREATE TEMPORARY TABLE soFoo (

id int auto_increment,

first int,

last int,

whenadded TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

primary key(id)

)

');

$pdo->exec('INSERT INTO soFoo (first,last) VALUES (0,1)');

$pdo->exec('INSERT INTO soFoo (first,last) VALUES (0,2)');

sleep(1);

$pdo->exec('INSERT INTO soFoo (first,last) VALUES (0,3)');

foreach( $pdo->query('SELECT * FROM soFoo', PDO::FETCH_ASSOC) as $row ) {

echo join(' | ', $row), "\n";

}

Conveniently, the timestamp is converted to the same datetime string representation as in the first example - at least with my PHP/PDO/mysqlnd version.

方便的是，時間戳被轉換為與第一個示例相同的datetime字符串表示形式——至少在我的PHP/PDO/mysqlnd版本中是這樣。

#4

6

The only reason I can think of is you are adding it as string 'now()', not function call now().

Or whatever else typo.

我能想到的唯一原因是您將它添加為string 'now()，而不是function call now()。或者其他問題。

SELECT NOW();

to see if it returns correct value?

看看它是否返回正確的值?

#5

2

now()

現在()

worked for me . but my field type is date only and yours is datetime. i am not sure if this is the case

為我工作。但我的字段類型是date only，而您的是datetime。我不確定這是不是真的

#6

1

These both work fine for me...

這兩款對我來說都沒問題……

$db = mysql_connect('localhost','user','pass');

mysql_select_db('test_db');

$stmt = "INSERT INTO `test` (`first`,`last`,`whenadded`) VALUES ".

"('{$first}','{$last}','NOW())";

$rslt = mysql_query($stmt);

$stmt = "INSERT INTO `users` (`first`,`last`,`whenadded`) VALUES ".

"('{$first}', '{$last}', CURRENT_TIMESTAMP)";

$rslt = mysql_query($stmt);

?>

Side note: mysql_query() is not the best way to connect to MySQL in current versions of PHP.

附注:mysql_query()不是連接MySQL的最佳方式。

#7

1

What about SYSDATE() ?

關於SYSDATE()?

$db = mysql_connect('localhost','user','pass');

mysql_select_db('test_db');

$stmt = "INSERT INTO `test` (`first`,`last`,`whenadded`) VALUES ".

"('{$first}','{$last}','SYSDATE())";

$rslt = mysql_query($stmt);

?>

查看MySQL中NOW()、SYSDATE()和CURRENT_DATE()之間的差異，了解更多關於NOW()和SYSDATE()的信息。