How to add a custom table in WordPress to collect search results

wp-search-custom-db.php

<?php

/*
HOW TO USE:
1. Modify the table and logging function if needed. You probably want to remove "groups" which for my case, represented 
   which group your user account belonged to. We needed to be able to isolate search requests based on group.
2. After your search term has been performed, log the search using rad_log_search_term().
3. Build some fancy dashboard to show the data, or be a nerd and do your own mysql queries every time.
4. IMPORTANT: The activation hook for "rad_activate_plugin" MUST go in your plugin main directory. If the plugin is active
   when you add this code, just call rad_activate_plugin() manually once, refresh a page, then remove the manual call.
*/

// On plugin activation, set up the database
function rad_activate_plugin() {
	// If you move rad_setup_database to another file (recommended) do:
	// include_once( dirname(__FILE__) . '/includes/setup-database.php' );
	rad_setup_database();
}
register_activation_hook( __FILE__, 'rad_activate_plugin' );


// Set up the database
function rad_setup_database() {
	// If you update the mysql code below, reactivate the plugin or call this function manually to make the changes.
	$db_version = '1.0.2';
	
	// Check the database version to see if we need to update.
	if ( get_option( 'rad_search_db_version' ) == $db_version ) return;
	
	// Prepare WPDB and dbDelta
	global $wpdb;
	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
	$charset_collate = $wpdb->get_charset_collate();
	
	// Create a new table: rad_keyword_searches
	// Which will be prefixed like: wp_rad_keyword_searches
	$table_name = $wpdb->prefix . 'rad_keyword_searches';
	
	$sql = <<<MYSQL
CREATE TABLE $table_name (
  keyword_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  search_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  keywords VARCHAR(255) NOT NULL DEFAULT '',
  category VARCHAR(255) NOT NULL DEFAULT '',
  group VARCHAR(255) NOT NULL DEFAULT '',
  user_id INT(8) UNSIGNED NOT NULL DEFAULT 0,
  result_count INT(8) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (keyword_id),
  KEY search_date (search_date),
  KEY keywords (keywords),
  KEY user_id (user_id),
  KEY result_count (result_count)
) $charset_collate;
MYSQL;
	
	// Add or update the table (this is magic!)
	dbDelta( $sql );
	
	// Save the "installed" version of the database
	update_option( 'rad_search_db_version', $db_version );
}

/**
 * Takes a search term and number of results that were found for it. Stores the value in the database with a timestamp.
 * Also includes Category and Groups which were filters I used, and the User ID who performed the search.
 *
 * @param $search
 * @param $category
 * @param $user_id
 * @param $groups
 * @param $result_count
 */
function rad_log_search_term( $search, $category, $user_id, $groups, $result_count ) {
	global $wpdb;
	
	// Don't log empty searches or groups
	if ( !$search || !$groups ) return;
	
	// Don't log short keywords
	if ( strlen($search) < 3 ) return;
	
	// Groups should be an array, repeat for each group to track multiple entries. Most of the time this should only have one group anyway.
	if ( $groups && is_array( $groups ) ) {
		foreach( $groups as $g ) {
			rad_log_search_term( $search, $category, $user_id, $g, $result_count );
		}
		return;
	}
	
	// Log a single group
	if ( $groups ) {
		$wpdb->insert(
			"{$wpdb->prefix}rad_keyword_searches",
			array(
				'search_date' => date('Y-m-d H:i:s'),
				'keywords' => substr( (string) $search, 0, 254 ),
				'category' => substr( (string) $category, 0, 254 ),
				'group' => substr( (string) $groups, 0, 254 ),
				'user_id' => absint($user_id),
				'result_count' => absint($result_count)
			),
			array(
				'%s',
				'%s',
				'%s',
				'%s',
				'%d',
				'%d'
			)
		);
	}
}