問題描述
我的 PHP 腳本出現問題，盡管在數據庫中標記為 int 和 tinyint，但從 MySQL 調用的值仍以字符串形式返回.
這是一個問題，因為在將基于 MySQL 日期的數組轉換為 JSON 數據時，應該是整數的值被放在雙引號中，這會在使用該 JSON 數據的 Javascript 和 iPhone 應用程序中引起問題.我得到的 JSON 值看起來像 "key" : "1"，而我想要的是 "key" : 1.
經過一些研究，似乎應該可以將值作為其原生類型獲取，只要一個有 PHP 5.3，并安裝了 mysqlnd 模塊.我有 5.3.3 并且 phpinfo() 似乎表明我已經安裝并運行了 mysqlnd 模塊:
mysqlnd 已啟用版本 mysqlnd 5.0.10 - 20111026但是，我的值仍然作為字符串返回.
我已經查看了 mysqlnd 的 PHP 手冊條目，它是總是有可能我錯過了顯而易見的事情，但我沒有看到任何表明我需要在代碼中執行任何特定操作才能獲得本機值的內容.
我究竟該怎么做才能在 PHP 中獲取 MySQL 函數以提供本機類型的 MySQL 結果?
<小時>為了方便下面的回答，這是我用來連接數據庫的命令:
私有函數databaseConnect(){$this->mysqli = new mysqli(Database::$DB_SERVER, Database::$DB_USERNAME, Database::$DB_PASSWORD);$this->mysqli->set_charset("utf8");返回真；}私有函數 dbConnect(){數據庫::$USE_MYSQLI = extension_loaded('mysqli');if (!$this->databaseConnect()){echo "無法連接到數據庫服務器";拋出新的異常()；}if (!$this->databaseSelectDB()){echo "數據庫服務器已連接，但系統找不到正確的數據庫";拋出新的異常()；}}私有函數databaseQuery($query){返回 $this->mysqli->query($query);}公共函數 doQuery($query){$result = $this->databaseQuery($query);如果($result == FALSE){//ErrorHandler::backtrace();die("此查詢無效:$query");}返回 $result;}私有函數 getRows($table, $matches, $orderBy = array(), $limit = array()){$calcFoundRows = '';如果(計數($限制)> 0){$calcFoundRows = 'SQL_CALC_FOUND_ROWS';}$query = '選擇' .$calcFoundRows .' * 從 ' .$table;如果(計數($匹配)> 0){$query .= 'WHERE';$keys = array_keys($matches);$first = 真；foreach ($keys 作為 $key){如果 (!$first){$query .= ' AND ';}$first = 假；//現在他可以安全地添加到查詢中//這是一個數組的唯一時間是當它被 getSelectedUsers 或 getSelectedArticles 調用時//在這種情況下，它是一個數組的數組，因為鍵(即列名)可能有多個//一個條件如果 (is_array($matches[$key])){$firstForColumn = 真；foreach ($matches[$key] 作為 $conditions){如果 (!$firstForColumn){$query .= ' AND ';}$firstForColumn = 假；//如果值是一個數組，我們會生成一個 OR 選擇如果 (is_array($conditions[1])){$firstOr = true;$query .= '(';foreach ($conditions[1] 作為 $value){如果 (!$firstOr){$query .= '或';}$firstOr = false;//在將他放入查詢之前清理這個人$this->cleanMySQLData($value);if ($conditions[0] == 選擇::$CONTAINS){//$query .= 'MATCH (' . $key . ') AGAINST (' . $value . ') ';$value = trim($value, "'");$value = "'%" .$價值."%'";$query .= $key .' 喜歡 ' .$值；}別的{$query .= $key .' ' .$條件[0].' ' .$值；}}$query .= ')';}別的{//在將他放入查詢之前清理這個人$var = $conditions[1];$this->cleanMySQLData($var);if ($conditions[0] == 選擇::$CONTAINS){//$query .= 'MATCH (' . $key . ') AGAINST (' . $var . ') ';$var = trim($var, "'");$var = "'%" .$var ."%'";$query .= $key .' 喜歡 ' .$var;}別的{$query .= $key .' ' .$條件[0].' ' .$var;}}}}別的{//在將他放入查詢之前清理這個人$this->cleanMySQLData($matches[$key]);$query .= $key .=".$matches[$key];}}}如果(計數($orderBy)> 0){$query .= " ORDER BY ";$first = 真；foreach ($orderBy as $orderCol){如果 (!$first){$query .= ',';}$query .= $orderCol;$first = 假；}}如果(計數($限制)> 0){$query .= ' LIMIT ' .$limit[0];如果(計數($限制)> 1){$query .= ',' .$限制[1];}}$result = $this->doQuery($query);$data = 數組();while ($row = $this->databaseFetchAssoc($result)){$data[] = $row;}如果 (strlen($calcFoundRows) > 0){$numRows = $this->databaseCountFoundRows();$key = '^^' .$表.'_selectionCount';Session::getSession()->putUserSubstitution($key, $numRows);}返回 $data;}我究竟該怎么做才能在 PHP 中獲取 MySQL 函數以提供本機類型的 MySQL 結果?
您連接到數據庫，然后準備查詢，執行它，綁定結果，然后獲取它.
讓我們逐行執行這些步驟:
$conn = new Mysqli('localhost', 'testuser', 'test', 'test');$stmt = $conn->prepare("SELECT id FROM config LIMIT 1");$stmt->execute();$stmt->bind_result($id);$stmt->fetch();var_dump($id);# 這是一個整數！這對我有用.由于您編寫的代碼更加復雜，因此您需要找到查詢數據庫的位置.檢查您是否正在使用 Mysqli::prepare()，如果沒有，介紹一下.
您還需要使用 Mysqli_Stmt::execute() 和然后 Mysqli_Stmt::bind_result() 否則(這里是整數)類型不是為該結果列保留.
I am having a problem in my PHP script where values called from MySQL are being returned as strings, despite being marked in the database as int and tinyint.
This is a problem because when converting an array based on MySQL date into JSON data, values that should be integers are placed in double quotes, which is causing trouble in both Javascript and iPhone apps that use that JSON data. I am getting JSON values that look like "key" : "1", when what I want is "key" : 1.
After doing some research, it seems that it should be possible to get the values as their native type so long as one has PHP 5.3, and the mysqlnd module installed. I have 5.3.3 and phpinfo() seems to indicate I have the mysqlnd module installed and running:
mysqlnd enabled
Version mysqlnd 5.0.10 - 20111026
However, my values are still being returned as strings.
I have looked at the PHP manual entry for mysqlnd, and it's always possible I'm missing the obvious, but I don't see anything that indicates I need to do anything specific in my code to get the native values.
What exactly do I do to get my MySQL functions in PHP to give me the MySQL results in their native type?
In order to fascillitate an answer below, this is the command I use to connect to the database:
private function databaseConnect()
{
$this->mysqli = new mysqli(Database::$DB_SERVER, Database::$DB_USERNAME, Database::$DB_PASSWORD);
$this->mysqli->set_charset("utf8");
return true;
}
private function dbConnect()
{
Database::$USE_MYSQLI = extension_loaded('mysqli');
if (!$this->databaseConnect())
{
echo "Cannot Connect To The Database Server";
throw new Exception();
}
if (!$this->databaseSelectDB())
{
echo "The database server connected, but the system could not find the right database";
throw new Exception();
}
}
private function databaseQuery($query)
{
return $this->mysqli->query($query);
}
public function doQuery($query)
{
$result = $this->databaseQuery($query);
if ($result == FALSE)
{
//ErrorHandler::backtrace();
die("This query did not work: $query");
}
return $result;
}
private function getRows($table, $matches, $orderBy = array(), $limit = array())
{
$calcFoundRows = '';
if (count($limit) > 0)
{
$calcFoundRows = ' SQL_CALC_FOUND_ROWS';
}
$query = 'SELECT ' . $calcFoundRows . ' * FROM ' . $table;
if (count($matches) > 0)
{
$query .= ' WHERE ';
$keys = array_keys($matches);
$first = true;
foreach ($keys as $key)
{
if (!$first)
{
$query .= ' AND ';
}
$first = false;
// now he is safe to add to the query
// the only time this is an array is when this is called by getSelectedUsers or getSelectedArticles
// in that case it is an array of array's as the key (which is the column name) may have more than
// one condition
if (is_array($matches[$key]))
{
$firstForColumn = true;
foreach ($matches[$key] as $conditions)
{
if (!$firstForColumn)
{
$query .= ' AND ';
}
$firstForColumn = false;
// if the value is an array we generate an OR selection
if (is_array($conditions[1]))
{
$firstOr = true;
$query .= '(';
foreach ($conditions[1] as $value)
{
if (!$firstOr)
{
$query .= ' OR ';
}
$firstOr = false;
// clean this guy before putting him into the query
$this->cleanMySQLData($value);
if ($conditions[0] == Selection::$CONTAINS)
{
//$query .= 'MATCH (' . $key . ') AGAINST (' . $value . ') ';
$value = trim($value, "'");
$value = "'%" . $value . "%'";
$query .= $key . ' LIKE ' . $value;
}
else
{
$query .= $key . ' ' . $conditions[0] . ' ' . $value;
}
}
$query .= ')';
}
else
{
// clean this guy before putting him into the query
$var = $conditions[1];
$this->cleanMySQLData($var);
if ($conditions[0] == Selection::$CONTAINS)
{
//$query .= 'MATCH (' . $key . ') AGAINST (' . $var . ') ';
$var = trim($var, "'");
$var = "'%" . $var . "%'";
$query .= $key . ' LIKE ' . $var;
}
else
{
$query .= $key . ' ' . $conditions[0] . ' ' . $var;
}
}
}
}
else
{
// clean this guy before putting him into the query
$this->cleanMySQLData($matches[$key]);
$query .= $key . " = " . $matches[$key];
}
}
}
if (count($orderBy) > 0)
{
$query .= " ORDER BY ";
$first = true;
foreach ($orderBy as $orderCol)
{
if (!$first)
{
$query .= ',';
}
$query .= $orderCol;
$first = false;
}
}
if (count($limit) > 0)
{
$query .= ' LIMIT ' . $limit[0];
if (count($limit) > 1)
{
$query .= ',' . $limit[1];
}
}
$result = $this->doQuery($query);
$data = array();
while ($row = $this->databaseFetchAssoc($result))
{
$data[] = $row;
}
if (strlen($calcFoundRows) > 0)
{
$numRows = $this->databaseCountFoundRows();
$key = '^^' . $table . '_selectionCount';
Session::getSession()->putUserSubstitution($key, $numRows);
}
return $data;
}
What exactly do I do to get my MySQL functions in PHP to give me the MySQL results in their native type?
You connect to the database, then you prepare your query, execute it, bind the result and then you fetch it.
Let's do these steps line-by-line:
$conn = new Mysqli('localhost', 'testuser', 'test', 'test');
$stmt = $conn->prepare("SELECT id FROM config LIMIT 1");
$stmt->execute();
$stmt->bind_result($id);
$stmt->fetch();
var_dump($id); # it's an int!
This works for me. As you wrote your code is more complex, you will need to locate the place where you query the database. Check that you're using Mysqli::prepare() and if not, introduce it.
You will also need to use Mysqli_Stmt::execute() and then Mysqli_Stmt::bind_result() otherwise the (here integer) type is not preserved for that result column.
這篇關于如何確保 MySQL 中的值在 PHP 中保持其類型?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！