Minus 7 Days From a Date

[Bopo]

Hi

 

Well basically I'm going to use a between clause in a query, to return records between two dates, todays date, and the date from 7 days ago, I can easily get today's date via:

 

$todaysdate = date('Y-m-d');

 

But how would I get the 7 days ago date? I'm presuming I would have to do something with $todaysdate and store the output in a variable.

 

Help appreciated.

 

 

[.josh]

examples in the manual for date shows how to get dates from past/future. http://www.php.net/date

[Bopo]

Thanks for the suggestion, I have managed to output the current date minus 7 days, but when trying the query out, nothing is being returned and I'm not getting any errors from PHP

 

 

<?php 

if($getdate == "7day") {
$sevendays = date ( $format, strtotime ( '-7 day' . $date ) );
$sql = "SELECT * FROM comments WHERE date BETWEEN '$todaysdate' AND '$sevendays'";


}

include("blogconnect.php");

$query = mysql_query($sql, $connect) or die (mysql_error()); 
while ($row = mysql_fetch_assoc($query)) {   //this is where the error is highlighted

echo $row['id'] . '<br /><br />';
echo $row['comments'] . '<br /><br />';


?>

 

I have a hunch feeling that maybe the dates are not being treated as actual dates, but this is only a guess, and I don't know how to figure out if there not.

[mike12255]

Make this be the first bit of code under the php opening tags and see if u get errors:

 

ini_set('error_reporting', E_ALL);

[Bopo]

Thanks for the suggestion, sadly I don't get any errors, here's all my current PHP, as maybe something else is causing it to fail.

 

<?php
ini_set('error_reporting', E_ALL);

session_start();

if($_SESSION['loggedin'] == "correct") {
echo "welcome";
} else {
header('Location: http://www.website.com/admin/login.php');
}

if(isset($_POST['submit'])) {

$getdate = $_POST['days'];
$todaysdate = date('Y-m-d'); 

$format = 'Y-m-d';
$date = date ( $todaysdate );


if($getdate == "7day") {
$sevendays = date ( $format, strtotime ( '-1 month' . $date ) );
$sql = "SELECT * FROM comments WHERE date BETWEEN '$todaysdate' AND '$sevendays'";


}

include("blogconnect.php");

$query = mysql_query($sql, $connect) or die (mysql_error()); 
while ($row = mysql_fetch_assoc($query)) {   //this is where the error is highlighted

echo $row['id'] . '<br /><br />';
echo $row['comments'] . '<br /><br />';

}
}
?>

 

SELECT * FROM comments WHERE date BETWEEN '2009-04-09' AND '2009-04-02'

[kenrbnsn]

To get today - 7 days:

<?php
$aweekago = date('Y-m-d',strtotime('-7 days'));
$today = date('Y-m-d');
?>

 

Ken

[Bopo]

Thanks for that, sadly it's still not working, I quickly did a SELECT * FROM comments, and all the rows were returned, therefore it's not a connection error or anything, also my dates are being stored in the DATE format, and are stored as YYYY-DD-MM

[Zane]

my dates are being stored in the DATE format, and are stored as YYYY-DD-MM

 

maybe something else is causing it to fail.

 

$format = 'Y-m-d';
$date = date ( $todaysdate );


if($getdate == "7day") {
$sevendays = date ( $format, strtotime ( '-1 month' . $date ) );

 

SELECT * FROM comments WHERE date BETWEEN '2009-04-09' AND '2009-04-02'

 

YYYY-DD-MM, eh

its seems as though you are formatting it as

YYYY-MM-DD

 

does this clear things up

[dadamssg]

try mktime()

 

something like

 

$sevendays=mktime ( int $month= date("n")  , int $day= date("j")-7  , int $year= date("Y") )

echo "date('M/j/Y    g:i a',strtotime($sevendays))";

 

 

[Bopo]

Thanks for the replies

 

dadamssg could I just store that in a variable or something, sorry but it really late here and I'm failing to see how to integrate it into my code.

[dadamssg]

$sql = "SELECT * FROM comments 
    WHERE date BETWEEN curdate() AND DATE(DATE_SUBTRACT(CURDATE(), INTERVAL 7 DAY))";

 

let mysql do your calcs

[Bopo]

Thanks for that, however I'm getting an error

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1

 

[Zane]

You have an error...in your SQL.....near '))' at line 1

 

hmmm....

[dadamssg]

maybe try switching them around...i dunno, im not the greatest at querying dates...might wanna post this in the mysql board...the guys in there have helped me with NUMEROUS date queries similar to this.

 

$sql = "SELECT * FROM comments
    WHERE date BETWEEN DATE(DATE_SUBTRACT(CURDATE(), INTERVAL 7 DAY)) AND curdate() ";

[Daniel0]

You can't just do "INTERVAL 7 DAY" alone. What's that supposed to mean? Go 7 days forward, go backwards, both?

 

 

SELET * FROM comments WHERE date BETWEEN CURDATE() AND CURDATE() - INTERVAL 7 DAYS;

[Bopo]

Okay either I'm a retard (probably) or MySQL hates me, but I'm getting another error with the query above

 

$sql = "SELECT * FROM comments WHERE date BETWEEN CURDATE() AND CURDATE() - INTERVAL 7 DAYS";

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DAYS' at line 1

[Zane]

take the S off of DAYS

 

MySQL should really fix a bug like that

 

The INTERVAL keyword and the unit specifier are not case sensitive.

 

The following table shows the expected form of the expr argument for each unit value.

unit Value Expected expr Format

MICROSECOND MICROSECONDS

SECOND SECONDS

MINUTE MINUTES

HOUR HOURS

DAY DAYS

WEEK WEEKS

MONTH MONTHS

QUARTER QUARTERS

YEAR YEARS

SECOND_MICROSECOND 'SECONDS.MICROSECONDS'

MINUTE_MICROSECOND 'MINUTES:SECONDS.MICROSECONDS'

MINUTE_SECOND 'MINUTES:SECONDS'

HOUR_MICROSECOND 'HOURS:MINUTES:SECONDS.MICROSECONDS'

HOUR_SECOND 'HOURS:MINUTES:SECONDS'

HOUR_MINUTE 'HOURS:MINUTES'

DAY_MICROSECOND 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'

DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS'

DAY_MINUTE 'DAYS HOURS:MINUTES'

DAY_HOUR 'DAYS HOURS'

YEAR_MONTH 'YEARS-MONTHS'

 

The return value depends on the arguments:

 

apparently it goes for everything