How to check boolean field of mysql table and convertion of 0 or 1 value to true/false in php

Question

In my query i have 3 tinyint fields which by fetching gives 0/1 value. But after fetching all the data i want to encode that result into JSON format which should get true/false instead of 0/1. In my code i have done type conversion, which gives proper result. But i should write for all the tinyint fields which i dont want. Is there any method to check tinyint field and for conversion.

PHP CODE: 


$result= mysql_query("select incident_main.inc_id as incident_id,
                      ward_master.ward_id as 'ward_id', 
                      GROUP_CONCAT(incident_log.inclog_date) as revisit_dates ,
                      inc_GISlat as lat,
                      inc_GISlon as lon,
            inc_closed as  b_is_closed,
              inc_closeDate as closed_date,
                      incident_statuscd.inc_status_desc as status, 
                      inc_date as 'incident_date', 
                      inc_patientName as 'patient_name',
                      inc_patientAge as 'patient_age',
                      inc_patientGender as 'patient_gender',
                      inc_patientMobile as'patient_mobile' ,
                      inc_patientAddress as 'patient_address',
                      inc_type as type,
                      inc_typeOther as 'type_other',
                      inc_diagnosis as diagnosis,
                      inc_recurrence as recurrence,
              inc_hospitalized as b_hospitalized, 
                      inc_treatment as treatment ,
                      inc_treatmentOther as 'treatment_other' 
                  from 
                      incident_main,
                      incident_statuscd ,
                      ward_master,
                      incident_log 
                  where 
                      inc_status=incident_statuscd.inc_status_id 
                     and 
                      incident_main.inc_patientWard=ward_master.ward_id 
                     and incident_log.inc_id=incident_main.inc_id ") 
                      or die(mysql_error);
while($res = mysql_fetch_assoc($result)) {
 foreach($res as $key => $value) {
     if(substr( $key, 0, 2 ) === "b_") {
         // assign new value
         $res[substr($key,2,strlen($key))] = !!$value;
         //clean up old value
         unset($res[$key]);
         };

  }
  $data=array_filter($res);

echo json_encode($data, true);

Please help!! Thanks in advance.

Answer 1

There is no real way to check for tinyint in PHP

what you could do is prepend your variables with a b_ and use regex/strpos/substr to filter that to typecast that into boolean. That way you only have to adapt your query and the script will do the casting for you.

$result= mysql_query("select incident_main.inc_id as incident_id,
                      ward_master.ward_id as 'ward_id', 
                      GROUP_CONCAT(incident_log.inclog_date) as revisit_dates ,
                      inc_GISlat as lat,
                      inc_GISlon as lon,
    --->              inc_closed as  b_is_closed,
    --->              inc_reviewed as b_is_reviewed,
                      inc_closeDate as closed_date,
                      incident_statuscd.inc_status_desc as status, 
                      inc_date as 'incident_date', 
                      inc_patientName as 'patient_name',
                      inc_patientAge as 'patient_age',
                      inc_patientGender as 'patient_gender',
                      inc_patientMobile as'patient_mobile' ,
                      inc_patientAddress as 'patient_address',
                      inc_type as type,
                      inc_typeOther as 'type_other',
                      inc_diagnosis as diagnosis,
                      inc_recurrence as recurrence,
    --->              inc_hospitalized as b_hospitalized, 
                      inc_treatment as treatment ,
                      inc_treatmentOther as 'treatment_other' 
                  from 
                      incident_main,
                      incident_statuscd ,
                      ward_master,
                      incident_log 
                  where 
                      inc_status=incident_statuscd.inc_status_id 
                     and 
                      incident_main.inc_patientWard=ward_master.ward_id 
                     and incident_log.inc_id=incident_main.inc_id ") 
                      or die(mysql_error);
while($res = mysql_fetch_assoc($result)) {
 foreach($res as $key => $value) {
     if(substr( $key, 0, 2 ) === "b_") {
         // assign new value
         $res[substr($key,2,strlen($key))] = !!$value;
         //clean up old value
         unset($res[$key]);
     }
  }
  $data=array_filter($res,'not_null');
}


function not_null($test) {
   return !($test === null);
}

EDIT

added a way to revert the boolean marked key to original value for normal code execution. Yes its a duplicate, but might help for further implementation/code execution without renaming everything to b_

Answer 2

Unfortunately, there is no way to distinguish TINYINT and integer in PHP. Therefore you’ll need to list the fields you want to transform. The shorten version of this is shown below:

$a = ['a' => 1, 'b' => 100, 'c' => 0 ];
$bools = ['a', 'c'];   // list of fields to transform

$result = array_map(function($item) use($bools,&$a) { 
  $key=key($a); next($a);  // we need a key to check it’s name
  return in_array($key, $bools) ? (bool)$item : $item; 
}, $a);

print_r(json_encode($result, true));
//⇒ {"a":true,"b":100,"c":false}

The latter will cast integer to bool if and only the key is listed in $bools array. For further details please refer to array_map docs.

Hope it helps.