长文本字段上准备好的mysqli select语句返回为空 [英] Prepared mysqli select statement on longtext field is coming back empty
问题描述
我有一个运行良好的数据库查询功能-除了我遇到了mysqli准备的语句和长文本字段的一个明显问题.发生的事情是，即使通过phpMyAdmin运行查询也可以，但longtext字段始终为空.根据 http://www.workinginboxershorts.com/php -mysqli-returns-empty-variables-from-longtext-column ，将数据类型切换为text即可解决此问题.但是，就我而言，我真的更愿意将字段保留为长文本，因为我可以预见到，多余的空间将是有价值的.
我正在使用参数化查询，这显然是问题所在.这是我的功能:
// Bind results to an array
// $stmt = sql query, $out = array to be returned
function stmt_bind_assoc (&$stmt, &$out) {
$data = mysqli_stmt_result_metadata($stmt);
$fields = array();
$out = array();
$fields[0] = $stmt;
$count = 1;
while($field = mysqli_fetch_field($data)) {
$fields[$count] = &$out[$field->name];
$count++;
}
call_user_func_array('mysqli_stmt_bind_result', $fields);
}
// DB Query
// $query = SQL query, $params = array of parameters, $rs = whether or not a resultset is expected, $newid = whether or not to retrieve the new ID value;
// $onedimensionkey = key required to convert array into simple one dimensional array
function db_query($query, $params, $rs = true, $newid = false, $onedimensionkey = false) {
$link = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
if (!$link) {
print 'Error connecting to MySQL Server. Errorcode: ' . mysqli_connect_error();
exit;
}
// Prepare the query and split the parameters array into bound values
if ($sql_stmt = mysqli_prepare($link, $query)) {
if ($params) {
$types = '';
$new_params = array();
$params_ref = array();
// Split the params array into types string and parameters sub-array
foreach ($params as $param) {
$types .= $param['type'];
$new_params[] = $param['value'];
}
// Cycle the new parameters array to make it an array by reference
foreach ($new_params as $key => $parameter) {
$params_ref[] = &$new_params[$key];
}
call_user_func_array('mysqli_stmt_bind_param', array_merge(array($sql_stmt, $types), $params_ref));
}
}
else {
print 'Error: ' . mysqli_error($link);
exit();
}
// Execute the query
mysqli_stmt_execute($sql_stmt);
// If there are results to retrive, do so
if ($rs) {
$results = array();
$rows = array();
$row = array();
stmt_bind_assoc($sql_stmt, $results);
while (mysqli_stmt_fetch($sql_stmt)) {
foreach ($results as $key => $value) {
$row[$key] = $value;
}
$rows[] = $row;
}
if ($onedimensionkey) {
$i = 0;
foreach ($rows as $row) {
$simplearray[$i] = $row[$onedimensionkey];
$i++;
}
return $simplearray;
}
else {
return $rows;
}
}
// If there are no results but we need the new ID, return it
elseif ($newid) {
return mysqli_insert_id($link);
}
// Close objects
mysqli_stmt_close($sql_stmt);
mysqli_close($link);
}
根据我发布的链接，有一个变通方法涉及完成工作的顺序，但是我以与示例完全不同的方式处理查询，或者只是不了解重要的事情. /p>
感谢任何可以提供帮助的人！
感谢Corina的回答，我已经解决了这一问题-对于遇到此问题的其他人，您只需要在mysql_stmt_execute命令后添加以下内容即可:
// Execute the query
mysqli_stmt_execute($sql_stmt);
// Store results
mysqli_stmt_store_result($sql_stmt);
我设法通过调用 php.net网站:
显然，如果您有长文本，您必须打电话 store_result，然后再使用bind_result.
I've got a database query function that works well -- except that I'm running into what's apparently a known issue with mysqli prepared statements and longtext fields. What happens is that the longtext field always comes up empty even though running the query through phpMyAdmin works fine. According to http://www.workinginboxershorts.com/php-mysqli-returns-empty-variables-from-longtext-column, switching the datatype to text solves the problem. However, in my case I'd really prefer to leave the field as longtext as I can foresee times when that extra space would be valuable.
I'm using parameterized queries, which evidently is the problem. Here's my function:
// Bind results to an array
// $stmt = sql query, $out = array to be returned
function stmt_bind_assoc (&$stmt, &$out) {
$data = mysqli_stmt_result_metadata($stmt);
$fields = array();
$out = array();
$fields[0] = $stmt;
$count = 1;
while($field = mysqli_fetch_field($data)) {
$fields[$count] = &$out[$field->name];
$count++;
}
call_user_func_array('mysqli_stmt_bind_result', $fields);
}
// DB Query
// $query = SQL query, $params = array of parameters, $rs = whether or not a resultset is expected, $newid = whether or not to retrieve the new ID value;
// $onedimensionkey = key required to convert array into simple one dimensional array
function db_query($query, $params, $rs = true, $newid = false, $onedimensionkey = false) {
$link = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
if (!$link) {
print 'Error connecting to MySQL Server. Errorcode: ' . mysqli_connect_error();
exit;
}
// Prepare the query and split the parameters array into bound values
if ($sql_stmt = mysqli_prepare($link, $query)) {
if ($params) {
$types = '';
$new_params = array();
$params_ref = array();
// Split the params array into types string and parameters sub-array
foreach ($params as $param) {
$types .= $param['type'];
$new_params[] = $param['value'];
}
// Cycle the new parameters array to make it an array by reference
foreach ($new_params as $key => $parameter) {
$params_ref[] = &$new_params[$key];
}
call_user_func_array('mysqli_stmt_bind_param', array_merge(array($sql_stmt, $types), $params_ref));
}
}
else {
print 'Error: ' . mysqli_error($link);
exit();
}
// Execute the query
mysqli_stmt_execute($sql_stmt);
// If there are results to retrive, do so
if ($rs) {
$results = array();
$rows = array();
$row = array();
stmt_bind_assoc($sql_stmt, $results);
while (mysqli_stmt_fetch($sql_stmt)) {
foreach ($results as $key => $value) {
$row[$key] = $value;
}
$rows[] = $row;
}
if ($onedimensionkey) {
$i = 0;
foreach ($rows as $row) {
$simplearray[$i] = $row[$onedimensionkey];
$i++;
}
return $simplearray;
}
else {
return $rows;
}
}
// If there are no results but we need the new ID, return it
elseif ($newid) {
return mysqli_insert_id($link);
}
// Close objects
mysqli_stmt_close($sql_stmt);
mysqli_close($link);
}
According to the link that I posted there is a workaround involving the order in which things are done, but either I'm handling my query in a completely different manner than the example or I'm simply not understanding something important.
Thanks to anyone who can help!
EDIT: Thanks to Corina's answer, I've solved this -- for anyone else who runs into the problem, you will simply need to add the following after the mysql_stmt_execute command:
// Execute the query
mysqli_stmt_execute($sql_stmt);
// Store results
mysqli_stmt_store_result($sql_stmt);
I managed to solve the same issue by calling mysqli_stmt_store_result before binding the data.
Someone had the same problem and shared the answer on the php.net website:
Apparently, if you have longtext present, you HAVE to call store_result before using bind_result.
这篇关于长文本字段上准备好的mysqli select语句返回为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
