CODEWITHSUNDEEP
phpmysql
user1231561
user1231561

Reputation: 3359

Count mysql records from this week - Monday - Sunday

Im currently trying to count/print out how many records I have in my database from this week (mon - sun) based on a unix timestamp.

I've had a go at the following, but just seems to print out 0 for me (so something is not right)

$sql = "
SELECT count(*) as totalWeek
FROM vote WHERE
WEEKOFYEAR(vote_time)=WEEKOFYEAR(CURDATE())";

$res = mysql_query($sql) or die( mysql_error());
$countWeek = mysql_result($res, 0);

echo $countWeek;

Upvotes: 1

Views: 777

Answers (3)

Danijel
Danijel

Reputation: 12689

You can use php strtotime function in your query to get all records between these two dates:

$start = date( 'Y-m-d H:i:s', strtotime("last Monday") );
$end = date( 'Y-m-d H:i:s' );

$sql= "SELECT count(*) as totalWeek FROM vote WHERE (vote_time BETWEEN '$start' AND '$end')";

Upvotes: 2

BellevueBob
BellevueBob

Reputation: 9618

If your vote_time column is a timestamp, you may need to convert it to a date. Also, I think you should also modify your condition to check the current year (unless your data will not span calendar years). So, try this:

SELECT count(*) as totalWeek
FROM vote 
WHERE WEEKOFYEAR(DATE(vote_time)) = WEEKOFYEAR(CURDATE())
  AND       YEAR(DATE(vote_time)) =       YEAR(CURDATE())

Upvotes: 2

mogul
mogul

Reputation: 4553

if your vote_time is in unix time format (seconds since 1970), you will have to translate them first:

mysql> select WEEKOFYEAR(from_unixtime(unix_timestamp()));
+---------------------------------------------+
| WEEKOFYEAR(from_unixtime(unix_timestamp())) |
+---------------------------------------------+
|                                          21 |
+---------------------------------------------+

Notice the from_unixtime call in there

Upvotes: 2

Related Questions