Fulltext Search Planner

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

/**
 * Creates a safe fulltext search plan with fallback options and relevance sorting.
 *
 * The function builds SQL, bind types, and parameters. The caller remains responsible
 * for preparing/executing the statement.
 *
 * @param string $table Database table name.
 * @param string $query Search query.
 * @param array $search_fields Fulltext fields.
 * @param array $options Allowlist, fallback, limit, and filter options.
 * @return array Search plan.
 */
function ogBuildFulltextSearchPlan($table, $query, $search_fields = array(), $options = array()) {
	$result = array(
		'success' => false,
		'message' => '',
		'data' => array()
	);

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

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

	$fields = array();
	foreach ((array)$search_fields as $field) {
		$field = trim((string)$field);
		if (!preg_match('/^[a-zA-Z0-9_]+$/', $field)) {
			continue;
		}
		if (!empty($allowed_fields) && !in_array($field, $allowed_fields, true)) {
			continue;
		}
		$fields[] = '`' . $field . '`';
	}
	if (empty($fields)) {
		$result['message'] = 'At least one approved search field is required.';
		return $result;
	}

	$boolean_query = preg_replace('/[^a-zA-Z0-9\s+\-*"_]/', ' ', $query);
	$boolean_query = trim(preg_replace('/\s+/', ' ', $boolean_query));
	if (empty($boolean_query)) {
		$result['message'] = 'Search query did not contain searchable text.';
		return $result;
	}

	$field_sql = implode(', ', $fields);
	$sql = 'SELECT *, MATCH (' . $field_sql . ') AGAINST (? IN BOOLEAN MODE) AS `relevance` FROM `' . $table . '` WHERE MATCH (' . $field_sql . ') AGAINST (? IN BOOLEAN MODE)';
	$types = 'ss';
	$params = array($boolean_query, $boolean_query);

	if (!empty($options['published_only'])) {
		$sql .= ' AND `is_published` = ?';
		$types .= 'i';
		$params[] = 1;
	}

	$sql .= ' ORDER BY `relevance` DESC';
	$limit = 25;
	if (!empty($options['limit'])) {
		$limit = (int)$options['limit'];
	}
	if ($limit < 1) {
		$limit = 25;
	}
	if ($limit > 100) {
		$limit = 100;
	}
	$sql .= ' LIMIT ' . $limit;

	$result['success'] = true;
	$result['message'] = 'Fulltext search plan built.';
	$result['data'] = array(
		'sql' => $sql,
		'types' => $types,
		'params' => $params,
		'query' => $boolean_query,
		'limit' => $limit
	);

	return $result;
}