How do I select a seven day interval from DB

kiwi2

Database
fields
user VARCHARect
pass VARCHARect
fr DATE
tom DATE
at TIME
ect, ect
MY PROBLEM IS A DATE SELECTION ONE;
How do I select a 7 day interval I've tried;
$SQL = "SELECT *, curdate() AS c,
dayofweek(curdate()) AS d FROM $table_name WHERE date BETWEEN date_sub(c INTERVAL (d -1) DAYS) AND date_add(c INTERVAL (7-c) DAYS";
BUT my while statement claims that fetch_array is not a valid resource.
My Input data for DATE is YYYY-MM-DD AND TIME HH:MM:SS ,
I need to print out an event schedule which I plan to match to image reminders per day per date this I can do by forcing an array from the table data and matching it to a simple getdate() funct but HOW THE $#%&^ DO I GET A SEVEN DAY INTERVAL FROM MY TABLE DATA!!!???

😃

troybtj

misread format of database and was using UNIXTIME here...

troybtj

Just noticed your date is in the correct format. In your DATE_ADD Function, you are missing the comma after the first c.


$SQL = "SELECT *, curdate() AS c,
dayofweek(curdate()) AS d FROM $table_name 
WHERE date BETWEEN date_sub(c, INTERVAL (d -1) DAY) 
AND date_add(c, INTERVAL (7-c) DAY) ";

--ETA: This code doesn't work because c and d aren't recognized in the functions after 'WHERE'.... I'm working on remembering now...

troybtj

I have up on Variables....



SELECT *  FROM $table_name WHERE date BETWEEN date_sub(curdate(), INTERVAL (dayofweek(curdate())-1) DAY) AND date_add(curdate(), INTERVAL (7-(curdate())) DAY);

Seems to work.