Show results for the week

mharrison · 12-12-2012, 06:27 PM

I'm trying to get my code to display all results entered into the table for the current week.

Right now my code looks like this:

Code:

$past = date('Y-m-d', strtotime('-1 weeks');
$result = mysql_query("SELECT * FROM patterns WHERE datetxt >= '$past' ORDER BY dsptxt");

however it keeps giving me errors no matter how I try to change the code. Right now the error is

Quote:

Parse error: syntax error, unexpected ';' in /homepages/22/d292640728/htdocs/crochet/phptest/new.php on line 9

I would like it to use Monday as the start day no matter what day of the week the query is run.

mharrison · 12-12-2012, 06:41 PM

I've also tried it this way:

Code:

$result = mysql_query("SELECT * FROM patterns WHERE YEARWEEK(datetxt = YEARWEEK(CURDATE())) ORDER BY dsptxt");

It doesn't error out, but doesn't display any results either and I have a test db it's connecting to where I have put 3 records in with the date being todays date.

mharrison · 12-12-2012, 06:45 PM

Figured it out...had my ()'s wrong.

Correctly works with

Code:

$result = mysql_query("SELECT * FROM patterns WHERE YEARWEEK(datetxt) = YEARWEEK(CURDATE()) ORDER BY dsptxt");

Fou-Lu · 12-12-2012, 07:19 PM

Glad to be of help! Hehehe.
The initial problem was here: $past = date('Y-m-d', strtotime('-1 weeks');. You're simply missing the ending ); however, using the functions within the query directly is IMO a much better option.

mharrison · 12-13-2012, 01:03 PM

Quote:

Originally Posted by Fou-Lu

Glad to be of help! Hehehe.
The initial problem was here: $past = date('Y-m-d', strtotime('-1 weeks');. You're simply missing the ending ); however, using the functions within the query directly is IMO a much better option.

Thanks! It always seems to be a missing element somewhere. Now, I do like the code I did end up getting working better, the question remains if I can use that code to display items in the database added 2 weeks ago, and 3 weeks ago.

It's basically for a "Recently Added" type page that shows "New this week", "New Last Week", and "New 3 Weeks Ago".

The 1st bit of code works, but when you get to the 2 and 3 weeks ago, it also displays everything from this week as well. Would like to isolate the query to just those items from 2 and 3 weeks in the past, if possible.

Fou-Lu · 12-13-2012, 02:52 PM

That's because of the operator in use. If you want to do that using PHP code, you need to select a date that is BETWEEN two other dates (one three weeks ago and one two weeks ago). Otherwise you get everything from three weeks ago to current.
MySQL can do that too, just use the date_add/date_sub or the overloads of the -/+ operators: WHERE datetxt BETWEEN CURDATE() - INTERVAL 3 WEEK AND CURDATE() - INTERVAL 2 WEEK looks like it would work.

YEARWEEK would work, except you need to check for the YEAR and you need to check when the current week is <= the provided week in the year (since then you have to go back and check last years records). This is easier to do if you manipulate the field itself, but you will slow down the query in doing so. So I think using BETWEEN is a better option.

mharrison · 12-14-2012, 01:48 AM

So are you saying it should look something like this?

Code:

$result = mysql_query("SELECT * FROM patterns WHERE datetxt BETWEEN CURDATE() - INTERVAL 2 WEEK AND CURDATE() ORDER BY dsptxt");

When I use that for both 2 and 3 week, it displays the same as my new this week query does.

Fou-Lu · 12-14-2012, 01:34 PM

Your missing the interval on the until date. That is the same as using datetxt >= CURDATE() - INTERVAL 2 WEEK. If you just want records from two weeks ago, but not from the current week, then you need to add the second interval condition.

mharrison · 12-14-2012, 03:50 PM

Quote:

Originally Posted by Fou-Lu

Your missing the interval on the until date. That is the same as using datetxt >= CURDATE() - INTERVAL 2 WEEK. If you just want records from two weeks ago, but not from the current week, then you need to add the second interval condition.

Ok, I understand what you are saying but at the same time I don't. At least, when it comes to how to write the query for it, I'm not sure what I need to do and where I need to do it. Heading back to google for some help on this query.

mharrison · 12-14-2012, 04:04 PM

GOT IT! I was re-reading the post and you specifically stated the answer, but I completely missed it.

Changed my code to look like this:

Code:

$result = mysql_query("SELECT * FROM patterns WHERE datetxt BETWEEN CURDATE() - INTERVAL 3 WEEK AND CURDATE() - INTERVAL 

2 WEEK

and BAM, works great!

Thanks!

Fou-Lu · 12-14-2012, 04:06 PM

That'll do it yep.
You can use variables to represent the intervals as well, which may be beneficial as you can calculate them based on the request.

mharrison · 12-14-2012, 04:08 PM

Quote:

Originally Posted by Fou-Lu

That'll do it yep.
You can use variables to represent the intervals as well, which may be beneficial as you can calculate them based on the request.

lol, one thing at a time...still very new to PHP/MySQL at least outside of what I have to do at work where I can just query the table with a GUI