How to select a Month AND year from mysql database using ajax

Question

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 :

June - 2013
July - 2013
August - 2013
September - 2013

This is the selectsummary.php :

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Sales Summary</title>
      <script type="text/javascript" src="../js/jquery/jquery.js"></script>
    <script type="text/javascript" src="../js/jqueryui/js/jquery-ui.js"></script>
       <link href="../js/select2/select2.css" rel="stylesheet"/>
        <script type="text/javascript" src="../js/select2/select2.js"></script>
         <script type="text/javascript">
            $(document).ready(function() { $("select").select2(); });
        </script>

        <?php
    include '../connectmysqli.php';
    include '../menu.php';
         echo '<link rel="stylesheet" href="../css/template/template.css" />';
    $salesID = rand().rand();
    $today = date("Y-m-d");

    ?>
    <script type="text/javascript" charset="utf-8">
            $(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 < data.length; x++) {
                            invoicerow += '<p>' + data[x]['invoiceID'] + '">' + data[x]['date'] + ' - ' + data[x]['grandtotal'] + ' - ' + data[x]['customerID'] + '</p>';
                        }
                        $('#summarycontent').html(invoicerow);
                      $("select").select2();
                    });

                    });     
                    });
            </script>
    </head>
    <body>
        <form method="post" action="addsalesubmit.php">
        <p>
          <select id="selectmonth">
            <option>Please Select A Monthly Summary To View</option>
            <?php

    $sql = <<<SQL
    SELECT YEAR(date) AS 'year', MONTHNAME(date) AS 'month'
    FROM `sales`
    GROUP BY YEAR(date), MONTHNAME(date) DESC
    SQL;

    if(!$result = $db->query($sql)){ die('There was an error running the query [' . $db->error . ']');}
    while($row = $result->fetch_assoc()){
    echo '<option value="'.$row['month'].'-'.$row['year'].'">'.$row['month'].' - '.$row['year'].'</option>';
    }
    echo '</select>';
            ?>
            <br />
            <br />
        </form>
    <div id="summarycontent"></div>

    </body>
    </html>

This is the select.php which the ajax uses to lookup the results and send them back to the main script :

            <?php include '../connectmysqli.php'; ?>
    <?php
    $monthyear = strtotime($_GET['monthyear']);
    $sql = 'SELECT * FROM sales WHERE date = ' . (int)$monthyear;
    $result = $db->query($sql);

    $json = array();
    while ($row = $result->fetch_assoc()) {
      $json[] = array(
        'invoiceID' => $row['invoiceID'],
        'date' => $row['date'],
        'grandtotal' => $row['grandtotal'],
        'customerID' => $row['customerID']
      );
    }
    echo json_encode($json);

    ?>

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 :

select.php?monthyear=July-2013
/manda/salessummary

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.

The database is as follows :

     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

EDIT >>>>

select.php now looks like this:

                    <?php include '../connectmysqli.php';

    $date_convert = date('Y-m-d', strtotime($_POST['monthyear']));

// Or, use LIST which would allow you to use the two date parts later.

list($month, $year) = explode('-', $_POST['monthyear']);

$SQL = "SELECT * FROM sales WHERE date = :date"; $STH = $db->prepare($SQL); $STH->bindParam(':date', $date_convert);

    $json = array();
    while ($row = $result->fetch_assoc()) {
      $json[] = array(
        'invoiceID' => $row['invoiceID'],
        'date' => $row['date'],
        'grandtotal' => $row['grandtotal'],
        'customerID' => $row['customerID']
      );
    }
    echo json_encode($json);

    ?>

Itay · Answer 1 · 2013-09-12T12:12:47.160

0

Try this.

Use strtotime().

Url: select.php?monthyear=July-2013

$date = mysql_real_escape_string($_POST['monthyear']);

$mysql_date = date('Y-m-d', strtotime($_GET['monthyear']));

$sql = 'SELECT * FROM sales WHERE date = ' . $mysql_date;

P.S - Don't just concatenate parameters to the sql command. We're not in the 90's anymore.

A better option would be using bind_param().

$mysql_date = date('Y-m-d', strtotime($_GET['monthyear']));

$sql = 'SELECT * FROM sales WHERE date = ?';

$sql->bind_param("s", $mysql_date);

edited Sep 12 '13 at 12:12

answered Sep 12 '13 at 11:05

Itay

16,601
2
51
72

