Using PDO prepared statement and a placeholder for a specific year, I can't echo any results

Question

I've recently learned the importance of prepared statements so I'm embarking on changing all my existing mysqli queries to PDO queries.

I have a table called "people" with fields called name and dob (DATETIME), which have data like:

name:  Johnny
dob:  2016-12-06 18:30:00

I supply a year in a PHP variable first, for example:

$theyear=2016;

Then, using this example, I'm trying to pull all names for people with a dob in 2016 and echo them, like this below, but it's not displaying any results (and there are plenty):

$stmt = $pdo->prepare("SELECT * FROM people WHERE `dob` BETWEEN ':theyear-01-01' AND ':theyear-12-31'");
$stmt->execute(['theyear' => $theyear]);
while ($row = $stmt->fetch()) {
    echo $row['name'] . "<br/>";
}

Being new to PDO, I'm sure I did something stupid/illogical.

Can anybody see my mistake/help me get in the right direction?

https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql a dupe of, technically — Funk Forty Niner, Jul 19 '17 at 14:56
and your query doesn't make sense though. You need to use different names if emulation is not set — Funk Forty Niner, Jul 19 '17 at 14:57
you need to set your variables' values *before* querying, then pass those on in the query *after*, this in a nutshell. Quite a few ways to do this actually. — Funk Forty Niner, Jul 19 '17 at 15:02
http://php.net/manual/en/pdo.error-handling.php - you're not checking for errors at all. What you posted would have thrown you something. — Funk Forty Niner, Jul 19 '17 at 15:16

score 2 · Answer 1 · edited Jul 19 '17 at 15:01

2

Try to change

':theyear-01-01'

To

CONCAT(:theyear, '-01-01')

And if emulation is not set, you need two different variables, :theyear1 and :theyear2 f.e.

edited Jul 19 '17 at 15:01

Nana Partykar

10,556
10
48
77

answered Jul 19 '17 at 14:58

MVG1984

635
4
15

4

Do or do not, there is no "try". A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – Jay Blanchard Jul 19 '17 at 15:00
This way worked, but I'll have to read up about emulation to decide if that should be set or not, thank you – user3304303 Jul 19 '17 at 15:12

Jay Blanchard · Accepted Answer · 2017-07-19T15:19:30.067

2

If you're going to concatenate, do it in the array, not the query and use separate placeholders for each variable:

$stmt = $pdo->prepare("SELECT * FROM people WHERE `dob` BETWEEN :theyearbegin AND :theyearend");
$stmt->execute([':theyearbegin' => $theyear.'-01-01', ':theyearend' => $theyear.'-12-31']);

Note that I have removed the quotes from the placeholders in the query. Any concatenation you do to be held in a placeholder for a prepared query should be done outside of the query itself, including queries where you use LIKE.

edited Jul 19 '17 at 15:19

answered Jul 19 '17 at 15:02

Jay Blanchard

34,243
16
77
119

The CONCAT way @MVG1984 mentioned worked, but it sounds like I shouldn't do that in the query itself, so I want your way to work but it's not for some reason. No errors, just echo's nothing. Maybe I had a typo or something, but I copied it straight from here so a little perplexed. Ill keep toying tho, thanks! – user3304303 Jul 19 '17 at 15:14
Found it. Yours was missing a hyphen before "12-31". Added it and it works now, thanks again for your knowledge! – user3304303 Jul 19 '17 at 15:17
Quick follow up if you'll indulge me... I see you have colons in the keys. I read that they are unnecessary. Do you feel they SHOULD be used for a specific reason? – user3304303 Jul 19 '17 at 15:19
Thanks @user3304303! I was staring at it and just didn't see it. Glad to have helped! – Jay Blanchard Jul 19 '17 at 15:19
1

I just keep the colons for a sanity check, they are not necessary. – Jay Blanchard Jul 19 '17 at 15:20

Using PDO prepared statement and a placeholder for a specific year, I can't echo any results

2 Answers2