使用正确的类型将mysql结果转换为json

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

我知道如何获取mysql-row并将其转换为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.

db-row有不同的类型,如int,float,string。通过使用json_encode()转换它,所有结果都是字符串。

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:

我想循环键并添加0因为:

first coding rule: DRY - dont repeat yourself

第一个编码规则:DRY - 不要重复自己

but i can't because:

但我不能因为:

row has also other types than numbers (string, date)

row还有其他类型而不是数字(字符串,日期)

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):

编辑(回答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:

我使用ajax调用这个php代码来获取动态的sql值。在我的javascript代码中,我使用如下结果:

result['intvalue1'] += 100;

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

假设intval1的json-result为50,计算结果如下:

"50100", not 150

“50100”,而不是150

3 个解决方案

#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).

下面的代码只是一个概念证明。它需要在函数/方法中进行封装,并在生产中使用它之前进行一些抛光(例如,在循环中调用mysqli_fetch_field()并在处理任何行之前存储它返回的对象,而不是每行一次)。

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.

它使用函数mysqli_fetch_field()来获取有关结果集的每一列的信息,并将那些具有数字类型的列转换为数字。可以在Mysqli预定义常量的文档页面中找到MYSQLI_TYPE_ *常量的值。

// 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");

#2

something like

$row['floatvalue1'] = reset( sscanf ( $row['floatvalue1']  , "%f" ));
$row['floatvalue2'] = reset( sscanf ( $row['floatvalue2']  , "%f" ));
$row['intvalue1'] = reset( sscanf ( $row['intvalue1']  , "%d" ));
json_encode($row);

#3

If you're simply trying to make sure that your values are operable with respect to their type, you need to first cast their type correctly.

如果您只是想确保您的值可以根据其类型进行操作,则需要首先正确地转换它们的类型。

Unless you need them server-side, I would just pass-on the json directly to the front-end and do the work there.

除非你需要服务器端,否则我只需将json直接传递给前端并在那里完成工作。

In Javascript, you could make an attempt at casting the numbers like so:

在Javascript中,您可以尝试将数字转换为:

function tryNumber(string){

    return !isNaN( parseInt(string) ) ? parseInt(string) : string;

}

function tryDate(string){

    return !isNaN( new Date(string).getTime() ) ? new Date(string) : string;

}

tryNumber('foo'); // "hello"
tryNumber('24'); // 24
tryDate('bar'); // "bar"
tryDate('December 17, 1995'); // "Sun Dec 17 1995 00:00:00 GMT+0000 (GMT)"

These two lines attempt to cast the values as a Date/Number. If they can't be cast, they will remain String's.

这两行尝试将值转换为日期/数字。如果他们不能被施放,他们将保持弦乐。

#1

// 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");

#2