Jump to content


Photo

Filter Pivot data from mysql...or not


  • Please log in to reply
12 replies to this topic

#1 garybrett37

garybrett37
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 05 December 2017 - 02:09 PM

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

 

   

#2 requinix

requinix
  • Administrators
  • Maddening Administrator
  • 9,558 posts
  • LocationWA

Posted 05 December 2017 - 02:59 PM

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 <=$year should reflect the same bounds you used when creating the dropdown list. The (int) forces the value to be a number for safety and is important to have.

Then put it in your query.
"SELECT...
Where
  Year(i.signedupdate)= {$year}
..."

The Reimann Zeta Function Trolley Problem | "Summer is when I, the great ice fairy, can show my true power!"

#3 garybrett37

garybrett37
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 05 December 2017 - 03:20 PM

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>


#4 requinix

requinix
  • Administrators
  • Maddening Administrator
  • 9,558 posts
  • LocationWA

Posted 05 December 2017 - 03:43 PM

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.
The Reimann Zeta Function Trolley Problem | "Summer is when I, the great ice fairy, can show my true power!"

#5 Psycho

Psycho
  • Moderators
  • Move along, nothing to see here
  • 11,864 posts
  • LocationCanada

Posted 05 December 2017 - 05:27 PM

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)

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#6 garybrett37

garybrett37
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 06 December 2017 - 09:45 AM

 

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



#7 garybrett37

garybrett37
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 06 December 2017 - 09:46 AM

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



#8 requinix

requinix
  • Administrators
  • Maddening Administrator
  • 9,558 posts
  • LocationWA

Posted 06 December 2017 - 09:58 AM

What's your code now? Including the form, the part where you get the year from the form, and executing the query.
The Reimann Zeta Function Trolley Problem | "Summer is when I, the great ice fairy, can show my true power!"

#9 garybrett37

garybrett37
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 06 December 2017 - 10:09 AM

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 by garybrett37, 06 December 2017 - 10:11 AM.


#10 requinix

requinix
  • Administrators
  • Maddening Administrator
  • 9,558 posts
  • LocationWA

Posted 06 December 2017 - 11:13 AM

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?
The Reimann Zeta Function Trolley Problem | "Summer is when I, the great ice fairy, can show my true power!"

#11 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 17,973 posts

Posted 06 December 2017 - 12:13 PM

Also, do you have a <form> with a POST method, or just a <select> element on its own?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#12 garybrett37

garybrett37
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 06 December 2017 - 12:48 PM

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>


#13 Psycho

Psycho
  • Moderators
  • Move along, nothing to see here
  • 11,864 posts
  • LocationCanada

Posted 07 December 2017 - 04:22 PM

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.


The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users