在 PHP 中使用时间线 Google Chart API - 日期/时间格式问题

我正在尝试实现 Google 的 Timeline API

我正在尝试输出一个看起来像这样的图表:(这是一个硬编码示例)

我当前的代码如下所示:

  '屏幕'，'类型' => '字符串')，数组('id' => '电影', 'type' => 'string'),array('id' => '开始时间', 'type' => 'date'),数组('id' => '结束时间'，'类型' => '日期'));while($res = mysqli_fetch_assoc($qresult)){$result[] = $res;}foreach ($result as $r) {$temp = 数组();$temp[] = array('v' => $r['screen_name']);$temp[] = array('v' => $r['title']);$temp[] = array('v' => 'new Date(0,0,0,'.date('H',strtotime($r['show_startime'])).','.date('i',strtotime($r['show_startime'])).','.date('s',strtotime($r['show_startime'])).')');$temp[] = array('v' => 'new Date(0,0,0,'.date('H',strtotime($r['show_endtime'])).','.date('i',strtotime($r['show_endtime'])).','.date('s',strtotime($r['show_endtime'])).')');$rows[] = array('c' => $temp);}$table['rows'] = $rows;$jsonTable = json_encode($table);打印_r($jsonTable);?><script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script><script type="text/javascript">google.charts.load("current", {packages:["timeline"]});google.charts.setOnLoadCallback(drawChart);函数 drawChart() {var data = new google.visualization.DataTable(<?php echo $jsonTable; ?>);var container = document.getElementById('test');var chart = new google.visualization.Timeline(container);变量选项 = {时间线:{ colorByRowLabel: true },背景颜色:'#ffd'};图表绘制(数据表，选项)；}<div id="测试" ></div>

解决方案

所以我回顾了我的代码而不是定义:

var dataTable = new google.visualization.DataTable(<?php echo $jsonTable; ?>);

我正在定义

var data = new google.visualization.DataTable(<?php echo $jsonTable; ?>);

我定义的变量名不正确，所以我的工作代码现在是:

 '屏幕'，'类型' => '字符串')，数组('id' => '电影', 'type' => 'string'),array('id' => '开始时间', 'type' => 'date'),数组('id' => '结束时间'，'类型' => '日期'));while($res = mysqli_fetch_assoc($qresult)){$result[] = $res;}foreach ($result as $r) {$temp = 数组();$temp[] = array('v' => $r['screen_name']);$temp[] = array('v' => $r['title']);$temp[] = array('v' => 'Date(0,0,0,'.date('H',strtotime($r['show_startime'])).','.date('i',strtotime($r['show_startime'])).','.date('s',strtotime($r['show_startime'])).')');$temp[] = array('v' => 'Date(0,0,0,'.date('H',strtotime($r['show_endtime'])).','.date('i',strtotime($r['show_endtime'])).','.date('s',strtotime($r['show_endtime'])).')');$rows[] = array('c' => $temp);}$table['rows'] = $rows;$jsonTable = json_encode($table);?><script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script><script type="text/javascript">google.charts.load('当前', {回调:drawChart，包:['时间线']});函数 drawChart() {var dataTable = new google.visualization.DataTable(<?php echo $jsonTable; ?>);var container = document.getElementById('example');var chart = new google.visualization.Timeline(container);图表绘制(数据表)；}<div id="示例" ></div>

I am trying to implement Google's Timeline API https://developers.google.com/chart/interactive/docs/gallery/timeline#controlling-the-colors

I am having issues with the date format as I am unsure of how to store/convert my time format to the correct format for the timeline chart.

My Database looks like this:

And I am trying to output a chart to look like this: (This is a hard coded example)

My current code looks like the following:

    <?php
