Using PDO and Between

Question

Ok, I've got a quick question. I'm using PDO to communicated with a MySQL db. Using BETWEEN with two dates as the variable is easy:

$db->query("SELECT * FROM awesome WHERE date BETWEEN :start AND :end");

What I don't think is easy is when I have one date and two columns. This doesn't work, no matter how loudly I cuss:

$db->query("SELECT * FROM awesome WHERE :one_date BETWEEN start_col AND end_col");

Is there a way to use BETWEEN without reverting to something awful like...?

$db->query("SELECT * FROM awesome WHERE '$one_date' BETWEEN start_col AND end_col");

Or should I just stick to not using BETWEEN in this case?

$db->query("SELECT * FROM awesome WHERE start_col<=:one_date1 AND end_col>=:one_date2")

Thanks!

well for one thing, `WHERE :one_date` you can't do that. You can't bind a column. So you can stop cussin' about it, now you know ;-) http://stackoverflow.com/questions/15182910/php-pdo-bind-table-name — Funk Forty Niner, Nov 22 '15 at 20:10
so, you want to check if two columns match the criteria, is that what your question is about? — Funk Forty Niner, Nov 22 '15 at 20:12

clemens321 · Answer 1 · 2015-11-22T20:17:44.793

1

~~BETWEEN only works with one column and two values.~~

But your last approach should work. That's at least the way to go

$db->query("SELECT * FROM awesome" .
    " WHERE start_col <= :one_date1 AND end_col >= :one_date2"
);

When using named parameters you should also be able to use the same name twice and only need to bind once.

Update

Well, just tested it and worked for me with " WHERE 'value' between col1 and col2". (tested postgres with datetime, mysql with integer)

edited Nov 22 '15 at 20:17

answered Nov 22 '15 at 20:08

clemens321

thanks. I was actually aware that using a value followed by between works. But when I've tried getting that to work as a prepared statement, it fails. Maybe it's my PDO class? – jonlink Nov 22 '15 at 21:09
@jonlink I've currently only a test environment with postgres, but this works with PDO and bindValue() as well. If it doesn't work for you, do you get any errors (see http://www.php.net/manual/en/pdo.error-handling.php / ERRMODE_EXCEPTION) – clemens321 Nov 22 '15 at 21:17

1 Answers1