When I use the above I get : select.php?monthyear=August-2013 /manda/salessummary GET 500 Internal Server Error text/html jquery.js:8526 Script 226 B 4 B 966 ms 954 ms in console – Iain Simpson Sep 12 '13 at 11:59
this is in the developer console on chrome, I presume its coming from ajax, from the php select file – Iain Simpson Sep 12 '13 at 12:03
Iv just noticed that iv used your bottom PDO method, but im using mysqli to connect to the DB and using mysqli on everything else as I cant work out PDO. – Iain Simpson Sep 12 '13 at 12:08
All I get is this logged and nothing back : select.php?monthyear=August-2013 /manda/salessummary – Iain Simpson Sep 12 '13 at 12:16
Try and debug your PHP file. I can't help from here. – Itay Sep 12 '13 at 12:17

TheCarver · Answer 2 · 2013-09-12T12:30:11.903

0

Convert Data

$date_convert = date('Y-m-d', strtotime($_POST['monthyear']));

// Or, use LIST which would allow you to use the two date parts later.

list($month, $year) = explode('-', $_POST['monthyear']);

Get Results

$SQL = "SELECT * FROM myTable WHERE date = :date";
$STH = $DBH->prepare($SQL);
$STH->bindParam(':date', $date_convert);

edited Sep 12 '13 at 12:30

answered Sep 12 '13 at 11:57

TheCarver

19,391
25
99
149

Im totally lost with PDO its like speaking spanish to me lol, I use mysqli – Iain Simpson Sep 12 '13 at 12:06
Thank!, im getting an error on this line in dreamweaver : $date_convert = date('Y-m-d', strtotime($_POST['monthyear']); – Iain Simpson Sep 12 '13 at 12:27
1

is it supposed to be $date_convert = date('Y-m-d', strtotime($_POST['monthyear'])); – Iain Simpson Sep 12 '13 at 12:28
On the server I get : [12-Sep-2013 06:33:42 America/Denver] PHP Notice: Undefined index: monthyear in /home3/websit52/public_html/manda/salessummary/select.php on line 3 [12-Sep-2013 06:33:42 America/Denver] PHP Notice: Undefined offset: 1 in /home3/websit52/public_html/manda/salessummary/select.php on line 7 [12-Sep-2013 06:33:42 America/Denver] PHP Fatal error: Call to a member function bindParam() on a non-object in /home3/websit52/public_html/manda/salessummary/select.php on line 11 – Iain Simpson Sep 12 '13 at 12:34
Iv just added the updated code above and now get : [12-Sep-2013 06:36:19 America/Denver] PHP Notice: Undefined index: monthyear in /home3/websit52/public_html/manda/salessummary/select.php on line 3 [12-Sep-2013 06:36:19 America/Denver] PHP Notice: Undefined offset: 1 in /home3/websit52/public_html/manda/salessummary/select.php on line 7 [12-Sep-2013 06:36:19 America/Denver] PHP Fatal error: Call to a member function bindParam() on a non-object in /home3/websit52/public_html/manda/salessummary/select.php on line 11 – Iain Simpson Sep 12 '13 at 12:37
Ok, iv just edited my question with the latest version of select.php so you can see whats going on. – Iain Simpson Sep 12 '13 at 12:40
It looks like you have some issues with your array. Please see this question that fixes the problem: http://stackoverflow.com/questions/12040680/php-undefined-index-undefined-offset-work-arounds. – TheCarver Sep 12 '13 at 12:58

score 0 · Answer 3 · answered Sep 12 '13 at 13:43

After much playing around, this finally worked for the select.php :

    <?php 
    error_reporting(E_ALL);
    ini_set('display_errors', '1');

            include '../connectmysqli.php';

            list($month, $year) = explode('-', $_GET['monthyear']);

             $date = date_parse($month);
      $month = $date['month'];


    $sql = "SELECT * FROM sales WHERE MONTH(date) = '$month' AND YEAR(date) = '$year'";
    $result = $db->query($sql);

    $json = array();
            while ($row = $result->fetch_assoc()) {
              $json[] = array(
                'invoiceID' => $row['invoiceID'],
                'date' => $row['date'],
                'grandtotal' => $row['grandtotal'],
                'customerID' => $row['customerID']
              );
            }
            echo json_encode($json);

            ?>

How to select a Month AND year from mysql database using ajax

3 Answers3