dsnm64969 2016-07-16 18:50
浏览 63
已采纳

PDO bindParam日期不起作用

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";
  • 写回答

1条回答 默认 最新

  • douhaoqiao9304 2016-07-16 19:38
    关注

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名