Powershell and API's
I have made similar.
Since I have no idea of Java/PHP in conjunction with json, I let PowerShell call the API and write anything to the database. A small table simply shows the contents of the database. Is not live, but better than nothing: D
Script below
Let someone find something useful
##MySQL
$db_host = ‘host’
$db_name = ‘db-name’
$db_user = ‘db-user’
$db_pass = ‘db-pass’
$db_table = ‘db-table’
##Vars
$lang = ‘de’
##MySQL-Connect
[void][system.reflection.Assembly]::LoadFrom(“C:\Program Files (x86)\MySQL\MySQL Connector Net 6.5.4\Assemblies\v4.0\MySQL.Data.dll”)
$dbconnect = New-Object MySql.Data.MySqlClient.MySqlConnection
$dbconnect.ConnectionString = “server=$db_host;user id=$db_user;password=$db_pass;database=$db_name;pooling=false”
$dbconnect.Open()
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $dbconnect
##World Events for 2206
$get = Invoke-WebRequest “https://api.guildwars2.com/v1/events.json?world_id=2206”
$get = $get | ConvertFrom-Json
for($i = 0; $i -lt $get.events.count;$i++) {
$map_id = $get.events.map_id[$i]
$event_id = $get.events.event_id[$i]
$status = $get.events.state[$i]
$sql.CommandText = “INSERT INTO $db_table (map_id, event_id, status) values (‘$map_id’, ‘$event_id’, ‘$status’) ON DUPLICATE KEY UPDATE status=‘$status’”
$sql.ExecuteNonQuery()
}
##Event-Names (You have to do this once!)
$get = Invoke-WebRequest “https://api.guildwars2.com/v1/event_names.json?lang=$lang”
$get = $get.Content | ConvertFrom-Json
for($i = 0; $i -lt $get.events.count;$i++) {
$event_id = $get.id[$i]
$event_name = $get.name[$i]
$sql.CommandText = “UPDATE $db_table SET `event_name` = ‘$event_name’ WHERE `event_id` = ‘$event_id’”
$sql.ExecuteNonQuery()
}
##Map-Names (You have to do this once!)
$get = Invoke-WebRequest “https://api.guildwars2.com/v1/map_names.json?lang=$lang”
$get = $get.Content | ConvertFrom-Json
for($i = 0; $i -lt $get.events.count;$i++) {
$map_id = $get.id[$i]
$map_name = $get.name[$i]
$sql.CommandText = “UPDATE $db_table SET `map_name` = ‘$map_name’ WHERE `map_id` = ‘$map_id’”
$sql.ExecuteNonQuery()
}
$dbconnect.Close()