Help with date function...

[savagenoob]

I am doing somthing complicated with dates and I think I am making it harder than I should. I need to subtract "x" number of months from the current date, then create 2 dates in that month that represent the first day, and last day of the month. Then make it in a format that SQL can read to get the SUMS I need between those dates. My last 2 strtotimes are wrong and are giving me the 1969 dates.

function commiss($nummonth){
$repnum = $_SESSION['REPID'];
$date = date("Y-m-d", strtotime("-" . $nummonth . ""));
$time2 = " 00:00:01";
$time = " 23:59:59";
$date1 =date("Y-m-d", strtotime(date('m').'/01/'.date('Y')));
$date2 =date("Y-m-d", strtotime('-1 second',strtotime('+1 month',strtotime(date('m').'/01/'.date('Y')))));
$date1 = date("Y-m-d", strtotime("-" . $nummonth . "", $date1));
$date2 = date("Y-m-d", strtotime("-" . $nummonth . "", $date2));
$date1 = $date1 . $time2;
$date2 = $date2 . $time;
$string = "SELECT SUM(premium), SUM(repcom) FROM commission WHERE repnum = '$repnum' BETWEEN '$date1' AND '$date2'";
$sqltotal = mysql_query($string)or die (mysql_error());
$sqlprem = mysql_fetch_array('[sUM(premium)]');
$sqlcomm = mysql_fetch_array('[sUM(repcom)]');
echo "Premium: " . $sqlprem;
echo "\n Rep Commission: " . $sqlprem;
}

[PFMaBiSmAd]

The following query should (untested) work -

 

$string =  "SELECT SUM(premium), SUM(repcom) FROM commission
WHERE repnum = '$repnum' AND EXTRACT(YEAR_MONTH FROM `date`) = EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL $nummonth MONTH)";

 

I assumed that your date or datetime information is in a column named `date` since that portion of your query statement was missing.

 

Just get the data that matches the year/month you are interested in.

 

Edit: I hope you are not calling your commiss() function inside of a loop, passing it a series of month numbers to get the results for a range of years/months. What you would do in this case is select the rows that cover the overall range of dates you are interested in and GROUP BY EXTRACT(YEAR_MONTH FROM `date`) to give you the SUM() information for each year/month within that overall range of dates.

 

If you are just calling your commiss() function once on a page to get the results for one month only, no problem, but executing queries inside of loops,even if the query is inside of a function, is not efficient.

[savagenoob]

Dang, I never used EXTRACT in my statements before. The date is stored as 'month year' in the table, such as September 2011, not a date stamp. Havent thought this all the way through I guess, I was focused on just getting my 2 dates setup for the search. Not sure if your method will find all data within that month, and be able to use 'month year' or if I have to store the date in a format sql can use.

[savagenoob]

You are right about  the query as well, forgot to add the index of where to search the dates

It should be like...

$string = "SELECT SUM(premium), SUM(repcom) FROM commission WHERE repnum = '$repnum' AND dateent BETWEEN '$date1' AND '$date2'";

[PFMaBiSmAd]

The date is stored as 'month year' in the table, such as September 2011

 

Then your original query (and what I posted) would never have worked for at least two reasons -

 

1) You are forming a YYYY-MM-DD HH:MM:SS value in the $datex variables and trying to compare that with what is in your table. The format of the two values must be identical for the comparison to work.

 

2) The September 2011 format cannot be used in a greater-than/less-than comparison because the year would need to be the left most field (most significant digits) and the month would need to be numeric (the month names when compared alphabetically are not in the actual month order.)

 

In general, you should store data that is associated with a date using the full actual date it occurred on. This will let you easily do any kind of manipulation of the data you may need.

[savagenoob]

Assuming I change the data to YYYY-MM-DD HH:MM:SS (as I am doing now ), then should I use a between search as I was attempting or your sql query as posted?

[xyph]

You got it.

[savagenoob]

Sorry to keep bothering, but does your sql query search for all entries in the given month? I am using interval '1', I have an entry of '2011-08-01 13:41:39' with data in the 'premium' and 'repcom' columns, but it is not echoing the data. I am using...

$string = "SELECT SUM(premium), SUM(repcom) FROM commission WHERE repnum = '$repnum' AND EXTRACT(YEAR_MONTH FROM `dateent`) = EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL $nummonth MONTH)";
$sqltotal = mysql_query($string)or die (mysql_error());
$sqlprem = mysql_fetch_array('[sUM(premium)]');
$sqlcomm = mysql_fetch_array('[sUM(repcom)]');

echo "Premium: " . $sqlprem;
echo "\nRep Commission: " . $sqlprem;

and $string is echoing 'SELECT SUM(premium), SUM(repcom) FROM commission WHERE repnum = '1' AND EXTRACT(YEAR_MONTH FROM `dateent`) = EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL 1 MONTH)Premium: Rep Commission: '

[PFMaBiSmAd]

You need to have php's error_reporting set to E_ALL and display_errors set to ON so that php will help you by reporting and displaying all the errors it detects.

 

You use mysql_fetch_xxxxx() statements on the result resource that the mysql_query statement returns.