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 3 of 3
  1. #1
    kjc
    kjc is offline
    New Coder
    Join Date
    Jun 2002
    Location
    England
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting data that was entered in the previous week

    I am building an e-commerce site for a client and require a page that will show products that were added to the database in the last week/ two weeks etc.

    When a product is added to the database it has three seperate fields storing the date added in(01 - 31 format) month (January-December) and year( xxxx).

    Is there anyway that I can run a mysql/php query that will just retrieve those products entered within the last xx weeks?

    All help greatefully appreciated.

    Kyle

  • #2
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,280
    Thanks
    4
    Thanked 83 Times in 82 Posts
    This is probably more an SQL question than a PHP question. It really would just require writing and SQL statement that takes todays date and then subtracts a week from it and pulling everything based off of that. I'll move this over to the SQL forum...
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #3
    Super Moderator
    Join Date
    May 2002
    Location
    Perth Australia
    Posts
    4,108
    Thanks
    11
    Thanked 101 Times in 99 Posts
    HI, is it too late to change the database format? reason I ask is its really much better for several reasons to store any date information as a MySQL or UNIX TIMESTAMP

    why?
    well MySQL can perform data calculations on all valid TIMESTAMPS i.e.

    "SELECT * FROM $table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30"

    which would give you the records from the last 30 days etc.


    As you are at the moment it is going to be awkward and innefficient to call stuff, this query will be slow unless all 3 fields are indexed (on a medium/large dataset)

    $day=15;
    $month=6;
    $year=2002;

    "SELECT * FROM $table WHERE month>='$month' && year>='$year && $day>='$day'"

    though it will work, but is there any reason you want 3 fields for the date? & can you change that now ?

    I have a full set of fucntions that I use for manipulating form data into MYSQL timestamps for data insertion if you need them.
    resistance is...

    MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)


  •  

    Posting Permissions

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