Retriving Records From Past Dates

[Bopo]

Hi everyone

 

Well basically I am allowing user to comment on pages, these are obviously stored in a MySQL DB with an ID and date, however I want there to be a combo box to show comments after certain periods of time e.g

 

Today

Yesterday

Past 7 Days

Month

All

 

Basically I'm struggling on the logic of how the sql query will work, I suppose turning back todays date by X amount depending on which of the options are select above could logically work, but I don't know how to write it

 

Help appreciated.

[taquitosensei]

something along these lines

$sql="select field1,field2 from table where date_format(datecolumn,'%Y-%m-%d') > '".date("Y-m-d", strtotime("-7 days"))."'"; 

would get you all the posts in the past 7 days

"-1 Days"  would get you yesterday

"-30 Days" would get you last 30 days

this would get last months

$sql="select field1,field2 from table where date_format(datecolumn,'%Y-%m-%d')='".date("Y-m",strtotime("-1 Month"))."'"; 

 

 

 

[gnawz]

MySQL DATE() functions....

Simple, effective and stable

 

DATESUB();  CURDATE(); DAY(); MONTH(); YEAR();

 

You can play with these

 

Examples I have used here:

 

 

 

<?
// XXXXXXXXXxDAYS
//$day being a variable for number of days...

$sql = "SELECT * FROM yourdb WHERE DATE_SUB(CURDATE(),INTERVAL '$day' DAY) <= datefieldtosubtractfrom";
?>
// XXXXXXXXXX  MONTH

// $month being month number from 1 t0 12

$sql = "SELECT * FROM yourdb WHERE MONTH(dbfield) = '$month' AND YEAR(dbfield) = YEAR(CURDATE())";

// XXXXXXX   YEAR

$sql = "SELECT * FROM yourdb WHERE DATE_SUB(CURDATE(),INTERVAL '$year' YEAR) <= dbfield";

 

 

I'm sure this will help

 

[Bopo]

Thanks for the useful posts, I'm getting a script error regarding a different line or than the sql query, and I'm 99% confident that the query is causing the problem, could someone look at my code and tell me where I'm going wrong, thanks.

 

<?php

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

$getdate = $_POST['days'];
$todaysdate = date("m.d.y"); 

if($getdate = "20day") {
$day = 30;
$sql = "SELECT * FROM comments WHERE date(CURDATE(), INTERVAL '$day' DAY) <= $todaysdate";  //date = column field in table

echo $sql;
}

include("blogconnect.php");

$query = mysql_query($sql, $connect); 
while ($row = mysql_fetch_assoc($query)) {

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

}
}
?>

[Bopo]

Can I get an opinion on if that looks correct, I can't find many examples to compare to sadly.

[taquitosensei]

this

if($getdate = "20day") {
   $day = 30;
   $sql = "SELECT * FROM comments WHERE date(CURDATE(), INTERVAL '$day' DAY) <= $todaysdate";  //date = column field in table
   
echo $sql;
}

should be this

if($getdate == "20day") {
   $day = 30;
   $sql = "SELECT * FROM comments WHERE date(CURDATE(), INTERVAL '$day' DAY) <= $todaysdate";  //date = column field in table
   
echo $sql;
}

as a comparison the equals sign needs to be a double or if you're also comparing type it's a triple. Not sure about the syntax on the query.

[gnawz]

I do not see your db field

 

Try

 

<?php

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

$getdate = $_POST['days'];
$todaysdate = date("m.d.y"); 

if($getdate = "20day") {
   $day = 30;
   $sql = "SELECT * FROM comments WHERE date(CURDATE(), INTERVAL '$day' DAY) <= your db field to compare with";  //date = column field in table
   
echo $sql;
}

include("blogconnect.php");

$query = mysql_query($sql, $connect); 
while ($row = mysql_fetch_assoc($query)) {
   
echo $row['id'] . '<br /><br />';
echo $row['comments'] . '<br /><br />';

}
}
?>