[SOLVED] Query display by DATETIME

[NerdConcepts]

Ok, building a comment system. What I cannot figure out is how to do display the number of comments that a person has depending on the date, like eBay has; 1 Month, 6 Months, 12 Months. I know how to display them depending on this month and day, but I don't want to have to keep changing the month and day, well everyday. I've been trying to get this to work but I cannot even come close to writing something that is dynamic like eBay has. If someone could point me in the right direction I was appreciate it. Thanks.

[GOLDfish_74]

I can't tell you the exact script but I was thinking, if there is no easy way:

you could make a script that takes the date: YYYY MM DD

And say you want one month ago:

Get all database results after YYYY MM-1 DD

($month = $month - 1;)

Then for 6 months: YYYY MM-6 DD

if $month < 1 {

$month = 12 - $month;

$year = $year - 1;

}

and so on.

I dont think that would be too challenging.

 

Good luck, hope that at least gives you a bright idea... far brighter than mine,

 

THEfish!

[paul2463]

//create timestamps
$now = strtotime("Now");
$nowm6 = strtotime("-6 month", $now); //now - 6 months
$nowm12 = strtotime("-12 month", $now); //now - 12 months

//create dates from the timestamps
$today = date("Y-m-d", $now);
$sixmonth = date("Y-m-d", $nowm6);
$twelvemonth = date("Y-m-d", $nowm12);


// search the database for counts
$query = "SELECT COUNT(posts) WHERE postdate > '$sixmonth'"; //posts in last 6 months
$query = "SELECT COUNT(posts) WHERE postdate > '$twelvemonth'"; //posts in the last 12 months

 

 

[NerdConcepts]

paul2463: Your awesome. I did some stuff a little different on the querys and I don't like to NOT set a default timezone, but it does work perfectly, and it's very simple.

 

//create timestamps

date_default_timezone_set('America/Chicago');

$now = strtotime("Now");
$nowm1 = strtotime("-1 month", $now); //now - 6 months
$nowm6 = strtotime("-6 month", $now); //now - 6 months
$nowm12 = strtotime("-12 month", $now); //now - 12 months

//create dates from the timestamps
$today = date("Y-m-d", $now);
$onemonth = date("Y-m-d", $nowm1);
$sixmonth = date("Y-m-d", $nowm6);
$twelvemonth = date("Y-m-d", $nowm12);

// search the database for counts
$query1m = "SELECT comm_id, DATE_FORMAT(comm_date, '%M %d, %Y') AS dr FROM vendor_comments WHERE comm_date > '$onemonth'";
$result1m = @mysql_query ($query1m); // Run the query
$num1m = mysql_num_rows($result1m);
echo '<span class="ContentText"><center>Comments this month: ' . $num1m . '</span></center><br />';

// search the database for counts
$query6m = "SELECT comm_id, DATE_FORMAT(comm_date, '%M %d, %Y') AS dr FROM vendor_comments WHERE comm_date > '$sixmonth'";
$result6m = @mysql_query ($query6m); // Run the query
$num6m = mysql_num_rows($result6m);
echo '<span class="ContentText"><center>Comments in Last 6 Months: ' . $num6m . '</span></center><br />';

$query12m = "SELECT comm_id, DATE_FORMAT(comm_date, '%M %d, %Y') AS dr FROM vendor_comments WHERE comm_date > '$twelvemonth'";
$result12m = @mysql_query ($query12m); // Run the query
$num12m = mysql_num_rows($result12m);
echo '<span class="ContentText"><center>Comments in Last 12 Months: ' . $num12m . '</span></center><br />';