长文本字段上准备好的 mysqli 选择语句返回空 [英] 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，将数据类型切换为文本即可解决问题.但是，在我的情况下，我真的更愿意在我可以预见的时间里尽可能长地离开该字段，这些额外的空间将是有价值的.

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!

感谢 Corina 的回答，我已经解决了这个问题——对于遇到问题的其他人，您只需在 mysql_stmt_execute 命令之后添加以下内容:

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);

推荐答案

我通过调用 mysqli_stmt_store_result 在绑定数据之前.

I managed to solve the same issue by calling mysqli_stmt_store_result before binding the data.

有人遇到了同样的问题并在 php.net 网站:

Someone had the same problem and shared the answer on the php.net website:

显然，如果你有长文本，你必须打电话在使用 bind_result 之前 store_result.

Apparently, if you have longtext present, you HAVE to call store_result before using bind_result.

http://bugs.php.net/bug.php?id=47928

这篇关于长文本字段上准备好的 mysqli 选择语句返回空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！