8

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?

miku
  • 181,842
  • 47
  • 306
  • 310
user121196
  • 30,032
  • 57
  • 148
  • 198

3 Answers3

14

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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • 1
    You can also do `SELECT bitfield+0` to cast to int. Is there any difference doing it that way or using `CAST()`? – Mike Dec 10 '13 at 22:12
  • 1
    To be binary-safe one can also use `$bitvalue = ($bitvalue === chr(0x01))` which will assign either `true` or `false` to `$bitvalue`. – Stanimir Stoyanov Jul 05 '15 at 15:00
7

You can also use: ord($bitvalue).

atmozer
  • 71
  • 1
  • 1
  • 1
    Thanks for your contribution, but I think this should be a comment on Marc B's answer - this answer does not stand alone. – Day Oct 21 '12 at 11:21
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient [reputation](http://stackoverflow.com/faq#reputation) you will be able to [comment on any post](http://stackoverflow.com/privileges/comment). – Day Oct 21 '12 at 11:22
  • @Mike - the question asks "why does this happen?". This "answer" does not answer that question. Marc B's answer does answer that question, which is why it has more upvotes. – Day Dec 10 '13 at 21:22
  • @Day Good point. However the accepted answer doesn't actually answer it either. – Mike Dec 10 '13 at 21:24
4

Use the BIN function in your SELECT.

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

paulmorriss
  • 2,579
  • 25
  • 30
webbiedave
  • 48,414
  • 8
  • 88
  • 101