h-sphere parent_child recursion

This script takes a h-sphere account_id and attempts to recurse the entire parent_child tree and fetch data from known associated tables. The list of associations is not complete but easily edited. Called with beast.php?account_id=123. It is intended only to give insight and a quick view of the H-Sphere account and associations. beast.assoc.php
<?php
$_direct_assoc = array(
'3ldomain'=>array(
	'table'=>'domains',
	'index'=>'id',
	'display'=>array('name')
),
'3l_dns_zone'=>array(
	'table'=>'dns_zones',
	'index'=>'id',
	'display'=>array('name','email','refresh','retry','expire','minimum','master','slave1','slave2')
),
'a_record'=>array(
	'table'=>'dns_records',
	'index'=>'id',
	'display'=>array('name','data','ttl','zone_id')
),
'antispam'=>array(
	'table'=>'antispam',
	'index'=>'id',
	'display'=>array('local','use_mdomain_prefs')
),
'antivirus'=>array(
	'table'=>'antivirus',
	'index'=>'id',
	'display'=>array('local','use_mdomain_prefs')
),
'cgi'=>array(
	'table'=>'apache_mime',
	'index'=>'id',
	'display'=>array('ext','mime_type','type','vhost_id')
),
'cgidir'=>array(
	'table'=>'apache_cgidir',
	'index'=>'id',
	'display'=>array('dir','alias')
),
'cname_record'=>array(
	'table'=>'dns_records',
	'index'=>'id',
	'display'=>array('name','data','ttl','zone_id')
),
'crontab'=>array(
	'table'=>'crontab',
	'index'=>'id',
	'display'=>array('login','num','command','mailto')
),
'cust_dns_record'=>array(
	'table'=>'dns_records',
	'index'=>'id',
	'display'=>array('name','data','ttl','zone_id')
),
'dns_zone'=>array(
	'table'=>'dns_zones',
	'index'=>'id',
	'display'=>array('name','email','refresh','retry','expire','minimum','master','slave1','slave2')
),
'domain'=>array(
	'table'=>'domains',
	'index'=>'id',
	'display'=>array('name')
),
'nodomain'=>array(
	'table'=>'domains',
	'index'=>'id',
	'display'=>array('name')
),
'domain_alias'=>array(
	'table'=>'domains',
	'index'=>'id',
	'display'=>array('name')
),
'domain_alias_a_record'=>array(
	'table'=>'dns_records',
	'index'=>'id',
	'display'=>array('name','data','ttl','zone_id')
),
'errorlog'=>array(
	'table'=>'apache_log',
	'index'=>'id',
	'display'=>array('file_name','logtype')
),
'ftp_user_traffic'=>array(
	'table'=>'traffics',
	'index'=>'id',
	'display'=>array('tt_size','tt_type')
),
'ftp_traffic'=>array(
	'table'=>'traffics',
	'index'=>'id',
	'display'=>array('tt_size','tt_type')
),
'hosting'=>array(
	'table'=>'apache_vhost',
	'index'=>'id',
	'display'=>array('host_id','indx','symlink','ssi','multiview','dir'/*,'entry'*/)
),
'hosting_alias'=>array(
	'table'=>'domain_resource_alias',
	'index'=>'id',
	'display'=>array('alias_domain_name','actual_resource_type','actual_domain_name')
),
'http_traffic'=>array(
	'table'=>'traffics',
	'index'=>'id',
	'display'=>array('tt_size','tt_type')
),
'ip'=>array(
	'table'=>'resource_amount',
	'index'=>'id',
	'display'=>array('amount')
),
'idomain_alias'=>array(
	'table'=>'vhost_alias',
	'index'=>'id',
	'display'=>array('alias')
),
'l_server'=>array(
	'table'=>'l_server',
	'index'=>'id',
	'display'=>array('name','group_id','p_server_id','type_id','signup')
),
'mail_domain'=>array(
	'table'=>'mail_domain',
	'index'=>'id',
	'display'=>array('catch_all')
),
'mail_forward'=>array(
	'table'=>'mail_forwards',
	'index'=>'id',
	'display'=>array('email_local','email_foreign')
),
'mail_quota'=>array(
	'table'=>'quotas',
	'index'=>'id',
	'display'=>array('size_mb')
),
'mail_domain_alias'=>array(
	'table'=>'mail_domain_aliases',
	'index'=>'id',
	'display'=>array('domain_alias')
),
'mail_service_alias'=>array(
	'table'=>'domain_resource_alias',
	'index'=>'id',
	'display'=>array('alias_domain_name','actual_resource_type','actual_domain_name')
),
'mailbox'=>array(
	'table'=>'mailboxes',
	'index'=>'id',
	'display'=>array('full_email','discard_mail','email')
),
'mail_service'=>array(
	'table'=>'mail_services',
	'index'=>'id',
	'display'=>array('mail_server')
),
'mail_traffic'=>array(
	'table'=>'traffics',
	'index'=>'id',
	'display'=>array('tt_size','tt_type')
),
'MySQL'=>array(
	'table'=>'mysqlres',
	'index'=>'id',
	'display'=>array('mysql_host_id'),
	'expand'=>array('mysql_host_id'=>'l_server')
),
'MySQLDatabase'=>array(
	'table'=>'mysqldb',
	'index'=>'id',
	'display'=>array('db_name','db_description','locked_by','parent_id')
),
'mysqldb_quota'=>array(
	'table'=>'quotas',
	'index'=>'id',
	'display'=>array('size_mb')
),
'MySQLUser'=>array(
	'table'=>'mysql_users',
	'index'=>'id',
	'display'=>array('login','locked_by','parent_id')
),
'mx'=>array(
	'table'=>'dns_records',
	'index'=>'id',
	'display'=>array('name','data','ttl','pref','zone_id')
),
'php3'=>array(
	'table'=>'resource_version',
	'index'=>'resource_id',
	'display'=>array('version')
),
'php3entry'=>array(
	'table'=>'apache_mime',
	'index'=>'id',
	'display'=>array('ext','mime_type','type','vhost_id')
),
'quota'=>array(
	'table'=>'quotas',
	'index'=>'id',
	'display'=>array('size_mb')
),
'referrerlog'=>array(
	'table'=>'apache_log',
	'index'=>'id',
	'display'=>array('logtype','file_name')
),
'subdomain'=>array(
	'table'=>'domains',
	'index'=>'id',
	'display'=>array('name')
),
'sharedssl'=>array(
	'table'=>'shared_ssl',
	'index'=>'id',
	'display'=>array('name','zone_id')
),
'sshresource'=>array(
	'table'=>'shells',
	'index'=>'id',
	'display'=>array('shell')
),
'ssi'=>array(
	'table'=>'apache_mime',
	'index'=>'id',
	'display'=>array('ext','mime_type','type','vhost_id')
),
'spf'=>array(
	'table'=>'spf',
	'index'=>'id',
	'display'=>array('processing')
),
'traffic'=>array(
	'table'=>'traffics',
	'index'=>'id',
	'display'=>array('tt_size','tt_type')
),
'transferlog'=>array(
	'table'=>'apache_log',
	'index'=>'id',
	'display'=>array('file_name','logtype')
),
'unixuser'=>array(
	'table'=>'unix_user',
	'index'=>'id',
	'display'=>array('login','group_name','user_id','dir','hostid'),
	'expand'=>array('hostid'=>'l_server')
),
'unixsubuser'=>array(
	'table'=>'unix_user',
	'index'=>'id',
	'display'=>array('login','group_name','user_id','dir','hostid'),
	'expand'=>array('hostid'=>'l_server')
),
'vhost_alias'=>array(
	'table'=>'vhost_alias',
	'index'=>'id',
	'display'=>array('alias')
),
'webalizer'=>array(
	'table'=>'apache_webalizer',
	'index'=>'id',
	'display'=>array('dir')
),
);

