php - 在 PHP 中使用 JSON 显示类别和内容

Question

我在使用 PHP 和 MySQL 来显示正确的 JSON 时遇到问题。尝试了很多我能找到的选项，但都无济于事。到目前为止，我设法获得了正确的 JSON 结构，但内容不断循环使用以前的数据。如何解决内容双循环问题？

使用 2 个表 - recipecategory 和 recipelibrary。

我的数据库图像:

PHP 代码:

<?php
    require("db.php");
        //fetch category table rows
        $sqlCat = "SELECT categoryName, uId FROM recipecategory";
        $resultCat = mysqli_query($conn, $sqlCat) or die ("Error in Selecting" .mysqli_error($conn));

        //create an array
        $recipeArray = array();
        $recipeList = array();

    //Loop category table
    while($rowCat = mysqli_fetch_assoc($resultCat)){

        //Add row to json - $recipeArray
        $recipeArray["title"] = $rowCat["categoryName"];

        //Store category name to $catName
        $catName = $rowCat["categoryName"];

        //Sql statement to fetch contents for every categories
        $sql = "SELECT A.*, B.categoryName FROM recipelibrary AS A INNER JOIN recipecategory AS B ON A.categorId = B.uId WHERE B.categoryName ='". $catName."'";
        $result = mysqli_query($conn, $sql) or die ("Error in Selecting" .mysqli_error($conn));
        //Loop contents row and add to json - $recipeArray
        while($row = mysqli_fetch_assoc($result)){
                $recipeArray["Section"][] = $row;
            }

        //Add $recipeArray to $recipeList
        $recipeList["data"][] = $recipeArray;
    }
    //display Json
    echo json_encode($recipeList);
?>

JSON 结果:(由于 Stack Overflow 中的字符限制，仅是我的 JSON 结果的一部分):

