使用PHP / MySQL日期查询表单提交到Google Visualization页面 [英] Form Submission using PHP/MySQL date query to a Google Visualization page
问题描述
图表看起来不错，但我想添加一个日历（日期选择器）到使饼图动态化。
我的日期范围选择器似乎正在工作。它从我的数据库中提取正确的数据。
SELECT DATE：
< img src =https://i.stack.imgur.com/lE3XW.jpgalt =在这里输入图片描述>
提交查询后： 返回以下字符串：（overall_ban_pos_pie_date.php） 这个字符串可以被谷歌可视化读取。如果我使用这个PHP页面作为我的饼图的数据表源代码，它将显示它的值。 我的问题/问题是： 当我点击提交查询按钮后，它将我引导到php字符串页面。我想要发生的是，当用户选择开始和结束日期并单击提交查询时，我需要根据从数据库查询的日期更改饼图，而不是直接指向overall_ban_pos_pie_date.php（我用它我的饼图使用JSON字符串作为数据表）。我希望它能将我重定向到我的
$ b
{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}]}]}
$ b 饼图页面：（calendar_test.html）
Can有人告诉我该怎么做？
PHP代码：（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）;
$ b $ col $ ='{cols：[{id：''。$ j。'，label：Column 1，type：string}， { ID： $ķ。 '， 标签： 计数， 类型： 号码}]，';
$ 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日期
'BETA'''。$ field1。''
AND'。$ field2。''
AND fk_IntCampID ='5'
AND eventScored ='Y'
AND scoreQuoteSent = 'Y'
GROUP BY fk_IntCampID）;
$ b $ while（$ r = mysql_fetch_assoc（$ query））{
$ rows [] ='{c：[{ v：'。'''。$ label [$ l]。'}，{v：'。 $ R [计数]}]}。
$ l ++;
}
$ google_JSON_row = implode（，，$ rows）;
echo $ cols。 'rows：['，$ google_JSON_row。]};
？>
HTML PAGE：显示日历和饼图（希望）
<！DOCTYPE html PUBLIC - // W3C // DTD XHTML 1.0 Strict // ENhttp://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 =stylesheettype =文本/ CSS/>
< script type =text / javascriptsrc =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-typecontent =text / html; charset = utf-8/>
< script type =text / javascriptsrc =https://www.google.com/jsapi>< / script>
< script type =text / javascript
src =https://www.google.com/jsapi?autoload={'modules':[{'name':'visualization'， '版本'： '1'， '包'：[ 'corechart'， '表'， '饼图']}]}>
< / script>
< script type =text / javascript>
google.setOnLoadCallback（pieChart）;
函数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（{$ b $'chartType'：'PieChart'，$ b $'containerId'：'pie_div'，
'dataTable'：pieData，
'options'：{
chartArea：{left：10，top：40，height：200，width：360}，
width：300，
$ height：260，
title：中性百分比，
titleTextStyle：{fontSize：12}，
tooltip：{showColorCode：true}，
legend：{textStyle： {fontSize：10}，position：'left'}，
pieSliceTextStyle：{fontSize：10}
}
}）;
pieChartWrapper.draw（）;
}
< / script>
< script>
$ b $（document）.ready（function（）{
$（＃datepicker）。datepicker（{dateFormat：yy-mm-dd}）;
}）;
$ b $（document）.ready（function（）{
$（＃datepicker2）。datepicker（{dateFormat：yy-mm-dd}）;
}）;点击（功能（e）{
e.preventDefault（）;
pieChart（）;
}（＃pieChart ）;
< / script>
< / head>
< body style =font-size：62.5％;>
< form action =overall_ban_pos_pie_date.phpmethod =post>
开始日期：< input type =textname =startdateid =datepicker/>
结束日期：< input type =textname =enddateid =datepicker2/>
< input type =submitid =pieChart/>
< / form>
< div id =pie_div>< / div>
< / body>
< / html>
试试像这样：
$（＃pieChart）。click（function（e）{
e.preventDefault（）;
pieChart（）;
}）;
这将禁用表单submit并调用 pichart（）函数。现在修改 var pieJsonData = ... 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;
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 + "&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>
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 + "&enddate=" + enddate,
dataType:"json",
async: false
}).responseText;
这篇关于使用PHP / MySQL日期查询表单提交到Google Visualization页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
