通过准备好的语句从数据库中获取行 [英] Fetching rows from database via a prepared statement
本文介绍了通过准备好的语句从数据库中获取行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我最初创建了以下查询,以从数据库中返回一些结果.
I initially created the below query to return some results from my database.
$result = mysqli_query($con, "SELECT bookingId, locationName
FROM bookings
WHERE username = '$one'");
$output = array();
while($row = mysqli_fetch_assoc($result)) {
$output[]=$row;
}
print(json_encode($output));
但是,我现在想使用准备好的语句并尝试了以下方法.但是它总是返回[]
.这是通过准备好的语句返回行的正确方法.
However, I now want to use prepared statements and have tried the below. But It always returns []
. Is this the correct way to return rows via a prepared statement.
$stmt = $con->prepare('SELECT bookingId,locationName
FROM bookings
WHERE username= ?');
$stmt->bind_param('s', $one);
$stmt->execute();
$stmt->bind_result($id, $loc);
$output = array();
while($row = $stmt->fetch()){
$output[] = $row;
}
$stmt->close();
print(json_encode($output));
推荐答案
问题:
与mysqli
中的PDO
不同,函数fetch()
不返回行,它仅返回布尔值或NULL,请检查文档:
Unlike PDO
in mysqli
the function fetch()
does not return a row, it just returns a boolean or NULL, check the docs:
#Value Description
#TRUE Success. Data has been fetched
#FALSE Error occurred
#NULL No more rows/data exists or data truncation occurred
解决方案
$sql = '
SELECT bookingid,
locationname
FROM bookings
WHERE username = ?
';
/* prepare statement */
if ($stmt = $con->prepare($sql)) {
$stmt->bind_param('s', $one);
$stmt->execute();
/* bind variables to prepared statement */
$stmt->bind_result($id, $loc);
$json = array();
/* fetch values */
if($stmt->fetch()) {
$json = array('id'=>$id, 'location'=>$loc);
}else{
$json = array('error'=>'no record found');
}
/* close statement */
$stmt->close();
}
/* close connection */
$con->close();
print(json_encode($json));
这篇关于通过准备好的语句从数据库中获取行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文