Trying to generate a piechart using dropdown menu and api but there is a json error for table showing invalid string.
Pie file
<?php
$dbHost = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName = 'techyari_demos';
// Create connection and select db
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
?>
<html>
<head>
<!--Load the AJAX API-->
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript" src="jquery-1.9.1.min.js"></script>
<script type="text/javascript">
// Load the Visualization API and the piechart,table package.
google.load('visualization', '1', {'packages':['corechart','table']});
function drawItems(num) {
var jsonPieChartData = $.ajax({
url: "getpiechartdata.php",
data: "q="+num,
dataType:"json",
async: false
}).responseText;
var jsonTableData = $.ajax({
url: "gettabledata.php",
data: "q="+num,
dataType:"json",
async: false
}).responseText;
// Create our data table out of JSON data loaded from server.
var piechartdata = new google.visualization.DataTable(jsonPieChartData);
var tabledata = new google.visualization.DataTable(jsonTableData);
// Instantiate and draw our pie chart, passing in some options.
var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
chart.draw(piechartdata, {
width: 700,
height: 500,
chartArea: { left:"5%",top:"5%",width:"90%",height:"90%" }
});
// Instantiate and draw our table, passing in some options.
var table = new google.visualization.Table(document.getElementById('table_div'));
table.draw(tabledata, {showRowNumber: true, alternatingRowStyle: true});
}
</script>
</head>
<body>
<form>
<select name="pt" onchange="drawItems(this.value)">
<option value="">Select a server:</option>
<?php
$dbHost = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName = 'techyari_demos';
// Make a MySQL Connection
$con = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName) or die(mysql_error());
mysqli_select_db($con,"techyari_demos") or die(mysqli_error());
// Create a Query
$sql_query = "SELECT id, servername FROM server ORDER BY servername ASC";
// Execute query
$result = mysqli_query($con,$sql_query) or die(mysqli_error());
while ($row = mysqli_fetch_array($result)){
echo '<option value='. $row['id'] . '>'. $row['servername'] . '</option>';
}
mysqli_close($con);
?>
</select>
</form>
<div id="chart_div"></div>
<div id="table_div"></div>
</body>
</html>
getpiechartdata
<?php
$q=$_GET["q"];
$dbHost = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName = 'techyari_demos';
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
$sql_query="SELECT * from entry";
// $sql_query = "SELECT nickname, name, j2.label, j2.pointsum FROM user JOIN ( SELECT j1.user_id, j1.label, name, hover, j1.pointsum FROM activityfield JOIN ( SELECT user_id, activity_id, label, field_id , SUM( points.points ) AS PointSum FROM points JOIN activity ON points.activity_id = activity.id WHERE points.user_id=" . $q . " GROUP BY points.user_id, points.activity_id, activity.label, activity.field_id ORDER BY points.activity_id ASC ) AS j1 ON activityfield.id = j1.field_id ) AS j2 ON j2.user_id = user.id WHERE pointsum > 0 ORDER BY j2.pointsum DESC;";
$con = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName)or die(mysql_error());;
if (!$con){ die('Could not connect: ' .mysqli_error());}
mysqli_select_db($con,"techyari_demos");
$result = mysqli_query($con,$sql_query);
echo "{ \"cols\": [ {\"id\":\"\",\"label\":\"Name-Label\",\"pattern\":\"\",\"type\":\"string\"}, {\"id\":\"\",\"label\":\"PointSum\",\"pattern\":\"\",\"type\":\"number\"} ], \"rows\": [ ";
$total_rows = mysqli_num_rows($result)or die(mysqli_error());;
$row_num = 0;
while($row = mysqli_fetch_array($result)){
$row_num++;
if ($row_num == $total_rows){
echo "{\"c\":[{\"v\":\"" . $row['date'] . "-" . $row['sname'] . "\",\"f\":null},{\"v\":" . $row['status'] . ",\"f\":null}]}";
} else {
echo "{\"c\":[{\"v\":\"" . $row['date'] . "-" . $row['sname'] . "\",\"f\":null},{\"v\":" . $row['status'] . ",\"f\":null}]}, ";
}
}
echo " ] }";
mysqli_close($con);
?>
gettabledata
<?php
$q=$_GET["q"];
$dbHost = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName = 'techyari_demos';
$sql_query="SELECT date,sname,dbs,status,updatedby from entry";
$con = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName);
if (!$con){ die('Could not connect: ' . mysql_error()); }
mysqli_select_db($con,"techyari_demos");
$result = mysqli_query($con,$sql_query);
echo "{\"c\":[{\"v\":\"" .'date'. "\",\"f\":null},{\"v\":\"" .'sname' . "\",\"f\":null},{\"v\":\"" .'dbs' . "\",\"f\":null},{\"v\":\"" .'status'. "\",\"f\":null},{\"v\":\"".'updatedby'."\",\"f\":null}]}, ";
$total_rows = mysqli_num_rows($result);
while($row = mysqli_fetch_array($result)){
echo "{\"c\":[{\"v\":\"" . $row['date'] . "\",\"f\":null},{\"v\":\"" . $row['sname'] . "\",\"f\":null},{\"v\":\"" . $row['dbs'] . "\",\"f\":null},{\"v\":\"" .$row['status']."\",\"f\":null},{\"v\":\"" . $row['updatedby']. "\",\"f\":null}]}, ";
}
/* $result = mysqli_query($sql_query2);
while($row = mysqli_fetch_array($result)){
echo "{\"c\":[{\"v\":\"" . $row['servername'] . "\",\"f\":null},{\"v\":\"" . "\",\"f\":null},{\"v\":\"" . "\",\"f\":null},{\"v\":\" Total \",\"f\":null},{\"v\":\"" . $row['dbs'] . "\",\"f\":null}]}";
}
echo " ] }";*/
mysqli_close($con);
?>
Further to my comments (and others comments), create some classes for re-use and readability, create a config for storage of re-usables, and then finally use json_encode() on arrays to create json strings for your ajax. I don't know if they are correctly formed, but they will be valid json strings:
/config.php
<?php
# Used for universal directory separator compatibility
define('DS',DIRECTORY_SEPARATOR);
# Store absolute paths for easy referencing
define('ROOT_DIR',__DIR__);
define('VENDOR_DIR',ROOT_DIR.DS.'core'.DS.'vendors');
# Store database connection credentials this one spot only
define('DB_HOST','localhost');
define('DB_NAME','techyari_demos');
define('DB_USER','root');
define('DB_PASS','');
# Start user session
session_start();
# Create class autoloader so you don't have to worry about including manually
spl_autoload_register(function($class) {
$path = str_replace(DS.DS,DS,VENDOR_DIR.DS.str_replace('\\',DS,$class).'.php');
if(is_file($path))
include_once($path);
});
/core/vendors/Database.php
<?php
class Database
{
private static $con;
private $query;
# Assign database
public static function init()
{
$db = new Database();
return $db->getConnection();
}
# Creates database connection
public function getConnection()
{
if(self::$con instanceof \PDO)
return $this;
try {
self::$con = new \PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,DB_USER,DB_NAME);
}
catch(\PDOException $e) {
}
return $this;
}
# Used to query database
public function query($sql,$bind=false)
{
if(!empty($bind)) {
foreach($bind as $key => $value) {
$skey = ":$key";
$setBind[$skey] = $value;
$this->query = self::$con->prepare($sql);
$this->query->execute($setBind);
}
}
else {
$this->query = self::$con->query($sql);
}
return $this;
}
# Used to fetch results from database
public function getResults($single=false)
{
$row = array();
while($result = $this->query->fetch(\PDO::FETCH_ASSOC)) {
$row[] = $result;
}
if(empty($row))
return $row;
return ($single)? $row[0] : $row;
}
}
/core/vendors/App.php
<?php
class App
{
# Easily return post values even if they don't exist without drawing errors
public function getPost($key=false)
{
if(!empty($key))
return (isset($_POST[$key]))? $_POST[$key] : false;
return $_POST;
}
# Easily return get values even if they don't exist without drawing errors
public function getGet($key=false)
{
if(!empty($key))
return (isset($_GET[$key]))? $_GET[$key] : false;
return $_GET;
}
# Easily return session values even if they don't exist without drawing errors
public function getSession($key=false)
{
if(!empty($key))
return (isset($_SESSION[$key]))? $_SESSION[$key] : false;
return $_SESSION;
}
# Used to render pages
public function render($file)
{
ob_start();
# Create instance of database
$db = Database::init();
# Include page to render
include($file);
# Assign view
$data = ob_get_contents();
ob_end_clean();
# Return for echo
return $data;
}
}
/index.php
<?php
if(!isset($this)) {
include_once(__DIR__.DIRECTORY_SEPARATOR.'config.php');
echo (new App())->render(__FILE__);
exit;
}
?>
<html>
<head>
<!--Load the AJAX API-->
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript" src="jquery-1.9.1.min.js"></script>
<script type="text/javascript">
// Load the Visualization API and the piechart,table package.
google.load('visualization', '1', {'packages':['corechart','table']});
function drawItems(num) {
var jsonPieChartData = $.ajax({
url: "getpiechartdata.php",
data: "q="+num,
dataType:"json",
async: false
}).responseText;
var jsonTableData = $.ajax({
url: "gettabledata.php",
data: "q="+num,
dataType:"json",
async: false
}).responseText;
// Create our data table out of JSON data loaded from server.
var piechartdata = new google.visualization.DataTable(jsonPieChartData);
var tabledata = new google.visualization.DataTable(jsonTableData);
// Instantiate and draw our pie chart, passing in some options.
var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
chart.draw(piechartdata, {
width: 700,
height: 500,
chartArea: { left:"5%",top:"5%",width:"90%",height:"90%" }
});
// Instantiate and draw our table, passing in some options.
var table = new google.visualization.Table(document.getElementById('table_div'));
table.draw(tabledata, {showRowNumber: true, alternatingRowStyle: true});
}
</script>
</head>
<body>
<form>
<select name="pt" onchange="drawItems(this.value)">
<option value="">Select a server:</option>
<?php
$servers = $db->query("SELECT id, servername FROM server ORDER BY servername ASC")->getResults();
foreach($servers as $row) { ?>
<option value="<?php echo $row['id'] ?>"><?php echo $row['servername'] ?></option>
<?php
}
?>
</select>
</form>
<div id="chart_div"></div>
<div id="table_div"></div>
</body>
</html>
/getpiechartdata.php
<?php
if(!isset($this)) {
include_once(__DIR__.DIRECTORY_SEPARATOR.'config.php');
echo (new App())->render(__FILE__);
exit;
}
# Internally retrieve the get value
$q = $this->getGet("q");
# Run your general query
$results = $db->query("SELECT * from entry",array($q))->getResults(true);
# Set base array/object
$base = array(
'cols'=>array(
array(
'id'=>'',
'label'=>'Name-Label',
'pattern' => '',
'type'=>'string'
),
array(
'id'=>'',
'label'=>'PointSum',
'pattern' => '',
'type'=>'number'
)
)
);
# Create default array
$base['rows'] = array();
# Loop results, building on base array/object
foreach($results as $row) {
$base['rows'][] = array(
'c'=>array(
array(
'v'=>$row['date'].' - '.$row['sname'],
'f'=>NULL
),
array(
'v'=>$row['status'],
'f'=>NULL
)
)
);
}
# Return results
echo json_encode($base);