MySQL current date between 2 set dates

Question

I need some help with a project I'm working on. There's a table with 2 dates: date1 and date2 (easier)

Now I need to show all rows where the current date is between date1 and date2. What I have so far is:

$date = date(Y-m-d);
$sql = 'SELECT * FROM boekingen WHERE "$date" BETWEEN date1 AND date2';

but this doesn't work. Although if I replace "$date" with 2017-01-06 it does work. Now how do I solve this problem? Thanks in advance!

How do I create SQL injection vulnerabilities in PHP? You should be using mysqli or PDO. A basic tutorial for either should tell you how to run a query with parameters. see: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php (Note: I'm a C# guy, so maybe there are better libraries, but these used to work) — ebyrob, Jan 06 '17 at 19:19
Did you not read the manual first before posting? http://php.net/manual/en/function.date.php what you have now will outright throw you an error about an `undefined Y constant` and `m` and `d` with `assumed 'Y'...` and `m` and `d`.................. — Funk Forty Niner, Jan 06 '17 at 19:21
*"Now how do I solve this problem?"* - You use the proper syntax. `date("Y-m-d")` — Funk Forty Niner, Jan 06 '17 at 19:25

score 1 · Answer 1 · answered Jan 06 '17 at 19:35

The manual on date() is clear as to its syntax and using quotes around the arguments.

http://php.net/manual/en/function.date.php

Since yours has none, PHP is assuming you have them pre-defined as constants.

Error reporting would have thrown you the following:

Notice: Use of undefined constant Y - assumed 'Y' in /path/to/file.php on line x
Notice: Use of undefined constant m - assumed 'm' in /path/to/file.php on line x
Notice: Use of undefined constant d - assumed 'd' in /path/to/file.php on line x

Examples taken from Example #4 date() Formatting from the manual:

<?php
// Assuming today is March 10th, 2001, 5:16:18 pm, and that we are in the
// Mountain Standard Time (MST) Time Zone

$today = date("F j, Y, g:i a");                 // March 10, 2001, 5:16 pm
$today = date("m.d.y");                         // 03.10.01
$today = date("j, n, Y");                       // 10, 3, 2001
$today = date("Ymd");                           // 20010310
$today = date('h-i-s, j-m-y, it is w Day');     // 05-16-18, 10-03-01, 1631 1618 6 Satpm01
$today = date('\i\t \i\s \t\h\e jS \d\a\y.');   // it is the 10th day.
$today = date("D M j G:i:s T Y");               // Sat Mar 10 17:16:18 MST 2001
$today = date('H:m:s \m \i\s\ \m\o\n\t\h');     // 17:03:18 m is month
$today = date("H:i:s");                         // 17:16:18
$today = date("Y-m-d H:i:s");                   // 2001-03-10 17:16:18 (the MySQL DATETIME format)
?>

score 1 · Accepted Answer · edited May 23 '17 at 11:53

1

You need to put quotes around the argument to date():

$date = date('Y-m-d');

And you need to wrap the string you assign to $sql in double quotes, otherwise the $date variable won't be expanded.

$sql = "SELECT * FROM boekingen WHERE '$date' BETWEEN date1 AND date2";

What is the difference between single-quoted and double-quoted strings in PHP?

edited May 23 '17 at 11:53

Community

1
1

answered Jan 06 '17 at 19:39

Barmar

741,623
53
500
612

score 0 · Answer 3 · answered Jan 06 '17 at 19:44

0

Use something like this

$date = date(Y-m-d);
$sql = 'SELECT * FROM boekingen WHERE STR_TO_DATE(\'$date'\', \'%m/%d/%Y\') BETWEEN date1 AND date2';

Make sure that date2 is greater than date1.

answered Jan 06 '17 at 19:44

score 0 · Answer 4 · answered Jan 06 '17 at 19:54

date = date(Y-m-d);
$sql = 'SELECT * FROM boekingen WHERE "$date"

That is the code on your question.

Using a single quote for string in php will not expand the variable.

Either use double quotes and single quotes around $date or concatenate eg

 $sql = 'SELECT * FROM boekingen WHERE "' . $date .'"

Or sprintf

 $sql = sprintf('SELECT * FROM boekingen WHERE "%s", $date)

MySQL current date between 2 set dates

4 Answers4