slork/properties.php

<?php

require "settings.php";

$properties = json_decode( '{
	"level": 95,
	"gender": "Female",
	"player_id": 12345,
	"name": "Unknown",
	"personalstats": {
		"useractivity": 26817266,
		"logins": 4361,
		"weaponsbought": 11148,
		"attackmisses": 23141,
		"attackdamage": 59342703,
		"attackhits": 52840,
		"axehits": 1067,
		"attackswon": 14058,
		"highestbeaten": 100,
		"bestkillstreak": 504,
		"jailed": 306,
		"mailssent": 1724,
		"attackcriticalhits": 10817,
		"slahits": 425,
		"moneymugged": 19552798251,
		"largestmug": 1000026663,
		"friendmailssent": 375,
		"roundsfired": 271152,
		"pishits": 346,
		"defendswon": 2765,
		"hospital": 8385,
		"defendslost": 3707,
		"rifhits": 6620,
		"attackslost": 797,
		"attacksassisted": 336,
		"attacksstealthed": 8335,
		"onehitkills": 6547,
		"dumpsearches": 1026,
		"dumpfinds": 1026,
		"itemsdumped": 5050,
		"itemssent": 3567,
		"drugsused": 5352,
		"cantaken": 77,
		"auctionswon": 16,
		"daysbeendonator": 1881,
		"spousemailssent": 104,
		"medicalitemsused": 21720,
		"companymailssent": 185,
		"trainsreceived": 1071,
		"cityfinds": 522,
		"theyrunaway": 1022,
		"failedbusts": 249,
		"peoplebusted": 3000,
		"pointsbought": 44095,
		"itemsbought": 3426,
		"xantaken": 4812,
		"traveltimes": 3475,
		"mextravel": 481,
		"itemsboughtabroad": 92754,
		"cantravel": 462,
		"lontravel": 387,
		"caytravel": 411,
		"argtravel": 350,
		"hawtravel": 165,
		"japtravel": 132,
		"switravel": 586,
		"soutravel": 133,
		"dubtravel": 172,
		"chitravel": 196,
		"shrtaken": 50,
		"shohits": 157,
		"piehits": 4646,
		"bountiescollected": 798,
		"totalbountyreward": 210769630,
		"medstolen": 8,
		"attacksdraw": 218,
		"virusescoded": 125,
		"yourunaway": 157,
		"victaken": 84,
		"overdosed": 108,
		"revivesreceived": 805,
		"auctionsells": 15,
		"exttaken": 246,
		"kettaken": 50,
		"respectforfaction": 76338,
		"smghits": 1663,
		"bountiesreceived": 206,
		"factionmailssent": 173,
		"peoplebought": 502,
		"peopleboughtspent": 54974080,
		"chahits": 234,
		"machits": 119,
		"heahits": 697,
		"networth": 30897667775,
		"grehits": 276,
		"missioncreditsearned": 40942,
		"contractscompleted": 1022,
		"dukecontractscompleted": 1022,
		"revives": 663,
		"missionscompleted": 1,
		"bountiesplaced": 486,
		"totalbountyspent": 194793585,
		"bloodwithdrawn": 5140,
		"lsdtaken": 11,
		"defendsstalemated": 28,
		"meritsbought": 45,
		"opitaken": 14,
		"nerverefills": 1409,
		"consumablesused": 24693,
		"candyused": 1838,
		"alcoholused": 13823,
		"energydrinkused": 9032,
		"tokenrefills": 201,
		"organisedcrimes": 158,
		"booksread": 10,
		"h2hhits": 147,
		"classifiedadsplaced": 6,
		"traveltime": 37998600,
		"unarmoredwon": 259,
		"stockpayouts": 487,
		"boostersused": 1189,
		"territorytime": 2631552,
		"rehabs": 6667,
		"rehabcost": 1404830000,
		"awards": 462,
		"statenhancersused": 5,
		"bestdamage": 11472,
		"racingpointsearned": 2126,
		"raceswon": 699,
		"racesentered": 2395,
		"receivedbountyvalue": 88109384,
		"pcptaken": 1,
		"cityitemsbought": 85276,
		"specialammoused": 10977,
		"spetaken": 1,
		"hollowammoused": 4000,
		"tracerammoused": 2498,
		"incendiaryammoused": 2493,
		"piercingammoused": 1986,
		"refills": 1612
	},
	"status": {
		"description": "Okay",
		"details": "",
		"state": "Okay",
		"color": "green",
		"until": 0
	}
}');

print( "<pre>" );
print_r( $properties );
print( "</pre>");

$list = [];
foreach ( $properties as $key => $val ) {
	if ( is_object( $val ) ) {
		foreach( $val as $subkey => $subval) {
			$list[] = [$subkey, $key];
		}
	} else {
		$list[] = [$key, ''];
	}
}
print("<pre>");
print_r( $list );
print("</pre>");

$pdo = null;
try {
	$pdo = new PDO("sqlite:" . "${db_path}/slork.sqlite");
} catch (PDOException $e) {
	// handle the exception here
}
if ( !$pdo ) {
	exit( "Cannot connect to database" );
}
$query = "DROP TABLE IF EXISTS properties;";
$pdo->exec( $query );
$query = <<<QUERY
	CREATE TABLE IF NOT EXISTS properties (
	    id INTEGER PRIMARY KEY, property TEXT, parent TEXT, UNIQUE(property));
QUERY;
$pdo->exec( $query );
$query = "INSERT INTO properties (property, parent) VALUES (?, ?) ON CONFLICT (property) DO NOTHING;";
$stmt = $pdo->prepare( $query );
foreach ( $list as $item ) {
	$stmt->execute( $item );
}
/**
$query = "SELECT property FROM properties;";
$stmt = $pdo->query( $query );
foreach( $stmt as $row ) {
	print( $row['property'] . ', ');
}
*/