Prepared Select Builder

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 readable prepared SELECT plan from allowed columns, filters, and sort rules.
 *
 * This function returns SQL, bind types, and bind parameters only. It does not execute
 * the query, which keeps database control explicit in the calling controller/helper.
 *
 * @param string $table Database table name from an approved allowlist.
 * @param array $columns Columns to select.
 * @param array $filters Filter rules keyed by column name.
 * @param array $options Query options such as allowed tables, allowed columns, order, limit, and offset.
 * @return array Prepared SELECT plan.
 */
function ogBuildPreparedSelect($table, $columns = array(), $filters = 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 (!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'];
	}

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

	if (empty($select_columns)) {
		$select_columns[] = '`id`';
	}

	$where_parts = array();
	$types = '';
	$params = array();
	$allowed_operators = array('=', '!=', '<', '<=', '>', '>=', 'LIKE', 'IN');

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

		$operator = '=';
		$value = $rule;
		if (is_array($rule)) {
			if (!empty($rule['operator'])) {
				$operator = strtoupper(trim((string)$rule['operator']));
			}
			if (array_key_exists('value', $rule)) {
				$value = $rule['value'];
			} else {
				$value = '';
			}
		}

		if (!in_array($operator, $allowed_operators, true)) {
			$operator = '=';
		}

		if ($operator == 'IN') {
			$values = array();
			if (is_array($value)) {
				$values = $value;
			}
			if (empty($values)) {
				continue;
			}
			$placeholders = array();
			foreach ($values as $item) {
				$placeholders[] = '?';
				$params[] = $item;
				if (is_int($item)) {
					$types .= 'i';
				} elseif (is_float($item)) {
					$types .= 'd';
				} else {
					$types .= 's';
				}
			}
			$where_parts[] = '`' . $field . '` IN (' . implode(', ', $placeholders) . ')';
		} else {
			$where_parts[] = '`' . $field . '` ' . $operator . ' ?';
			$params[] = $value;
			if (is_int($value)) {
				$types .= 'i';
			} elseif (is_float($value)) {
				$types .= 'd';
			} else {
				$types .= 's';
			}
		}
	}

	$sql = 'SELECT ' . implode(', ', $select_columns) . ' FROM `' . $table . '`';
	if (!empty($where_parts)) {
		$sql .= ' WHERE ' . implode(' AND ', $where_parts);
	}

	if (!empty($options['order_by'])) {
		$order_by = trim((string)$options['order_by']);
		if (preg_match('/^[a-zA-Z0-9_]+$/', $order_by)) {
			if (empty($allowed_columns) || in_array($order_by, $allowed_columns, true)) {
				$direction = 'ASC';
				if (!empty($options['order_direction']) && strtoupper((string)$options['order_direction']) == 'DESC') {
					$direction = 'DESC';
				}
				$sql .= ' ORDER BY `' . $order_by . '` ' . $direction;
			}
		}
	}

	if (!empty($options['limit'])) {
		$limit = (int)$options['limit'];
		if ($limit > 0) {
			$sql .= ' LIMIT ' . $limit;
			if (!empty($options['offset'])) {
				$offset = (int)$options['offset'];
				if ($offset > 0) {
					$sql .= ' OFFSET ' . $offset;
				}
			}
		}
	}

	$result['success'] = true;
	$result['message'] = 'Prepared SELECT plan built.';
	$result['data'] = array(
		'sql' => $sql,
		'types' => $types,
		'params' => $params
	);

	return $result;
}