CODEWITHSUNDEEP
phpmysqlsqljson
Whispers22
Whispers22

Reputation: 3

Problem encoding PHP/MySQL arrays to JSON

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"
                }

            ]
        }
    ]

Upvotes: 0

Views: 35

Answers (1)

splash58
splash58

Reputation: 26153

Reset the array in each itteration

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

Upvotes: 1

Related Questions