Generating reports based on date/time conditions

[gnawz]

 

Hi developpers,

I need to generate reports in three ways:

 

I have a Date field in my database...

 

today

$query = 'SELECT * FROM 'table' WHERE Date = today';

 

Past seven days

$query = 'SELECT * FROM 'table' WHERE Date - 7';

 

Choose month( drop down preferably)

 

$query = 'SELECT * FROM 'table' WHERE Date = "'.$_POST['monthselected'].'"';

 

Please help on how to do the SQLs based on date

 

 

[Caesar]

How are your dates stored in the database? Make sure you provide a little bit of background, code, goals or details before asking certain questions...as it may make it easier for the crazies here to answer them.

[westmatrix99]

I use this on one of my sites to check if the user is activated within 3 days (72Hours) if not they get deleted.

 

<?php 
$sql = "SELECT * FROM tbltime";
$result = mysql_query($sql);
while($row_rstime = mysql_fetch_array($result)){
$activated = $row_rstime['active'];
$when = strtotime($row_rstime['date_joined']); // "2007-05-24 20:50:53"
if ($activated == 'Y' || time() < ($when+60*60*24*3)) {  
echo 'Whatever'<br>';
}else{
//Get the date and hour, 72 hours ago
$minus = strtotime("-72 hours");
$date = date("Y-m-d H:i:s", $minus);
$query = "Do something here if needed";
$delete = mysql_query($query);
echo 'Whatever'<br>';
}
}
?>

 

Hope this will help you get an idea?

Otherwise search php.net for date and time functions.

 

Cheers

West

[gnawz]

I have a Date field in my database...

 

today

$query = 'SELECT * FROM 'table' WHERE Date = today';

 

Past seven days

$query = 'SELECT * FROM 'table' WHERE Date - 7';

 

Choose month( drop down preferably)

 

$query = 'SELECT * FROM 'table' WHERE Date = "'.$_POST['monthselected'].'"';

 

Please help on how to do the SQLs based on date

 

my dates are stored in mysql with field type DATETIME so they give date and time.

 

 

 

 

[westmatrix99]

Post your special date format here, then relook at the code I posted.

 

Cheers

West

[akitchin]

use MySQL to your full advantage here.  you have several functions at your disposal:

 

NOW() gives the current date and time.

 

DATE_FORMAT() adjusts the date column into a certain format - handy for comparing, for example, month name or number.

 

DATE_SUB() and DATE_ADD() allow you to add or subtract a certain amount of time from a given date/time, in a slightly semantic way (simply choose your interval and how much of that interval, ie. 7 DAY).

 

look at this page for the documentation.

[gnawz]

I'm really appreciating this help.