$connect=mysqli_connect("localhost","root","","smartcinema");
if (mysqli_connect_errno())
{
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$query = "SELECT a.screen_name, m.title, s.show_startime, s.show_endtime FROM timetable AS t INNER JOIN showing AS s ON s.showing_id = t.showing_id JOIN auditorium AS a ON a.screen_id = t.screen_id JOIN movies AS m ON m.movie_id = t.movie_id";
$qresult = mysqli_query($connect,$query);
$rows = array();
$table = array();




$table['cols'] = array (
  array('id' => 'Screen', 'type' => 'string'),
  array('id' => 'Movie', 'type' => 'string'),
  array('id' => 'Start time', 'type' => 'date'),
  array('id' => 'End time', 'type' => 'date')
  );

while($res = mysqli_fetch_assoc($qresult)){
  $result[] = $res;
}

foreach ($result as $r) {

  $temp = array();
  $temp[] = array('v' => $r['screen_name']);
  $temp[] = array('v' => $r['title']);
  $temp[] = array('v' => 'new Date(0,0,0,'.date('H',strtotime($r['show_startime'])).','.date('i',strtotime($r['show_startime'])).','.date('s',strtotime($r['show_startime'])).')');
  $temp[] = array('v' => 'new Date(0,0,0,'.date('H',strtotime($r['show_endtime'])).','.date('i',strtotime($r['show_endtime'])).','.date('s',strtotime($r['show_endtime'])).')');
  $rows[] = array('c' => $temp);

}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
print_r($jsonTable);
?>

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
  google.charts.load("current", {packages:["timeline"]});
  google.charts.setOnLoadCallback(drawChart);
  function drawChart() {
    var data = new google.visualization.DataTable(<?php echo $jsonTable; ?>);
    var container = document.getElementById('test');
    var chart = new google.visualization.Timeline(container);


    var options = {
      timeline: { colorByRowLabel: true },
      backgroundColor: '#ffd'
    };

    chart.draw(dataTable, options);
  }
</script>

<div id="test" ></div>

解决方案

So I was looking back over my code and instead of defining:

var dataTable = new google.visualization.DataTable(<?php echo $jsonTable; ?>);

I was defining

var data = new google.visualization.DataTable(<?php echo $jsonTable; ?>);

The variable name i was defining was incorrect so my working code is now :

<?php
$connect=mysqli_connect("localhost","root","","smartcinema");
if (mysqli_connect_errno())
{
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$query = "SELECT a.screen_name, m.title, s.show_startime, s.show_endtime FROM timetable AS t INNER JOIN showing AS s ON s.showing_id = t.showing_id JOIN auditorium AS a ON a.screen_id = t.screen_id JOIN movies AS m ON m.movie_id = t.movie_id";
$qresult = mysqli_query($connect,$query);
$rows = array();
$table = array();




$table['cols'] = array (
  array('id' => 'Screen', 'type' => 'string'),
  array('id' => 'Movie', 'type' => 'string'),
  array('id' => 'Start time', 'type' => 'date'),
  array('id' => 'End time', 'type' => 'date')
  );

while($res = mysqli_fetch_assoc($qresult)){
  $result[] = $res;
}

foreach ($result as $r) {

  $temp = array();
  $temp[] = array('v' => $r['screen_name']);
  $temp[] = array('v' => $r['title']);
  $temp[] = array('v' => 'Date(0,0,0,'.date('H',strtotime($r['show_startime'])).','.date('i',strtotime($r['show_startime'])).','.date('s',strtotime($r['show_startime'])).')');
  $temp[] = array('v' => 'Date(0,0,0,'.date('H',strtotime($r['show_endtime'])).','.date('i',strtotime($r['show_endtime'])).','.date('s',strtotime($r['show_endtime'])).')');
  $rows[] = array('c' => $temp);

}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
?>

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {
  callback: drawChart,
  packages: ['timeline']
});

function drawChart() {
  var dataTable = new google.visualization.DataTable(<?php echo $jsonTable; ?>);
  var container = document.getElementById('example');
  var chart = new google.visualization.Timeline(container);
  chart.draw(dataTable);
}
</script>
<div id="example" ></div>