Skip to content
Home
← Back to Functions
Code

Duplicate Row Finder

Finds duplicate database rows by one or more natural-key fields.

Function signature

ogFindDuplicateRowsByKey(table, key_fields = array(), options = array())

Categories

  • Database Integrity

Parameters

tableTable name.key_fieldsFields that define duplication.optionsOptional allowed tables/columns and having threshold. Recognized keys: `allowed_columns`, `allowed_tables`, `threshold`.

Return value

Public-safe status string returned by the function.

  • success
  • message
  • data

Compatibility

Existing function name, slug, path, and call order preserved; advertised metadata corrected to the actual source behavior.

Minimum PHP version: 7.4

Security notes

Use caller-owned allowlists and procedural mysqli prepared execution where SQL plans are returned; validate file paths, MIME policies, and permissions before file or download workflows.

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

/**
 * Builds a duplicate-row detection plan for one or more natural-key fields.
 *
 * The function returns SQL only. The caller keeps execution and permissions explicit.
 *
 * @param string $table Table name.
 * @param array $key_fields Fields that define duplication.
 * @param array $options Optional allowed tables/columns and having threshold.
 * @return array Duplicate row query plan.
 */
function ogFindDuplicateRowsByKey($table, $key_fields = array(), $options = array()) {
	$result = array(
		'success' => false,
		'message' => '',
		'data' => array()
	);

	$table = trim((string)$table);
	if (empty($table) || !preg_match('/^[a-zA-Z0-9_]+$/', $table)) {
		$result['message'] = 'Invalid table name.';
		return $result;
	}

	if (!is_array($options)) {
		$options = array();
	}
	if (!empty($options['allowed_tables']) && is_array($options['allowed_tables'])) {
		if (!in_array($table, $options['allowed_tables'], true)) {
			$result['message'] = 'Table is not allowlisted.';
			return $result;
		}
	}

	$allowed_columns = array();
	if (!empty($options['allowed_columns']) && is_array($options['allowed_columns'])) {
		$allowed_columns = $options['allowed_columns'];
	}

	$fields = array();
	foreach ((array)$key_fields as $field) {
		$field = trim((string)$field);
		if (!preg_match('/^[a-zA-Z0-9_]+$/', $field)) {
			continue;
		}
		if (!empty($allowed_columns) && !in_array($field, $allowed_columns, true)) {
			continue;
		}
		$fields[] = '`' . $field . '`';
	}

	if (empty($fields)) {
		$result['message'] = 'At least one key field is required.';
		return $result;
	}

	$threshold = 1;
	if (!empty($options['threshold'])) {
		$threshold = (int)$options['threshold'];
	}
	if ($threshold < 1) {
		$threshold = 1;
	}

	$sql = 'SELECT ' . implode(', ', $fields) . ', COUNT(*) AS duplicate_count FROM `' . $table . '` GROUP BY ' . implode(', ', $fields) . ' HAVING COUNT(*) > ' . $threshold;

	$result['success'] = true;
	$result['message'] = 'Duplicate row query plan built.';
	$result['data'] = array('sql' => $sql);

	return $result;
}