PHP Iterate through JSON and save via T-SQL-CodePudding

I have PHP code that returns the values below from a website I have subscribed to, my question is how can I loop through this array and echo each line? Along with that, I want to insert each record in a MS-SQL database, if possible, preferably through a bulk insert.

{
  "count": 8,
  "messages": [
    {
      "t": 1638413506,
      "f": 1073743361,
      "tp": "evt",
      "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.026467, 121.721675\nTime: 2021-12-02 10:51:46\nhttp://maps.google.com/?q=17.026467,121.721675",
      "x": 121.721674983,
      "y": 17.0264666677,
      "rt": 0,
      "p": {}
    },
    {
      "t": 1638415192,
      "f": 1073743361,
      "tp": "evt",
      "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.023507, 121.721527\nTime: 2021-12-02 11:19:52\nhttp://maps.google.com/?q=17.023507,121.721527",
      "x": 121.721526655,
      "y": 17.0235066672,
      "rt": 0,
      "p": {}
    },
    {
      "t": 1638416233,
      "f": 1073743361,
      "tp": "evt",
      "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.023335, 121.721730\nTime: 2021-12-02 11:37:13\nhttp://maps.google.com/?q=17.023335,121.721730",
      "x": 121.721729978,
      "y": 17.0233349999,
      "rt": 0,
      "p": {}
    },
    {
      "t": 1638426180,
      "f": 1073743361,
      "tp": "evt",
      "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.023990, 121.721965\nTime: 2021-12-02 14:23:00\nhttp://maps.google.com/?q=17.023990,121.721965",
      "x": 121.721965027,
      "y": 17.0239899993,
      "rt": 0,
      "p": {}
    },
    {
      "t": 1638430480,
      "f": 1073743361,
      "tp": "evt",
      "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.014735, 121.721372\nTime: 2021-12-02 15:34:40\nhttp://maps.google.com/?q=17.014735,121.721372",
      "x": 121.721371651,
      "y": 17.0147350003,
      "rt": 0,
      "p": {}
    },
    {
      "t": 1638439534,
      "f": 1073743361,
      "tp": "evt",
      "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.014727, 121.721405\nTime: 2021-12-02 18:05:34\nhttp://maps.google.com/?q=17.014727,121.721405",
      "x": 121.721405029,
      "y": 17.0147266666,
      "rt": 0,
      "p": {}
    },
    {
      "t": 1638450807,
      "f": 1073743361,
      "tp": "evt",
      "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Dalig, Burgos, Isabela, Philippines\nCoordinates: 17.014760, 121.722102\nTime: 2021-12-02 21:13:27\nhttp://maps.google.com/?q=17.014760,121.722102",
      "x": 121.722101657,
      "y": 17.0147599995,
      "rt": 0,
      "p": {}
    },
    {
      "t": 1638467462,
      "f": 1073743361,
      "tp": "evt",
      "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.015082, 121.721735\nTime: 2021-12-03 01:51:02\nhttp://maps.google.com/?q=17.015082,121.721735",
      "x": 121.721735001,
      "y": 17.0150816669,
      "rt": 0,
      "p": {}
    }
  ]
}

CodePudding user response：

The response you are receiving from the website is in JSON, you can convert this to an array in PHP and iterate through every message and echo out whatever variable you like, along with saving it to a database.

<?php

// The JSON below is fetched from the website's API endpoint.
$json = '{"count":8,"messages":[{"t":1638413506,"f":1073743361,"tp":"evt","et":"(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.026467, 121.721675\nTime: 2021-12-02 10:51:46\nhttp:\/\/maps.google.com\/?q=17.026467,121.721675","x":121.721674983,"y":17.0264666677,"rt":0,"p":{}},{"t":1638415192,"f":1073743361,"tp":"evt","et":"(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.023507, 121.721527\nTime: 2021-12-02 11:19:52\nhttp:\/\/maps.google.com\/?q=17.023507,121.721527","x":121.721526655,"y":17.0235066672,"rt":0,"p":{}},{"t":1638416233,"f":1073743361,"tp":"evt","et":"(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.023335, 121.721730\nTime: 2021-12-02 11:37:13\nhttp:\/\/maps.google.com\/?q=17.023335,121.721730","x":121.721729978,"y":17.0233349999,"rt":0,"p":{}},{"t":1638426180,"f":1073743361,"tp":"evt","et":"(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.023990, 121.721965\nTime: 2021-12-02 14:23:00\nhttp:\/\/maps.google.com\/?q=17.023990,121.721965","x":121.721965027,"y":17.0239899993,"rt":0,"p":{}},{"t":1638430480,"f":1073743361,"tp":"evt","et":"(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.014735, 121.721372\nTime: 2021-12-02 15:34:40\nhttp:\/\/maps.google.com\/?q=17.014735,121.721372","x":121.721371651,"y":17.0147350003,"rt":0,"p":{}},{"t":1638439534,"f":1073743361,"tp":"evt","et":"(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.014727, 121.721405\nTime: 2021-12-02 18:05:34\nhttp:\/\/maps.google.com\/?q=17.014727,121.721405","x":121.721405029,"y":17.0147266666,"rt":0,"p":{}},{"t":1638450807,"f":1073743361,"tp":"evt","et":"(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Dalig, Burgos, Isabela, Philippines\nCoordinates: 17.014760, 121.722102\nTime: 2021-12-02 21:13:27\nhttp:\/\/maps.google.com\/?q=17.014760,121.722102","x":121.722101657,"y":17.0147599995,"rt":0,"p":{}},{"t":1638467462,"f":1073743361,"tp":"evt","et":"(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.015082, 121.721735\nTime: 2021-12-03 01:51:02\nhttp:\/\/maps.google.com\/?q=17.015082,121.721735","x":121.721735001,"y":17.0150816669,"rt":0,"p":{}}]}';

