Solved

MySQL Query For Day of Week

Posted on 2009-04-13
7
462 Views
Last Modified: 2013-12-12
Given a MySQL Table fields for date[DATE] and text[VARCHAR(255)], how can I formulate a MySQL query to get all the text of a day on the current week.

For example, Monday is the start of the week. I want to get all the data for monday. I then want to get the data for Tuesday (which would return nothing, because there hasn't been any data entered for tomorrow yet). And so on for the rest of the current week.

0
Comment
Question by:DVation191
  • 3
  • 2
  • 2
7 Comments
 
LVL 1

Expert Comment

by:joshbenner
Comment Utility
If you're building the query in PHP, then you just query for the data you need:

SELECT text FROM table WHERE date = '2009-04-13'

In PHP, your code might resemble:
<?php

$sql = 'SELECT text FROM table WHERE date = \''.date('Y-m-d').'\'';

$result = mysql_query($sql);

?>

Open in new window

0
 
LVL 14

Expert Comment

by:racek
Comment Utility
SELECT WEEKDAY(date) , xxxx
from yourTable
WHERE WEEK(date) = WEEK/NOW())
0
 
LVL 14

Assisted Solution

by:racek
racek earned 200 total points
Comment Utility

oops
 

SELECT WEEKDAY(date) , xxxx

from yourTable

WHERE WEEK(date) = WEEK(NOW())

Open in new window

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 20

Author Comment

by:DVation191
Comment Utility
The problem with selecting it by date explicitly is that I'll be dynamically displaying content using PHP and the "day of the week" will change week to week.


@racek, correct me if I'm wrong, but won't that just select all of today's data? I need all the data for the current week.
0
 
LVL 1

Accepted Solution

by:
joshbenner earned 300 total points
Comment Utility

<?php

$now = time();

$currentDOW = date('w', $now);

$wantedDOW = 3; // 0 = sunday, 6 = saturday

$diff = $wantedDOW - $currentDOW;

$date = date('Y-m-d', strtotime("$diff day", $now));

$sql = "SELECT text FROM table WHERE date = '$date'";

Open in new window

0
 
LVL 14

Expert Comment

by:racek
Comment Utility
WEEK(NOW()) ... this week
WEEK(date) ... week of date in tatabase ... date is column name

query returns all records from this week :-)

SELECT WEEKDAY(date) , xxxx
from yourTable
WHERE WEEK(date) = WEEK(NOW())
0
 
LVL 20

Author Comment

by:DVation191
Comment Utility
Although you both answered the question, I ended up using joshbenner's snippet to achieve my goal. Thank you both.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

763 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

7 Experts available now in Live!

Get 1:1 Help Now