Select records from today, this week, this month php mysql
PhpMysqlDateSelectPhp Problem Overview
I imagine this is pretty simple, but can't figure it out. I'm trying to make a few pages - one which will contain results selected from my mysql db's table for today, this week, and this month. The dates are entered when the record is created with date('Y-m-d H:i:s');
. Here's what I have so far:
day where date>(date-(606024))
"SELECT * FROM jokes WHERE date>(date-(60*60*24)) ORDER BY score DESC"
week where date>(date-(606024*7))
"SELECT * FROM jokes WHERE date>(date-(60*60*24*7)) ORDER BY score DESC"
month (30 days) where date>(date-(606024*30))
"SELECT * FROM jokes WHERE date>(date-(60*60*24*30)) ORDER BY score DESC"
Any ideas would be much appreciated. Thanks!
Php Solutions
Solution 1 - Php
Assuming your date column is an actual MySQL date column:
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY score DESC;
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK) ORDER BY score DESC;
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 MONTH) ORDER BY score DESC;
Solution 2 - Php
Try using date and time functions (MONTH(), YEAR(), DAY(), MySQL Manual)
This week:
SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW());
Last week:
SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW())-1;
Solution 3 - Php
Current month:
SELECT * FROM jokes WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date)=MONTH(NOW());
Current week:
SELECT * FROM jokes WHERE WEEKOFYEAR(date) = WEEKOFYEAR(NOW());
Current day:
SELECT * FROM jokes WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW()) AND DAY(date) = DAY(NOW());
This will select only current month, really week and really only today :-)
Solution 4 - Php
Nathan's answer will give you jokes from last 24, 168, and 744 hours, NOT the jokes from today, this week, this month. If that's what you want, great, but I think you might be looking for something different.
Using his code, at noon, you will get the jokes beginning yesterday at noon, and ending today at noon. If you really want today's jokes, try the following:
SELECT * FROM jokes WHERE date >= CURRENT_DATE() ORDER BY score DESC;
You would have to do something a little different from current week, month, etc., but you get the idea.
Solution 5 - Php
Everybody seems to refer to date being a column in the table.
I dont think this is good practice. The word date might just be a keyword in some coding language (maybe Oracle) so please change the columnname date to maybe JDate.
So will the following work better:
SELECT * FROM jokes WHERE JDate >= CURRENT_DATE() ORDER BY JScore DESC;
So we have a table called Jokes with columns JScore and JDate.
Solution 6 - Php
A better solution for "today" is:
SELECT * FROM jokes WHERE DATE(date) = DATE(NOW())
Solution 7 - Php
Nathan's answer is very close however it will return a floating result set. As the time shifts, records will float off of and onto the result set. Using the DATE()
function on NOW()
will strip the time element from the date creating a static result set. Since the date()
function is applied to now()
instead of the actual date
column performance should be higher since applying a function such as date()
to a date column inhibits MySql's ability to use an index.
To keep the result set static use:
SELECT * FROM jokes WHERE date > DATE_SUB(DATE(NOW()), INTERVAL 1 DAY)
ORDER BY score DESC;
SELECT * FROM jokes WHERE date > DATE_SUB(DATE(NOW()), INTERVAL 1 WEEK)
ORDER BY score DESC;
SELECT * FROM jokes WHERE date > DATE_SUB(DATE(NOW()), INTERVAL 1 MONTH)
ORDER BY score DESC;
Solution 8 - Php
I think using NOW()
function is incorrect for getting time difference. Because by NOW()
function every time your are calculating the past 24 hours.
You must use CURDATE()
instead.
function your_function($time, $your_date) {
if ($time == 'today') {
$timeSQL = ' Date($your_date)= CURDATE()';
}
if ($time == 'week') {
$timeSQL = ' YEARWEEK($your_date)= YEARWEEK(CURDATE())';
}
if ($time == 'month') {
$timeSQL = ' Year($your_date)=Year(CURDATE()) AND Month(`your_date`)= Month(CURDATE())';
}
$Sql = "SELECT * FROM jokes WHERE ".$timeSQL
return $Result = $this->db->query($Sql)->result_array();
}
Solution 9 - Php
Well, this solution will help you select only current month, current week and only today
SELECT * FROM games WHERE games.published_gm = 1 AND YEAR(addedon_gm) = YEAR(NOW()) AND MONTH(addedon_gm) = MONTH(NOW()) AND DAY(addedon_gm) = DAY(NOW()) ORDER BY addedon_gm DESC;
For Weekly added posts:
WEEKOFYEAR(addedon_gm) = WEEKOFYEAR(NOW())
For Monthly added posts:
MONTH(addedon_gm) = MONTH(NOW())
For Yearly added posts:
YEAR(addedon_gm) = YEAR(NOW())
you'll get the accurate results where show only the games added today, otherwise you may display: "No New Games Found For Today". Using ShowIF recordset is empty transaction.
Solution 10 - Php
To get last week's data in MySQL. This works for me even across the year boundries.
select * from orders_order where YEARWEEK(my_date_field)= YEARWEEK(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK));
To get current week's data
select * from orders_order where YEARWEEK(date_sold)= YEARWEEK(CURRENT_DATE());
Solution 11 - Php
I suggest you to do like that:
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY score DESC;
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK) ORDER BY score DESC;
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 MONTH) ORDER BY score DESC;
Solution 12 - Php
You can do same thing using single query
SELECT sum(if(DATE(dDate)=DATE(CURRENT_TIMESTAMP),earning,null)) astodays,
sum(if(YEARWEEK(dDate)=YEARWEEK(CURRENT_DATE),earning,null)) as weeks,
IF((MONTH(dDate) = MONTH(CURRENT_TIMESTAMP()) AND YEAR(dDate) = YEAR(CURRENT_TIMESTAMP())),sum(earning),0) AS months,
IF(YEAR(dDate) = YEAR(CURRENT_TIMESTAMP()),sum(earning),0) AS years,
sum(fAdminFinalEarning) as total_earning FROM `earning`
Hope this works.