title: MySQL select bit(1) shows as string(3)
body: <p>I have a table with a column <code>bit(1)</code> and the following query: <code>SELECT my_bit FROM my_table</code>.</p>
  
  <p>When I <code>echo</code> the result on a <code>php</code> page the value shows up as <code>string(3)</code> <code>%qu</code> <strong>whether the bit value on a column is 0 or 1</strong>. How is this possible?</p>
  
  <p>The following query resolved the issue when <code>echoing</code> on a web page: <code>SELECT CAST(my_bit AS UNSIGNED) AS my_bit FROM my_table</code>.</p>
  
  <p>However, both of the queries above work on a command line tool. No <code>string(3)</code>s there.</p>
  
  <p>Also everything works when selecting data, no difference between</p>
  
  <ol>
  <li>SELECT * FROM my_table WHERE my_bit=0</li>
  <li>SELECT * FROM my_table WHERE my_bit=(0)</li>
  </ol>
  
  <p>when using command line tool or web interface <code>php</code> pages. (The latter is suggested <a href="https://stackoverflow.com/questions/839596/mysql-how-to-query-a-column-whose-type-is-bit">here</a>.) <code>Echoing</code> has to be done using <code>CAST</code> function but the <code>WHERE</code> is not affected by the parentehis: correct rows are returned.</p>
  
  <h2>Main questions</h2>
  
  <ol>
  <li>How come the returned <code>bit</code> type value is <code>echoed</code> as identical <code>string</code> whether the <code>bit</code> value is 0 or 1?</li>
  <li>Are there any special issues that should be taken into consideration when inserting/selecting data using <code>bit(1)</code> type column? (Quick testing shows that everything works as intended: 0 get inserted as 0 and 1 as 1, but I might be missing something.)</li>
  </ol>
  
  <p>I'm testing this locally with MAMP: PHP 5.3.2 and MySQL 5.1.44. Command line tool refers to Sequel Pro (not MAMP's PhpAdmin). <code>Php</code> pages are <code>utf-8</code> and queries use <code>SET NAMES 'utf8'</code>.</p>
  
  <hr>
  
  <h2>Update: code</h2>
  
  <pre><code>CREATE TABLE `my_table` (
    `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    `my_bit` bit(1) NOT NULL,
    PRIMARY KEY (`id`,`lto_muu`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
  
  INSERT INTO `my_table` (`id`, `my_bit`) VALUES (null, 0), (null,1)
  
  //php page
  //query option 1
  SELECT `id`, `my_bit`  FROM `my_table`
  //query option 2
  SELECT `id`, CAST(`my_bit` AS UNSIGNED) AS `my_bit`  FROM `my_table`
  
  $mysqli = new mysqli("localhost", "root", "root","my_db");
  $q = "SELECT `id`, `my_bit`  FROM `my_table`";//returns 2 rows
  
  $r = $mysqli-&gt;query($q);
  
  while($row = mysqli_fetch_array($r,MYSQLI_ASSOC)){
  echo 'id: ' . $row['id'] . ' - bit: ' . $row['my_bit'] . '&lt;br /&gt;';
  }
  </code></pre>
  
  <p>Query option 1 prints out: </p>
  
  <pre><code>id: 1 - bit: %qu
  id: 2 - bit: %qu
  </code></pre>
  
  <p>Query option 2 prints out: </p>
  
  <pre><code>id: 1 - bit: 0
  id: 2 - bit: 1
  </code></pre>
  
  <hr>
  
  <h2>Update 2: Álvaro's code</h2>
  
  <pre><code>$conn = new mysqli('localhost', 'root', 'root','test');
  //$mysqli-&gt;set_charset('utf8');
  $conn-&gt;query('DROP TABLE IF EXISTS bit_test');
  $conn-&gt;query('CREATE TABLE bit_test (
      my_bit BIT(1) NULL,
      my_multiple_bit BIT(8) NULL
  )');
  $conn-&gt;query("INSERT INTO bit_test (my_bit, my_multiple_bit) VALUES (b'0', b'111')");
  $conn-&gt;query("INSERT INTO bit_test (my_bit, my_multiple_bit) VALUES (b'1', b'10000000')");
  
  //opt 1
  $q = 'SELECT cast(my_bit as unsigned) as my_bit, my_multiple_bit FROM bit_test';
  //opt2 
  //$q = 'SELECT my_bit, my_multiple_bit FROM bit_test';
  $r = $conn-&gt;query($q);
  while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){
      echo bin2hex($row['my_bit']) . '&lt;br /&gt;';
      echo bin2hex($row['my_multiple_bit']) . '&lt;br /&gt;';
      var_dump($row);
      echo '&lt;br /&gt;&lt;br /&gt;';
  }
  </code></pre>
  
  <p>Using <strong>PHP 5.2.12</strong> opt 1 and 2 both print:</p>
  
  <pre><code>30
  07
  array(2) {
    ["my_bit"]=&gt;
    string(1) "0"
    ["my_multiple_bit"]=&gt;
    string(1) ""
  }
  31
  80
  array(2) {
    ["my_bit"]=&gt;
    string(1) "1"
    ["my_multiple_bit"]=&gt;
    string(1) "�"
  }
  </code></pre>
  
  <p>Using <strong>PHP 5.3.2</strong> opt 1 prints:</p>
  
  <pre><code>30
  257175
  array(2) {
    ["my_bit"]=&gt;
    string(1) "0"
    ["my_multiple_bit"]=&gt;
    string(3) "%qu"
  }
  31
  257175
  array(2) {
    ["my_bit"]=&gt;
    string(1) "1"
    ["my_multiple_bit"]=&gt;
    string(3) "%qu"
  }
  </code></pre>
  
  <p>And opt 2:</p>
  
  <pre><code>257175
  257175
  array(2) {
    ["my_bit"]=&gt;
    string(3) "%qu"
    ["my_multiple_bit"]=&gt;
    string(3) "%qu"
  }
  257175
  257175
  array(2) {
    ["my_bit"]=&gt;
    string(3) "%qu"
    ["my_multiple_bit"]=&gt;
    string(3) "%qu"
  }
  </code></pre>
  
  <p>PHP 5.3.2 alvays prints <code>%qu</code>. Does this make any sense?</p>