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 delta降尺度计算的一些细节,有偿
  • ¥15 Arduino红外遥控代码有问题
  • ¥15 数值计算离散正交多项式
  • ¥30 数值计算均差系数编程
  • ¥15 redis-full-check比较 两个集群的数据出错
  • ¥15 Matlab编程问题
  • ¥15 训练的多模态特征融合模型准确度很低怎么办
  • ¥15 kylin启动报错log4j类冲突
  • ¥15 超声波模块测距控制点灯,灯的闪烁很不稳定,经过调试发现测的距离偏大
  • ¥15 import arcpy出现importing _arcgisscripting 找不到相关程序