0

Good morning, I have the mysql query below to display the years sales which worked fine until the year changed as it now shows Jan 2012 & Jan 2013 combined.

To try to resolve I placed a drop down form box (id=YearSelect) with value of 2012 & 2013, is it possible I can control this query with the value selected? Should the end user select 2012 then that value would be sent to column Year as a type of where clause?

<form id="yearselect">
<select name="year">
  <option value="">Select year </option>
  <option value="2012">2012</option>
  <option value="2013">2013</option>
</select>
</form>



Select
  *,
  If(q.Adviser Is Null, 1, 0) As remove
From
  (Select
      a.ContactFullName As Adviser,  
      YEAR(b.CaseDate) As Year,
      Sum(If(Month(b.StatusSubmittedDate) = 1, b.CaseCommission, 0)) As Jan,
      Sum(If(Month(b.StatusSubmittedDate) = 2, b.CaseCommission, 0)) As Feb,
      Sum(If(Month(b.StatusSubmittedDate) = 3, b.CaseCommission, 0)) As Mar,
      Sum(If(Month(b.StatusSubmittedDate) = 4, b.CaseCommission, 0)) As Apr,
      Sum(If(Month(b.StatusSubmittedDate) = 5, b.CaseCommission, 0)) As May,
      Sum(If(Month(b.StatusSubmittedDate) = 6, b.CaseCommission, 0)) As Jun,
      Sum(If(Month(b.StatusSubmittedDate) = 7, b.CaseCommission, 0)) As Jul,
      Sum(If(Month(b.StatusSubmittedDate) = 8, b.CaseCommission, 0)) As Aug,
      Sum(If(Month(b.StatusSubmittedDate) = 9, b.CaseCommission, 0)) As Sep,
      Sum(If(Month(b.StatusSubmittedDate) = 10, b.CaseCommission, 0)) As Oct,
      Sum(If(Month(b.StatusSubmittedDate) = 11, b.CaseCommission, 0)) As Nov,
      Sum(If(Month(b.StatusSubmittedDate) = 12, b.CaseCommission, 0)) As Decb,
      Sum(b.CaseCommission) As Total
    From
      tblcontacts a Inner Join
      tblcases b On a.ContactID = b.ContactAssignedTo
    Where
      WHERE Year(b.StatusSubmittedDate) = ".(int)$_POST['year']."
    Group By
      a.ContactFullName WITH ROLLUP
    Having
      Sum(b.CaseCommission) > 0.01) q
Order By
  If(q.Adviser Is Null, 1, 0), q.Total Desc

Any advice appreciated.

Improve this question
1
  • 1
    That is not PHP. Why is this question tagged PHP? Commented Jan 7, 2013 at 11:28

2 Answers 2

Reset to default
1

What about changing this WHERE:

Where
  b.StatusSubmittedDate > '2012 - 01 - 01'

to:

Where
  YEAR(b.StatusSubmittedDate) = ".(int)$YEAR_VALUE_FROM_DROPDOWN."

That should be enough...

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

thanks for your suggestion I have probably implemented it wrong though. Updated code is now in original post but this returns "Notice: Undefined variable: SelectYear in C:\wamp\sales.php on line 41". Guessing its not the id of the form field that it needs in where statement?
@gary Nope, it is not the ID of the form, You have to provide the right value from the combobox either from a $_POST or a $_GET array. Let's assume the name of the combobox field is year (thus having <select name="year">...</select>) and You are posting the form then the WHERE would be: Where YEAR(b.StatusSubmittedDate) = ".(int)$_POST['year']."
have also tried that code and reflected in my original post but I also receive "Notice: Undefined index: year". Cant work this out, the output of 'YEAR(b.StatusSubmittedDate)' is always 2012 or 2013 in my query which is correct so I am guessing its the select value that cant be located?
@gary Post the code for Your form where the select is present and also the PHP code where You receive the values submitted...
Hi @shadyyx, thanks againmanaged to work it out, when the page first loads there is no value for the filter so it errors. When I selected a year it then filters the query correctly until the next page load. Somehow need to find how to make 2013 the default year on page load.
|
1