"data": [
    {
        "title": "Western",
        "Section": [
            {
                "id": "1",
                "uid": "7UtP7q",
                "categorId": "sy0Sbt",
                "recipeName": "recipeTitle_1",
                "recipeDesc": "Suspendisse id metus et elit rhoncus molestie. Interdum et malesuada fames ac ante ipsum primis in faucibus. Ut egestas, neque a sollicitudin suscipit, felis orci molestie orci, quis consectetur urna magna nec mi. Fusce a commodo augue. Integer maximus porttitor elit. Morbi molestie libero a nibh accumsan tincidunt.",
                "recipeIngredients": "150g (5oz) bulgur wheat|3 tbsp olive oil|1 lemon, zested and juiced|1 small shallot, finely chopped|1 tsp ground cumin|100g (3 1/2oz) kale, very finely chopped|1 x 230g pack sweet vine ripened tomatoes, chopped|1/2 cucumber, diced|handful chopped mint|handful chopped flat-leaf parsley|100g (3 1/2oz) raisins|1 x 225g pack lighter halloumi, sliced into 12",
                "recipeDirection": "Bring a pan of water to the boil, add the bulgur wheat, cover, then cook for 15 minutes. Drain excess liquid and cool.|In a bowl, whisk 2 tbsp olive oil, the lemon juice and zest, the shallot and the cumin. Stir into the bulgur wheat, reserving some to drizzle.|Stir through the kale, tomatoes, cucumber, herbs and raisins, mix well, then season with black pepper.|Heat a frying pan over a medium heat and add the remaining oil. Fry the halloumi for 1-2 minutes on each side, until crisp and golden. To serve, top the tabbouleh with the halloumi and drizzle with the remaining dressing.",
                "userId": null,
                "categoryName": "Western"
            },
            {
                "id": "2",
                "uid": "3nV9jp",
                "categorId": "sy0Sbt",
                "recipeName": "recipeTitle_2",
                "recipeDesc": "Praesent consectetur ex vel est pharetra, sollicitudin convallis nulla dignissim. Duis facilisis accumsan erat id suscipit. Nam sodales tortor nec eros vulputate tristique consectetur sit amet elit. In et ultricies turpis.",
                "recipeIngredients": "150g (5oz) bulgur wheat|3 tbsp olive oil|1 lemon, zested and juiced|1 small shallot, finely chopped|1 tsp ground cumin|100g (3 1/2oz) kale, very finely chopped|1 x 230g pack sweet vine ripened tomatoes, chopped|1/2 cucumber, diced|handful chopped mint|handful chopped flat-leaf parsley|100g (3 1/2oz) raisins|1 x 225g pack lighter halloumi, sliced into 12",
                "recipeDirection": "Bring a pan of water to the boil, add the bulgur wheat, cover, then cook for 15 minutes. Drain excess liquid and cool.|In a bowl, whisk 2 tbsp olive oil, the lemon juice and zest, the shallot and the cumin. Stir into the bulgur wheat, reserving some to drizzle.|Stir through the kale, tomatoes, cucumber, herbs and raisins, mix well, then season with black pepper.|Heat a frying pan over a medium heat and add the remaining oil. Fry the halloumi for 1-2 minutes on each side, until crisp and golden. To serve, top the tabbouleh with the halloumi and drizzle with the remaining dressing.",
                "userId": null,
                "categoryName": "Western"
            },
            {
                "id": "4",
                "uid": "EzfzGJ",
                "categorId": "sy0Sbt",
                "recipeName": "recipeTitle_4",
                "recipeDesc": "Cras id finibus ante. Sed aliquet vel tellus sit amet luctus. Donec ante augue, suscipit eget blandit quis, accumsan ac est. Fusce eu lorem commodo, maximus nisl non, ornare odio.",
                "recipeIngredients": "150g (5oz) bulgur wheat|3 tbsp olive oil|1 lemon, zested and juiced|1 small shallot, finely chopped|1 tsp ground cumin|100g (3 1/2oz) kale, very finely chopped|1 x 230g pack sweet vine ripened tomatoes, chopped|1/2 cucumber, diced|handful chopped mint|handful chopped flat-leaf parsley|100g (3 1/2oz) raisins|1 x 225g pack lighter halloumi, sliced into 12",
                "recipeDirection": "Bring a pan of water to the boil, add the bulgur wheat, cover, then cook for 15 minutes. Drain excess liquid and cool.|In a bowl, whisk 2 tbsp olive oil, the lemon juice and zest, the shallot and the cumin. Stir into the bulgur wheat, reserving some to drizzle.|Stir through the kale, tomatoes, cucumber, herbs and raisins, mix well, then season with black pepper.|Heat a frying pan over a medium heat and add the remaining oil. Fry the halloumi for 1-2 minutes on each side, until crisp and golden. To serve, top the tabbouleh with the halloumi and drizzle with the remaining dressing.",
                "userId": null,
                "categoryName": "Western"
            },
            {
                "id": "7",
                "uid": "0ex4YA",
                "categorId": "sy0Sbt",
                "recipeName": "recipeTitle_7",
                "recipeDesc": "Morbi vestibulum tellus sed commodo vehicula. Phasellus elementum, sem ut sagittis fringilla, dolor ex facilisis massa, eget accumsan felis ligula sed dolor. Sed interdum lectus nec nisi ullamcorper, et congue tellus faucibus.",
                "recipeIngredients": "150g (5oz) bulgur wheat|3 tbsp olive oil|1 lemon, zested and juiced|1 small shallot, finely chopped|1 tsp ground cumin|100g (3 1/2oz) kale, very finely chopped|1 x 230g pack sweet vine ripened tomatoes, chopped|1/2 cucumber, diced|handful chopped mint|handful chopped flat-leaf parsley|100g (3 1/2oz) raisins|1 x 225g pack lighter halloumi, sliced into 12",
                "recipeDirection": "Bring a pan of water to the boil, add the bulgur wheat, cover, then cook for 15 minutes. Drain excess liquid and cool.|In a bowl, whisk 2 tbsp olive oil, the lemon juice and zest, the shallot and the cumin. Stir into the bulgur wheat, reserving some to drizzle.|Stir through the kale, tomatoes, cucumber, herbs and raisins, mix well, then season with black pepper.|Heat a frying pan over a medium heat and add the remaining oil. Fry the halloumi for 1-2 minutes on each side, until crisp and golden. To serve, top the tabbouleh with the halloumi and drizzle with the remaining dressing.",
                "userId": null,
                "categoryName": "Western"
            }
        ]
    },
    {
        "title": "Chinese",
        "Section": [
            {
                "id": "1",
                "uid": "7UtP7q",
                "categorId": "sy0Sbt",
                "recipeName": "recipeTitle_1",
                "recipeDesc": "Suspendisse id metus et elit rhoncus molestie. Interdum et malesuada fames ac ante ipsum primis in faucibus. Ut egestas, neque a sollicitudin suscipit, felis orci molestie orci, quis consectetur urna magna nec mi. Fusce a commodo augue. Integer maximus porttitor elit. Morbi molestie libero a nibh accumsan tincidunt.",
                "recipeIngredients": "150g (5oz) bulgur wheat|3 tbsp olive oil|1 lemon, zested and juiced|1 small shallot, finely chopped|1 tsp ground cumin|100g (3 1/2oz) kale, very finely chopped|1 x 230g pack sweet vine ripened tomatoes, chopped|1/2 cucumber, diced|handful chopped mint|handful chopped flat-leaf parsley|100g (3 1/2oz) raisins|1 x 225g pack lighter halloumi, sliced into 12",
                "recipeDirection": "Bring a pan of water to the boil, add the bulgur wheat, cover, then cook for 15 minutes. Drain excess liquid and cool.|In a bowl, whisk 2 tbsp olive oil, the lemon juice and zest, the shallot and the cumin. Stir into the bulgur wheat, reserving some to drizzle.|Stir through the kale, tomatoes, cucumber, herbs and raisins, mix well, then season with black pepper.|Heat a frying pan over a medium heat and add the remaining oil. Fry the halloumi for 1-2 minutes on each side, until crisp and golden. To serve, top the tabbouleh with the halloumi and drizzle with the remaining dressing.",
                "userId": null,
                "categoryName": "Western"
            },
            {
                "id": "2",
                "uid": "3nV9jp",
                "categorId": "sy0Sbt",
                "recipeName": "recipeTitle_2",
                "recipeDesc": "Praesent consectetur ex vel est pharetra, sollicitudin convallis nulla dignissim. Duis facilisis accumsan erat id suscipit. Nam sodales tortor nec eros vulputate tristique consectetur sit amet elit. In et ultricies turpis.",
                "recipeIngredients": "150g (5oz) bulgur wheat|3 tbsp olive oil|1 lemon, zested and juiced|1 small shallot, finely chopped|1 tsp ground cumin|100g (3 1/2oz) kale, very finely chopped|1 x 230g pack sweet vine ripened tomatoes, chopped|1/2 cucumber, diced|handful chopped mint|handful chopped flat-leaf parsley|100g (3 1/2oz) raisins|1 x 225g pack lighter halloumi, sliced into 12",
                "recipeDirection": "Bring a pan of water to the boil, add the bulgur wheat, cover, then cook for 15 minutes. Drain excess liquid and cool.|In a bowl, whisk 2 tbsp olive oil, the lemon juice and zest, the shallot and the cumin. Stir into the bulgur wheat, reserving some to drizzle.|Stir through the kale, tomatoes, cucumber, herbs and raisins, mix well, then season with black pepper.|Heat a frying pan over a medium heat and add the remaining oil. Fry the halloumi for 1-2 minutes on each side, until crisp and golden. To serve, top the tabbouleh with the halloumi and drizzle with the remaining dressing.",
                "userId": null,
                "categoryName": "Western"
            },
            {
                "id": "4",
                "uid": "EzfzGJ",
                "categorId": "sy0Sbt",
                "recipeName": "recipeTitle_4",
                "recipeDesc": "Cras id finibus ante. Sed aliquet vel tellus sit amet luctus. Donec ante augue, suscipit eget blandit quis, accumsan ac est. Fusce eu lorem commodo, maximus nisl non, ornare odio.",
                "recipeIngredients": "150g (5oz) bulgur wheat|3 tbsp olive oil|1 lemon, zested and juiced|1 small shallot, finely chopped|1 tsp ground cumin|100g (3 1/2oz) kale, very finely chopped|1 x 230g pack sweet vine ripened tomatoes, chopped|1/2 cucumber, diced|handful chopped mint|handful chopped flat-leaf parsley|100g (3 1/2oz) raisins|1 x 225g pack lighter halloumi, sliced into 12",
                "recipeDirection": "Bring a pan of water to the boil, add the bulgur wheat, cover, then cook for 15 minutes. Drain excess liquid and cool.|In a bowl, whisk 2 tbsp olive oil, the lemon juice and zest, the shallot and the cumin. Stir into the bulgur wheat, reserving some to drizzle.|Stir through the kale, tomatoes, cucumber, herbs and raisins, mix well, then season with black pepper.|Heat a frying pan over a medium heat and add the remaining oil. Fry the halloumi for 1-2 minutes on each side, until crisp and golden. To serve, top the tabbouleh with the halloumi and drizzle with the remaining dressing.",
                "userId": null,
                "categoryName": "Western"
            },
            {
                "id": "7",
                "uid": "0ex4YA",
                "categorId": "sy0Sbt",
                "recipeName": "recipeTitle_7",
                "recipeDesc": "Morbi vestibulum tellus sed commodo vehicula. Phasellus elementum, sem ut sagittis fringilla, dolor ex facilisis massa, eget accumsan felis ligula sed dolor. Sed interdum lectus nec nisi ullamcorper, et congue tellus faucibus.",
                "recipeIngredients": "150g (5oz) bulgur wheat|3 tbsp olive oil|1 lemon, zested and juiced|1 small shallot, finely chopped|1 tsp ground cumin|100g (3 1/2oz) kale, very finely chopped|1 x 230g pack sweet vine ripened tomatoes, chopped|1/2 cucumber, diced|handful chopped mint|handful chopped flat-leaf parsley|100g (3 1/2oz) raisins|1 x 225g pack lighter halloumi, sliced into 12",
                "recipeDirection": "Bring a pan of water to the boil, add the bulgur wheat, cover, then cook for 15 minutes. Drain excess liquid and cool.|In a bowl, whisk 2 tbsp olive oil, the lemon juice and zest, the shallot and the cumin. Stir into the bulgur wheat, reserving some to drizzle.|Stir through the kale, tomatoes, cucumber, herbs and raisins, mix well, then season with black pepper.|Heat a frying pan over a medium heat and add the remaining oil. Fry the halloumi for 1-2 minutes on each side, until crisp and golden. To serve, top the tabbouleh with the halloumi and drizzle with the remaining dressing.",
                "userId": null,
                "categoryName": "Western"
            },
            {
                "id": "3",
                "uid": "nEdCe3",
                "categorId": "wNXTav",
                "recipeName": "recipeTitle_3",
                "recipeDesc": "Etiam id elementum sapien. Cras bibendum dolor felis, nec egestas diam posuere id. Vivamus feugiat urna et libero cursus fermentum.",
                "recipeIngredients": "150g (5oz) bulgur wheat|3 tbsp olive oil|1 lemon, zested and juiced|1 small shallot, finely chopped|1 tsp ground cumin|100g (3 1/2oz) kale, very finely chopped|1 x 230g pack sweet vine ripened tomatoes, chopped|1/2 cucumber, diced|handful chopped mint|handful chopped flat-leaf parsley|100g (3 1/2oz) raisins|1 x 225g pack lighter halloumi, sliced into 12",
                "recipeDirection": "Bring a pan of water to the boil, add the bulgur wheat, cover, then cook for 15 minutes. Drain excess liquid and cool.|In a bowl, whisk 2 tbsp olive oil, the lemon juice and zest, the shallot and the cumin. Stir into the bulgur wheat, reserving some to drizzle.|Stir through the kale, tomatoes, cucumber, herbs and raisins, mix well, then season with black pepper.|Heat a frying pan over a medium heat and add the remaining oil. Fry the halloumi for 1-2 minutes on each side, until crisp and golden. To serve, top the tabbouleh with the halloumi and drizzle with the remaining dressing.",
                "userId": null,
                "categoryName": "Chinese"
            },
            {
                "id": "5",
                "uid": "8NMelb",
                "categorId": "wNXTav",
                "recipeName": "recipeTitle_5",
                "recipeDesc": "Curabitur est massa, fermentum vitae orci a, pulvinar hendrerit neque. Curabitur finibus ante nec dignissim tempor. Proin eget purus augue. Pellentesque quis eros id turpis tristique condimentum a gravida quam.",
                "recipeIngredients": "150g (5oz) bulgur wheat|3 tbsp olive oil|1 lemon, zested and juiced|1 small shallot, finely chopped|1 tsp ground cumin|100g (3 1/2oz) kale, very finely chopped|1 x 230g pack sweet vine ripened tomatoes, chopped|1/2 cucumber, diced|handful chopped mint|handful chopped flat-leaf parsley|100g (3 1/2oz) raisins|1 x 225g pack lighter halloumi, sliced into 12",
                "recipeDirection": "Bring a pan of water to the boil, add the bulgur wheat, cover, then cook for 15 minutes. Drain excess liquid and cool.|In a bowl, whisk 2 tbsp olive oil, the lemon juice and zest, the shallot and the cumin. Stir into the bulgur wheat, reserving some to drizzle.|Stir through the kale, tomatoes, cucumber, herbs and raisins, mix well, then season with black pepper.|Heat a frying pan over a medium heat and add the remaining oil. Fry the halloumi for 1-2 minutes on each side, until crisp and golden. To serve, top the tabbouleh with the halloumi and drizzle with the remaining dressing.",
                "userId": null,
                "categoryName": "Chinese"
            },
            {
                "id": "10",
                "uid": "vOOL8r",
                "categorId": "wNXTav",
                "recipeName": "recipeTitle_10",
                "recipeDesc": "Pellentesque blandit facilisis pharetra. Suspendisse libero lectus, malesuada non enim in, convallis cursus erat. Nunc ac congue odio. Integer nibh nibh, luctus et ornare eget, aliquam sit amet velit.",
                "recipeIngredients": "150g (5oz) bulgur wheat|3 tbsp olive oil|1 lemon, zested and juiced|1 small shallot, finely chopped|1 tsp ground cumin|100g (3 1/2oz) kale, very finely chopped|1 x 230g pack sweet vine ripened tomatoes, chopped|1/2 cucumber, diced|handful chopped mint|handful chopped flat-leaf parsley|100g (3 1/2oz) raisins|1 x 225g pack lighter halloumi, sliced into 12",
                "recipeDirection": "Bring a pan of water to the boil, add the bulgur wheat, cover, then cook for 15 minutes. Drain excess liquid and cool.|In a bowl, whisk 2 tbsp olive oil, the lemon juice and zest, the shallot and the cumin. Stir into the bulgur wheat, reserving some to drizzle.|Stir through the kale, tomatoes, cucumber, herbs and raisins, mix well, then season with black pepper.|Heat a frying pan over a medium heat and add the remaining oil. Fry the halloumi for 1-2 minutes on each side, until crisp and golden. To serve, top the tabbouleh with the halloumi and drizzle with the remaining dressing.",
                "userId": null,
                "categoryName": "Chinese"
            }
        ]
    },    

Answer 1

我终于解决了。我的数据库表没有问题，问题出在PHP上。这有两个问题。首先是数组的变量，其次是在 while 循环中如何将数据存储到数组中，

第一个问题

我这样声明了我的数组，这没有任何问题，但是如果我使用函数或方法来使用这个数组，我需要将其设为全局。这样，我就可以访问任何 PHP 中的变量。

$recipeArray = array();
$recipeList = array();

创建全局变量的正确方法

引用:w3schools

$recipeArray = array();

//call for function
categoryContent()

function categoryContent() { 
    $GLOBALS['recipeArray'][] = $rowContent; 
}

第二个问题

第一个问题解决后，它仍然重复前一个类别的数据，因此简单的解决方案是使用 unset() 函数。

引用:w3resource

while($rowCat = mysqli_fetch_array($resultCat)){
    $GLOBALS['recipeArray']["title"] = $rowCat['categoryName'];
    categoryContent($rowCat['categoryName']);

    $recipeList["data"][]=$recipeArray;
}

通过在 while 循环内 $GLOBALS['recipeArray']["title"] = $rowCat['categoryName'] 之前添加 unset($GLOBALS['recipeArray']); ;，我得到了我正在寻找的最终 JSON 结果。

while($rowCat = mysqli_fetch_array($resultCat)){
    unset($GLOBALS['recipeArray']);
    $GLOBALS['recipeArray']["title"] = $rowCat['categoryName'];
    categoryContent($rowCat['categoryName']);

    $recipeList["data"][]=$recipeArray;
}