Filtering Database Query Using Time

Question

Hi so I have a PHP page which returns information and the time that information was inputted in.

What I am wanting to do is filter that time so for example only entries between now and six months ago appear, and I also wish to have another option which does between now and twelve months ago.

The information would be printed with json so that Ajax can make use of the data easily. I am posting a variable with the value 6 or 12 which determines the desired length however I seem to not be able to find any SQL statements which would work.

I have tried using the WEEKOFYEAR() SQL function to no avail it just threw errors.

$sql = "SELECT data, data2 FROM userdetails where userid = '$Id'";
$result = $conn->query($sql);

The field which is set as a date is called dateSet and I am thinking whether to use a between function within the SQL. The information is put into an array so doing it within the SQL statement would be beneficial.

Is this `mysqli` or PDO? If so, you really need to be using **prepared statements** and avoid putting values like `$Id` directly in your query. — tadman, Apr 24 '16 at 22:25
"between now and six months ago" erm, that would be "between six months ago and now" — Strawberry, Apr 24 '16 at 22:44

score 0 · Answer 1 · edited May 23 '17 at 12:16

0

Try filtering between dates

SELECT data, data2
FROM `userdetails
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')

as shown here How do I query between two dates using MySQL?

edited May 23 '17 at 12:16

Community

1
1

answered Apr 24 '16 at 22:54

hounded

666
10
21

I still need the userid to be there aswell so that it does not reveal all the table only ones relating to that user. Could I add it on the end? – User5916261 Apr 25 '16 at 15:38
.. AND WHERE uid = id – hounded Apr 25 '16 at 21:04

Filtering Database Query Using Time

1 Answers1