mysql中json对象类型转换,使用正确的类型将mysql结果转换为json

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

解决方案

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)."\n");

echo('fixed types: '.json_encode($fixed)."\n");