Jump to content

Filter Pivot data from mysql...or not


garybrett37

Recommended Posts

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

 

   
Link to comment
Share on other sites

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}
..."
Link to comment
Share on other sites

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>
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)
Link to comment
Share on other sites

 

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 :) 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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>
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

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