$allMessages = json_decode($json, true); // Convert to associative array.

foreach($allMessages["messages"] as $i => $message) // Iterate through every message.
{
    // $message here now references each message within the data.
    // For this example, output the 'tp' of the message on a new line.
    echo "[Message " . $i . "] " . $message["tp"] . "<br><br>";

    // Insert further code here to save the $message into your SQL database.
}

For saving information to the database using a Microsoft SQL server, see: Microsoft SQL Server for PHP - Getting Started

CodePudding user response：

JSON in one shot.

Fiddle

SQL

DECLARE @json NVARCHAR(MAX) =
N'[
    {
        "count": 8,
        "messages": [
            {
                "t": 1638413506,
                "f": 1073743361,
                "tp": "evt",
                "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.026467, 121.721675\nTime: 2021-12-02 10:51:46\nhttp://maps.google.com/?q=17.026467,121.721675",
                "x": 121.721674983,
                "y": 17.0264666677,
                "rt": 0,
                "p": {}
            },
            {
                "t": 1638415192,
                "f": 1073743361,
                "tp": "evt",
                "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.023507, 121.721527\nTime: 2021-12-02 11:19:52\nhttp://maps.google.com/?q=17.023507,121.721527",
                "x": 121.721526655,
                "y": 17.0235066672,
                "rt": 0,
                "p": {}
            },
            {
                "t": 1638416233,
                "f": 1073743361,
                "tp": "evt",
                "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.023335, 121.721730\nTime: 2021-12-02 11:37:13\nhttp://maps.google.com/?q=17.023335,121.721730",
                "x": 121.721729978,
                "y": 17.0233349999,
                "rt": 0,
                "p": {}
            },
            {
                "t": 1638426180,
                "f": 1073743361,
                "tp": "evt",
                "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.023990, 121.721965\nTime: 2021-12-02 14:23:00\nhttp://maps.google.com/?q=17.023990,121.721965",
                "x": 121.721965027,
                "y": 17.0239899993,
                "rt": 0,
                "p": {}
            },
            {
                "t": 1638430480,
                "f": 1073743361,
                "tp": "evt",
                "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.014735, 121.721372\nTime: 2021-12-02 15:34:40\nhttp://maps.google.com/?q=17.014735,121.721372",
                "x": 121.721371651,
                "y": 17.0147350003,
                "rt": 0,
                "p": {}
            },
            {
                "t": 1638439534,
                "f": 1073743361,
                "tp": "evt",
                "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.014727, 121.721405\nTime: 2021-12-02 18:05:34\nhttp://maps.google.com/?q=17.014727,121.721405",
                "x": 121.721405029,
                "y": 17.0147266666,
                "rt": 0,
                "p": {}
            },
            {
                "t": 1638450807,
                "f": 1073743361,
                "tp": "evt",
                "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Dalig, Burgos, Isabela, Philippines\nCoordinates: 17.014760, 121.722102\nTime: 2021-12-02 21:13:27\nhttp://maps.google.com/?q=17.014760,121.722102",
                "x": 121.722101657,
                "y": 17.0147599995,
                "rt": 0,
                "p": {}
            },
            {
                "t": 1638467462,
                "f": 1073743361,
                "tp": "evt",
                "et": "(NotificationNo: GPS0006)\nAlert!\n\nMOTION DETECTION: Tractor #16\n\nSuspicious movement of vehicle is detected while engine is off! \n\nCurrent Location: Bacnor East, Burgos, Isabela, Philippines\nCoordinates: 17.015082, 121.721735\nTime: 2021-12-03 01:51:02\nhttp://maps.google.com/?q=17.015082,121.721735",
                "x": 121.721735001,
                "y": 17.0150816669,
                "rt": 0,
                "p": {}
            }
        ]
    }
]';

SELECT ISJSON(@json);

-- INSERT INTO <target table>
SELECT message.*
FROM OPENJSON(@json) as messages
   CROSS APPLY OPENJSON(messages.value, '$.messages') 
WITH
(
    t NVARCHAR(10) '$.t'
    , f NVARCHAR(10) '$.f'
    , tp NCHAR(3) '$.tp'
    , et NVARCHAR(MAX) '$.et' 
    , x NVARCHAR(20) '$.x'
    , y NVARCHAR(20) '$.y'
    , rt INT '$.rt'
    , p NVARCHAR(100) '$.p'
) 
AS message;