Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2007-10-17
Medium Priority
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
  • 2
LVL 24

Accepted Solution

mankowitz earned 252 total points
ID: 20092534
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

ID: 20093194
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

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

Assisted Solution

nplib earned 248 total points
ID: 20094719
$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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

575 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