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)=?
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/
Query 1 fails because you can't quote a placeholder.
Query 2 fails because the single quotes get escaped by the driver and then the whole escaped string gets wrapped in quotes. Roughly
date BETWEEN '\'2018-07-01\''
There are a few ways you could accomplish this.
Take the first 7 values of your date column and compare that against your string.
$date = '2018-07';
...then execute the query like ...
WHERE substr(date, 1, 7) = ?
You also could use the concat method which keeps the placeholder from being quoted.
$july="07";
$query=$conn->prepare("SELECT * FROM table WHERE date BETWEEN concat('2018-', ?, '-01') AND concat('2018-', ?, '-31')");
$query->execute(array($july, $july));
$row=$query->setFetchMode();