Column Type Profiler

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

/**
 * Profiles likely data types for each column in sampled row data.
 *
 * The result is advisory only. It returns confidence counts rather than forcing
 * a permanent schema decision.
 *
 * @param array $rows List of associative or numeric rows.
 * @param int $sample_size Maximum rows to inspect.
 * @return array Structured result with column profile counts and likely types.
 */
function ogProfileColumnTypes($rows, $sample_size = 50) {
	$result = array(
		'success' => false,
		'message' => '',
		'data' => array()
	);

	if (!is_array($rows)) {
		$result['message'] = 'Rows must be an array.';
		return $result;
	}

	$sample_size = (int)$sample_size;
	if ($sample_size < 1) {
		$sample_size = 50;
	}

	$profiles = array();
	$row_count = 0;

	foreach ($rows as $row) {
		if ($row_count >= $sample_size) {
			break;
		}
		if (!is_array($row)) {
			continue;
		}
		$row_count++;

		foreach ($row as $column => $value) {
			$column = (string)$column;
			if (!array_key_exists($column, $profiles)) {
				$profiles[$column] = array(
					'blank' => 0,
					'integer' => 0,
					'decimal' => 0,
					'email' => 0,
					'date' => 0,
					'boolean' => 0,
					'text' => 0,
					'likely_type' => 'text'
				);
			}

			$value = trim((string)$value);
			if ($value === '') {
				$profiles[$column]['blank']++;
			} elseif (preg_match('/^-?[0-9]+$/', $value)) {
				$profiles[$column]['integer']++;
			} elseif (preg_match('/^-?[0-9]+\.[0-9]+$/', $value)) {
				$profiles[$column]['decimal']++;
			} elseif (filter_var($value, FILTER_VALIDATE_EMAIL) !== false) {
				$profiles[$column]['email']++;
			} elseif (strtotime($value) !== false && preg_match('/[0-9]{2,4}/', $value)) {
				$profiles[$column]['date']++;
			} elseif (preg_match('/^(yes|no|true|false|0|1)$/i', $value)) {
				$profiles[$column]['boolean']++;
			} else {
				$profiles[$column]['text']++;
			}
		}
	}

	foreach ($profiles as $column => $profile) {
		$best_type = 'text';
		$best_count = -1;
		foreach ($profile as $type => $count) {
			if ($type == 'likely_type') {
				continue;
			}
			if ($type != 'blank' && $count > $best_count) {
				$best_type = $type;
				$best_count = $count;
			}
		}
		$profiles[$column]['likely_type'] = $best_type;
	}

	$result['success'] = true;
	$result['message'] = 'Column types profiled.';
	$result['data'] = array(
		'rows_sampled' => $row_count,
		'columns' => $profiles
	);

	return $result;
}