Jump to content

Year selection and months


kessels1234

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

$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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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..

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.