Form Submission using PHP/MySQL date query to a Go

2020-03-30 04:32发布

问题:

Im creating pie charts on google visualization using pulled data from PHP/MySQL.

The chart seems nice, but I wanted to add a calendar (date picker) to make the pie chart dynamic.

My date range picker seems to be working. It pulls the right data from my database.

SELECT DATE:

AFTER SUBMITTING QUERY:

It returns this string: (overall_ban_pos_pie_date.php)

{"cols":[{"id":"0","label":"Column 1","type":"string"},{"id":"1","label":"Count","type":"number"}],"rows":[{"c":[{"v":"String Value 1"},{"v":6}]},{"c":[{"v":"String Value 2"},{"v":4}]}]}

This string is readable by google visualization. If I used this PHP page as my Data Table source for my Pie Chart, It will display its values.

MY PROBLEM/QUESTION IS:

After I clicked on "Submit Query" button, it directs me to the php string page. What I want to happen is when the user selects Start and End dates and clicks on submit query, the pie chart that I have needs to change based on the dates queried from the database and not directed to the overall_ban_pos_pie_date.php (which I used for my Pie chart as data table using JSON string). I want this to redirect me to my

Pie Chart Page: (calendar_test.html)

Can someone tell me how to do that? Thanks in advance.

PHP CODE: (overall_ban_pos_pie_date.php)

<?php
$con = mysql_connect("localhost","root","");
if (!$con)

  die('Could not connect: ' . mysql_error());

mysql_select_db("db_campanaltest", $con);

$j=0;
$k=1;
$l=0;

$label = array("String Value 1","String Value 2");

$cols = '{"cols":[{"id":"'.$j.'","label":"Column 1","type":"string"},{"id":"'.$k.'","label":"Count","type":"number"}],';

$field1 = $_POST['startdate']; 
$field2 = $_POST['enddate']; 

$query = mysql_query("SELECT fk_IntCampID, COUNT( * ) AS count
FROM tbl_trans2
WHERE date
BETWEEN '".$field1."'
AND '".$field2."'
AND fk_IntCampID =  '1'
AND eventScored =  'Y'
AND scoreQuoteSent =  'Y'
OR date
BETWEEN '".$field1."'
AND '".$field2."'
AND fk_IntCampID = '5'
AND eventScored = 'Y'
AND scoreQuoteSent = 'Y'
GROUP BY fk_IntCampID");



while($r = mysql_fetch_assoc($query)){

  $rows[] = '{"c":[{"v":'.'"'. $label[$l].'"},{"v":'. $r['count'].'}]}';
$l++;

}



$google_JSON_row =implode(",",$rows);



echo $cols . '"rows":[',$google_JSON_row ."]}";

?>

HTML PAGE: Displays the calendar and pie chart (hopefully)

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>

  <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
 <script type="text/javascript" src="http://code.jquery.com/jquery-1.6.2.min.js"></script>
  <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"></script>
  <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript"
  src="https://www.google.com/jsapi?autoload={'modules':[{'name':'visualization','version':'1','packages':['corechart','table','piechart']}]}">
</script>

 <script type="text/javascript">


google.setOnLoadCallback(pieChart);


function pieChart() {
      var startdate  = "";
      var enddate  =  "";
         if ($("#datepicker").hasClass('hasDatepicker')) {
            startdate  = $("#datepicker").datepicker('getDate');
             }
           if ($("#datepicker2").hasClass('hasDatepicker')) {
           enddate  = $("#datepicker2").datepicker('getDate');
            }

      var pieJsonData = $.ajax({
          url: "overall_ban_pos_pie_date.php?startdate=" + startdate + "&amp;enddate=" + enddate,
          dataType:"json",
          async: false
          }).responseText;

var pieData = new google.visualization.DataTable(pieJsonData);


var pieChartWrapper = new google.visualization.ChartWrapper({
          'chartType': 'PieChart',
          'containerId': 'pie_div',
      'dataTable':pieData,
          'options': {
    chartArea:{left:10,top:40,height:200,width:360},
    width:300, 
    height:260,
    title: "Neutral Percentage", 
    titleTextStyle:{fontSize:12},
    tooltip:{showColorCode:true},
    legend:{textStyle:{fontSize: 10},position:'left'},
    pieSliceTextStyle:{fontSize: 10}
          }
        });



pieChartWrapper.draw();


}
  </script>

  <script>

  $(document).ready(function() {
    $("#datepicker").datepicker({dateFormat: "yy-mm-dd"});
  });

  $(document).ready(function() {
    $("#datepicker2").datepicker({dateFormat: "yy-mm-dd"});
  });


  $("#pieChart").click(function(e) {
    e.preventDefault();
     pieChart();
  });
  </script>



</head>
<body style="font-size:62.5%;">

  <form action="overall_ban_pos_pie_date.php" method="post">

Start Date: <input type="text" name="startdate" id="datepicker"/>
End Date: <input type="text" name="enddate" id="datepicker2"/>

<input type="submit"  id="pieChart"/>

</form>
<div id="pie_div"></div>     
</body>
</html>

回答1:

Try like this :

$("#pieChart").click(function(e) {
   e.preventDefault();
   pieChart();
});

This will disable the form submit and call the pichart() function. Now modify the var pieJsonData = ... line of pieChart() like this:

var startdate  = "";
var enddate  =  "";
if ($("#datepicker").hasClass('hasDatepicker')) {
    startdate  = $("#datepicker").datepicker('getDate');
}
if ($("#datepicker2").hasClass('hasDatepicker')) {
    enddate  = $("#datepicker2").datepicker('getDate');
}
var pieJsonData = $.ajax({
     url: "overall_ban_pos_pie_date.php?startdate=" + startdate + "&amp;enddate=" + enddate,
     dataType:"json",
     async: false
     }).responseText;