Fulltext Search Health Check

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

/**
 * Fulltext Search Health Check.
 *
 * Purpose:
 * Confirms whether expected search columns are covered by a FULLTEXT index.
 *
 * @param mysqli $database Active procedural mysqli connection resource object.
 * @param string $table_name Table to inspect.
 * @param array $expected_columns Columns expected to participate in fulltext search.
 * @return array Fulltext readiness report.
 */
function ogSnippetFulltextSearchHealthCheck(mysqli $database, string $table_name, array $expected_columns): array {
	$table_name = trim($table_name);

	if (preg_match('/^[a-zA-Z0-9_]+$/', $table_name) !== 1) {
		return array(
			'ready' => false,
			'message' => 'Table name is not an approved identifier.',
			'indexes' => array(),
			'missing_columns' => $expected_columns
		);
	}

	$expected_map = array();
	$covered_map = array();
	$indexes = array();

	foreach ($expected_columns as $column_name) {
		$column_name = trim((string) $column_name);

		if (preg_match('/^[a-zA-Z0-9_]+$/', $column_name) === 1) {
			$expected_map[$column_name] = true;
		}
	}

	$result = mysqli_query($database, "SHOW INDEX FROM `".$table_name."` WHERE Index_type = 'FULLTEXT'");

	if ($result === false) {
		return array(
			'ready' => false,
			'message' => 'FULLTEXT index lookup failed.',
			'indexes' => array(),
			'missing_columns' => array_keys($expected_map)
		);
	}

	while ($row = mysqli_fetch_assoc($result)) {
		$key_name = (string) $row['Key_name'];
		$column_name = (string) $row['Column_name'];

		if (isset($indexes[$key_name]) === false) {
			$indexes[$key_name] = array();
		}

		$indexes[$key_name][] = $column_name;
		$covered_map[$column_name] = true;
	}

	mysqli_free_result($result);

	$missing_columns = array();

	foreach ($expected_map as $column_name => $flag) {
		if (isset($covered_map[$column_name]) === false) {
			$missing_columns[] = $column_name;
		}
	}

	$ready = false;

	if (count($missing_columns) === 0 && count($expected_map) > 0) {
		$ready = true;
	}

	return array(
		'ready' => $ready,
		'message' => 'FULLTEXT search index review completed.',
		'indexes' => $indexes,
		'missing_columns' => $missing_columns
	);
}

if (isset($database_link) === true && $database_link instanceof mysqli) {
	$search_health = ogSnippetFulltextSearchHealthCheck($database_link, 'items', array('title', 'description'));
	echo 'Vulcan search health ready: '.(int) $search_health['ready'];
}