// type_names known to not have a normal relationship model

$_ignore_assoc = array('billviewer','tt','account_preview','easyapp');
?>
beast.php
<?php
if(empty($_REQUEST['account_id'])) {
exit;
}

class pg {
	public function __construct() {
		$this->pgcon = pg_connect("host=1.2.3.4 port=5432 dbname=hsphere user=custom password=yourpassword") or die('DB Connection ERROR: ' . pg_last_error());
	}

	public function q($_q) {
		$_res = pg_query($this->pgcon,$_q);
	return $_res;
	}
	
	public function srl($_q,$_index) {
		$_res = $this->q($_q);
		while($_row = pg_fetch_assoc($_res)) {
			$_data[$_row[$_index]] = $_row;
		}
		return $_data;
	}
	
	public function srr($_q) {
		$_res = $this->q($_q);
		$_row = pg_fetch_assoc($_res);
		return $_row;
	}
}

function hs_assoc_fetch($_assoc,$_index_id) {
	global $_pg,$_direct_assoc;
	$_q = "SELECT ".implode(",",$_direct_assoc[$_assoc]['display'])." FROM ".$_direct_assoc[$_assoc]['table']." WHERE ".$_direct_assoc[$_assoc]['index']." = '".$_index_id."';";
	return $_pg->srr($_q);
}

$_pg = new pg();

include('beast.assoc.php');

$_q = "select * from type_name ORDER by id;";
$_tns = $_pg->srl($_q,"id");

$_q = "select * from parent_child where parent_id='".pg_escape_string($_REQUEST['account_id'])."' and parent_type=0 and child_type !=0 order by child_type;";
$_masters = $_pg->srl($_q,"child_id");

