How can I return the keys from fetch_assoc as strings so that the array can be valid JSON data? (PHP / mysqli)

Question

Here's my function:

<?php   

function getResults($query) {
    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    $conn->set_charset("utf8");
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $result = $conn->query($query);
    if ($result) {
        $rows = array();
        while ($row = $result->fetch_assoc()) {
          $rows[] = $row;
        }
        return $rows;
    } else {
        return mysqli_error($conn);
    }
}

?>

And I use it here:

if ($_POST["action"] == "getCitiesByState") {
    $cities = getResults("SELECT * FROM tbl_cidades WHERE estado_id = ".$_POST["state_id"]);
    echo json_encode($cities, JSON_UNESCAPED_UNICODE);
    die();
}

It outputs:

{id: "8853", estado_id: "26", nome: "Adamantina", cep: "17800000", loc_no_abrev: "Adamantina"},…]

The id, estado_id, nome, etc, all unquoted, form invalid JSON data. How can I return them as strings so that they can be valid JSON?

Here's one example of the output of var_dump($cities)

array(1) { [0]=> array(5) { ["id"]=> string(4) "1778" ["estado_id"]=> string(1) "7" ["nome"]=> string(9) "Brasília" ["cep"]=> string(0) "" ["loc_no_abrev"]=> string(9) "Brasília" } }

What version of PHP are you using? I can't replicate this behaviour. Have you tried doing this manually with a plain array? — miken32, Dec 08 '15 at 21:39
I can't replicate this to. Try to print something like json_encode(['id'=>15, 'name'=>'john snow'], JSON_PRETTY_PRINT); — Sergey Belyakov, Dec 08 '15 at 21:43
Your example works, and bizarrely enough, the other echo prints with quotes as well after I've used your example. It makes no sense. The version is: PHP/5.6.14 — Ericson Willians, Dec 08 '15 at 21:53
It must be related to unicode **as always**, since the whole data is in UTF8 and JSON does not like it (Portuguese characters). — Ericson Willians, Dec 08 '15 at 21:55
Unlikely the database column names would be problematic... Can you do a `var_dump()` of the `$cities` array and add it to the question? — miken32, Dec 08 '15 at 22:01
For some obscure reason, this is not valid JSON. I can't make an Ajax call with it, it keeps giving me Syntax Error. [I have another question about this issue](http://stackoverflow.com/questions/34162425/status-200-ok-same-domain-valid-json-data-and-no-response-ajax). It's too frustrating. — Ericson Willians, Dec 08 '15 at 22:09

score 0 · Answer 1 · answered Dec 08 '15 at 22:17

Things may be getting hung up on the Unicode text; try modifying your database code as follows:

function getResults($query) {
    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    $conn->set_charset("utf8");
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $result = $conn->query($query);
    if ($result) {
        $rows = array();
        while ($row = $result->fetch_assoc()) {
            $rows[] = array_map("utf8_encode", $row);
        }
        return $rows;
    } else {
        return mysqli_error($conn);
    }
}

(By the way, you're introducing a lot of overhead by reconnecting to the database with every query.)

score 0 · Answer 2 · answered Aug 28 '17 at 09:51

0

You can simply get the keys by looping the fetched array

    while($row = $result->fetch_assoc()) { //each row
      foreach($row as $key => $value){ // each key in each row
        echo $key;
      }
    }

answered Aug 28 '17 at 09:51

Kareem

5,068
44
38

score -4 · Answer 3 · edited Dec 08 '15 at 21:52

-4

Like this ?

while($row = $result->fetch_array(MYSQL_ASSOC)) {
  $rows [] = $row;
}
echo json_encode($rows);

edited Dec 08 '15 at 21:52

Giacomo1968

25,759
11
71
103

answered Dec 08 '15 at 21:33

thepiyush13

1,321
1
8
9

Why should the OP "try this"? A ***good answer*** will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO. – Jay Blanchard Dec 08 '15 at 21:34
I"m doing this already, since $cities is **exactly** what was returned from getResults. – Ericson Willians Dec 08 '15 at 21:36

How can I return the keys from fetch_assoc as strings so that the array can be valid JSON data? (PHP / mysqli)

3 Answers3