我有 PHP 代碼可以從我訂閱的網站回傳以下值,我的問題是如何遍歷這個陣列并回顯每一行?除此之外,我想在 MS-SQL 資料庫中插入每條記錄,如果可能的話,最好通過批量插入。
{
"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": {}
}
]
}
uj5u.com熱心網友回復:
您從網站收到的回應是 JSON 格式,您可以將其轉換為 PHP 中的陣列,并遍歷每條訊息并回顯出您喜歡的任何變數,并將其保存到資料庫中。
<?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.
}
有關使用 Microsoft SQL 服務器將資訊保存到資料庫的資訊,請參閱:Microsoft SQL Server for PHP - Getting Started
uj5u.com熱心網友回復:
一槍搞定 JSON。
小提琴
查詢陳述句
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;
轉載請註明出處,本文鏈接:https://www.uj5u.com/gongcheng/377698.html
標籤:php sql-server
