时间戳

首页 > 解决方案 > MySql Inner Join 不在 Google 图表中显示数据

php - MySql Inner Join 不在 Google 图表中显示数据

问题描述

我是一个完全的新手，并试图对我向经理和求职者进行的调查进行可视化。我试图在谷歌图表中将结果显示为折线图，经理和申请人在 Y 轴上输入，在 X 轴上询问他们的主题。我使用内部连接将他们的答案拉到一个表中，并将主题作为行，将经理和申请人的答案作为列。但它不会显示结果，并且在检查页面时出现“未定义变量”错误。

      <?php include "db.php";?> 
<html>
  <head>
    <script type="text/javascript" 
src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
      google.charts.load('current', {'packages':['corechart']});
      google.charts.setOnLoadCallback(drawChart);

      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['experience', 'strengths', 'cultural_fit'],
          <?php
            $query="select p.output, sum(case when r.candname = 'manager' 
then s.answer else 0 end) as Manager, sum(case when r.candname = 
'applicant01' then s.answer else 0 end) as Applicant01 from questions p 
inner join graphtable s on p.q_id = s.q_id inner join applied r on 
s.candidate = r.candidate group by p.output";
            $res=mysqli_query($conn,$query);
            while($data= mysqli_fetch_array($res)){
                $year=$data['experience'];
                $sale=$data['strengths'];
                $expense=$data['cultural_fit'];

          ?>
            ['<?php echo $experience;?>',<?php echo $strengths;?>,<?php 
echo $cultural_fit;?>],
          <?php
               }

          ?>
        ]);

        var options = {
          title: 'Score comparison',
          curveType: 'function',
          legend: { position: 'bottom' }
        };

        var chart = new 
google.visualization.LineChart(document.getElementById('curve_chart'));

        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id="curve_chart" style="width: 900px; height: 500px"></div>
  </body>
</html>

我有三张桌子：

  1. 应用
candidate | candname
-----------------------
1           manager
2           applicant01
  1. 问题
q_id      | output
---------------------
1           experience
2           strengths
3           cultural_fit
  1. 图形表（是的，我知道我在命名东西方面很糟糕 - 直到凌晨才解决这个问题并没有帮助）
candidate | answer |  q_id
     1        7       1
     1       20       2
     1       14       3
     2        9       1
     2       22       2
     2       18       3

我非常感谢您能提供的任何帮助 - 在此先感谢！特别感谢您以最简单的方式回答！

标签： phpmysqlgoogle-visualization

解决方案

您正在旋转结果，以便每个申请人位于不同的列中。但是在您的可视化中，您希望每个问题都成为一列。所以查询应该是：

SELECT a.candname,
       SUM(CASE WHEN q.output = 'experience' THEN g.answer END) AS experience,
       SUM(CASE WHEN q.output = 'strengths' THEN g.answer END) AS strengths,
       SUM(CASE WHEN q.output = 'cultural_fit' THEN g.answer END) AS cultural_fit
FROM questions AS q
JOIN graphtable AS g ON q.q_id = g.q_id
JOIN applied AS a ON a.candidate = g.candidate
GROUP BY g.candidate

然后您需要在获取后设置正确的变量名称：

            while($data= mysqli_fetch_array($res)){
                $candname = $data['candname']
                $experience=$data['experience'];
                $strengths=$data['strengths'];
                $cultural_fit=$data['cultural_fit'];
                echo json_encode([$candname, $experience, $strengths, $cultural_fit]) . ",";
            }

推荐阅读