如何使用php [关闭]在json中返回一个mysql select作为数组数组

Question

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.

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

Answer 1

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);
    }

Used as in the following:

    <?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();
    ?>

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.