Problem encoding PHP/MySQL arrays to JSON

Question

I have the following tables in MySQL:

recipe: id_recipe (PKey), name_recipe, description
url_pic: id_img, url_img, id_recipe (FKey)

Using PHP, I am trying to get JSON which contains every recipe, along with its pictures' urls (an array). My problem is that the pictures array of recipe 2 includes the pictures of both recipes! I'm a beginner and I can't find where the problem is. I've included below the JSON I want, my PHP code, and the JSON I actually get:

Desired JSON:

[
    {
        "recette": {
            "id_recipe": "1",
            "name_recipe": "..",
            "description":"..."
            },
        "pictures": [
            {
                "id_url": "1",
                "url": "picture 1 of recipe 1"
            },
            {
                "id_url": "2",
                "url": "picture 2 of recipe 1"
            }      
        ]
    },
    {
        "recette": {
            "id_recipe": "2",
            "name_recipe": "....",
            "description": "...."
        },
        "pictures": [
            {
                "id_url": "3",
                "url": "picture 1 of recipe 2"
            },
            {
                "id_url": "4",
                "url": "picture 2 of recipe 2"
            }

        ]
    }
]

PHP:

$sql = "SELECT id_recipe,name_recipe,description 
        FROM recipe";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $data['recipe'] = $row;   
        $sql2= "SELECT id_url,url 
                FROM url_img as u
                WHERE u.id_recipe = '$row[id_recipe]'";         

        $result2 = $conn->query($sql2);   
        if($result2->num_rows > 0){
            while($row2 = $result2->fetch_array(MYSQL_ASSOC)) {
                $array[] = $row2;                  
            }
            $data['pictures'] = $array; 
        }

        $all[] = $data; 
    }

    header('Content-Type: application/json');
    echo json_encode($all,JSON_UNESCAPED_UNICODE);
}

Actual JSON:

    [
        {
            "recette": {
                "id_recipe": "1",
                "name_recipe": "..",
                "description":"..."
                },
            "pictures": [
                {
                    "id_url": "1",
                    "url": "picture 1 of recipe 1"
                },
                {
                    "id_url": "2",
                    "url": "picture 2 of recipe 1"
                }      
            ]
        },
        {
            "recette": {
                "id_recipe": "2",
                "name_recipe": "....",
                "description": "...."
            },
            "pictures": [
                {
                    "id_url": "1",
                    "url": "picture 1 of recipe 1"
                },
                {
                    "id_url": "2",
                    "url": "picture 2 of recipe 1"
                },
                 {
                    "id_url": "3",
                    "url": "picture 1 of recipe 2"
                },
                {
                    "id_url": "4",
                    "url": "picture 2 of recipe 2"
                }

            ]
        }
    ]

splash58 · Accepted Answer · 2018-09-30 10:04:53Z

1

Reset the array in each itteration

$result2 = $conn->query($sql2);
$array = []; // Add this line
if($result2->num_rows > 0){

edited Sep 30, 2018 at 10:04

answered Sep 29, 2018 at 20:42

splash58

26.2k3 gold badges25 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Problem encoding PHP/MySQL arrays to JSON

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related