在PDO中将列转换为布尔值

Question

I have a field ('done') in the Database which is boolean. The function which GET/ the rows doesn't return a json with that field as boolean but it returns 1 or 0.

function getTasks() {
$sql = "SELECT id,task_name,done FROM tarea ORDER BY id";
try {
    $db = getConnection();
    $stmt = $db->query($sql);
    $tasks= $stmt->fetchAll(PDO::FETCH_OBJ);
    $db = null;
    echo json_encode($tasks);
} catch(PDOException $e) {
    echo '{"error":{"text":'. $e->getMessage() .'}}';
}
}

How can I get THAT column ('done' column) as boolean? I mean, Is posible to cast a column with PDO::PARAM_BOOL ??

Here is my sample of UPDATE that inserts booleans in the database and it works perfectly:

function updateTask($id) {
$request = Slim::getInstance()->request();
$body = $request->getBody();
$task = json_decode($body);
$sql = "UPDATE tarea SET task_name=:task_name, done=:done WHERE id=:id";

try {
    $db = getConnection();
    $stmt = $db->prepare($sql);
    $stmt->bindParam("task_name", $task->task_name);

    $stmt->bindParam("done", $task->done,PDO::PARAM_BOOL);  // cast before execute

    $stmt->bindParam("id", $id);
    $stmt->execute();
    $db = null;
    echo json_encode($task);
} catch(PDOException $e) {
    echo '{"error":{"text":'. $e->getMessage() .'}}';
}
}

Answer 1

In SQL there are no booleans really, as you notice the 1 and 0 .

The right terminology is BIT field. you should not try to modify it, if you retrieve it as 1 and 0 then you should insert it as 1 or 0.

Also note that the JSON in the catch will produce is not valid, never build json manually. So change :

echo '{"error":{"text":'. $e->getMessage() .'}}';

to

echo json_encode(array('error'=>$e->getMessage()));