如何确保 MySQL 中的值在 PHP 中保持其类型?
我的 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.
相关文章