使用MySQL和PHP读取部分JSON数据 [英] Reading partial json data with MySQL and PHP

问题描述

我使用的CMS在MySQL中以JSON格式保存资源(在一个表中). 以及来自MySQL的普通数据.当我遍历数据时，我得到一个数组+ JSON数据.但是我需要在JSON数据中搜索XNCatnCode.

I use a CMS that saves resources in JSON format in MySQL (In one table). Together with normal data from MySQL. When I loop through the data, I get an array + the JSON data. But I need to search in the JSON data for the XNCatnCode.

Trying to load the data

 $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);转储数据. 那会回来

I can dump the data using var_dump($resources); in the second loop. That will return

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

但是我无法遍历数据，搜索XNCatnCode. (该值必须为3). 当我查看转储时，直接通过$Products = $SQL->fetchAll("SELECT * FROM pages WHERE parent = 1")中的var_dump.我得到了这个数据:)

But I cannot loop through the Data, search for the XNCatnCode. (the value has to be 3 for). When I look at the dump, directly via a var_dump from $Products = $SQL->fetchAll("SELECT * FROMpagesWHEREparent= 1"). I get this Data back :)

最底线，我该如何循环遍历json数据并选择所有具有XNCatnCode 3 :)的产品?

Bottom line, How can I loop trought the json data and select all Products who have 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"
  }
}

推荐答案

foreach ($Products as $Product)
{

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

我在JSON解码之前删除了$.这样脚本就可以了:)

I removed the $ before JSON decode. So the script works :)

这篇关于使用MySQL和PHP读取部分JSON数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！