php reading mysql bit field returning weird character

Question

I am using mysql_fetch_assoc($query), one of the bit field returns out to be , which is supposedly to be true.
The problem is that I also need to output this to xml and it's an illegal xml character. the charset for the db table is utf-8. why does this happen?

Answer 1

Use the BIN function in your SELECT.

http://dev.mysql.com/doc/refman/5.0/en/bit-field-values.html

Answer 2

MySQL is literally returning 0x00 and 0x01 for the bit fields. You'll have to convert them into something appropriate either PHP-side

$bitvalue = ($bitvalue == 0x01) ? 'TRUE' : 'FALSE'

or in the query:

SELECT CAST(bitfield AS unsigned int)
FROM ...

which will convert it to an int and return as '0' and '1' (0x48 and 0x49).

Just as an aside, some of the older mysql libraries pre-date support for real bit fields in MySQL (when they were silently converted to char(1)) and will trash the values, so if you're stuck with one of those dinosaur versions, you may have to use the query version rather than the PHP-side conversion.