CODEWITHSUNDEEP
mysqldategroup-bysql-order-by
8guadalupe8
8guadalupe8

Reputation: 25

MySQL PHP - group results by day/month/year?

I have a table (Logs) in which I keep logs of what happens on my server, and I wanted to make a function that would sort the results I got.. To explain better:

My query is. 

SELECT * 
  FROM Logs 
  WHERE username = '$user' 
  ORDER BY cur_timestamp DESC

(cur_timestamp is a field in my log table, the timestamp of record insertion)
($user is the username (set previously))

This returns an array as usual of all the records in descending order. Now what I want to do, is to query the database and get it to only return records that are within a certain day or month, etc, for example, if the user selects a month, I want it to return all the records in only that month.

I have been trying to research what to do, and things seem to be pointing for me to use the GROUP BY technique, except this only returns one result. I'm not so sure. Any help would be appreciated! Thanks!

Upvotes: 0

Views: 1694

Answers (2)

juergen d
juergen d

Reputation: 204774

Example (Logs in Januar 2012):

SELECT * 
FROM Logs 
WHERE username = '$user' 
and cur_timestamp between '2012-01-01' and '2012-01-31'
ORDER BY cur_timestamp DESC

Only Today:

SELECT * 
FROM Logs 
WHERE username = '$user' 
and date(cur_timestamp) = curdate()
ORDER BY cur_timestamp DESC

If you want to a specific time from the past til now you can use DATEADD.

Example (Logs of last 30 days):

SELECT * 
FROM Logs 
WHERE username = '$user' 
and date(cur_timestamp) >= DATE_ADD(curdate(), INTERVAL -30 DAY)
ORDER BY cur_timestamp DESC

Upvotes: 3

Katai
Katai

Reputation: 2937

If I understand your problem correctly, I dont think that you need to GROUP BY, but just to add a WHERE to select all Logs in a certain time range

SELECT * 
FROM Logs 
WHERE username = '$user'
AND cur_timestamp > '2010-01-01'
AND cur_timestamp < '2012-12-01'
ORDER BY cur_timestamp DESC

The only thing you need to do, is to adjust your date ranges. No need for any GROUP BY

Upvotes: 0

Related Questions