Filter Pivot data from mysql...or not

[garybrett37]

Hi there I been trying this for days with no joy so thought I'd ask the question to see if its even possible?

 

I have a php pivot based on mysql query, it works great but I would like to have an external filter for years..Currently i can only include 2017 for example in the where clause, I've tried to remove that but cant seem to work out how to have it display current years data on load then filter it by user input of year. 

 

Basically I'm trying to return all data but initially only display current year. Then when user selects a year from a dropdown it filters it to only that year? I think half my problem is that the date fields (i.signedupdate) are all summed so I cant actually target a year (2017, 2016, 2015 etc etc). Is this even possible?

 

SELECT * FROM 

  (SELECT c.Adviser,

  Sum(Case When Month(i.signedupdate) = 1 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Jan,

  Sum(Case When Month(i.signedupdate) = 2 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Feb,

  Sum(Case When Month(i.signedupdate) = 3 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Mar,

  Sum(Case When Month(i.signedupdate) = 4 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Apr,

  Sum(Case When Month(i.signedupdate) = 5 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) May,

  Sum(Case When Month(i.signedupdate) = 6 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Jun,

  Sum(Case When Month(i.signedupdate) = 7 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Jul,

  Sum(Case When Month(i.signedupdate) = 8 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Aug,

  Sum(Case When Month(i.signedupdate) = 9 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Sept,

  Sum(Case When Month(i.signedupdate) = 10 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Oct,

  Sum(Case When Month(i.signedupdate) = 11 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Nov,

  Sum(Case When Month(i.signedupdate) = 12 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Dece,

  Sum(i.comms + i.broker_fee + i.legal_fees) As Total

From

  tbl_lead i Inner Join

  tbl_clients c On c.client_id = i.client_id

Where

    Year(i.signedupdate)= 2017

Group By c.Adviser with rollup) As t

 

   

[requinix]

If that query works for any year you put into it then it will work for what you want.

 

In PHP, decide which year to show.

$year = date("Y"); // this year
if (isset($_POST["year"]) && $_POST["year"] >= 2000 && $_POST["year"] <= $year) {
	$year = (int)$_POST["year"];
}

The >=2000 and

 

Then put it in your query.

"SELECT...
Where
  Year(i.signedupdate)= {$year}
..."

[garybrett37]

Hi many many thanks for taking the time, really appreciate it.

 

As I understand or probably dont, I added the where clause as suggested, then created the php and a standard select drop down as below. When the form loads initially it just displays some random values in the total columns fields and the filter doesn't change anything. I know I've got it wrong but cannot see where!

Is it because the date field (i.signedupdate) is formatted as month in the main query, as such there is no year column to filter on?

SELECT * FROM
  (SELECT c.Adviser,
  Sum(Case When Month(i.signedupdate) = 1 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Jan,
  Sum(Case When Month(i.signedupdate) = 2 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Feb,
  Sum(Case When Month(i.signedupdate) = 3 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Mar,
  Sum(Case When Month(i.signedupdate) = 4 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Apr,
  Sum(Case When Month(i.signedupdate) = 5 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) May,
  Sum(Case When Month(i.signedupdate) = 6 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Jun,
  Sum(Case When Month(i.signedupdate) = 7 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Jul,
  Sum(Case When Month(i.signedupdate) = 8 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Aug,
  Sum(Case When Month(i.signedupdate) = 9 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Sept,
  Sum(Case When Month(i.signedupdate) = 10 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Oct,
  Sum(Case When Month(i.signedupdate) = 11 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Nov,
  Sum(Case When Month(i.signedupdate) = 12 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Dece,
  Sum(i.comms + i.broker_fee + i.legal_fees) As Total
From
  tbl_lead i Inner Join
  tbl_clients c On c.client_id = i.client_id
Where
  Year(i.signedupdate)= '{$year}'
Group By c.Adviser with rollup) As t

<?php
  $year = date("Y"); // this year
  if (isset($_POST["year"]) && $_POST["year"] >= 2000 && $_POST["year"] <= $year) {
   $year = (int)$_POST["year"];
  }
    ?>
    <select name="$year">
    <option value="2017">2017</option>
    <option value="2016">2016</option>
    <option value="2015">2015</option>
</select>

[requinix]

No, it's not that. What matters is i.signedupdate itself - if it's a DATE or DATETIME then you can MONTH() or YEAR() it however you want. It's not like you're making it be a month value - MONTH() and such are like any other function call you're used to.

 

1. The select should be named just "year", but anything you want works too.

2. Whatever the name is, that's what you use with $_POST. Right now you'd have to do $_POST['$year'] but that's weird so don't and stick with a normal name.

[Psycho]

Maybe I am missing something, but I think that query is much more complicated than it needs to be. Just include the month in the GROUP BY clause instead of all those case statements.

 

SELECT c.Adviser,
       SUM(i.comms + i.broker_fee + i.legal_fees) as total
       MONTH(i.signedupdate) as month
 
From tbl_lead i
INNER JOIN tbl_clients c
  ON c.client_id = i.client_id
 
WHERE YEAR(i.signedupdate) = 2017
 
GROUP BY c.Adviser, MONTH(i.signedupdate)

[garybrett37]

 

Maybe I am missing something, but I think that query is much more complicated than it needs to be. Just include the month in the GROUP BY clause instead of all those case statements.

SELECT c.Adviser,
       SUM(i.comms + i.broker_fee + i.legal_fees) as total
       MONTH(i.signedupdate) as month
 
From tbl_lead i
INNER JOIN tbl_clients c
  ON c.client_id = i.client_id
 
WHERE YEAR(i.signedupdate) = 2017
 
GROUP BY c.Adviser, MONTH(i.signedupdate)

Thanks for your help, it does look complex I admit but its a pivot/crosstab style layout, so Adviser name on left, then months of the year as columns from left to right. All taht works great, its just trying to filter the data by year where I am failing. Been looking at it so long now I cant even begin to work it out  

[garybrett37]

No, it's not that. What matters is i.signedupdate itself - if it's a DATE or DATETIME then you can MONTH() or YEAR() it however you want. It's not like you're making it be a month value - MONTH() and such are like any other function call you're used to.

 

1. The select should be named just "year", but anything you want works too.

2. Whatever the name is, that's what you use with $_POST. Right now you'd have to do $_POST['$year'] but that's weird so don't and stick with a normal name.

I understand but for whatever reason it just doesn't pull the years data through, very odd..

[requinix]

What's your code now? Including the form, the part where you get the year from the form, and executing the query.

[garybrett37]

What's your code now? Including the form, the part where you get the year from the form, and executing the query.

SELECT * FROM
  (SELECT c.Adviser,
  Sum(Case When Month(i.signedupdate) = 1 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Jan,
  Sum(Case When Month(i.signedupdate) = 2 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Feb,
  Sum(Case When Month(i.signedupdate) = 3 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Mar,
  Sum(Case When Month(i.signedupdate) = 4 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Apr,
  Sum(Case When Month(i.signedupdate) = 5 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) May,
  Sum(Case When Month(i.signedupdate) = 6 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Jun,
  Sum(Case When Month(i.signedupdate) = 7 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Jul,
  Sum(Case When Month(i.signedupdate) = 8 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Aug,
  Sum(Case When Month(i.signedupdate) = 9 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Sept,
  Sum(Case When Month(i.signedupdate) = 10 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Oct,
  Sum(Case When Month(i.signedupdate) = 11 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Nov,
  Sum(Case When Month(i.signedupdate) = 12 Then i.comms + i.broker_fee + i.legal_fees Else 0 End) Dece,
  Sum(i.comms + i.broker_fee + i.legal_fees) As Total
From
  tbl_lead i Inner Join
  tbl_clients c On c.client_id = i.client_id
Where
  Year(i.signedupdate)= '{$year}'
Group By c.Adviser with rollup) As t

  <div style="margin:10px">
<?php
  $year = date("Y"); // this year
  if (isset($_POST["year"]) && $_POST["year"] >= 2000 && $_POST["year"] <= $year) {
   $year = (int)$_POST["year"];
  }
    ?>
    <select name="year">
    <option value="2017">2017</option>
    <option value="2016">2016</option>
    <option value="2015">2015</option>
</select>
          <br>
      </div>

On load the totals column has some values but not all, the months have no values and select doesn't change anything. If I change the where clause to 2017 the values come back but cant then filter. Im trying various different methods but none so far that will fit 

Edited December 6, 2017 by garybrett37

[requinix]

Also have to see the bit where you execute the query. Not just the SQL.

 

...but it's probably not necessary. Move the $year stuff to before you do the query. Can't very well use $year if it hasn't been defined yet, can you?

[Barand]

Also, do you have a <form> with a POST method, or just a <select> element on its own?

[garybrett37]

Also, do you have a <form> with a POST method, or just a <select> element on its own?

Hi I had tried select on its own and also within a form;

<form action="" method="post">
<select name="year">
<option value="2016">2017</option>
<option value="2017">2016</option>
<option value="2015">2015</option>
</select>
<input type="submit">

</form>

[Psycho]

Thanks for your help, it does look complex I admit but its a pivot/crosstab style layout, so Adviser name on left, then months of the year as columns from left to right. All taht works great, its just trying to filter the data by year where I am failing. Been looking at it so long now I cant even begin to work it out

 

The intended format of the output should not be the determining factor on how to create your query. The query should pull the data in the most efficient manner possible and be agnostic to the intended display of the data. I provided would provide a much more efficient result. There should be code to then format that output into the intended display.

 

But, you are free to do as you wish.