php/mysql dates

[frank754]

Thanks for your help in the past, and this forum is great.
I have a question about dates, and in searching, there are huge amounts of results, but very hard to narrow it down to exactly what I'm asking.
In using PHP, I have some variables pulled from mysql databases (in the date format, no time) such as:

$a = "2009-03-31";

how would I, within php, subtract 28 days and get

$b = "2009-03-03"; (with a rollover if it goes below the first of the month)

then next,
the same calendar day of the previous month:
$b = "2009-02-31";
(which doesn't exist of course but hopefully a mysql query could use that in a date range anyway and create a report without an error)

Also for example get the start of the previous month:

$b = "2009-02-01";

or change the year
$b="2008-03-31";

Is there a simple way, at least for the first task (subtract days or 1 calendar month) without writing a script to parse the string and do it the hard way?

Also, say I have
$a = "2009-03-09"; and
$c = "2009-01-17";
How could I tell the actual number of calendar days between them, where it would check for leap year, etc. ?

These would be used in queries such as:
mysql_query("SELECT * from $vtable WHERE (trxdate <= '$a') and (trxdate >= '$b' and (other_conditions, etc.) " );

[pritaeas]

I normally use the mysql functions for this:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

[frank754]

That's all well & good, and the mysql commands work fine on the mysql monitor, but I'm not sure what to do within php.
For example from what you said I came up wiht this:

$qa = mysql_query("SELECT DATE_SUB('curdate()', INTERVAL 31 DAY) ");
while ($row = mysql_fetch_array($qa)) { $a = $qa; }
$qb = mysql_query("SELECT CURDATE() ");
while ($row = mysql_fetch_array($qb)) { $b = $qb; }
mysql_query("SELECT * from $vtable WHERE (trxdate >= '$a') and (trxdate <= '$b') " );

but that doesn't work.
I'm sure it's very simple but I'm just miising the syntax somewhere.
It would be great if I could use the mysql functions in this way rather than php.
Perhaps it doesn't transport to php, as I tried to echo a variable using this method once, and got a "Resource ID #8" error.

[nav33n]

The way you are doing it is wrong.
Try this.

$qa = mysql_query("SELECT DATE(DATE_SUB(now() , INTERVAL 31 DAY))  as actual_date");
while ($row = mysql_fetch_array($qa)) { 
	$a = $row[actual_date]; 
}
$qb = mysql_query("SELECT CURDATE();");
while ($row = mysql_fetch_array($qb)) { 
	$b = $row[0]; 
}
mysql_query("SELECT * from $vtable WHERE (trxdate >= '$a') and (trxdate <= '$b') " );

[frank754]

Excellent, nav33n, that was exactly what I needed...
You saved the day. It works perfectly.
Have a good day, and I will add to your rep.

[nav33n]

Thats awesome! Cheers!