CODEWITHSUNDEEP
mysql
gary
gary

Reputation: 319

Mysql filter query from drop down box

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.

Upvotes: 0

Views: 260

Answers (2)

Hugo Delsing
Hugo Delsing

Reputation: 14173

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.

Upvotes: 1

shadyyx
shadyyx

Reputation: 16065

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

Upvotes: 1

Related Questions