Safe Bulk Update 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.
 */

/**
 * Safe Bulk Update Planner.
 *
 * Purpose:
 * Plans a guarded bulk update without executing it.
 *
 * @param string $table_name Table planned for update.
 * @param array $allowed_columns Columns approved for changes and filters.
 * @param array $filter Required filter columns and values.
 * @param array $changes Columns and values to update.
 * @param int $maximum_rows Maximum rows allowed for the write step.
 * @return array Bulk update plan with count SQL, update SQL, values, and refusal reasons.
 */
function ogSnippetSafeBulkUpdatePlanner(string $table_name, array $allowed_columns, array $filter, array $changes, int $maximum_rows): array {
	$table_name = trim($table_name);
	$allowed_map = array();
	$set_parts = array();
	$where_parts = array();
	$values = array();
	$reasons = array();

	if (preg_match('/^[a-zA-Z0-9_]+$/', $table_name) !== 1) {
		$reasons[] = 'Table name is not an approved identifier.';
	}

	if ($maximum_rows < 1) {
		$reasons[] = 'Maximum row guard must be at least one.';
	}

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

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

	foreach ($changes as $column_name => $value) {
		$column_name = trim((string) $column_name);

		if (isset($allowed_map[$column_name]) === false) {
			$reasons[] = 'Change column not allowed: '.$column_name;
			continue;
		}

		$set_parts[] = '`'.$column_name.'` = ?';
		$values[] = $value;
	}

	foreach ($filter as $column_name => $value) {
		$column_name = trim((string) $column_name);

		if (isset($allowed_map[$column_name]) === false) {
			$reasons[] = 'Filter column not allowed: '.$column_name;
			continue;
		}

		$where_parts[] = '`'.$column_name.'` = ?';
		$values[] = $value;
	}

	if (count($set_parts) === 0) {
		$reasons[] = 'No approved update columns were provided.';
	}

	if (count($where_parts) === 0) {
		$reasons[] = 'Bulk update requires a WHERE filter.';
	}

	if (count($reasons) > 0) {
		return array(
			'approved' => false,
			'count_sql' => '',
			'update_sql' => '',
			'values' => array(),
			'maximum_rows' => $maximum_rows,
			'reasons' => $reasons
		);
	}

	$where_sql = implode(' AND ', $where_parts);
	$count_sql = 'SELECT COUNT(*) AS affected_preview FROM `'.$table_name.'` WHERE '.$where_sql;
	$update_sql = 'UPDATE `'.$table_name.'` SET '.implode(', ', $set_parts).' WHERE '.$where_sql.' LIMIT '.(int) $maximum_rows;

	return array(
		'approved' => true,
		'count_sql' => $count_sql,
		'update_sql' => $update_sql,
		'values' => $values,
		'maximum_rows' => $maximum_rows,
		'reasons' => array()
	);
}

$bulk_plan = ogSnippetSafeBulkUpdatePlanner(
	'items',
	array('status', 'category_slug'),
	array('category_slug' => 'expanse-tools'),
	array('status' => 'draft'),
	25
);

echo 'Bulk plan approved: '.(int) $bulk_plan['approved'];