php - 用MySQL和PHP读取部分json数据

Question

我使用的 CMS 在 MySQL 中以 JSON 格式保存资源(在一个表中)。连同来自 MySQL 的正常数据。当我循环遍历数据时，我得到一个数组 + JSON 数据。但我需要在 JSON 数据中搜索 XNCatnCode。

正在尝试加载数据

 $Products = $SQL->fetchAll("SELECT * FROM `pages` WHERE `parent` = 1");

    foreach ($Products as $Product => $value) {
        foreach (json_decode($value['resources'], true) as $resources => $data) {
            // var_dump($resources);
            foreach ($resources['XNCatnCode'] as $resource => $value) {
                # code...
                // }

            }
        }
    }

我可以在第二个循环中使用 var_dump($resources); 转储数据。那将返回

string(5) "Model"
string(8) "ItemCode"
string(8) "ItemDesc"
string(7) "ExtDesc"

但我无法循环遍历数据，无法搜索 XNCatnCode。 (该值必须为 3)。当我查看转储时，直接通过 $Products = $SQL->fetchAll("SELECT * FROMpagesWHEREparent= 1")。我得到了这些数据:)

最重要的是，我如何循环遍历 json 数据并选择所有具有 XNCatnCode 3 :) 的产品？

array(1) {
  [0]=>
  array(26) {
    ["id"]=>
    string(1) "3"
    ["trunk"]=>
    string(0) ""
    ["parent"]=>
    string(1) "1"
    ["in_nav"]=>
    string(2) "on"
    ["nav_title"]=>
    string(9) "USB STICK"
    ["route"]=>
    string(9) "usb-stick"
    ["path"]=>
    string(19) "producten/usb-stick"
    ["title"]=>
    string(9) "USB STICK"
    ["meta_keywords"]=>
    string(0) ""
    ["meta_description"]=>
    string(0) ""
    ["open_graph"]=>
    NULL
    ["seo_invisible"]=>
    string(0) ""
    ["template"]=>
    string(8) "Products"
    ["external"]=>
    string(0) ""
    ["new_window"]=>
    string(0) ""
    ["resources"]=>
    string(1898) "{
    "Model": "Simple",
    "ItemCode": "Mijn COde",
    "ItemDesc": "Gaaf ding",
    "ExtDesc": "Heeeeeeel gaaf ding",
    "ItemKeywords": "Gaaf",
    "Size": "5 meter",
    "SizeGrid": "",
    "Gender": "",
    "WeightGR": "",
    "LengthCM": "",
    "HeightCM": "",
    "WidthCM": "",
    "DiameterCM": "",
    "SizeCombined": "",
    "QtyperCarton": "",
    "DecoPackagingIndiv": "",
    "DecoPackagingIndivType": "",
    "DecoPackaging": "",
    "GrossWeightKG": "",
    "NettWeightKG": "",
    "ExportLcm": "",
    "ExportWcm": "",
    "ExportHcm": "",
    "CountryOfOrigin": "",
    "HSCode": "",
    "ImpAllMethods": "",
    "ImpMethodDefault": "",
    "ImpAllPositions": "",
    "ImpPositionDefault": "",
    "ImpPositionSimpleDefault": "",
    "ImpWidthDefaultMM": "",
    "ImpHeightDefaultMM": "",
    "ImpDiameterDefaultMM": "",
    "ImpSizeDefaultMM": "",
    "MaxColoursDefault": "",
    "Brand": "",
    "XNGroupCode": "",
    "XNGroupDesc": "",
    "XNCatnCode": "3",
    "XNCatDesc": "",
    "ColorDesc": "",
    "SimpleColor": "",
    "PMSColorReference": "",
    "BasicColor": "",
    "PenInkColor": "",
    "Material": "",
    "BatteryType": "",
    "Features": "",
    "BestSeller": "",
    "EANCode": "",
    "ThematicItem": "",
    "Compliances": "",
    "ImageMain": "",
    "ImageDecoY1": "",
    "ImageDecoY2": "",
    "ImageDecoY3": "",
    "ImagePackage": "",
    "ImageFront": "",
    "ImageBack": "",
    "ImageExtra1": "",
    "ImageExtra2": "",
    "ImageExtra3": "",
    "ImageDetail1": "",
    "ImageDetail2": "",
    "ImageDetail3": "",
    "ImagePrintLinesDefault": "",
    "MarkSegment": "",
    "MainCat": "",
    "EOYCat": "",
    "LaunchDate": "",
    "Language": "",
    "HEXcolor": "",
    "ImageGroup": "",
    "ImageMood1": "",
    "ImageMood2": "",
    "ImageMood3": "",
    "ImageModel": "",
    "VideoUrl1": "",
    "VideoUrl2": "",
    "NewItem": ""
}"
    ["archived"]=>
    string(0) ""
    ["archived_inherited"]=>
    string(0) ""
    ["publish_at"]=>
    NULL
    ["expire_at"]=>
    NULL
    ["max_age"]=>
    string(1) "0"
    ["last_edited_by"]=>
    string(1) "1"
    ["ga_page_views"]=>
    string(1) "0"
    ["position"]=>
    string(1) "0"
    ["created_at"]=>
    string(19) "2018-12-25 08:27:55"
    ["updated_at"]=>
    string(19) "2018-12-26 05:33:41"
  }
}

Answer 1

foreach ($Products as $Product)
{

        $decoded = json_decode($Product['resources'], true);
        if(isset($decoded['XNCatnCode']))
        {
             //This $Product has XNCatnCode
        }
}

我在 JSON 解码之前删除了 $。所以脚本有效:)