Php Mysql YTD sales function loop

I would like PHP source code that will take the current month when the report is run and extract data a year back , month  by month in a table . example extracting sum of the sales from a  table in mysql and displaying the sum by each month in a table

TomCatELAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EnclavetCommented:
function countytd($first, $last, $month, $year) {
        global $setpt, $additionalopts;
        $date = "$year-$month-01";
        $back = fiscalyear();
        $query = "SELECT COUNT(*) as count FROM maindata
      WHERE PT = '$setpt' AND Firstname = '$first' AND Lastname = '$last' $additionalopts
                           AND AdmitDate BETWEEN DATE_SUB('$date', INTERVAL ".$back." MONTH)  
                           AND DATE_SUB(DATE_ADD('$date', INTERVAL 1 MONTH), INTERVAL 1 DAY)";      
        $result = mysql_query($query);
        $num = mysql_numrows($result);
        $i = 0;
        $count = mysql_result($result, $i, "count");
        return $count;
      }
0
EnclavetCommented:
Oops posted too soon.

Anyway the below source code will do exactly what you want, Change the COUNT(*) to the data you want to get. Also $back will be how many months to lookback. I had a function that calculated the how many months back was the start of the fiscalyear(). If you want the data 12 months back from the parameters $month,$year then set $back = 12.

EX:

Passing
$first= firstname
$last = lastname
$month = 12
$year = 2010
$back = 12
Will give you all the records back 12 months from 12-2010 with $first= firstname and $last = lastname.

      function countytd($first, $last, $month, $year) {
        global $setpt, $additionalopts;
        $date = "$year-$month-01";
        $back = fiscalyear();
        $query = "SELECT COUNT(*) as count FROM maindata
            WHERE PT = '$setpt' AND Firstname = '$first' AND Lastname = '$last' $additionalopts
                           AND AdmitDate BETWEEN DATE_SUB('$date', INTERVAL ".$back." MONTH)  
                           AND DATE_SUB(DATE_ADD('$date', INTERVAL 1 MONTH), INTERVAL 1 DAY)";      
        $result = mysql_query($query);
        $num = mysql_numrows($result);
        $i = 0;
        $count = mysql_result($result, $i, "count");
        return $count;
      }

0
TomCatELAuthor Commented:
I need to extract one value example sales . And to show the sum value off each month for example 20100101 to 20100131 it needs to go back 12 months and show it in a table .Please explain above example more please
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

TomCatELAuthor Commented:
so it needs to look like

date      sales (sum of sales between month)

jan 09    2400
feb 09   3200

Thanks
0
EnclavetCommented:
If you want to do something like that then you really need a function like this:

function countmonth($month,$year) {
$startdate = "$year-$month-01";
$enddate = "$year-$month-31";
$query = "SELECT SUM(sales) as salestotal FROM maindata WHERE Date BETWEEN $startdate AND $enddate";
$result = mysql_query($query);
$salestotal = mysql_result($result, 0, "salestotal");
return $salestotal
}

Then you loop it 12 times and make sure you take in account going from JAN -> DEC

$month = 5; //starting in may
$year = 2010;
for($i=0;$i<12;$i++) {
$salestotal = countmonth($month,$year);
echo "$month-$year: $salestotal<br>";
$month--;
if($month == 0) {
$month = 12;
$year--;
}
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TomCatELAuthor Commented:
Thank you you are going in the right direction . One problem it has to run backwards from let's say mar10  backwards to mar 09 . So the loop has to have a date function in it . because it will be 03 02 01 12 11 10 etc  ...

0
EnclavetCommented:
We are dealing with raw numbers here:

This is the part of the code that will take care of that:
$month--;
if($month == 0) {
$month = 12;
$year--;
}

so after each iteration of the loop the $month and $year will be different:
5-2010
4-2010
3-2010
2-2010
1-2010
12-2009
11-2009
10-2009
9-2009
8-2009
7-2009

etc...
0
TomCatELAuthor Commented:
Thanks for explanation will try and assign points if it is the right solution
0
TomCatELAuthor Commented:
Hi  Enclavet:

Okay i have come right , thank you . Just one problem in code $startdate and $enddate in query has to be in ' ' then it works .

I will award you the points but , i would have like to have seen a php date function used as my next question will be by week

Thanks




function countmonth($month,$year) {
$startdate = "$year-$month-01";
$enddate = "$year-$month-31";
$query = "SELECT SUM(sales) as salestotal FROM bakery WHERE date BETWEEN '$startdate' AND '$enddate'";
$result = mysql_query($query);
$salestotal = mysql_result($result, 0, "salestotal");
return $salestotal;
}

//Then you loop it 12 times and make sure you take in account going from JAN -> DEC

$month = 05; //starting in may
$year = 2010;
for($i=0;$i<12;$i++) {
$salestotal = countmonth($month,$year);
echo "$month-$year: $salestotal<br>";
$month--;
if($month == 0) {
$month = 12;
$year--;
}
}
?>
0
TomCatELAuthor Commented:
The solution worked but i would have like to have seen a php date function used or mysql date function
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.