如何使用 MySQLi 和准备好的语句将一行选择导出到 JSON

Question

我正在处理下面的代码。如何以 JSON 格式导出一行的结果？

我试过这样

$arr = [];

$arr = $stmt->get_result()->fetch();

但我收到此错误：

未捕获的错误：调用未定义的方法 mysqli_stmt::get_result()

$stmt = $mysqli -> prepare('SELECT id, name, email, phone FROM users WHERE id = ?');

$userId = 1; // or $_GET['userId'];

$stmt -> bind_param('i', $userId);

$stmt -> execute();

$stmt -> store_result();

$stmt -> bind_result($id, $name, $email, $phone);

$stmt -> fetch();

Answer 1

您必须使用非常旧的 PHP 版本，可能是 PHP 5.3。我强烈建议尽快升级到最新版本。升级后，您可以使用以下内容。$stmt = $mysqli->prepare('SELECT id, name, email, phone FROM users WHERE id = ?');$userId = 1; // or $_GET['userId'];$stmt->bind_param('i', $userId);$stmt->execute();$json = json_encode($stmt->get_result()->fetch_assoc());

Answer 2

我在这里的 php 文档中找到了一个有用的示例：https ://www.php.net/manual/en/mysqli-stmt.bind-result.php基于该示例，这是我为您推荐的内容：$results = array();$stmt = $mysqli -> prepare('SELECT id, name, email, phone FROM users WHERE id = ?');$userId = 1; // or $_GET['userId'];$stmt -> bind_param('i', $userId);$stmt -> execute();$stmt -> bind_result($id, $name, $email, $phone);while($stmt->fetch()){    $results[] = array(        "id"=>$id,        "name"=>$name,        "email"=>$email,        "phone"=>$phone    );}$stmt->close();$output = json_encode($results);

Answer 3

使用PDO：$db = new PDO("mysql:host=$hostname;dbname=$dbname",$username,$password);$sth = $db->prepare("SELECT id, name, email, phone FROM users WHERE id = ?");$sth->execute([ $userId ]); // or $_GET['userId'];$dbUser = $sth->fetch(PDO::FETCH_ASSOC);echo json_encode($dbUser);如果您的项目API使用：header('Access-Control-Allow-Origin: *'); // For CORSheader('Content-Type: application/json');