CODEWITHSUNDEEP
phpmysql
Khan
Khan

Reputation: 1518

How to select date range in mysql?

i want to select a date range in mysql.

$dateFrom='2016-01-01';
$dateTo='2016-01-02';

NOT WORKING

query ="SELECT ".$name.",timestamp FROM people where age=".$age." AND timestamp>=".$dateFrom." AND timestamp<.$dateTo";

WORKING

query ="SELECT ".$name.",timestamp FROM people where age=".$age." AND timestamp>='2016-01-01' AND timestamp<'2016-01-02';

Upvotes: 0

Views: 634

Answers (5)

S.Hafner
S.Hafner

Reputation: 7

"SELECT ".$name.",timestamp 
FROM people WHEREage=".$age." 
AND timestamp BETWEEN'".$datefrom."' AND'".$dateTo."'

Upvotes: 0

Cristian Gon&#231;alves
Cristian Gon&#231;alves

Reputation: 912

You've a sintax error on your (not working) code.

You can use sprintf function - http://php.net/manual/en/function.sprintf.php - to concat strings;

Live demo: http://ideone.com/8uWe6R

Example using sprintf to format SQL Query

Upvotes: 0

Virb
Virb

Reputation: 1578

Check the difference.

Your code:

query ="SELECT ".$name.",timestamp FROM people where age=".$age." AND timestamp>=".$dateFrom." AND timestamp<.$dateTo";

Updated code:

query ="SELECT ".$name.",timestamp FROM people where age=".$age." AND timestamp>='".$dateFrom."' AND timestamp<'".$dateTo."';

Upvotes: 0

Dave Cross
Dave Cross

Reputation: 69314

In situations like these, it's often a good idea to just print out the SQL that you have generated. For your failing code you will get something like this:

SELECT some_name,timestamp
FROM people where age=40
AND timestamp>=2016-01-01
AND timestamp<2016-01-02

See the timestamps? They are unquoted strings. The SQL compiler will probably try to do some kind of subtraction there (2016 - 1 - 1).

As Alok Patel has pointed out, one solution is to change your code to put quote characters around your dates. Another solution is to treat your dates as numbers, as numbers don't need to be quoted and MySQL is quite happy to use a date in the format 20160101 as a number.

But I have to reiterate what other people have said. Interpolating user input into an SQL string is a very bad idea. Please don't do that!

Upvotes: 1

Alok Patel
Alok Patel

Reputation: 8032

You will need to put ' single quotes arround dates variable.

query ="SELECT ".$name.",timestamp FROM people where age=".$age." AND timestamp>='".$dateFrom."' AND timestamp<'".$dateTo."';

Make sure you're open to SQL injection attacks, you should better use Prepared statements to avoid the same.

Upvotes: 5

Related Questions