PDO bindParam为日期不工作 [英] PDO bindParam for date not working
问题描述
$ db = DB :: getConnection（）;
$ stmt = $ db-> prepare（SELECT * FROM`flights` WHERE`date` BETWEEN：befDate AND：aftDate
AND`from` =：from
AND`to =：to
AND`weight`> =：weight）;
$ stmt-> bindParam（'：befDate'，$ befDate，PDO :: PARAM_STR）; // $ befDate ='2016-07-21';
$ stmt-> bindParam（'：aftDate'，$ aftDate，PDO :: PARAM_STR）; // $ aftDate ='2016-07-28';
$ stmt-> bindParam（'：from'，$ from，PDO :: PARAM_INT）;
$ stmt-> bindParam（'：to'，$ to，PDO :: PARAM_INT）;
$ stmt-> bindParam（'：weight'，$ weight，PDO :: PARAM_INT）;
$ ok = $ stmt-> execute（）;
if（$ ok）{
if（$ stmt-> fetchColumn（）> = 1）{
$ result = $ stmt-> fetchAll（）;
}
else {
$ result ='nope';
}
return $ result;
}
else {
return false;
}
问题是，它总是返回0个搜索结果。我试图通过phpMyAdmin手动运行我的SQL生成，发现问题是因为PDO生成的SQL是：
SELECT * FROM`FLIGHTS` WHERE`DATE` BETWEEN 2016-07-17 AND 2016-07-25 AND`FROM` = 1237 AND`TO` = 2380 AND`WEIGHT`> = 4
而从中获取结果的正确SQL应为：
SELECT * FROM`FLIGHTS` WHERE`DATE` BETWEEN'2016-07-17'AND'2016-07-25'AND`FROM` = 1237 AND`TO` = 2380 AND `WEIGHT`> = 4
即，单引号之间的日期值。现在如果我在PDO中为我的SQL添加引号，如：
$ stmt = $ db-> prepare（SELECT * FROM `flight` WHERE`date` BETWEEN'：befDate'AND'：aftDate'
AND`from` =：from
AND`to` =：to
AND`weight`> = ：重量）;
我得到参数号无效：绑定变量的数量不符合令牌数错误。
感谢您提前的帮助！
更新：
我的航班表结构是：
CREATE TABLE`flights`（
`fid` int（30）NOT NULL，
`user_id` int（30）NOT NULL，
`date` date NOT NULL，
`from` int（30）NOT NULL，
`to` int 30）NOT NULL，
`weight` int（30）NOT NULL，
`size` varchar（30）NOT NULL，
`details` varchar（200）NOT NULL，
`price` int（50）NOT NULL，
`airline` varchar（100）NOT NULL，
`pnr` varchar（100）NOT NULL
）ENGINE = InnoDB DEFAULT CHARSET = utf8 ;
我尝试从查询中删除所有引号，并将其放在单个不间断的行中：
$ stmt = $ db-> prepare（SELECT * FROM flights WHERE date BETWEEN：befDate AND：aftDate AND from =：from AND to =：to AND weight> =：weight）;
但仍然无法正常工作...
更新2
要确定在使用PDO绑定参数（没有单引号的语句）之后，SQL语句看起来像我在函数开始时与我的SQL相同并回覆它来查看结果：
$ _ SESSION ['err'] =SELECT * FROM flights WHERE date BETWEEN $ befDate AND $ aftDate
AND from = $ from
AND to = $ to
AND weight> = $ weight;
这是您的主要问题：
if（$ stmt-> fetchColumn（）> = 1）{
$ result = $ stmt-> fetchAll（） ;
}
else {
$ result ='nope';
}
对fetchColumn（）的调用使结果集超过第一行。那么当你调用fetchAll（）时，它只会获取剩余的行。它不能返回并获取第一行，那丢失了。所以如果你的查询结果只有一行，你将永远看不到它。
相反，我建议这段代码：
$ result = $ stmt-> fetchAll（）;
if（empty（$ result））{
$ result =nope;
}
其他提示： p>
切勿将参数占位符放在引号内。如果这样做，它们不再是参数占位符，它们只是字符串，如befDate。这些不是有效的日期文字。
表达式中的参数，如 BETWEEN：befDate AND：aftDate 不要作为查询生成 BETWEEN 2016-07-17 ANDDATE7-25 。参数永远不会成为这样的表达式，它们总是成为每个参数的标量值（例如引用的日期文字）。
我尝试过您的代码。首先我启用了MySQL通用查询日志：
mysql> SET GLOBAL general_log = ON;
现在我可以看到什么MySQL认为是由PDO提交的查询。我运行了PHP脚本，并阅读了我的一般查询日志（/var/lib/mysql/localhost.log在我的虚拟机上）：
160716 19:26:16 8连接root @ localhost测试
8查询SELECT * FROM`flights` WHERE`date` BETWEEN NULL AND NULL
AND`from` = NULL
AND`to` = NULL
AND`weight`> = NULL
8退出
嗯，我忘了设置绑定参数的变量的值。如果您在任何这些变量中没有任何值，它将解释为什么你的结果是空的，因为与NULL的比较不是真的。所以我编辑了PHP，首先将样本值设置为变量。
$ befDate ='2016-07-21'
$ aftDate ='2016-07-28';
$ from = 1;
$ to = 2;
$ weight = 10;
我再次运行查询，在日志中我看到以下内容：
160716 19:33:17 13查询SELECT * FROM`flights` WHERE`date` BETWEEN'2016-07-21'AND' 28'
AND`from` = 1
AND`to` = 2
AND`weight`> = 10
这证明PDO确实在参数化值（如果是字符串或日期）附带引号。
I am a beginner at PDO, I was making a php function to return search results of flights, this is my code:
$db = DB::getConnection();
$stmt = $db->prepare("SELECT * FROM `flights` WHERE `date` BETWEEN :befDate AND :aftDate
AND `from` = :from
AND `to` = :to
AND `weight` >= :weight");
$stmt->bindParam(':befDate', $befDate, PDO::PARAM_STR); //$befDate = '2016-07-21';
$stmt->bindParam(':aftDate', $aftDate, PDO::PARAM_STR); //$aftDate = '2016-07-28';
$stmt->bindParam(':from', $from, PDO::PARAM_INT);
$stmt->bindParam(':to', $to, PDO::PARAM_INT);
$stmt->bindParam(':weight', $weight, PDO::PARAM_INT);
$ok = $stmt->execute();
if ($ok) {
if ($stmt->fetchColumn()>=1) {
$result = $stmt->fetchAll();
}
else{
$result = 'nope';
}
return $result;
}
else{
return false;
}
The problem is, it is always returning 0 search results. I tried to run my SQL generated through function manually in phpMyAdmin and found that problem is because the SQL being generated by PDO is:
"SELECT * FROM `FLIGHTS` WHERE `DATE` BETWEEN 2016-07-17 AND 2016-07-25 AND `FROM` = 1237 AND `TO` = 2380 AND `WEIGHT` >= 4"
while the correct SQL from which I am getting results should be:
"SELECT * FROM `FLIGHTS` WHERE `DATE` BETWEEN '2016-07-17' AND '2016-07-25' AND `FROM` = 1237 AND `TO` = 2380 AND `WEIGHT` >= 4"
i.e, with date values between single quotes. Now if I add quotes to my SQL in PDO like:
$stmt = $db->prepare("SELECT * FROM `flights` WHERE `date` BETWEEN ':befDate' AND ':aftDate'
AND `from` = :from
AND `to` = :to
AND `weight` >= :weight");
I get "Invalid parameter number: number of bound variables does not match number of tokens" error. Thanks for you help in advance!
UPDATE:
My "flights" table structure is:
CREATE TABLE `flights` (
`fid` int(30) NOT NULL,
`user_id` int(30) NOT NULL,
`date` date NOT NULL,
`from` int(30) NOT NULL,
`to` int(30) NOT NULL,
`weight` int(30) NOT NULL,
`size` varchar(30) NOT NULL,
`details` varchar(200) NOT NULL,
`price` int(50) NOT NULL,
`airline` varchar(100) NOT NULL,
`pnr` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
I tried removing all quotes from query and put it in a single unbroken line as well:
$stmt = $db->prepare("SELECT * FROM flights WHERE date BETWEEN :befDate AND :aftDate AND from = :from AND to = :to AND weight >= :weight");
but still it's not working...
UPDATE 2 To determine what the SQL statement looked like after binding params with PDO (the statement without single quotes) I made a session variable same as my SQL in the beginning of the function and echoed it to view result:
$_SESSION['err'] = "SELECT * FROM flights WHERE date BETWEEN $befDate AND $aftDate
AND from = $from
AND to = $to
AND weight >= $weight";
This is your primary problem:
if ($stmt->fetchColumn()>=1) {
$result = $stmt->fetchAll();
}
else{
$result = 'nope';
}
The call to fetchColumn() advances the result set past its first row. Then when you call fetchAll(), it only fetches the remaining rows. It can't go back and fetch the first row, that's lost. So if your query result had only one row, you'll never see it.
Instead, I would suggest this code:
$result = $stmt->fetchAll();
if (empty($result)) {
$result = "nope";
}
Other tips:
Never put parameter placeholders inside quotes. If you do, they are not parameter placeholders anymore, they're just literal strings like ":befDate". These are not valid date literals.
The parameters in an expression like BETWEEN :befDate AND :aftDate don't produce BETWEEN 2016-07-17 AND 2016-07-25 as a query. Parameters never become expressions like that, they always become a scalar value (e.g. a quoted date literal) per parameter.
I tried your code. First I enabled the MySQL general query log:
mysql> SET GLOBAL general_log = ON;
Now I can see exactly what MySQL thinks is the query submitted by PDO. I ran the PHP script, and read my general query log (/var/lib/mysql/localhost.log on my virtual machine):
160716 19:26:16 8 Connect root@localhost on test
8 Query SELECT * FROM `flights` WHERE `date` BETWEEN NULL AND NULL
AND `from` = NULL
AND `to` = NULL
AND `weight` >= NULL
8 Quit
Ah, I forgot to set values for the variables bound to the parameters. If you had no value in any of these variables, it would explain why your result is empty, because any comparison to NULL is not true. So I edited the PHP to set sample values to the variables first.
$befDate = '2016-07-21';
$aftDate = '2016-07-28';
$from = 1;
$to = 2;
$weight = 10;
I ran the query again, and in the log I see the following:
160716 19:33:17 13 Query SELECT * FROM `flights` WHERE `date` BETWEEN '2016-07-21' AND '2016-07-28'
AND `from` = 1
AND `to` = 2
AND `weight` >= 10
This proves that PDO does put quotes around a parameterized value (if it's a string or a date).
这篇关于PDO bindParam为日期不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
