cPanel Question posted in
The official documentation can be seen here.

Presenting MYSQL nested JSON object with php – CPanel

I have a MYSQL Table stored using 000webhostapp cpanel, here is what it look like.

id | name  |             J_Object
--------------------------------------------------------- 
1  | deckA | {"type":"A", "ports": {"hdmi": 1, "usb":2}}
2  | deckB | {"type":"B", "ports": {"hdmi": 3, "usb":2}}
3  | deckC | {"type":"A", "ports": {"hdmi": 1, "usb":2}}
4  | deckD | {"type":"B", "ports": {"hdmi": 3, "usb":2}}

Using PHP, I would like to present it like the below to act as a HTTP to pass the data to other platforms.

[
    {
        "id": "1",
        "name": "deckA",
        "J_Object": [{"type":"A", "ports": {"hdmi": 1, "usb":2}}]
    },
    {
        "id": "2",
        "name": "deckB",
        "J_Object": [{"type":"B", "ports": {"hdmi": 3, "usb":2}}]
    },
    {
        "id": "1",
        "name": "deckC",
        "J_Object": [{"type":"A", "ports": {"hdmi": 1, "usb":2}}]
    },
    {
        "id": "1",
        "name": "deckD",
        "J_Object": [{"type":"B", "ports": {"hdmi": 3, "usb":2}}]
    }
]

However, with my PHP I written below, what follows after is what I got. And it is definitely not what I wanted.

The PHP code:

$search = "SELECT * FROM `database`.`products` 
            WHERE JSON_EXTRACT(`J_Object` , '$.ports.usb') > 2 
            AND JSON_EXTRACT(`J_Object` , '$.ports.hdmi') > 1;";

$result = mysqli_query($link, $search);

if ($result) {
    //Printing out the details of the rows

    while($array = mysqli_fetch_assoc($result)) {
        $jsonData[]=$array;
    }

    $nonArr = json_encode($jsonData, JSON_PRETTY_PRINT);
    $stringArr = (string)$nonArr;
    $nonSlash = str_replace("\","", $stringArr);
    echo $nonSlash;
}  

mysqli_close($link)

And the below is the unwanted results:

[
    {
        "id": "1",
        "name": "deckA",
        "J_Object": "{"type":"A", "ports": {"hdmi": 1, "usb":2}}"
    },
    {
        "id": "2",
        "name": "deckB",
        "J_Object": "{"type":"B", "ports": {"hdmi": 3, "usb":2}}'
    },
    {
        "id": "1",
        "name": "deckC",
        "J_Object": "{"type":"A", "ports": {"hdmi": 1, "usb":2}}"
    },
    {
        "id": "1",
        "name": "deckD",
        "J_Object": "{"type":"B", "ports": {"hdmi": 3, "usb":2}}"
    }
]

It looks like its the same but if you notice the [ ] is changed with ” ” which also means that the nested object is not quite right.
Can any pro out there show me how do I get the correct output?

Tags:

2

Answers


  1. 0

    This would work for you. It’s just a quick fix with preg_replace.

    $result = mysqli_query($link, $search);

if ($result) {
    //Printing out the details of the rows

    while($array = mysqli_fetch_assoc($result)) {
        $jsonData[]=$array;
    }

    $nonArr = json_encode($jsonData, JSON_PRETTY_PRINT);
    $stringArr = (string)$nonArr;
    $nonSlash = str_replace("\","", $stringArr);

    // quick fix
    $nonSlash = preg_replace("/["]{1}[{]{1}/", '[{', $nonSlash);
    $nonSlash = preg_replace("/[}]{1}["]{1}/", '}]', $nonSlash);
    echo $nonSlash;
}

    Output:

    [
    {
        "id": "1",
        "name": "deckA",
        "J_Object": [{"type":"A", "ports": {"hdmi": 1, "usb":2}}]
    },
    {
        "id": "2",
        "name": "deckB",
        "J_Object": [{"type":"B", "ports": {"hdmi": 3, "usb":2}}]
    },
    {
        "id": "3",
        "name": "deckC",
        "J_Object": [{"type":"A", "ports": {"hdmi": 1, "usb":2}}]
    },
    {
        "id": "4",
        "name": "deckD",
        "J_Object": [{"type":"B", "ports": {"hdmi": 3, "usb":2}}]
    }
]

    Hope you find this helpful. Happy coding..

    Login or Signup to reply.
  2. 0

    You could try a much simpler way.

    $result = mysqli_query($link, $search);

if ($result) {
    //Printing out the details of the rows

    while($array = mysqli_fetch_assoc($result)) {
        $array['J_Object'] = [json_decode($array['J_Object'], true)];
        $jsonData[]=$array;
    }

    echo json_encode($jsonData, JSON_PRETTY_PRINT);
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search