PDO bindParam日期不起作用

Question

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";

Answer 1

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