PHP PDO Bit (1) returns invalid data type

Question

When I run this query with PDO in mysql db, it returns the wrong data types.

<?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 strings become strings as data types, except for the BIT field, it becomes something else ...

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

Is there a dynamic way to get PDOs to return the correct data types?

Answer 1

You use the bit field (1) to represent a boolean value ( TRUE / FALSE ).

The database client maps bit fields (which may be more than one bit) onto lines in which one character represents an octet.

You can use the bit (1) field as a PHP string using the ord() function, since it treats the string as one octet:

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

You cannot use $Site_Approved directly because it is a string, and it will always evaluate to TRUE regardless of whether the first bit is set or not.

Alternatively, you can specify the database value in the SQL query already in decimal value, which may be what you are looking for:

 s.Site_Approved+0 AS Site_Approved 

Decimal values ​​ranging from 0 to 1 behave very much like PHP booleans (they just don't share the type, the rest is the same).