Duplicate Slug Investigator

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.
 */

/**
 * Duplicate Slug Investigator.
 *
 * Purpose:
 * Finds repeated slug values and lists the IDs attached to each duplicate.
 *
 * @param mysqli $database Active procedural mysqli connection resource object.
 * @param string $table_name Table to inspect.
 * @param string $slug_column Slug column name.
 * @param string $id_column ID column name.
 * @return array Duplicate slug report.
 */
function ogSnippetDuplicateSlugInvestigator(mysqli $database, string $table_name, string $slug_column, string $id_column): array {
	$table_name = trim($table_name);
	$slug_column = trim($slug_column);
	$id_column = trim($id_column);
	$identifiers = array($table_name, $slug_column, $id_column);

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

	$sql = 'SELECT `'.$slug_column.'` AS slug_value, COUNT(*) AS slug_count, GROUP_CONCAT(`'.$id_column.'` ORDER BY `'.$id_column.'` SEPARATOR \',\') AS affected_ids '
		.'FROM `'.$table_name.'` '
		.'WHERE `'.$slug_column.'` <> \'\' '
		.'GROUP BY `'.$slug_column.'` '
		.'HAVING COUNT(*) > 1 '
		.'ORDER BY slug_count DESC, slug_value ASC';

	$result = mysqli_query($database, $sql);
	$duplicates = array();

	if ($result === false) {
		return array(
			'duplicate_count' => 0,
			'duplicates' => array(),
			'message' => 'Duplicate slug query failed.'
		);
	}

	while ($row = mysqli_fetch_assoc($result)) {
		$duplicates[] = array(
			'slug' => (string) $row['slug_value'],
			'count' => (int) $row['slug_count'],
			'affected_ids' => explode(',', (string) $row['affected_ids'])
		);
	}

	mysqli_free_result($result);

	return array(
		'duplicate_count' => count($duplicates),
		'duplicates' => $duplicates,
		'message' => 'Duplicate slug investigation completed.'
	);
}

if (isset($database_link) === true && $database_link instanceof mysqli) {
	$duplicate_report = ogSnippetDuplicateSlugInvestigator($database_link, 'items', 'slug', 'id');
	echo 'Kobayashi slug duplicates: '.$duplicate_report['duplicate_count'];
}