Year selection and months

[kessels1234]

Hi,

 

I'm new here and this is my first post. I'm having a little problem with my script and hope someone can help.

 

I have the following table:

PROVISIE with the following records:

provisie_id

werknemer_id

date

klant_naam

fact_nr

opmerking

bedrag

 

I have a selectlist to select (a period of time)year1 and year2. Between these years I like to know for every month how much sales (bedrag) are being made by the specific employee (werknemer_id)

 

for now i can do it static for a specific month with the next query which works well:

$testsql = "SELECT SUM(bedrag) FROM provisie WHERE werknemer_id = ".$validid." AND MONTH(date)=1"; 


    $testresult = mysql_query($testsql);
    while($customrows = mysql_fetch_assoc($testresult))
    {
    echo $customrows['SUM(bedrag)'];    
    } 

 

So what I want.

The employee selects year 1 and year 2. Let's say 2009 and 2010. The script should then give the next output:

Jan (total amount of all sales in January 2008)

Feb (total amount of all sales in January 2008)

March (total amount of all sales in January 2008)

April (total amount of all sales in January 2008)

.....

.....

.....

Dec (total amount of all sales in January 2008)

 

I hope I made clear what my question is.

 

any suggestions welcome.

 

Danny

PS date is also date in mysql

[PFMaBiSmAd]

This should (untested) select the rows you want -

$start = '2009';
$end = '2010';
$testsql = "SELECT SUM(bedrag) as total, EXTRACT(YEAR_MONTH FROM date) as y_m FROM provisie WHERE werknemer_id = $validid AND YEAR(date) BETWEEN '$start' AND '$end' GROUP BY y_m";

 

[kessels1234]

Hi PFMaBiSmAd,

 

Thanks for your answer,

 

I think this will help me but i have to test some things to get it just the way I want. Maybe I have to get back to.

 

Thanks again

Danny

[kessels1234]

Ok next problem with this:

 

How can I  make an output like this (e.g. $start = 2007 $end = 2010 with the possibility that there are months with no sales (=zero)):

 

2007

Jan    Feb    Mar    Apr    Jun    Jul    Aug    Sept    Okt    Nov    Dec

0        0        1500  2000  1100  1200 800    999      650    799    899

 

2008

Jan    Feb    Mar    Apr    Jun    Jul    Aug    Sept    Okt    Nov    Dec

0        0        1500  2000  1100  1200 800    999      650    799    899

 

2009

Jan    Feb    Mar    Apr    Jun    Jul    Aug    Sept    Okt    Nov    Dec

0        0        1500  2000  1100  1200 800    999      650    799    899

 

2010

Jan    Feb    Mar    Apr    Jun    Jul    Aug    Sept    Okt    Nov    Dec

5000  2500  0        0        0        0      0        0          0        0        0

 

To get all the data I'm using this sql:

$testsql = "SELECT SUM(bedrag) as total, EXTRACT(YEAR FROM date) as y, EXTRACT(MONTH FROM date) as m FROM provisie WHERE werknemer_id = $validid AND YEAR(date) BETWEEN '$start' AND '$end' GROUP BY y,m";

 

Months are given in numbers but they need the names as shown in my example.

To accomplish this my guess is to do it with 'for loops' but I cant figure out how I can do this.

 

Hope you can help me

Thanks in advance

Danny

[fenway]

Yeah, that's more tricky... you'll have create a derived table contain a row for each month required, and then LEFT JOIN that in.  Easy to do if you have an integers table lying around.

[kessels1234]

Hi Fenway,

 

Don't know exactly what you mean/ or how to accomplish but what I really need is all those values separate(month-total by year) so I can write them eventualy to an xml file so I can use charts to visualize the selected statistics. I know that this is maybe too difficult for a newbee but I want to learn and already finnished my application so far that everyone that uses it is very happy.

 

Maybe I need a different approach with the given records in the database and use a different sql statement?

 

Thanks,

Danny

[Zane]

you can either make a loop, querying the database for every year to get new results..

 

Or you can figure out a complicated query to give you every single value you need.  This is usually the best way...  but after getting this data you would need to loop through every result.. check if it's January and if it is.. start a new variable/array/"tr" tag containing this "new" data.

[kessels1234]

Any idea of an example on how the sql and the code should look like?

[Zane]

$monthsTR .= "Jan\n";
$monthsTR .= "Feb\n";
$monthsTR .= "Mar\n";
$monthsTR .= "Apr\n";
$monthsTR .= "May\n";
$monthsTR .= "Jun\n";
$monthsTR .= "Jul\n";
$monthsTR .= "Aug\n";
$monthsTR .= "Sep\n";
$monthsTR .= "Oct\n";
$monthsTR .= "Nov\n";
$monthsTR .= "Dec\n";

$testsql = "SELECT SUM(bedrag) as total, EXTRACT(YEAR FROM date) as y, EXTRACT(MONTH FROM date) as m FROM provisie WHERE werknemer_id = $validid AND YEAR(date) BETWEEN '$start' AND '$end' GROUP BY y,m";
$result = mysql_query($testsql);
for($i = 0; $testdata = mysql_fetch_assoc($result); $i++) {
     if($testdata['m'] == "January") echo ($i>0) ? "$monthsTR\n" : "$monthsTR\n";
     echo "$testdata['total']";
}

something like that... NOT TESTED

[kessels1234]

Hi zanus,

 

I'm trying to figure out what your code does. As soon as the for loop comes it gives me a blank screen (no errors).

 

for($i = 0; $testdata = mysql_fetch_assoc($testresult); $i++) {
     if($testdata['m'] == "January") echo ($i>0) ? "</tr><tr>$monthsTR</tr><tr>\n" : "<tr>$monthsTR</tr><tr>\n";
     echo "<td>$testdata['total']</td>";
}

 

this code: if($testdata['m'] == "January") is ofcourse wrong because Januari doesn't exist. Whatever I try to do to correct this, it keeps giving a blank screen.

 

Thanks for taking the time to help me with this. Really appreciate it.

[Zane]

it may be malformed HTML.. like I said.. I didn't test it.

 

try opening and closing a table tag.. directly before and after the for loop.. respectively.

 

echo "</pre>
<table>";
for($i = 0; $testdata = mysql_fetch_assoc($testresult); $i++) {
     if($testdata['m'] == "January") echo ($i>0) ? "$monthsTR\n" : "$monthsTR\n";
     echo "$testdata['total']";
}
echo "</ta

As for the testdata == January part.. idk.  I tried to make it obvious that you need to check to see if it's January ... to start a new listing.  You'll have to tweak it to work from what you have..

[fenway]

Well, my approach involves generating a table, on the fly, of years and months for the range you want -- the rest is easy.

[kessels1234]

Don't really get what you mean by this. If you have some example code to demonstrate what you mean I can try to figure out.

 

Thanks again for your help,

Danny

[fenway]

See here -- trivial to use date math and generate what you need.

[kessels1234]

Thankx,

 

I'm gonna check it out

 

Danny