php - MySql的内部联接不显示数据在谷歌图表

我是一个完全的新手，并且试图对我给经理和求职者所做的调查进行可视化处理。我正在尝试将结果显示为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>

我有三个表：

已应用

candidate | candname
-----------------------
1           manager
2           applicant01

问题

q_id      | output
---------------------
1           experience
2           strengths
3           cultural_fit

graphtable（是的，我知道我在命名方面很糟糕-直到凌晨工作都无济于事）

candidate | answer |  q_id
     1        7       1
     1       20       2
     1       14       3
     2        9       1
     2       22       2
     2       18       3

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

最佳答案

您正在调整结果，以便每个申请人都在不同的栏中。但是在您的可视化中，您希望每个问题都是一列。因此查询应为：

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]) . ",";
            }