Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Show results for the week

    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
    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.

  • #2
    New Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts
    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.

  • #3
    New Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts
    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");

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • Users who have thanked Fou-Lu for this post:

    mharrison (12-13-2012)

  • #5
    New Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    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.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • #7
    New Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts
    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.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • Users who have thanked Fou-Lu for this post:

    mharrison (12-14-2012)

  • #9
    New Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    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.

  • #10
    New Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts
    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!

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • #12
    New Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •