使用正确的类型将mysql结果转换为json [英] convert mysql result to json with correct types

问题描述

我知道如何获取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

但是:

db行具有不同的类型，例如int，float，string. 通过使用json_encode()进行转换，所有结果都是字符串.

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'];

我想遍历键并添加0，因为:

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

• 第一个编码规则:干燥-不要重复自己

但是我不能因为:

• 行还具有数字(字符串，日期)以外的其他类型
• 有很多列
• 设计在开发人员中，因此列名经常更改

在此先感谢您的英语不好:-)

Thanks in advance and excuse my bad english :-)

编辑(回答Casimir et Hippolyte的评论问题):

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

我使用ajax调用此php代码以动态获取sql值.在我的javascript代码中，我使用如下结果:

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;

让我们说intval1的json结果为50，计算结果为:

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

"50100"，而不是150

"50100", not 150

推荐答案

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

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() 来获取有关结果集并将具有数字类型的那些列转换为数字. MYSQLI_TYPE_* 常量的值可以在 Mysqli预定义常量.

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

这篇关于使用正确的类型将mysql结果转换为json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！