function child_fetch($_pcs) {
	global $_pg,$_tns,$_level,$_direct_assoc,$_missing_assoc,$_ignore_assoc;
	if(!empty($_pcs['child_id']) && !empty($_pcs['child_type'])) {
		$_q = "select * from parent_child where parent_id='".$_pcs['child_id']."' and parent_type='".$_pcs['child_type']."' order by child_type;";
		$_pcs_cs = $_pg->srl($_q,"child_id");
		if(count($_pcs_cs) >=1 && !empty($_pcs_cs)) {
			$_level++;
			foreach($_pcs_cs as $_k => $_c) {
				unset($_hs_assoc);
				if(!empty($_direct_assoc[$_tns[$_c['child_type']]['name']]['table'])) {
					$_hs_assoc = hs_assoc_fetch($_tns[$_c['child_type']]['name'],$_c['child_id']);
				} else {
					if(!in_array($_tns[$_c['child_type']]['name'],$_ignore_assoc)) {
						$_missing_assoc[] = $_tns[$_c['child_type']]['name'];
					}
				}
				
				echo "<tr>";
				echo "<td width='25%' style='font-size: 10pt'>";
				for($_i=0; $_i < $_level; $_i++) {
					echo "&nbsp;&nbsp;&nbsp;&nbsp;";
				}
				echo $_tns[$_c['child_type']]['name']." (".$_c['child_type']."/".$_c['child_id'].") <a name='".$_c['child_id']."'/>";
				echo "</td>";
				echo "<td style='font-size: 8pt'>";
				if(is_array($_hs_assoc)) {
					echo "id '".$_c['child_id']."' in '".$_direct_assoc[$_tns[$_c['child_type']]['name']]['table']."':<br/>";
					foreach($_hs_assoc as $_k => $_v) {
						echo "&nbsp;&nbsp;".$_k.'='.$_v.'<br/>';
					}
					if(is_array($_direct_assoc[$_tns[$_c['child_type']]['name']]['expand'])) {
						foreach($_direct_assoc[$_tns[$_c['child_type']]['name']]['expand'] as $_k => $_v) {
							$_expanded = hs_assoc_fetch($_v,$_hs_assoc[$_k]);
							if(is_array($_expanded)) {
								echo "id '".$_hs_assoc[$_k]."' in '".$_direct_assoc[$_v]['table']."':<br/>";
								foreach($_expanded as $_k => $_v) {
									echo "&nbsp;&nbsp;".$_k.'='.$_v.'<br/>';
								}
							}
						}
					}
				}
				echo "</td>";
				echo "</tr>";
				
				child_fetch($_c);
			}
			$_level--;
		}
	}
}

echo '<table width="100%" border="1">';
foreach($_masters as $_c) {
	$_level = 0;
	echo "<tr>";
	echo "<td style='font-size: 10pt'>";
	echo $_tns[$_c['child_type']]['name']." (".$_c['child_type']."/".$_c['child_id'].") <a name='".$_c['child_id']."'/>";
	echo "</td>";
	echo "<td style='font-size: 8pt'>";
	unset($_hs_assoc);
	if(!empty($_direct_assoc[$_tns[$_c['child_type']]['name']]['table']) && !in_array($_tns[$_c['child_type']]['name'],$_ignore_assoc)) {
		$_hs_assoc = hs_assoc_fetch($_tns[$_c['child_type']]['name'],$_c['child_id']);
	}
	if(is_array($_hs_assoc)) {
		echo "id '".$_c['child_id']."' in '".$_direct_assoc[$_tns[$_c['child_type']]['name']]['table']."':<br/>";
		foreach($_hs_assoc as $_k => $_v) {
			echo "&nbsp;&nbsp;".$_k.'='.$_v.'<br/>';
		}
		if(is_array($_direct_assoc[$_tns[$_c['child_type']]['name']]['expand'])) {
			foreach($_direct_assoc[$_tns[$_c['child_type']]['name']]['expand'] as $_k => $_v) {
				$_expanded = hs_assoc_fetch($_v,$_hs_assoc[$_k]);
				if(is_array($_expanded)) {
					echo "id '".$_hs_assoc[$_k]."' in '".$_direct_assoc[$_v]['table']."':<br/>";
					foreach($_expanded as $_k => $_v) {
						echo "&nbsp;&nbsp;".$_k.'='.$_v.'<br/>';
					}
				}
			}
		}
	} else {
		if(!in_array($_tns[$_c['child_type']]['name'],$_ignore_assoc)) {
			$_missing_assoc[] = $_tns[$_c['child_type']]['name'];
		}
	}
	echo "</td>";
	echo "</tr>";
	child_fetch($_c);
}
echo "</table>";


if(is_array($_missing_assoc)) {
	$_missing_assoc = array_unique($_missing_assoc);
	echo "<hr/>Missing or different association types: ".implode(",",$_missing_assoc);
}
?>