MySQL正在将我的时间戳记值转换为0000-00-00 [英] MySQL is converting my timestamp values to 0000-00-00
问题描述
用户名
,密码
,电子邮件
全部插入到MySQL中。 注册
和 last_seen
不要。 我以为我正在使用 getTimestamp()
错误,但它回应了我需要的。但是,当我尝试将两个时间戳记字段插入到MySql中时,我在这些字段中看到 0000-00-00 00:00:00
,而不是在sql查询之前回显的内容。我在这里做错了什么?非常感谢任何帮助。
$ date = new DateTime();
$ time = $ date-> getTimestamp();
echo'< div class =box_grey>'。 $时间。 '< / div>';
$ sql ='INSERT INTO users(username,password,email,registered,last_seen)
VALUES(:username,:password,:email,,registered,,last_seen)';
$ stmt = $ pdo-> prepare($ sql);
$ stmt-> bindParam(':username',$ username);
$ stmt-> bindParam(':password',$ password);
$ stmt-> bindParam(':email',$ email);
$ stmt-> bindParam(':registered',$ time);
$ stmt-> bindParam(':last_seen',$ time);
$ stmt-> execute();
print'< div class =box_grey> errorCode()'。 $ stmt-> errorCode()。 '< br />< br />';
print_r($ stmt-> errorInfo());
echo'< / div>';
echo'< div class =box_grey>注册成功!< / div>';
以下是 SHOW CREATE TABLE users;
我的$。
$ b
CREATE TABLE`users`(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT'User'的唯一ID号',
`username` varchar(16)NOT NULL,
`password` varchar(100)NOT NULL,
`email` varchar(254)NOT NULL,
`registered` timestamp NOT NULL DEFAULT'0000-00-00 00:00:00',
`last_seen` timestamp NOT NULL DEFAULT'0000-00-00 00: 00:00',
PRIMARY KEY(`id`))
ENGINE = InnoDB AUTO_INCREMENT = 9 DEFAULT CHARSET = utf8
这很常见。
你混淆了mysql的时间戳,这个时间戳实际上是一个DATETIME,像UNIX时间戳,是自1970-01-01以来通过的秒数。您需要更改字段格式或插入值。
还可以方便地找到一些mysql函数 - CURDATE()或NOW()例如
$ sql ='INSERT INTO users VALUES(NULL,:username,:password,:email,NOW()现在())';
$ stmt = $ pdo-> prepare($ sql);
$ stmt-> execute(array($ username,$ password,$ email));
但是个人我可以避免mysql的时间戳。他们可以意外地改变,毁了你的所有数据。我会使用 DATETIME
并手动设置所有值。
I'm new to PHP and am currently still learning. I'm having issues with my registration form I think. username
, password
, email
all insert into MySQL successfully. registered
and last_seen
do not.
I thought I was using getTimestamp()
wrong, but it echos what I need. However when I try to insert both timestamp fields into MySql, I see 0000-00-00 00:00:00
in those fields instead of what it echoed before the sql query. What am I doing wrong here? Any help is much appreciated.
$date = new DateTime();
$time = $date->getTimestamp();
echo '<div class="box_grey">' . $time . '</div>';
$sql = ' INSERT INTO users (username, password, email, registered, last_seen)
VALUES (:username, :password, :email, :registered, :last_seen)';
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':registered', $time);
$stmt->bindParam(':last_seen', $time);
$stmt->execute();
print '<div class="box_grey">errorCode() ' . $stmt->errorCode() . '<br /><br />';
print_r($stmt->errorInfo());
echo '</div>';
echo '<div class="box_grey">Registered successfully!</div>';
Here's what SHOW CREATE TABLE users;
shows me.
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'User''s unique ID number',
`username` varchar(16) NOT NULL,
`password` varchar(100) NOT NULL,
`email` varchar(254) NOT NULL,
`registered` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_seen` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`))
ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
That occurs pretty often.
You're confusing mysql timestamp which is actually a DATETIME like value with UNIX timestamp, which is number of seconds passed since 1970-01-01.
You need to either change field format or the inserting value.
Also you can find some mysql functions handy - CURDATE() or NOW () for example
$sql = 'INSERT INTO users VALUES (NULL,:username,:password,:email,NOW(),NOW())';
$stmt = $pdo->prepare($sql);
$stmt->execute(array($username, $password, $email));
But personally I'd avoid mysql timestamps. they can be changed unexpectedly, ruining all your data. I'd use DATETIME
and set all values manually.
这篇关于MySQL正在将我的时间戳记值转换为0000-00-00的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!