Considering the PHP tag I assume you are showing the results on a PHP bases page. You can use the following code to do what you want.

<form id="yearselect" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>" >
<select name='selyear' onchange='document.getElementById("yearselect").submit()'>
  <option value=''> - Select year </option>
  <option value='2012'>2012</option>
  <option value='2013'>2013</option>
</select>
</form>

<?
$y = date("Y");
if (isset($_POST['selyear']))
{
  if (checkdate(1, 1, $_POST['selyear']) && $_POST['selyear']>=2012)
    $y = $_POST['selyear'];
}

$query = 'Select
  *,
  If(q.Adviser Is Null, 1, 0) As remove
From
  (Select
      a.ContactFullName As Adviser,  
      YEAR(b.CaseDate) As Year,
      Sum(If(Month(b.StatusSubmittedDate) = 1, b.CaseCommission, 0)) As Jan,
      Sum(If(Month(b.StatusSubmittedDate) = 2, b.CaseCommission, 0)) As Feb,
      Sum(If(Month(b.StatusSubmittedDate) = 3, b.CaseCommission, 0)) As Mar,
      Sum(If(Month(b.StatusSubmittedDate) = 4, b.CaseCommission, 0)) As Apr,
      Sum(If(Month(b.StatusSubmittedDate) = 5, b.CaseCommission, 0)) As May,
      Sum(If(Month(b.StatusSubmittedDate) = 6, b.CaseCommission, 0)) As Jun,
      Sum(If(Month(b.StatusSubmittedDate) = 7, b.CaseCommission, 0)) As Jul,
      Sum(If(Month(b.StatusSubmittedDate) = 8, b.CaseCommission, 0)) As Aug,
      Sum(If(Month(b.StatusSubmittedDate) = 9, b.CaseCommission, 0)) As Sep,
      Sum(If(Month(b.StatusSubmittedDate) = 10, b.CaseCommission, 0)) As Oct,
      Sum(If(Month(b.StatusSubmittedDate) = 11, b.CaseCommission, 0)) As Nov,
      Sum(If(Month(b.StatusSubmittedDate) = 12, b.CaseCommission, 0)) As Decb,
      Sum(b.CaseCommission) As Total
    From
      tblcontacts a Inner Join
      tblcases b On a.ContactID = b.ContactAssignedTo
    Where
      b.StatusSubmittedDate > \''.$y.' - 01 - 01\'
      AND b.StatusSubmittedDate < \''.$y.' - 12 - 31\'
    Group By
      a.ContactFullName WITH ROLLUP
    Having
      Sum(b.CaseCommission) > 0.01) q
Order By
  If(q.Adviser Is Null, 1, 0), q.Total Desc'


  //output results
?>

Now I didnt check your query as you mentioned it works. Also instead of posting the form on changing the select list, you could also alter it to use AJAX or whatever to make it better. But I guess you get the idea.

Also I now check for a valid year, not just 2012 or 2013. That depends on what you want.

Improve this answer

5 Comments

Thanks, for your input Hugo much appreciated. This code also returns an Undefined variable: Year, and page error is '\''..' - 01 - 01\' AND b.StatusSubmittedDate < \''..' - 12 - 31\'.
My mistake, i switch from $year to $y halfway during the post and forgot to update some. I updated it. Should work now.
No problem Hugo, I also didnt see it! Have made the changes but still see an error such as "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\''.2013.' - 01 - 01\' AND Year(b.StatusSubmittedDate) < \''.2013.' - 12 -' at line 1"
Then you are not using $query to run the code but copied the query and put it between double quotes instead of the single quotes I used. You can either change it to single quotes or change the lines to b.StatusSubmittedDate > '$y - 01 - 01' AND b.StatusSubmittedDate < '$y - 12 - 31'
Thanks Hugo, I had implemented the other solution which now works before I seen your post, may I say a thank you for your help with this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.