WHERE date BETWEEN not working

Question

I am trying to count logins by date range by counting how many times the auto integer (id) appears between a start and end date.

I get the start date and end date from a form in a previous page (y-m-d).

$start_date=$_POST['start_date']; /*in this case its "2014-10-10"*/
$end_date=$_POST['end_date']; /*in this case its "2014-10-20"*/


$sql = <<<SQL
SELECT id, COUNT(*) as login_count FROM `usage`  
GROUP BY id   
WHERE date
BETWEEN $start_date AND $end_date
SQL;

However I keep getting the following syntax error

"There was an error running the query [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE date BETWEEN 2014-10-10 AND 2014-10-20' at line 3]"

What am I doing wrong?

Unless you quote date strings, it looks like straight arithmetic, 2014 minus 10 minus 10 which gives 1994 — Mark Baker, Oct 20 '14 at 08:23
use brackets http://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql — Josua Marcel C, Oct 20 '14 at 08:23

score 1 · Answer 1 · answered Oct 20 '14 at 08:22

1

your $sql should be :

$sql = <<<SQL
SELECT id, COUNT(*) as login_count FROM `usage`  
WHERE date
BETWEEN $start_date AND $end_date
GROUP BY id   
SQL;

as GROUP BY CLAUSE is AFTER WHERE syntactically

answered Oct 20 '14 at 08:22

Satya

8,693
5
34
55

1

Should also quote the date values – Mark Baker Oct 20 '14 at 08:24

score 1 · Answer 2 · edited Oct 20 '14 at 09:03

1

You need to put ''s around date literals in sql like so:

$response = mysql_query("SELECT * FROM `db`.`$sql_table` WHERE (date BETWEEN '$end_date' AND '$start_date')  ORDER by id ASC ")or die(mysql_error());

edited Oct 20 '14 at 09:03

Rhumborl

16,349
4
39
45

answered Oct 20 '14 at 08:34

Jeffrey Yee

11
4

1

this does answer the question (apparently correctly), but it is more useful to to add a brief explanation of your answer. Just something like "You need to put quotes around date literals" would be enough. – Rhumborl Oct 20 '14 at 09:03
@Rhumborl, got it thanks. will do on my next answers. just new to stackoverflow. – Jeffrey Yee Oct 20 '14 at 09:10

score 1 · Accepted Answer · answered Oct 20 '14 at 08:34

1

try it 

$sql = <<<SQL
SELECT id, COUNT(*) as login_count FROM `usage`  
WHERE date
BETWEEN '$start_date' AND '$end_date'
GROUP BY id   
SQL;

answered Oct 20 '14 at 08:34

Hara Prasad

704
6
15

score 0 · Answer 4 · answered Oct 20 '14 at 08:22

0

Firstly you should quote your dates '$start_date' AND '$end_date' and you should definitely make sure those values are escaped to avoid SQL injection.

answered Oct 20 '14 at 08:22

Ukuser32

2,147
2
22
32

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. – skolima Oct 20 '14 at 09:45
Fair do's but no one has pointed out about SQL injection as this code would be very vulnerable? – Ukuser32 Oct 20 '14 at 11:24
Sure, but this should go into a comment :-) – skolima Oct 20 '14 at 13:08

score 0 · Answer 5 · answered Oct 20 '14 at 08:23

0

You have mistakes in your SQL. Try this:

SELECT id, COUNT(*) as login_count FROM `usage`  
WHERE (date BETWEEN $start_date AND $end_date)
GROUP BY id;

answered Oct 20 '14 at 08:23

Alternatex

1,505
4
24
47

WHERE date BETWEEN not working

5 Answers5