CODEWITHSUNDEEP
phpsql
PaperBagged
PaperBagged

Reputation: 177

How do I automatically display data by current week and last week?

I have a this weeks current totals, from monday to monday and Last weeks totals. I have been manually updating the timestamps to reflect the current and last week. I would like have it just pull the data from the current week from monday to monday and last weeks from monday to monday

This weeks code.

echo "This Week&nbsp;<br />";
$result = mysql_query("SELECT * FROM form_2 WHERE timestamp BETWEEN '2011-11-7' AND '2011-11-14' AND checkcollec = 'yes'");
$num_rows = mysql_num_rows($result);

echo "<b>Checks Collected:</b> $num_rows &nbsp;";

Last weeks Code.

echo "<hr />Last Week&nbsp;<br />";
$result = mysql_query("SELECT * FROM form_2 WHERE timestamp BETWEEN '2011-10-31' AND '2011-11-07' AND checkcollec = 'yes'");
$num_rows = mysql_num_rows($result);

echo "<b>Checks Collected:</b> $num_rows &nbsp;";

How can I do this so I do not have to keep changing it manually each week.

Upvotes: 1

Views: 2832

Answers (5)

Jams Katropolitan
Jams Katropolitan

Reputation: 1

I think the question is about showing data in current week. TS said that data show from monday to monday and last weeks from monday to monday. in my suggest, we can use WEEKOFYEAR function to get data in a week.

This Week:

SELECT * 
FROM form_2 
WHERE WEEKOFYEAR(timestamp)=WEEKOFYEAR(CURDATE()) 
    AND checkcollec = 'yes'

Last Week:

SELECT *
FROM form_2 
WHERE WEEKOFYEAR(timestamp)=WEEKOFYEAR(CURDATE())-1  
    AND checkcollec = 'yes'

Upvotes: 0

Flo
Flo

Reputation: 1671

All other soltions do give the results for the last 7 days, as I understand you want the week from monday to monday and the week before that. Depending on which sql server you have, you can use a week() function like

WHERE week(timestamp) = week(NOW())

The syntax may be different and maybe you need to set a second parameter for week so that it starts with monday.

Upvotes: 2

flesk
flesk

Reputation: 7579

This week:

SELECT * FROM form_2 WHERE timestamp BETWEEN curdate() - weekday(curdate()) AND curdate() - weekday(curdate()) + 7 AND checkcollec = 'yes'

Last week:

SELECT * FROM form_2 WHERE timestamp BETWEEN curdate() - weekday(curdate()) - 7 AND curdate() - weekday(curdate()) AND checkcollec = 'yes'

Upvotes: 0

Bjoern
Bjoern

Reputation: 16304

Change the SQL-Part to

SELECT * 
FROM form_2 
WHERE TIMESTAMP BETWEEN DATE(NOW()) AND DATE_ADD(DATE(NOW()), INTERVAL -7 DAY) 
    AND checkcollec = 'yes';

If NOW() is not the date-variable you need, just substitute it with a variable of your needs, p.e. while setting it in your php script somewhere.

Upvotes: 5

Jeune
Jeune

Reputation: 3538

$today = date("Y-m-d");
$todayMinusSeven = date("Y-m-d",strtotime("-7 days"));

$query = "SELECT * FROM form2 WHERE timestamp BETWEEN '$todayMinusSeven' AND 
    '$today' AND checkcollec = 'yes'";

echo $query;

Upvotes: 4

Related Questions