CODEWITHSUNDEEP
phpmysql
Austin Gayler
Austin Gayler

Reputation: 4356

Get MySQL database entries within last week

I am working with PHP and MySQL to develop a web app. Users can do activities in it, and the activities are logged into a database with the date and the user who did the activity. I need a query that finds all of the activities done by a given user in the last week (the date column is a unix timestamp).

Upvotes: 0

Views: 1069

Answers (4)

arheops
arheops

Reputation: 15259

There are also nice functions date_sub and date_add

So your query can be writed as

SELECT * FROM tableName WHERE timeColumn > UNIX_TIMESTAMP( date_sub(now(),interval 1 week)) and UserName = 'givenUserName'

Upvotes: 1

FMCorz
FMCorz

Reputation: 2706

Using strtotime() will help you just what you need. Considering that your week start on Monday, this should do the trick:

$referenceDate = time();
$lastWeek = date('-7 days', $referenceDate);

if (date('w') != 1) {
  $lastWeekBegins = date('last Monday', $lastWeek);
} else {
  $lastWeekBegins = $lastWeek;
}

if (date('w') != 0) {
  $lastWeekEnds = date('next Sunday', $lastWeek);
} else {
  $lastWeekEnds = $lastWeek;
}

$query = "SELECT * FROM activity WHERE date >= '{$lastWeekBegins}' AND date <= '{$lastWeekEnds}'";

Upvotes: 0

jmishra
jmishra

Reputation: 2081

Try this 

       $lastWeek=strtotime("-1 week");         
         $lastWeekActivityQuery="SELECT * FROM activity WHERE activityDate BETWEEN '$lastWeek' AND NOW() ORDER BY activityDate DESC LIMIT 100 ";
         $result=mysql_query($lastWeekActivityQuery);

Making use of the fact that you are storing dates in TIMESTAMP format

Upvotes: 0

Niros
Niros

Reputation: 642

This is the query you need. replace tableName and TimeColumn with the real names

SELECT * FROM tableName WHERE timeColumn > UNIX_TIMESTAMP() - 24 * 3600 * 7 and UserName = 'givenUserName'

Upvotes: 2

Related Questions