title: How to select a Month AND year from mysql database using ajax
body: <p>I am using the following script to retrieve all the invoices in a particular month / year.
  The php pulls all the years and dates from the database, groups them together and puts them into a select menu, giving a result similar to :</p>
  
  <pre><code>June - 2013
  July - 2013
  August - 2013
  September - 2013
  </code></pre>
  
  <p>This is the selectsummary.php :</p>
  
  <pre><code>    &lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
      &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt;
      &lt;head&gt;
      &lt;meta http-equiv="Content-Type" content="text/html; charset=utf-8" /&gt;
      &lt;title&gt;Sales Summary&lt;/title&gt;
        &lt;script type="text/javascript" src="../js/jquery/jquery.js"&gt;&lt;/script&gt;
      &lt;script type="text/javascript" src="../js/jqueryui/js/jquery-ui.js"&gt;&lt;/script&gt;
         &lt;link href="../js/select2/select2.css" rel="stylesheet"/&gt;
          &lt;script type="text/javascript" src="../js/select2/select2.js"&gt;&lt;/script&gt;
           &lt;script type="text/javascript"&gt;
              $(document).ready(function() { $("select").select2(); });
          &lt;/script&gt;
  
          &lt;?php
      include '../connectmysqli.php';
      include '../menu.php';
           echo '&lt;link rel="stylesheet" href="../css/template/template.css" /&gt;';
      $salesID = rand().rand();
      $today = date("Y-m-d");
  
      ?&gt;
      &lt;script type="text/javascript" charset="utf-8"&gt;
              $(document).ready(function(){
              $('#selectmonth').on('change', function (){
  
                // THIS IS WHERE I AM TRYING TO WORK OUT HOW TO RETRIEVE THE JSON AND THEN PLACE THE RESULTS INSIDE THE "summarycontent" DIV.     
  
                       $.getJSON('select.php', {monthyear: $(this).val()}, function(data){
                          var invoicerow = '';
                          for (var x = 0; x &lt; data.length; x++) {
                              invoicerow += '&lt;p&gt;' + data[x]['invoiceID'] + '"&gt;' + data[x]['date'] + ' - ' + data[x]['grandtotal'] + ' - ' + data[x]['customerID'] + '&lt;/p&gt;';
                          }
                          $('#summarycontent').html(invoicerow);
                        $("select").select2();
                      });
  
                      });     
                      });
              &lt;/script&gt;
      &lt;/head&gt;
      &lt;body&gt;
          &lt;form method="post" action="addsalesubmit.php"&gt;
          &lt;p&gt;
            &lt;select id="selectmonth"&gt;
              &lt;option&gt;Please Select A Monthly Summary To View&lt;/option&gt;
              &lt;?php
  
      $sql = &lt;&lt;&lt;SQL
      SELECT YEAR(date) AS 'year', MONTHNAME(date) AS 'month'
      FROM `sales`
      GROUP BY YEAR(date), MONTHNAME(date) DESC
      SQL;
  
      if(!$result = $db-&gt;query($sql)){ die('There was an error running the query [' . $db-&gt;error . ']');}
      while($row = $result-&gt;fetch_assoc()){
      echo '&lt;option value="'.$row['month'].'-'.$row['year'].'"&gt;'.$row['month'].' - '.$row['year'].'&lt;/option&gt;';
      }
      echo '&lt;/select&gt;';
              ?&gt;
              &lt;br /&gt;
              &lt;br /&gt;
          &lt;/form&gt;
      &lt;div id="summarycontent"&gt;&lt;/div&gt;
  
      &lt;/body&gt;
      &lt;/html&gt;
  </code></pre>
  
  <p>This is the select.php which the ajax uses to lookup the results and send them back to the main script :</p>
  
  <pre><code>            &lt;?php include '../connectmysqli.php'; ?&gt;
      &lt;?php
      $monthyear = strtotime($_GET['monthyear']);
      $sql = 'SELECT * FROM sales WHERE date = ' . (int)$monthyear;
      $result = $db-&gt;query($sql);
  
      $json = array();
      while ($row = $result-&gt;fetch_assoc()) {
        $json[] = array(
          'invoiceID' =&gt; $row['invoiceID'],
          'date' =&gt; $row['date'],
          'grandtotal' =&gt; $row['grandtotal'],
          'customerID' =&gt; $row['customerID']
        );
      }
      echo json_encode($json);
  
      ?&gt;
  </code></pre>
  
  <p>The problem I am having is im not sure how to convert the text such as 'July-2013' into something the select.php can use. As at the moment if I use chrome developer tools to see whats going on I get the following :</p>
  
  <pre><code>select.php?monthyear=July-2013
  /manda/salessummary
  </code></pre>
  
  <p>So the date is going out ok, but im not sure how to then use it at the other end to select the dates in that month / year as its in the wrong format.</p>
  
  <p>The database is as follows : </p>
  
  <pre><code>     id invoiceID   salesID customerID  vehicleID   date    comments    subtotal    vat grandtotal  description1    qty1T   linetotal1T stock1T stock2T description2    qty2T   linetotal2T stock3T description3    qty3T   linetotal3T stock4T description4    qty4T   linetotal4T stock5T description5    qty5T   linetotal5T discount
      68  1     1512428605    82428579    134722464   2013-07-08      22.48   4.50    26.98   Bridestone Pt34 - 175/55/18/W/63 - (99 In Stock) -...   1   22.48   711022407                                                               
  </code></pre>
  
  <p>EDIT >>>></p>
  
  <p>select.php now looks like this:</p>
  
  <pre><code>                    &lt;?php include '../connectmysqli.php';
  
      $date_convert = date('Y-m-d', strtotime($_POST['monthyear']));
  </code></pre>
  
  <p>// Or, use LIST which would allow you to use the two date parts later.</p>
  
  <p>list($month, $year) = explode('-', $_POST['monthyear']);</p>
  
  <p>$SQL = "SELECT * FROM sales WHERE date = :date";
  $STH = $db->prepare($SQL);
  $STH->bindParam(':date', $date_convert);</p>
  
  <pre><code>    $json = array();
      while ($row = $result-&gt;fetch_assoc()) {
        $json[] = array(
          'invoiceID' =&gt; $row['invoiceID'],
          'date' =&gt; $row['date'],
          'grandtotal' =&gt; $row['grandtotal'],
          'customerID' =&gt; $row['customerID']
        );
      }
      echo json_encode($json);
  
      ?&gt;
  </code></pre>