How can i sort mysql records by year/month/week?

Posted on 2007-10-17
Last Modified: 2013-12-13
Hello. I made an events system, i add the event in the database with the following fields: id, title, content, date. And on the site i have 3 buttons: week's events, month's events and archive. What i want to do is first to build the archive button, to display year, after i click the year to display the months, after i click the month to display the weeks and after i click the week to list events by title. If i make this i think i can make week's events and month's events by myself. I appreciate your time and i wait for feedback. Thanks in advance.
Question by:dadone
    LVL 24

    Accepted Solution

    if you want to select by year, do

    SELECT * FROM table where year(`date`)=2007;

    By month

    SELECT * FROM table where year(`date`)=2007 and month(`date`)=4;

    For week, you have to know the week number

    SELECT * FROM table where weekofyear(`date`)=20;


    Author Comment

    Thank you for reply. I was thinking more like automatic thing. The ideea is: i have the archive page where i display years like, let's say:
    every year has a link like, let's say index.php?year=2007
    on the new page i see 12 months, every month with link like index.php?year=2007&month=
    after i select the mont i was hoping to get the weeks from that month in a new page, but i could just get only the days too(showing weeks here, would help me when i make week's events and month's events).Right now the system works as SELECT * FROM tabel ORDER BY id DESC.
    I just don't know how to start making this system :(.Any other replies are appreciated.Cheers.
    LVL 17

    Expert Comment

    Have the web page run that sql query when the page is loaded from the link.
    LVL 17

    Assisted Solution

    $year = intval($_GET['year']);

    $sql = "SELECT * FROM table where year(`date`)=$year;";

    then have it run the query, and return the results at the start of index.php

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    The viewer will learn how to count occurrences of each item in an array.
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now