如何确保MySQL中的值保持其类型在PHP？ [英] How do I make sure that values from MySQL keep their type in PHP?
问题描述
我在PHP脚本中遇到一个问题，尽管在数据库中标记为 int 和 tinyint 。
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.
这是一个问题，因为当将基于MySQL日期的数组转换为JSON数据时，应该是整数的值放在双引号，这导致使用该JSON数据的JavaScript和iPhone应用程序的麻烦。我得到的JSON值看起来像key：1，当我想要的是key：1 。
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.
进行一些研究后，似乎应该可以只要有一个PHP 5.3，并且安装了 mysqlnd 模块，就可以得到这些值作为它们的本地类型。我有5.3.3和 phpinfo（）似乎表明我有 mysqlnd 模块安装并运行：
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.
我看过 PHP手动输入mysqlnd ，它总是可能我错过了明显，但我没有看到任何表明我需要在我的代码中做任何具体的事情以获得本机值。
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.
我在PHP中获取我的MySQL函数给我MySQL
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;
}
推荐答案
我通过PHP获取我的MySQL函数给我的原生类型的MySQL结果究竟是什么？
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!
这适用于我。当你编写代码更复杂，你将需要找到你查询数据库的地方。检查您是否使用 Mysqli :: prepare（）
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.
您还需要使用 Mysqli_Stmt :: execute（） ，然后 Mysqli_Stmt :: bind_result（） 否则不会为该结果列保留（此处为整数）类型。
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？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
