Why can not the JSON database value be decoded in PHP?

Question

Why can not the JSON database value be decoded in PHP?

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I use the following function to insert a JSON string in my database:

$a_value = 1;
$b_value = 0;
$c_value = 1;

$feature = array("A" => $a_value,
                    "B" => $b_value,
                    "C" => $c_value,
                );

$featureJson = json_encode($feature);

I get this

{"A":"1","B":"0","C":"1"}

Followed on another part of my system I use this to get it

$queryDeEjemplo = SELECT * FROM features WHERE id = 1
$json = $stmt->fetch();
// Resultado: string "{"A":"1","B":"0","C":"1"}"
$resultado = json_decode($json);
// Resultado: NULL

Why is this happening?

I use MYSQL . The field feature is type VARCHAR of 500 characters.

php

asked by Victor Alvarado 19.02.2018 в 14:48

source

3 answers

0

As of version 5.7 of MySQL you can store JSON as you would in a traditional file; all you need is to create the field that will store this info as follows:

CREATE TABLE mi_tabla( 
   id INT,
   campo_json JSON NOT NULL,
   detalle VARCHAR(100) NOT NULL
);

Note that JSON in the 3rd line of the previous code is the type of data that the field stores.

More info: link

answered by 19.02.2018 в 14:57

0

I found the problem in the following:

I was getting an array with the other fields in the table.

I was trying to convert the entire array.

Then just check the field, but it does not work either.

I continued to convert the json to an array, but accessing the value, not taking the entire array.

$resultJson = $stmt->traerUnico();
// array ["features"] => string {"A":"1","B":"0","C":"1"}
$result = json_decode($resultJson["features"]);
// object(stdClass)#7 ["A"] => 1, ["B"] => 2, ["C"] => 3

answered by 19.02.2018 в 15:07

Problems building a dictionary to return a Queryset How to delete an Id and restructure the database

score 2 · Accepted Answer

The problem

The problem is that:

You are selecting all columns in the table: SELECT * FROM features WHERE id = 1;

You are creating an array: $json = $stmt->fetch(); Something like this:

Array
(
    [persona_id] => 9
    [persona_nom] => {"A":"1","B":"0","C":"1"}
    [ciudad_id] => 12
)

You are trying to apply json_decode to that array: $resultado = json_decode($json); When that function should receive as parameter a string, not an array .

The result of that procedure is the following Warning:

PHP Warning: json_decode () expects parameter 1 to be string, array given

and you get a value NULL because the parameter could not be decoded because it was incorrect.

The solutions

1.

Since the course JSON, (I'm guessing because it's actually a VARCHAR within which you're saving a string in the form of JSON), it's in a single column of that table, you can get it by accessing the data in that column specifically.

For example:

$json = $stmt->fetch(PDO::FETCH_ASSOC);
$resultado = json_decode($json["persona_nom"]);
print_r($resultado);

There you will have on screen:

{"A":"1","B":"0","C":"1"}

2.

Anyway, here the use of json_decode is redundant, since, supposedly , there should be a valid json in the VARCHAR column. In fact, if you do this:

print_r ($json["persona_nom"]);

The result is the same as if you were using json_decode :

{"A":"1","B":"0","C":"1"}

And if that value should be received as a valid JSON elsewhere (an Ajax call, a REST server, etc), you could use the header before the print_r would suffice.

3. The best solution

Nothing better than when they treat you like what you are. In that sense, MySQL allows you to use a data type JSON , maybe it's the more appropriate, if you want to store JSON objects in your database.