php - convert mysql result to json with correct types

Question

I know how to get a mysql-row and convert it to json:

$row = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM table WHERE id=1"));
echo json_encode($row); // it's an ajax-call

but:

the db-row has different types like int, float, string. by converting it using json_encode() all results are strings.

Is there a better way to correct the types than this:

$row['floatvalue1'] = 0+$row['floatvalue1'];
$row['floatvalue2'] = 0+$row['floatvalue2'];
$row['intvalue1'] = 0+$row['intvalue1'];

I would like to loop through the keys and add 0 because:

• first coding rule: DRY - dont repeat yourself

but i can't because:

• row has also other types than numbers (string, date)
• there are many columns
• design is in dev, so columns-names often changes

Thanks in advance and excuse my bad english :-)

EDIT (to answer the comment-question from Casimir et Hippolyte):

I call this php-code using ajax to get dynamically sql-values. in my javascript-code i use the results like this:

result['intvalue1'] += 100;

lets say the json-result of intval1 is 50, the calculated result is:

"50100", not 150

Answer 1

The code below is just a proof of concept. It needs encapsulation in a function/method and some polishing before using it in production (f.e. call mysqli_fetch_field() in a loop and store the objects it returns before processing any row, not once for every row).

It uses the function mysqli_fetch_field() to get information about each column of the result set and converts to numbers those columns that have numeric types. The values of MYSQLI_TYPE_* constants can be found in the documentation page of Mysqli predefined constants.

// Get the data
$result = mysqli_query($db, "SELECT * FROM table WHERE id=1");
$row    = mysqli_fetch_assoc($result);

// Fix the types    
$fixed = array();
foreach ($row as $key => $value) {
    $info = mysqli_fetch_field($result);
    if (in_array($info->type, array(
            MYSQLI_TYPE_TINY, MYSQLI_TYPE_SHORT, MYSQLI_TYPE_INT24,    
            MYSQLI_TYPE_LONG, MYSQLI_TYPE_LONGLONG,
            MYSQLI_TYPE_DECIMAL, 
            MYSQLI_TYPE_FLOAT, MYSQLI_TYPE_DOUBLE
    ))) {
        $fixed[$key] = 0 + $value;
    } else {
        $fixed[$key] = $value;
    }
}

// Compare the results
echo('all strings: '.json_encode($row)."
");
echo('fixed types: '.json_encode($fixed)."
");