Select rows between two dates where dates are variables

Question

I have an SELECT statement in php and I need to select all rows of the table where the "date" column is in between a start date and end date that will be defined by variables.

I have this working perfectly fine when I define the dates directly in the SELECT statement as shown below:

date BETWEEN "2015-02-03" AND "2015-02-05"

However, when I try to do the same thing but with variables, it doesn't seem to work:

date BETWEEN "$startdate" AND "$enddate"

Where

$startdate = "2015-02-03";
$enddate = "2015-02-05";

Hope all this makes sense, Cheers in advance.

Full code snippet is here as requested:

$startdate = "2015-02-03";
$enddate = "2015-02-05";

$sql = 'SELECT record_number, date, manmod, description, cal, serial, datein, dateout, retdate, refex, refexdate, sellersname, sellersaddress, buyersname, buyersaddress, rfddealer, del, warranty, months FROM record WHERE del="Live" AND date BETWEEN "$startdate" AND "$enddate" ORDER BY record_number DESC';

Since the first query (without variable dates) works fine, the problem probably lies in PHP building the query. Can you verify that the query that is being executed, is OK? — Nic Wortel
– Nic Wortel, Commented Feb 6, 2015 at 12:29
Does using curly braces work? date BETWEEN "{$startdate}" AND "{$enddate}" — ZZ-bb
– ZZ-bb, Commented Feb 6, 2015 at 12:32

Nic Wortel · Accepted Answer · 2015-02-06 12:50:21Z

1

From the PHP website:

Note: Unlike the double-quoted and heredoc syntaxes, variables and escape sequences for special characters will not be expanded when they occur in single quoted strings.

In other words, since your query is in single quotes, the $startdate and $enddate variables are not interpreted as variables. The SQL query that is send to the database will literally contain $startdate and $enddate, and will therefore look something like this:

'SELECT column1, column2 FROM table WHERE date BETWEEN "$startdate" AND "$enddate" ORDER BY record_number DESC';

(I've simplified the query a bit for readability purposes)

Obviously, the database does not know how to interpret PHP variables, it will look for records with a date between those two strings, finds nothing and therefore returns 0 records.

In order to paste the contents of the variables in your SQL query, you will have to do one of two things:

Option 1: replace the single quotes with double quotes

If you choose this option, make sure that you either escape the existing double quotes, or change them into single quotes:

$sql = "SELECT column1, column2 FROM table WHERE date BETWEEN '$startdate' AND '$enddate' ORDER BY record_number DESC";

Option 2: concatenate the strings manually

You can also build op the query manually from multiple parts, and glue them together using PHP's concatenation operator, the dot (.).

$sql = 'SELECT column1, column2 FROM table WHERE date BETWEEN "' . $startdate . '" AND "' . $enddate;

edited Feb 6, 2015 at 12:50

answered Feb 6, 2015 at 12:35

Nic Wortel

11.4k7 gold badges63 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

al.tron Over a year ago

Ladies and Gentlemen we have a winner, take a bow sir.

Nic Wortel Over a year ago

Happy to help. Oh and, if the $startdate and $enddate variables are coming from user input, make sure you know what SQL injection is and how to prevent it. Otherwise, your application/website might be vulnerable for attacks.

Bill Gregg · Accepted Answer · 2015-02-06 12:26:35Z

0

You should convert it to a greater than and less than equation:

 date > '$startdate' AND date < '$enddate'

answered Feb 6, 2015 at 12:26

Bill Gregg

7,1572 gold badges24 silver badges40 bronze badges

1 Comment

al.tron Over a year ago

Hi, I have actually tried this before and it still wasn't working, thanks for the input though.

Kirk Beard · Accepted Answer · 2015-02-06 12:42:03Z

0

The issue isn't with your SQL, but with your PHP.

You need to read up on how string concatenation works and how PHP treats strings that use ' and " differently when wrapped around strings.

$sql = 'SELECT * FROM record WHERE date BETWEEN "$startdate" AND "$enddate"';

Should be changed to one of the following:

$sql = 'SELECT * FROM record WHERE date BETWEEN "' . $startdate . '" AND "' . $enddate . '"';

OR:

$sql = "SELECT * FROM record WHERE date BETWEEN '$startdate' AND '$enddate'";

I've simplified the SQL to highlight the real issue at hand.

answered Feb 6, 2015 at 12:42

Kirk Beard

9,89312 gold badges45 silver badges48 bronze badges

Collectives™ on Stack Overflow

Select rows between two dates where dates are variables

3 Answers 3

Option 1: replace the single quotes with double quotes

Option 2: concatenate the strings manually

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Option 1: replace the single quotes with double quotes

Option 2: concatenate the strings manually

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related