-2

i have a table field that's type is tinyint. so the values in mysql can be stored as 1 for true and 0 for false.

i used php's mysqli module to fetch the data. e.g.

$result = $mysqli->query("SELECT * FROM table_x_y");
$row = $result->fetch_assoc()

now i get a data row of the table. but the fields with the type tinyint are integers not boolean. the problem come with php's json_encode. i want to create a JSON object. But the boolean fields are still integer (in $row) and the encode function treated them as integer not as a boolean.

the result of the json object looks like

[
  {
    "foo": 1,
    "bar": 2
  },
  {
    "foo": 0,
    "bar": 1
  }
]

but i should look like 

[
  {
    "foo": true,
    "bar": 2
  },
  {
    "foo": false,
    "bar": 1
  }
]

Info foo datatype is tinyint. bar datatype is int

JuKe
  • 663
  • 2
  • 7
  • 20
  • Possible duplicate of [Which MySQL data type to use for storing boolean values](https://stackoverflow.com/questions/289727/which-mysql-data-type-to-use-for-storing-boolean-values) – Alex Tartan Jul 04 '17 at 18:16
  • 1
    Why should it interpret `tinyint` as a boolean? You'll need to convert those manually. – tadman Jul 04 '17 at 18:16
  • because boolean types are synonyms for TINYINT(1) – JuKe Jul 04 '17 at 18:30

1 Answers1

1

You need to explicitly typecast $row['foo'] to boolean while creating the dynamic array, kind of like this:

$array = array();
$result = $mysqli->query("SELECT * FROM table_x_y");
while($row = $result->fetch_assoc()){
    $array[] = array('foo' => (bool)$row['foo'], 'bar' => $row['bar']);
}
echo json_encode($array);
Rajdeep Paul
  • 16,887
  • 3
  • 18
  • 37
  • so there is no way to configure mysql to cast tinyint into boolean while fetching the data? – JuKe Jul 04 '17 at 18:33
  • all thread i read, said that tinyint and boolean are synonyms https://stackoverflow.com/questions/11167793/boolean-or-tinyint-confusion?answertab=active#tab-top – JuKe Jul 04 '17 at 18:43
  • @JuKe Let me rephrase my point, from **PHP's perspective** it doesn't automatically convert any binary value(`0` or `1`) to `TRUE` or `FALSE` i.e they are not interchangeably interpreted, unless you explicitly specify it using typecasting. – Rajdeep Paul Jul 04 '17 at 18:50