8

I am getting some data from a database and am encoding it to json:

$json = "";
if($result = $dbc->query($query)) {
    $num = $result->num_rows;
    for($i = 0; $i < $num; $i++) {
        $row = $result->fetch_array();
        $json .= json_encode($row);
        if($i != ($num-1)) {
        $json .= ',';
        }
    }
}

but instead of getting the json string in the format:

{"name:"joe", "age":"22", "etc":"etc"}

I'm getting every value duplicated because it is giving me the element name as being both the index of an associative and non-associative array. So I'm getting:

{"0":"joe", "name":"joe", "1":"22", "age":"22", "3":"etc", "etc":"etc"}

While I can still use the json. It is still twice the size that I want it to be and so not efficient. Is there anyway I can get the json_encode method to just give me the associative array inices as the json tags? (Wrong words to describe these things no doubt)

Many thanks

Joe
  • 4,852
  • 10
  • 63
  • 82

3 Answers3

24

This is because you are using fetch_array() (emphasis mine):

mysqli_fetch_array() is an extended version of the mysqli_fetch_row() function. In addition to storing the data in the numeric indices of the result array, the mysqli_fetch_array() function can also store the data in associative indices, using the field names of the result set as keys.

Use fetch_assoc() instead.

Pekka
  • 442,112
  • 142
  • 972
  • 1,088
  • I just realized I've been creating arrays with duplicated content for years because I always used mysql_fetch_array(). – andreszs Dec 02 '14 at 16:45
3

Pekka is probably right, but I would like to add that you are making more work for yourself by calling json_encode() for every row. It's probably better to build your data structure, and then call json_encode() on that:

$rows = array();
if ($result = $dbc->query($query)) {
    $num = $result->num_rows;
    for ($i = 0; $i < $num; $i++) {
        $rows[] = $result->fetch_assoc();
    }
}
$json = json_encode($rows);

Marking as community wiki as this is a suggestion on practice and not an answer.

ken
  • 3,650
  • 1
  • 30
  • 43
  • Unfortunately, even with native functions like `json_encode()`, devs are still trying to go to the trouble of manually constructing a JSON string. I see it all the time, it happens in JavaScript too -- people don't realize that there's a `JSON.stringify()` which modern browsers provide natively (and which can be downloaded from json.org to support users without modern browsers). – ken Jan 11 '11 at 22:09
  • Thanks very much for making that point Ken. Your way seems much easier and cleaner. :) – Joe Jan 12 '11 at 10:17
2

Just change $row = $result->fetch_array(); to $row = $result->fetch_assoc();

Valera Leontyev
  • 1,191
  • 6
  • 14
  • Probably because it is a redundant answer (of yours) and offers less information. I think the gist of this site isn't just giving answers, but giving a little explanation to go with it. Nobody learns anything if they don't understand what they were doing wrong, or why it is wrong. – ken Jan 12 '11 at 15:27