Click Log: Custom Database Table

Lesson 19 of 25 5 min read
  • JNext lesson
  • KPrevious lesson
  • FSearch lessons
  • EscClear search

Log every redirect click to a dedicated database table within your WordPress installation. This self-contained click tracking solution requires no external analytics service. It captures referrer, user agent, hashed IP (for privacy), country, and timestamp for every click.

Requirements

  • GT Link Manager 1.5+ installed and active
  • No external services needed — everything runs locally in your WordPress database

Installation

  • Add the snippet below to your theme’s functions.php or a site-specific plugin
  • The database table (wp_gtlm_click_log) is created automatically on first admin page load via dbDelta
  • Pair with the Click Stats Dashboard Widget snippet for an admin dashboard view of your data

The Code

<?php
/**
 * GT Link Manager - Click Log (Custom Database Table)
 *
 * Logs every redirect click to a dedicated database table. Captures referrer,
 * user agent, IP (hashed for privacy), country, and timestamp.
 *
 * This is a self-contained click log — no external service needed.
 * Pair with click-stats-dashboard.php for an admin dashboard widget.
 *
 * The table is created automatically on first use (via dbDelta).
 *
 * Schema: wp_gtlm_click_log
 *   id, link_id, slug, target_url, referrer, user_agent, ip_hash,
 *   country, status_code, created_at
 */

defined( 'ABSPATH' ) || exit;

// ── Configuration ────────────────────────────────────────────────────────────
// Set to true to store a hashed (anonymized) IP. Set to false to skip IP entirely.
define( 'GTLM_CLICKLOG_STORE_IP', true );

// Maximum number of rows to keep. Set to 0 for unlimited.
// When exceeded, oldest rows are purged via a daily cron.
define( 'GTLM_CLICKLOG_MAX_ROWS', 100000 );
// ─────────────────────────────────────────────────────────────────────────────

/**
 * Create the click log table on admin init (runs once via dbDelta).
 */
add_action( 'admin_init', 'gtlm_clicklog_maybe_create_table' );

function gtlm_clicklog_maybe_create_table(): void {

	$installed_version = get_option( 'gtlm_clicklog_db_version', '0' );
	$current_version   = '1.0';

	if ( version_compare( $installed_version, $current_version, '>=' ) ) {
		return;
	}

	global $wpdb;
	$table   = $wpdb->prefix . 'gtlm_click_log';
	$charset = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE {$table} (
		id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		link_id BIGINT(20) UNSIGNED NOT NULL,
		slug VARCHAR(255) NOT NULL DEFAULT '',
		target_url TEXT NOT NULL,
		referrer TEXT NOT NULL,
		user_agent VARCHAR(500) NOT NULL DEFAULT '',
		ip_hash VARCHAR(64) NOT NULL DEFAULT '',
		country VARCHAR(2) NOT NULL DEFAULT '',
		status_code SMALLINT(3) NOT NULL DEFAULT 301,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY  (id),
		KEY link_id (link_id),
		KEY created_at (created_at),
		KEY slug (slug(191))
	) {$charset};";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql );

	update_option( 'gtlm_clicklog_db_version', $current_version );
}

/**
 * Log a click when a redirect fires.
 */
add_action( 'gtlm_before_redirect', 'gtlm_clicklog_record', 5, 4 );

function gtlm_clicklog_record( array $link, string $target_url, int $status, array $headers ): void {
	global $wpdb;

	$table = $wpdb->prefix . 'gtlm_click_log';

	$ip_hash = '';
	if ( GTLM_CLICKLOG_STORE_IP ) {
		$raw_ip  = gtlm_clicklog_get_client_ip() ?? '';
		// Hash with a daily-rotating salt so IPs can't be reversed but same-day dedup still works.
		$ip_hash = $raw_ip ? hash( 'sha256', $raw_ip . gmdate( 'Y-m-d' ) . wp_salt( 'auth' ) ) : '';
	}

	// Try to detect country from CloudFlare or server headers.
	$country = '';
	if ( ! empty( $_SERVER['HTTP_CF_IPCOUNTRY'] ) ) {
		$country = strtoupper( substr( sanitize_text_field( $_SERVER['HTTP_CF_IPCOUNTRY'] ), 0, 2 ) );
	}

	$wpdb->insert(
		$table,
		[
			'link_id'     => (int) $link['id'],
			'slug'        => $link['slug'] ?? '',
			'target_url'  => $target_url,
			'referrer'    => sanitize_text_field( $_SERVER['HTTP_REFERER'] ?? '' ),
			'user_agent'  => mb_substr( sanitize_text_field( $_SERVER['HTTP_USER_AGENT'] ?? '' ), 0, 500 ),
			'ip_hash'     => $ip_hash,
			'country'     => $country,
			'status_code' => $status,
			'created_at'  => current_time( 'mysql', true ),
		],
		[ '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s' ]
	);
}

/**
 * Schedule daily cleanup cron.
 */
add_action( 'init', function () {
	if ( GTLM_CLICKLOG_MAX_ROWS > 0 && ! wp_next_scheduled( 'gtlm_clicklog_cleanup' ) ) {
		wp_schedule_event( time(), 'daily', 'gtlm_clicklog_cleanup' );
	}
} );

add_action( 'gtlm_clicklog_cleanup', 'gtlm_clicklog_purge_old_rows' );

function gtlm_clicklog_purge_old_rows(): void {
	global $wpdb;

	if ( GTLM_CLICKLOG_MAX_ROWS <= 0 ) {
		return;
	}

	$table = $wpdb->prefix . 'gtlm_click_log';
	$count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

	if ( $count > GTLM_CLICKLOG_MAX_ROWS ) {
		$excess = $count - GTLM_CLICKLOG_MAX_ROWS;
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$table} ORDER BY id ASC LIMIT %d", $excess ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}
}

/**
 * Get the real client IP.
 *
 * @return string|null
 */
function gtlm_clicklog_get_client_ip(): ?string {

	$headers = [ 'HTTP_CF_CONNECTING_IP', 'HTTP_X_FORWARDED_FOR', 'HTTP_X_REAL_IP', 'REMOTE_ADDR' ];

	foreach ( $headers as $header ) {
		if ( ! empty( $_SERVER[ $header ] ) ) {
			$ip = strtok( sanitize_text_field( $_SERVER[ $header ] ), ',' );
			if ( filter_var( trim( $ip ), FILTER_VALIDATE_IP ) ) {
				return trim( $ip );
			}
		}
	}

	return null;
}

How It Works

  • Creates a wp_gtlm_click_log table automatically using WordPress’s dbDelta on first run
  • Hooks into gtlm_before_redirect at priority 5 (runs before analytics hooks) to log every click
  • IPs are hashed with a daily-rotating salt (SHA-256 + wp_salt + date) — they cannot be reversed but same-day deduplication still works
  • Detects country code from CloudFlare’s CF-IPCountry header when available
  • Includes a daily cron job that purges old rows when the table exceeds GTLM_CLICKLOG_MAX_ROWS (default: 100,000)

Configuration Notes

Two constants control behavior:

  • GTLM_CLICKLOG_STORE_IP — Set to true (default) to store hashed IPs, or false to skip IP storage entirely for maximum privacy
  • GTLM_CLICKLOG_MAX_ROWS — Maximum rows to keep (default: 100,000). Set to 0 for unlimited. Oldest rows are purged daily via WP-Cron

The table schema includes indexes on link_id, created_at, and slug for fast queries. This is the foundation for the Click Stats Dashboard Widget snippet.