PHP can't decode JSON encoded with json_encode

Question

I'm using this code to encode large(cca 60mb) geojson before storing it to database:

// controller
public function importZones () {

    ini_set('max_execution_time', '0');
    ini_set('memory_limit', '-1');
    ini_set("precision", -1);
    ini_set("serialize_precision", -1);

    $this->load->helper('url');

    $filePath = base_url('assets/zones/zone.geojson');

    $jsonStream = \JsonMachine\JsonMachine::fromFile($filePath, "/features");
    $this->PolygonModel->import($jsonStream)

}
// model
public function import ($jsonStream) {
    $import = [];
    foreach ($jsonStream as $name => $data) {
        // $coordinates is nested indexed array with possibly thousands of elements
        $coordinates = json_encode($data['geometry']['coordinates']); 
        $import['baz'][] = [
             'foo' => 'bar',
             'coordinates' => $coordinates
        ];
    }
    echo json_last_error_msg(); // gives no errors 

   // insert encoded data to db...
}

I'm using this code to decode the string after selecting it from database

// decode and fix json if corrupted (huge arrays..)

private function decodeZoneCoords ($coordsJson) {

 $decoded = json_decode($coordsJson, true);

 if (!$decoded) {
    // some fixes I've found online
    $json = $coordsJson;
    // before removing cntrl I get the  “Control character error, possibly incorrectly encoded” error
    $json = preg_replace('/[[:cntrl:]]/', '', $coordsJson);
    $json = preg_replace('/[[:^print:]]/', '', $coordsJson);
    $json = mb_convert_encoding($json, "UTF-8");
    $json = ltrim($json . '"' . "]", '1');
    if (0 === strpos(bin2hex($json), 'efbbbf')) {
       $json = substr($json, 3);
    }

    $decoded = json_decode($json, true);
    if (!$decoded) {
        $json = rtrim($json, ',"]') . '"]';
        $decoded = json_decode($json, true);
    }
 } 

 if (!$decoded) {
  echo "<pre>";
    var_dump( json_decode($json, true) ); // null
  echo "</pre>";
  echo json_last_error_msg(); // "syntax error"
  echo 'JSON ERROR(from zone):';
  echo '<hr>';
  echo $json;
  die();
}

 return $decoded;

}

When I try to decode it I get syntax error message from json_last_error_msg function and json_encode function returns null.

Edit:

I just realized that var_dumping the string gives me this: string(65535) "...

Could it be that MySql truncates my JSON after that lenght even though im using the text field to store encoded string?

65535 is the exact length of a text field in MySQL. If your json string is longer than that, use mediumtext or longtext. - dev.mysql.com/doc/refman/8.0/en/string-type-syntax.html — aynber
– aynber, Commented Jan 30, 2020 at 17:17
Thanks for tip, that was my problem, part of json was being cut out because it was too long. If you submit that as an answer I will gladly accept it. — failedCoder
– failedCoder, Commented Jan 30, 2020 at 17:23
@failedCoder or even better: MySQL has a specific data type for JSON data. Rather than using text, mediumtext or longtext you should be using JSON for the data type — Javier Larroulet
– Javier Larroulet, Commented Jan 30, 2020 at 20:42

aynber · Accepted Answer · 2020-01-30 17:26:15Z

1

65535 is the exact length of a text field in MySQL. If your json string is longer than that, use mediumtext (16,777,215 characters) or longtext (4,294,967,295 characters).

answered Jan 30, 2020 at 17:26

aynber

23.2k9 gold badges57 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Javier Larroulet · Accepted Answer · 2020-01-30 20:44:12Z

1

If you're storing JSON data in your MySQL database, you shouldn't use neither text nor mediumtext nor longtext. There's a JSON datatype created specifically for storing JSON data in your tables.

I'd suggest altering your table and changing the column type to JSON instead of any text type.

answered Jan 30, 2020 at 20:44

Javier Larroulet

3,2303 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

PHP can't decode JSON encoded with json_encode

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related