[Resolved] MySQL & selecting last month

Kold

Hello!

I need to select things from a mySQL database where the timestamp column is last month.

So far i am using this;

$TimeStampA = Date("Ymd000000");
$TimeStampB = Date("Ymd000000", time()-Date("t")*24*3600);
$Month = mysql_query("SELECT `ID` FROM `tblTest` WHERE `Submit_Time` BETWEEN '$TimeStampA' AND '$TimeStampB'", $dbhndl);

The problem i have with this is that it treats the current day as the start of the month, which i dont want. (Also, it uses the number of days from this month as last mont, but i have only just realised!)

I also have to select everything from this week, but have the same problem.

I think i need to use 'mktime' but i dont really know.

If anyone could help i would really appreciate it!
Thanks.

devinemke

$last_month_month = date('m', strtotime('last month'));
$last_month_year = date('Y', strtotime('last month'));

// if your date column is a DATE or DATETIME type:
$sql = "SELECT * FROM table WHERE MONTH(date) = '$last_month_month' AND YEAR(date) = '$last_month_year'";

// if your date column is a timestamp:
$sql = "SELECT * FROM table WHERE MONTH(FROM_UNIXTIME(date)) = '$last_month_month' AND YEAR(FROM_UNIXTIME(date)) = '$last_month_year'";

Kold

Thanks!

The problem i have now is getting data from this week;

$TimeStampA = date('w', strtotime('this week')); 
$TimeStampB = date('Y', strtotime('this month'));

$sql = "SELECT * FROM tblTest WHERE WEEK(`Submit_Time`,2) = '$TimeStampA' AND YEAR(`Submit_Time`) = '$TimeStampB'";

The ',2' in WEEK is to make sunday the beginning of the week. The Submit_Time field is a mySQL timestamp.

The above code dow not work, do you have any idea why? Here is the query it creates when i echo it;

SELECT * FROM tblTest WHERE AND WEEK(Submit_Time,2) = '5' AND YEAR(Submit_Time) = '2004'

Where the '5' is comming from?!

Thanks for your help!

devinemke

$last_sunday = date('Y-m-d', strtotime('last Sunday'));
$this_sunday = date('Y-m-d', strtotime('this Sunday'));
$sql = "SELECT * FROM table WHERE date BETWEEN '$last_sunday' AND '$this_sunday'";

Kold

Thank you very much for your help!