Using PHP/MySQL
I'm trying to create a select statement that gets the data from the least day of the current week (I'm using it to show data on a certain player 'this week'). The week starts on Sunday. Sundays's data may not always exist therefore if the Sunday data isn't found then it would use the next earliest day found, Monday, Tuesday, etc.
My date column is named 'theDate' and the datatype is 'DATE'
The query would need to be something like:
SELECT *
FROM table_name
WHERE name = '$username'
AND [...theDate = earliest day of data found for the current week week]
LIMIT 1
It would return a single row of data.
This is a query I tried for getting the 'this week' data, It doesn't seem to work correctly on Sunday's it shows nothing:
SELECT *
FROM table_name
WHERE playerName = '$username'
AND YEARWEEK(theDate) = YEARWEEK(CURRENT_DATE)
ORDER BY theDate;
This is the query that I'm using to get 'this months' data and it works even if the first day of the months data is not found, it will use the earliest date of data found in the current month/year (this query works perfect for me):
SELECT *
FROM table_name
WHERE playerName = '$username'
AND theDate >= CAST( DATE_FORMAT( NOW(),'%Y-%m-01') AS DATE)
ORDER BY theDate
LIMIT 1
Without trying this, you probably need an inner query:
select *
from table_name tn
where tn.the_date =
(select min(the_date)
from table_name
where WEEKOFYEAR(the_date) = WEEKOFYEAR(CURDATE())
and YEAR(the_date) = YEAR(CURDATE()))
viz, give me the row(s) in the table with a date equal to the earliest date in the table in the current week and year.
Try the following, replace YOUR_DATE with the date from the column you want (theDate):
SELECT ADDDATE(YOUR_DATE, INTERVAL 1-DAYOFWEEK(YOUR_DATE) DAY)
FirstDay from dual
Did you try:
SELECT ADDDATE(theDate , INTERVAL 1-DAYOFWEEK(theDate ) DAY) FirstDay
FROM table_name
WHERE playerName = '$username'
ORDER BY theDate DESC
LIMIT 1
Try this
SELECT * FROM table_name WHERE name = '$username'
AND your_data IS NOT NULL
AND WEEK(the_date,0 = WEEK(NOW(),0))
ORDER BY DATE_FORMAT(the_date,'%w') ASC