在AJAX中执行SQL准备语句

Question

我可以通过Chart.js成功地创建一个图形。问题是，我希望根据特定客户的ID生成一个图形，例如，如果我要访问customer id=21&operation=edit。我想使用bdi与客户ID 21的日期生成一个图表。

我尝试过许多不同的解决方案，但都没有成功。我想知道是否存在通过AJAX执行SQL请求并使用这些数据生成图形的问题。

下面是我的SQL代码，它从URL中获取ID并生成一个可由chart.js读取的字符串，以创建一个线条图：

​

<?php
//Gets customer ID from URL 
$cid = htmlentities ($_GET['customer_id']);
//query to get data from the table
$sql = sprintf("SELECT treatment_log.bdi, treatment_log.date FROM treatment_log WHERE treatment_fk = ? ORDER BY created_at");

$stmt = mysqli_stmt_init($conn);
mysqli_stmt_prepare($stmt, $sql);
mysqli_stmt_bind_param($stmt, "i", $cid);
mysqli_stmt_execute($stmt);
$data = array();
mysqli_stmt_bind_result($stmt, $bdi, $date);
while(mysqli_stmt_fetch($stmt)) {
  $data[] = array('bdi' => $bdi, 'date' => $date);
}
//free memory associated with result
$result->close();

//now print the data
print json_encode($data); ?>

​

下面是我用来生成线条图的代码：

​

$(document).ready(function(){
  $.ajax({
    url: "http://localhost/test/data.php",
    method: "GET",
    success: function(data) {
      console.log(data);
      var bdi = [];
      var date = [];

      for(var i in data) {
        date.push( data[i].date);
        bdi.push(data[i].bdi);
      }

      var chartdata = {
        labels: date,
        datasets : [
          {
            label: 'BDI',
            backgroundColor: 'rgba(239, 243, 255, 0.75)',
            borderColor: 'rgba(84, 132, 255, 0.75)',
            hoverBackgroundColor: 'rgba(200, 200, 200, 1)',
            hoverBorderColor: 'rgba(200, 200, 200, 1)',

            data: bdi
          }
        ]
      };

      var ctx = $("#mycanvas");

      var barGraph = new Chart(ctx, {
        type: 'line',
        data: chartdata,
        options: {
          responsive: true,
          legend: {
            position: 'bottom',

          },
          scales: {
            yAxes: [{
              ticks: {
                fontColor: "rgba(0,0,0,0.5)",
                fontStyle: "bold",
                beginAtZero: true,
                maxTicksLimit: 5,
                padding: 20
              },
              gridLines: {
                drawTicks: false,
                drawBorder: false,

              }
            }],
            xAxes: [{
              gridLines: {
                zeroLineColor: "transparent",
                display: false
              },
              ticks: {
                padding: 20,
                fontColor: "rgba(0,0,0,0.5)",
                fontStyle: "bold"
              }
            }]
          },

          tooltips: {
            backgroundColor: 'rgba(255,255,255)',
            titleFontColor: 'rgb(184,189,201)',
            bodyFontColor: 'black',
            displayColors: false,
            borderColor: 'rgb(214,217,225)',
            borderWidth: 1,
            caretSize: 5,
            cornerRadius: 2,
            xPadding: 10,
            yPadding: 10
          }
        }
      });
    },
    error: function(data) {
      console.log(data);
    }
  });
});

​

目前，我使用的是具有一串数据的URL http://localhost/test/data.php：

[{"bdi":"4","date":"2018-07-11"},{"bdi":"1","date":"2018-07-21"},{"bdi":"5","date":"2018-07-21"},{"bdi":"34","date":"2018-07-21"},{"bdi":"34","date":"2018-07-21"},{"bdi":"3","date":"2018-07-22"},{"bdi":"2","date":"2018-07-23"},{"bdi":"12","date":"2018-07-23"},{"bdi":"3","date":"2018-07-24"},{"bdi":"2","date":"2018-07-25"},{"bdi":"12","date":"2018-07-30"},{"bdi":"3","date":"2018-07-30"},{"bdi":"4","date":"2018-07-30"},{"bdi":"11","date":"2018-07-30"}]

而不是使用URL链接数据。我希望AJAX运行我的SQL代码并生成图形(其中treatment_fk是一个变量)。

问题：

1.能否在AJAX中执行一个SQL命令来生成一个图，其中ID是从中收集的变量(我将如何做到这一点？)

2.有更好的方法吗？我该怎么做？

Answer 1

ajax请求中添加一个参数

$.ajax({
    url: "data.php",
    data: { 
        "ChartType": 1/*Or change to drop down selection id*/,
        "customer_id":1
    },
    type: "GET",
    success: function(response) {
        console.log(response);
    },
    error: function(xhr) {

    }
});

您可以在上面看到ChartType，动态地传递它的值，您希望对其分隔sql query/

现在在服务器端：

$cid = htmlentities ($_GET['customer_id']);
$cType = htmlentities ($_GET['ChartType']);

if($cType==1)
    $sql = sprintf("SELECT treatment_log.bdi, treatment_log.date FROM treatment_log WHERE treatment_fk = ? ORDER BY created_at");
else if($cType==2)
    $sql = sprintf("SELECT __ other column name __ WHERE cid=$cid");
else 
    $sql = sprintf("SELECT __ other column name __ WHERE cid=$cid");

我知道这不是完整的代码，但是它会让你知道怎么做。