Auto-Increment Gap Review

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

/**
 * Auto-Increment Gap Review.
 *
 * Purpose:
 * Reviews ID range density and the next auto-increment value for a table.
 *
 * @param mysqli $database Active procedural mysqli connection resource object.
 * @param string $table_name Table to inspect.
 * @param string $id_column Integer ID column.
 * @return array Auto-increment review data.
 */
function ogSnippetAutoIncrementGapReview(mysqli $database, string $table_name, string $id_column): array {
	$table_name = trim($table_name);
	$id_column = trim($id_column);

	if (preg_match('/^[a-zA-Z0-9_]+$/', $table_name) !== 1 || preg_match('/^[a-zA-Z0-9_]+$/', $id_column) !== 1) {
		return array(
			'ok' => false,
			'message' => 'Table or ID column is not an approved identifier.',
			'min_id' => 0,
			'max_id' => 0,
			'row_count' => 0,
			'possible_gap_count' => 0,
			'next_auto_increment' => 0
		);
	}

	$range_sql = 'SELECT MIN(`'.$id_column.'`) AS min_id, MAX(`'.$id_column.'`) AS max_id, COUNT(*) AS row_count FROM `'.$table_name.'`';
	$range_result = mysqli_query($database, $range_sql);

	if ($range_result === false) {
		return array(
			'ok' => false,
			'message' => 'ID range query failed.',
			'min_id' => 0,
			'max_id' => 0,
			'row_count' => 0,
			'possible_gap_count' => 0,
			'next_auto_increment' => 0
		);
	}

	$range_row = mysqli_fetch_assoc($range_result);
	mysqli_free_result($range_result);

	$min_id = 0;
	$max_id = 0;
	$row_count = 0;

	if (is_array($range_row) === true) {
		if (isset($range_row['min_id']) === true) {
			$min_id = (int) $range_row['min_id'];
		}

		if (isset($range_row['max_id']) === true) {
			$max_id = (int) $range_row['max_id'];
		}

		if (isset($range_row['row_count']) === true) {
			$row_count = (int) $range_row['row_count'];
		}
	}

	$possible_gap_count = 0;

	if ($row_count > 0 && $max_id >= $min_id) {
		$possible_gap_count = ($max_id - $min_id + 1) - $row_count;
	}

	$current_database = '';
	$database_result = mysqli_query($database, 'SELECT DATABASE() AS current_database');

	if ($database_result !== false) {
		$database_row = mysqli_fetch_assoc($database_result);

		if (is_array($database_row) === true && isset($database_row['current_database']) === true) {
			$current_database = (string) $database_row['current_database'];
		}

		mysqli_free_result($database_result);
	}

	$next_auto_increment = 0;

	if ($current_database !== '') {
		$schema_sql = mysqli_real_escape_string($database, $current_database);
		$table_sql = mysqli_real_escape_string($database, $table_name);
		$auto_sql = "SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '".$schema_sql."' AND TABLE_NAME = '".$table_sql."'";
		$auto_result = mysqli_query($database, $auto_sql);

		if ($auto_result !== false) {
			$auto_row = mysqli_fetch_assoc($auto_result);

			if (is_array($auto_row) === true && isset($auto_row['AUTO_INCREMENT']) === true) {
				$next_auto_increment = (int) $auto_row['AUTO_INCREMENT'];
			}

			mysqli_free_result($auto_result);
		}
	}

	return array(
		'ok' => true,
		'message' => 'Auto-increment review completed. Gaps alone are not an error.',
		'min_id' => $min_id,
		'max_id' => $max_id,
		'row_count' => $row_count,
		'possible_gap_count' => $possible_gap_count,
		'next_auto_increment' => $next_auto_increment
	);
}

if (isset($database_link) === true && $database_link instanceof mysqli) {
	$gap_report = ogSnippetAutoIncrementGapReview($database_link, 'items', 'id');
	echo 'Enterprise ID gaps reviewed: '.$gap_report['possible_gap_count'];
}