Orphaned Pivot Row Auditor

Code

<?php

/*
 * Copyright (c) 2026 Jeffery L. Paris <jparis@phpog.com>.
 * Free for personal and internal use. Paid project use requires visible credit
 * to Jeffery L. Paris. Corporate use requires a paid license fee unless a
 * separate written license states otherwise.
 */

/**
 * Orphaned Pivot Row Auditor.
 *
 * Purpose:
 * Counts pivot rows whose linked parent records are missing.
 *
 * @param mysqli $database Active procedural mysqli connection resource object.
 * @param string $pivot_table Pivot table name.
 * @param string $left_column Pivot column pointing to the left parent table.
 * @param string $left_table Left parent table name.
 * @param string $left_key Left parent primary-key column.
 * @param string $right_column Pivot column pointing to the right parent table.
 * @param string $right_table Right parent table name.
 * @param string $right_key Right parent primary-key column.
 * @return array Orphan counts and a limited sample list.
 */
function ogSnippetOrphanedPivotRowAuditor(mysqli $database, string $pivot_table, string $left_column, string $left_table, string $left_key, string $right_column, string $right_table, string $right_key): array {
	$identifiers = array($pivot_table, $left_column, $left_table, $left_key, $right_column, $right_table, $right_key);

	foreach ($identifiers as $identifier) {
		if (preg_match('/^[a-zA-Z0-9_]+$/', $identifier) !== 1) {
			return array(
				'ok' => false,
				'message' => 'One or more identifiers are not approved.',
				'left_orphans' => 0,
				'right_orphans' => 0,
				'samples' => array()
			);
		}
	}

	$sql = 'SELECT p.`'.$left_column.'` AS left_value, p.`'.$right_column.'` AS right_value, '
		.'CASE WHEN l.`'.$left_key.'` IS NULL THEN 1 ELSE 0 END AS left_missing, '
		.'CASE WHEN r.`'.$right_key.'` IS NULL THEN 1 ELSE 0 END AS right_missing '
		.'FROM `'.$pivot_table.'` p '
		.'LEFT JOIN `'.$left_table.'` l ON l.`'.$left_key.'` = p.`'.$left_column.'` '
		.'LEFT JOIN `'.$right_table.'` r ON r.`'.$right_key.'` = p.`'.$right_column.'` '
		.'WHERE l.`'.$left_key.'` IS NULL OR r.`'.$right_key.'` IS NULL '
		.'LIMIT 25';

	$result = mysqli_query($database, $sql);
	$left_orphans = 0;
	$right_orphans = 0;
	$samples = array();

	if ($result === false) {
		return array(
			'ok' => false,
			'message' => 'Pivot orphan audit query failed.',
			'left_orphans' => 0,
			'right_orphans' => 0,
			'samples' => array()
		);
	}

	while ($row = mysqli_fetch_assoc($result)) {
		if ((int) $row['left_missing'] === 1) {
			$left_orphans++;
		}

		if ((int) $row['right_missing'] === 1) {
			$right_orphans++;
		}

		$samples[] = array(
			'left_value' => (string) $row['left_value'],
			'right_value' => (string) $row['right_value'],
			'left_missing' => (int) $row['left_missing'],
			'right_missing' => (int) $row['right_missing']
		);
	}

	mysqli_free_result($result);

	$is_clean = false;

	if ($left_orphans === 0 && $right_orphans === 0) {
		$is_clean = true;
	}

	return array(
		'ok' => $is_clean,
		'message' => 'Pivot orphan audit completed.',
		'left_orphans' => $left_orphans,
		'right_orphans' => $right_orphans,
		'samples' => $samples
	);
}

if (isset($database_link) === true && $database_link instanceof mysqli) {
	$pivot_audit = ogSnippetOrphanedPivotRowAuditor($database_link, 'item_categories', 'item_id', 'items', 'id', 'category_id', 'categories', 'id');
	echo 'Nostromo pivot audit clean: '.(int) $pivot_audit['ok'];
}