在PHP中获取SQL命令的ID

Question

我正在使用chart.js库创建图形。我可以使用用户的个人ID成功地创建图形。但是，我想重写我的代码，这样我就可以从URL中提取用户的ID，并根据这个特定的ID生成图形。

这里是我现在用来生成图中的点的代码：

​

<?php
//setting header to json
header('Content-Type: application/json');

//database
define('DB_HOST', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'test');

//get connection
$mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);

if(!$mysqli){
	die("Connection failed: " . $mysqli->error);
}

//query to get data from the table
$query = sprintf("SELECT treatment_log.bdi, treatment_log.date FROM treatment_log WHERE treatment_fk = 21 ORDER BY created_at");

//execute query
$result = $mysqli->query($query);

//loop through the returned data
$data = array();
foreach ($result as $row) {
	$data[] = $row;
}

//free memory associated with result
$result->close();

//close connection
$mysqli->close();

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

​

以下是页面输出的内容：

​

[{"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"}]

​

在这种情况下，"21“是特定的用户。我熟悉预先准备的声明，并试图将其改写为预先准备的语句。

这里是我的尝试：

​

<?php
//setting header to json
header('Content-Type: application/json');

//database
define('DB_HOST', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'test');

//get connection
$mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);

if(!$mysqli){
	die("Connection failed: " . $mysqli->error);
}

$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);
$result = mysqli_stmt_get_result($stmt);   

//execute query
$results = $mysqli->query($sql);

//loop through the returned data
$data = array();
foreach ($results as $row) {
	$data[] = $row;
}

//free memory associated with result
$results->close();

//close connection
$mysqli->close();

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

​

显然，我的代码似乎不起作用。我们将非常感谢您的帮助。

Answer 1

改变这一点：

$result = mysqli_stmt_get_result($stmt);   

//execute query
$results = $mysqli->query($sql);

//loop through the returned data
$data = array();
foreach ($results as $row) {
    $data[] = $row;
}

这方面：

$data = array();
mysqli_stmt_bind_result($stmt, $bdi, $date);
while(mysqli_stmt_fetch($stmt)) {
    $data[]['bdi'] = $dbi;
    $data[]['date'] = $date;
}