Jump to content

garybrett37

New Members
  • Content count

    6
  • Joined

  • Last visited

Community Reputation

0 Neutral

About garybrett37

  • Rank
    Newbie
  1. garybrett37

    Filter Pivot data from mysql...or not

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

    Filter Pivot data from mysql...or not

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

    Filter Pivot data from mysql...or not

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

    Filter Pivot data from mysql...or not

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

    Filter Pivot data from mysql...or not

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

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.