如何使用PHP在JSON返回一个选择MySQL作为一个数组的数组 [英] how to return a mysql select as an array of arrays in json using php

问题描述

我想导出一个MySQL的要求（选择）阵列的一个JSON阵列，使 JSON.parse 在JavaScript会返回一个数组的数组，而不是一个对象作为会发生什么如下回答类似问题的阵列 previously问。

I am trying to export a mysql request (select) to a json array of array, so that JSON.parse in javascript will return an array of arrays and not an array of objects as what will happen following answers to similar questions previously asked.

什么应改为翻译一个MySQL的请求到数组的JSON数组？

What should be changed to translate a mysql request into a json array of array?

推荐答案

有关 json_en code 返回数组的一个JSON数组，你需要一个数字索引数组。

For json_encode to return a JSON array of array you need a numerically indexed array.

这可以用下面的通用功能来获得：

This can be obtained with the following, generic function:

    function SqlSelectToJsonTable($result,$withNames=false){
        // result a mysqli::query object, 
        // withNames: boolean, should the column names be returned as first row
        $return_arr = array();
        echo $result->num_rows;
        if ($result->num_rows > 0){
            if($withNames == true){
                $return_arr[0] = array();
                $fields = $result->fetch_fields();
                foreach ($fields as $field){
                    echo "column: ".$field->name." ";
                    array_push($return_arr[0],$field->name);
                }
            }
            while($row = $result->fetch_assoc()) {
                $return_arr[]= array_values($row);
            }
        } else {
            echo "0 results";
        }
        return json_encode($return_arr);
    }

在以下用作：

    <?php
    $servername = "localhost";
    $username = "user";
    $password = "password";
    $dbname = "test";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    $sql = "SELECT id, title FROM Posts";
    $result = $conn->query($sql);

    $jsonTable = SqlSelectToJsonTable($result);
    echo '<br/>'.$jsonTable;

    echo "<script type=\"text/javascript\">
        var jsTable = JSON.parse('".$jsonTable."');
        </script>";

    $conn->close();
    ?>

在哪里jsTable将有效是一个数组的数组。注意选项 withNames 允许的列名，如果你尝试的通过这个数组的JavaScript谷歌可视化。

Where jsTable will effectively be an array of arrays. Note the option withNames allowing to add the column names as the first row, usefull if you try to pass this array to javascript for google visualization.

这篇关于如何使用PHP在JSON返回一个选择MySQL作为一个数组的数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！