h-sphere parent_child orphans

This script takes no input, recurses the known h-sphere table relations, looks for rows which do not have matching parent_child.child_id, outputs detail and overall statsĀ at the bottom. This script uses an improved/fixed version of theĀ beast.assoc.php used in previous posts (eg crontab is not assoc). 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');
?>
orphans.php
<?php
ini_set('display_errors',1);
ini_set('memory_limit','1G');

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;
	}
}

$_pg = new pg();
include('beast.assoc.php');

if(is_array($_direct_assoc)) {
  foreach($_direct_assoc as $_assoc => $_values) {
    $_check_tables[$_values['table']] = $_values['index'];
  }
}

// none PC table remove
unset($_check_tables['l_server']);

$_pcs = $_pg->srl("SELECT child_id FROM parent_child;","child_id");

echo "<table width=100% border=1>";
if(is_array($_check_tables)) {
  foreach($_check_tables as $_table => $_index) {
    $_q = "SELECT ".pg_escape_string($_index)." FROM ".pg_escape_string($_table).";";
    $_tbl_ids = $_pg->srl($_q,$_index);
    if(is_array($_tbl_ids)) {
      foreach($_tbl_ids as $_tbl_id => $_tbl_idv) {
        $_total_compared += 1;
        if(empty($_pcs[$_tbl_id])) {
          $_total_orphans += 1;
          $_total_bytbl[$_table] += 1;
          $_orphan = $_pg->srr("SELECT * FROM ".pg_escape_string($_table)." WHERE ".pg_escape_string($_index)."='".pg_escape_string($_tbl_id)."';");
          ?>
          <tr>
            <td><?=$_table; ?></td>
            <td><?=$_tbl_id; ?></td>
            <td>
            <?php
  echo implode(', ', array_map(
    function ($v, $k) { return sprintf("%s='%s'", $k, $v); },
    $_orphan,
    array_keys($_orphan)
  ));
  ?>
            </td>
          </tr>
          <?
        }
      }
    }
  }
}
echo "</table>";
echo "<h2>Compared ".$_total_compared." table rows against ".count($_pcs)." parent_child rows</h2>";
echo "<h2>".$_total_orphans." orphans (".number_format(($_total_orphans/$_total_compared)*100,1)."%)</h2>";
echo "<h2>By type:</h2>";
echo "<pre>";
print_r($_total_bytbl);
echo "</pre>";
?>