我有一个地方插入一条数据库记录,报错:
insert into receipt(rent,deposit,paidup,createdtime,depositback,coperator) values(50,1,51,'2012-04-10 17:44:47','0','zxy')You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '17:44:47' at line 1时间是从$dt=date("Y-m-d H:i:s");得到的。
我去查这条记录实际上已经被插入表receipt了.下面是从phpmyadmin拷贝出来的有用的表结构:
字段 类型 空 默认 额外
id int(11) 否 无 AUTO_INCREMENT
rent float 否 无
deposit float 否 无
paidup float 否 无
depositback varchar(1) 否 无
createdtime datetime 否 无
coperator varchar(30) 否 无
dbtime datetime 是 NULL
dboperator varchar(30) 是 NULL 为什么出错了还能插入成功呀?
这个错什么意思呀?
insert into receipt(rent,deposit,paidup,createdtime,depositback,coperator) values(50,1,51,'2012-04-10 17:44:47','0','zxy')You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '17:44:47' at line 1时间是从$dt=date("Y-m-d H:i:s");得到的。
我去查这条记录实际上已经被插入表receipt了.下面是从phpmyadmin拷贝出来的有用的表结构:
字段 类型 空 默认 额外
id int(11) 否 无 AUTO_INCREMENT
rent float 否 无
deposit float 否 无
paidup float 否 无
depositback varchar(1) 否 无
createdtime datetime 否 无
coperator varchar(30) 否 无
dbtime datetime 是 NULL
dboperator varchar(30) 是 NULL 为什么出错了还能插入成功呀?
这个错什么意思呀?
我怀疑createdtime是date类型,而不是datetime. 因为date类型正好只接受2012-04-10 17:44:47 前面的2012-04-10。而datetime类型接受 2012-04-10 17:44:47尽管是phpmyadmin的查询结果,但仍然有必要建议你运行以下SQL语句
SHOW CREATE TABLE `receipt`;
看看结果
运行结果如下:
| receipt | CREATE TABLE `receipt` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rent` float NOT NULL,
`deposit` float NOT NULL,
`paidup` float NOT NULL,
`depositback` varchar(1) NOT NULL,
`createdtime` datetime NOT NULL,
`coperator` varchar(30) NOT NULL,
`dbtime` datetime DEFAULT NULL,
`dboperator` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
会不会是php里面插入语句有问题:
$dt=date("Y-m-d H:i:s");
$database->insert("receipt","rent,deposit,paidup,createdtime,depositback,coperator,dbtime","$rent,$deposit,$total,'$dt','0','zxy','0000-00-00 00:00:00'");
value是datetime类型插入的时候要加单引号吗?
我加不加都是一样效果。
$dt=date("Y-m-d H:i:s");
$database->insert("receipt","rent,deposit,paidup,createdtime,depositback,coperator,dbtime","$rent,$deposit,$total,'$dt','0','zxy','0000-00-00 00:00:00'");
报错的插入语句就是由下面这两个语句生成的。
$dt=date("Y-m-d H:i:s");
$database->insert("receipt","rent,deposit,paidup,createdtime,depositback,coperator,dbtime",
"$rent,$deposit,$total,'$dt','0','zxy','0000-00-00 00:00:00'");
你可以贴出$database类里面,拼装出来的SQL语句
我做了个测试,同样的表结构,同样的SQL语句,没有遇到任何问题
class db
{
private $host;
private $user;
private $pw;
private $con;
private $dbname;
function __construct($host,$user,$pw,$dbname)
{
$this->host=$host;
$this->user=$user;
$this->pw=$pw;
$this->dbname=$dbname;
$this->connect();
}
function __destruct()
{
mysql_close($this->con);
}
function update($table,$name,$cond)
{
$sql="update $table set $name where $cond";
$result=mysql_query($sql);
if(!$result)
die (mysql_error());
}
function connect()
{
$this->con=mysql_connect($this->host,$this->user,$this->pw);
if(!$this->con) die(mysql_error());
mysql_select_db($this->dbname) or die(mysql_error());
}
function query($name,$table,$cond)
{
if(($name=="")&&($cond==""))
$sql="select * from $table";
else
{
if($name=="")
$sql="select * from $table where $cond";
else
$sql="select $name from $table where $cond";
}
echo "$sql";
$result=mysql_query($sql);
if(!$result)
die (mysql_error());
else
return $result;
}
function insert($table,$name,$value)
{
if($name=="")
$sql="insert into $table values($value)";
else
{
$sql="insert into $table ($name) values ($value)";
echo $sql;
}
$result=mysql_query($sql);
if(!$result)
die (mysql_error());
}
}
这是php里面对db的操作:
$database=new db("localhost","root","19801010_zxy","zxy_hms_db");$dt=date("Y-m-d H:i:s");
$database->insert("receipt","rent,deposit,paidup,createdtime,depositback,coperator,dbtime","$rent,$deposit,$total,'$dt','0','zxy','0000-00-00 00:00:00'");
$rresult=$database->query("","receipt","createdtime='$dt'");
$rrow=mysql_fetch_array($rresult);
$rid=$rrow['id'];
"$rent,$deposit,$total,'$dt','0','zxy','0000-00-00 00:00:00'");
这条语句每次需要手动的换行,不换行就被csdn咔嚓掉了。
$rent,$deposit,$total,'$dt'的值要存在,不存在的话组成的sql就有问题。导致错误。
$dt不加单引号就会出现You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '17:44:47' at line 1这个问题。
'$dt'加上单引号就会出现Column count doesn't match value count at row 1这个问题。
但是不知道怎么办。