問題描述

我有一個運行良好的數據庫查詢功能——除了我遇到了顯然是 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 選擇語句返回空的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！