javascript - 如何在jquery High-chart中填充多表数据

问题描述

我正在尝试使用以下代码将 UNION ALL 的多个表数据提取到高图表，但我无法做到这一点。

<?php
require_once("db.php");

$db->beginTransaction();

try{

$query = "SELECT SUM(marks) AS marks, DATE(date_column) AS daTe, 'class 1' AS class FROM table1 Group By DATE(date_column)
UNION ALL
SELECT SUM(marks) AS marks, DATE(date_column) AS daTe, 'class 2' AS class FROM table2 Group By DATE(date_column)
UNION ALL
SELECT SUM(marks) AS marks, DATE(date_column) AS daTe, 'class 3' AS class FROM table3 Group By DATE(date_column)";
$done= $db->prepare($query);
$done->execute();
$display = '';
$row = $done->fetch()
    $result[] = $row;
      //$display .="{name:".$row->class.", data:".$row->marks.", date:".$row->daTe."},";

$db->commit();
}
catch(PDOException $e){
   echo 'Connection failed: ' . $e->getMessage();
}

表 1（第 1 类）

表 2（第 2 类）

表 3（第 3 类）

标签： javascriptphpmysqlchartshighcharts

我已更改您的查询以获得所需的输出。结果将如下表所示。

查询给出如下结果。

<?php
require_once("db.php");

$db->beginTransaction();
try{    
    $query = "SELECT dr.dates, SUM(a.marks) AS t1marks, SUM(b.marks) AS t2marks, SUM(c.marks) AS t3marks  FROM 
    (SELECT DISTINCT(DATE(date_column)) AS dates FROM `table1`
    UNION
    SELECT DISTINCT(DATE(date_column)) FROM `table2`
    UNION
    SELECT DISTINCT(DATE(date_column)) FROM `table3`) AS dr 
    LEFT JOIN table1 AS a ON dr.dates = DATE(a.date_column) 
    LEFT JOIN table2 AS b ON dr.dates = DATE(b.date_column)
    LEFT JOIN table3 AS c ON dr.dates = DATE(c.date_column)
    GROUP BY dr.dates ORDER BY dates ASC";
    $done= $db->prepare($query);
    $done->execute();
    $rows = $done->fetchAll(PDO::FETCH_ASSOC);
    $dates = $t1marks = $t2marks = $t3marks = array();

    foreach ($rows as $row) {
        $dates[] = $row['dates'];
        $t1marks[] = $row['t1marks'] == null ? 0 : (int) $row['t1marks'];
        $t2marks[] = $row['t2marks'] == null ? 0 : (int) $row['t2marks'];
        $t3marks[] = $row['t3marks'] == null ? 0 : (int) $row['t3marks'];
    }

    $output = json_encode( array( array( 'data' => $t1marks, 'name'  => 'class 1'),
        array( 'data' => $t2marks, 'name'  => 'class 2'),
        array( 'data' => $t3marks, 'name'  => 'class 3')
     ) );   

    $db->commit();
}
catch(PDOException $e){
   echo 'Connection failed: ' . $e->getMessage();
}
?>
<!DOCTYPE html>
<html>
<body>
   <div id="container" style="height: 370px; width: 100%;"></div>

<script src="https://code.highcharts.com/highcharts.js"></script>
<script src="https://code.highcharts.com/modules/series-label.js"></script>
<script src="https://code.highcharts.com/modules/exporting.js"></script>
<script src="https://code.highcharts.com/modules/export-data.js"></script>
<script>

   Highcharts.chart('container', {

    title: {
    text: 'Solar Employment Growth by Sector, 2010-2016'
    },

    subtitle: {
    text: 'Source: thesolarfoundation.com'
    },

    yAxis: {
        title: {
            text: 'Number of Employees'
        }
    },
    xAxis: { categories: <?php echo json_encode($dates); ?> },
    legend: {
        layout: 'vertical',
        align: 'right',
        verticalAlign: 'middle'
    },

    plotOptions: {
        line: {
      dataLabels: {
        enabled: true
      },
      enableMouseTracking: false
    }
    },

    series: <?php echo $output; ?>,    

    responsive: {
        rules: [{
            condition: {
                maxWidth: 500
            },
            chartOptions: {
                legend: {
                   layout: 'horizontal',
                    align: 'center',
                    verticalAlign: 'bottom'
                }
            }
        }]
    }

});
</script>
</body>
</html>

根据小提琴，以下数组中的以下元素应匹配。

我们可以为不匹配的列元素添加 null 或零。在这里，我添加了 0 以获得优雅的图形外观。其他明智的线将在值之间中断。

根据您的输入输出图表..

javascript - 如何在jquery High-chart中填充多表数据

问题描述

解决方案

推荐阅读