slork/properties.php

<?php

require "settings.php";

$properties = json_decode( '{
	"level": 95,
	"gender": "Female",
	"player_id": 123123123,
	"name": "onttwothree",
	"rank": "Elite Trader",
	"property": "Private Island",
	"signup": "2015-02-17 17:31:35",
	"awards": 464,
	"friends": 576,
	"enemies": 109,
	"forum_posts": 1610,
	"karma": 709,
	"age": 1903,
	"role": "Civilian",
	"donator": 1,
	"property_id": 2008619,
	"personalstats": {
		"useractivity": 26842211,
		"logins": 4361,
		"weaponsbought": 11148,
		"attackmisses": 23172,
		"attackdamage": 59366907,
		"attackhits": 52882,
		"axehits": 1067,
		"attackswon": 14064,
		"highestbeaten": 100,
		"bestkillstreak": 504,
		"jailed": 306,
		"mailssent": 1725,
		"attackcriticalhits": 10825,
		"slahits": 427,
		"moneymugged": 19552822850,
		"largestmug": 1000026663,
		"friendmailssent": 375,
		"roundsfired": 271310,
		"pishits": 349,
		"defendswon": 2765,
		"hospital": 8416,
		"defendslost": 3737,
		"rifhits": 6620,
		"attackslost": 797,
		"attacksassisted": 336,
		"attacksstealthed": 8338,
		"onehitkills": 6548,
		"dumpsearches": 1026,
		"dumpfinds": 1026,
		"itemsdumped": 5050,
		"itemssent": 3569,
		"drugsused": 5361,
		"cantaken": 77,
		"auctionswon": 16,
		"daysbeendonator": 1884,
		"spousemailssent": 104,
		"medicalitemsused": 21791,
		"companymailssent": 185,
		"trainsreceived": 1071,
		"cityfinds": 522,
		"theyrunaway": 1022,
		"failedbusts": 249,
		"peoplebusted": 3002,
		"pointsbought": 44095,
		"itemsbought": 3426,
		"xantaken": 4821,
		"traveltimes": 3490,
		"mextravel": 483,
		"itemsboughtabroad": 93148,
		"cantravel": 463,
		"lontravel": 391,
		"caytravel": 418,
		"argtravel": 350,
		"hawtravel": 165,
		"japtravel": 132,
		"switravel": 587,
		"soutravel": 133,
		"dubtravel": 172,
		"chitravel": 196,
		"shrtaken": 50,
		"shohits": 157,
		"piehits": 4646,
		"bountiescollected": 798,
		"totalbountyreward": 210769630,
		"medstolen": 8,
		"attacksdraw": 218,
		"virusescoded": 125,
		"yourunaway": 158,
		"victaken": 84,
		"overdosed": 109,
		"revivesreceived": 806,
		"auctionsells": 15,
		"exttaken": 246,
		"kettaken": 50,
		"respectforfaction": 76352,
		"smghits": 1663,
		"bountiesreceived": 206,
		"factionmailssent": 173,
		"peoplebought": 502,
		"peopleboughtspent": 54974080,
		"chahits": 234,
		"machits": 119,
		"heahits": 697,
		"networth": 30913812683,
		"grehits": 276,
		"missioncreditsearned": 41062,
		"contractscompleted": 1026,
		"dukecontractscompleted": 1026,
		"revives": 663,
		"missionscompleted": 1,
		"bountiesplaced": 487,
		"totalbountyspent": 194943585,
		"bloodwithdrawn": 5140,
		"lsdtaken": 11,
		"defendsstalemated": 28,
		"meritsbought": 45,
		"opitaken": 14,
		"nerverefills": 1413,
		"consumablesused": 24821,
		"candyused": 1838,
		"alcoholused": 13924,
		"energydrinkused": 9059,
		"tokenrefills": 202,
		"organisedcrimes": 158,
		"booksread": 10,
		"h2hhits": 148,
		"classifiedadsplaced": 6,
		"traveltime": 38094300,
		"unarmoredwon": 261,
		"stockpayouts": 489,
		"boostersused": 1190,
		"territorytime": 2655360,
		"rehabs": 6695,
		"rehabcost": 1410430000,
		"awards": 464,
		"statenhancersused": 5,
		"bestdamage": 11472,
		"racingpointsearned": 2146,
		"raceswon": 701,
		"racesentered": 2410,
		"receivedbountyvalue": 88109384,
		"pcptaken": 1,
		"cityitemsbought": 85276,
		"specialammoused": 10977,
		"spetaken": 1,
		"hollowammoused": 4000,
		"tracerammoused": 2498,
		"incendiaryammoused": 2493,
		"piercingammoused": 1986,
		"refills": 1615
	},
	"status": {
		"description": "Traveling to Canada",
		"details": "",
		"state": "Traveling",
		"color": "blue",
		"until": 0
	},
	"criminalrecord": {
		"selling_illegal_products": 5003,
		"theft": 25055,
		"auto_theft": 3003,
		"drug_deals": 697,
		"computer_crimes": 7206,
		"murder": 10019,
		"fraud_crimes": 10340,
		"other": 5007,
		"total": 66330
	},
	"life": {
		"current": 6937,
		"maximum": 6937,
		"increment": 416,
		"interval": 300,
		"ticktime": 8,
		"fulltime": 0
	},
	"job": {
		"position": "Director",
		"company_id": 68490,
		"company_name": "Farmers Market &#128004;"
	},
	"faction": {
		"position": "Member",
		"faction_id": 8336,
		"days_in_faction": 1612,
		"faction_name": "Relentless"
	},
	"married": {
		"spouse_id": 1926474,
		"spouse_name": "adlowe07",
		"duration": 1667
	},
	"basicicons": {
		"icon7": "Female",
		"icon3": "Donator",
		"icon8": "Married - To adlowe07",
		"icon73": "Company - Director of Farmers Market &#128004; (Farm)",
		"icon9": "Faction - Member of Relentless",
		"icon35": "Bazaar - This person has items in their bazaar for sale",
		"icon71": "Traveling"
	},
	"states": {
		"hospital_timestamp": 0,
		"jail_timestamp": 0
	},
	"last_action": {
		"status": "Offline",
		"timestamp": 1588623674,
		"relative": "28 minutes ago"
	}
}');

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'] . ', ');
}
*/