PHP PDO Bit(1)返回错误的数据类型 [英] PHP PDO Bit(1) returns wrong data type

问题描述

当我使用PDO运行此查询到mysql db时，它返回错误的数据类型.

When I run this query with PDO to a mysql db it returns wrong datatypes.

<?php
$parameters = array(":1",92323);

$query = " SELECT s.Site_ID,s.Site_Url,s.Site_Name, s.Site_Approved, s.Site_Status, s.Thumbnailed ,st.Description AS Site_Status_Desc 
FROM Sites s
LEFT JOIN Sites_Status st ON st.Status_ID = s.Site_Status
WHERE s.Account_ID = :1";

try {

    $this->DBH = new PDO("mysql:host={$host};dbname={$db}", $user, $pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

    $this->stmt = $this->DBH->prepare($query);


    if(count($parameters)>0) {

        foreach ($parameters as $key => $var) {

            switch ($var) {
                case is_int( $var ):
                    $this->stmt->bindValue($key,$var, PDO::PARAM_INT);
                    break;
                case is_bool( $var ):
                    $this->stmt->bindValue($key,$var, PDO::PARAM_BOOL);
                    break;
                case is_null( $var ):
                    $this->stmt->bindValue($key,$var, PDO::PARAM_NULL);
                    break;

                default:
                    $this->stmt->bindValue($key,$var, PDO::PARAM_STR);
                    break;
            }


        }

    }

    if($this->stmt->execute()) {

        // Set how many rows the query resulted in
        $this->num_rows = $this->stmt->rowCount();

        return $this->stmt->fetchObject();
    } else {
        return false;
    }

 } catch (PDOException $e) {
    $this->error_handler($e);
}

除BIT字段外，所有行都作为数据类型的字符串而变成字符串...

All rows becomes strings as data types, except the BIT field, it becomes something different...

public 'Site_Approved' => string '�' (length=1)

是否有一种动态方式使PDO返回正确的数据类型?

Is there a dynamic way to make PDO return correct data types ?

推荐答案

您正在使用Bit(1)字段表示布尔值(TRUE/FALSE).

You're using a Bit(1) field to represent a boolean value (TRUE/FALSE).

数据库客户端将位字段(可以大于一位)映射到字符串，其中一个字符代表一个八位位组.

The database client maps bit fields (which can be larger than one bit) onto strings in which one character represents an Octet.

您还可以通过ord()函数将Bit(1)字段用作PHP字符串，因为它将字符串视为一个八位位组:

You can than just use your Bit(1) field as PHP string via the ord() function as it treats the string as a single Octet:

if (ord($Site_Approved)) {
     ...
}

您不能直接使用$Site_Approved，因为它是一个字符串，并且无论是否设置了第一位，它始终会评估为TRUE.

You can't use $Site_Approved directly because it's a string and it would always evaluate to TRUE regardless if it's first bit is set or not.

或者，您可以将SQL查询中的数据库值已经强制转换为十进制，这可能是您要查找的内容:

Alternatively, you can cast the database value in the SQL query already to a decimal which might be what you're looking for:

s.Site_Approved+0 AS Site_Approved

0到1范围内的十进制值的行为与PHP的布尔值非常相似(它们只是不共享类型，其余相同).

Decimal values in the range of 0 to 1 behave very similar to PHP's booleans (they just don't share the type, the rest is the same).

这篇关于PHP PDO Bit(1)返回错误的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！