使用PHP通过变量选择日期之间的行

Question

I'm trying to fetch all rows where the date column's value is a day in july using the code below:

$july="07";
$query=$conn->prepare("SELECT * FROM table WHERE date BETWEEN '2018-?-01' AND '2018-?-31'");
$query->execute($july,$july);
$row=$query->setFetchMode();

I have also tried like this:

$july="07";
$month_1="'2018-".$july."-01'";
$month_2="'2018-".$july."-31'";

$query=$conn->prepare("SELECT * FROM table WHERE date BETWEEN ? AND ?");
$query->execute(array($month_1,$month_2));
$row=$query->setFetchMode();

1st case, I get the following error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens' in file.php on line 35

I assume I get that error because I cannot have '?' in a query between single brackets.

Second case, I get nothing at all.

Solved it with this:

SELECT * FROM table WHERE YEAR(date)=2018 AND MONTH(date)=?

Answer 1

First case error is because you passed two variables to PDO::execute() and its expecting array there.

Suggested Reading...

http://php.net/manual/en/pdostatement.execute.php

Second case .. ummm.. you sure it does nothing?

Select * from mytable where date <= '2018-07-31' and date >= '2018-07-01'  

or whatever... this maybe ...

select * from mytable where date between UNIX_TIMESTAMP(STR_TO_DATE('Jul 01 2018 12:00AM', '%M %d %Y %h:%i%p')) and UNIX_TIMESTAMP(STR_TO_DATE('Jul 31 2018 11:59PM', '%M %d %Y %h:%i%p'))

Never use * to return all columns in a table–it’s lazy. You should only extract the data you need. Even if you require every field, your tables will inevitably change. -https://www.sitepoint.com/mysql-mistakes-php-developers/