PHP PDO Bit(1) returns wrong data type

Question

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);
}

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

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

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

Answer 1

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.

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)) {
     ...
}

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.

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

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).