From b93a38a2f78121cb1ba5a4014cbe396824bca822 Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Mon, 29 Oct 2018 13:01:12 -0400 Subject: [PATCH] Add fulltext search to member_name Redoing wmta search for members. --- index.php | 2 +- lib/GlmDataAbstract/DataAbstract.php | 30 ++++--- models/front/members/list.php | 90 +++++++++++++------ ...1.1.41.sql => create_database_V1.1.42.sql} | 3 +- setup/databaseScripts/dbVersions.php | 1 + ..._V1.1.40.sql => drop_database_V1.1.42.sql} | 0 .../update_database_V1.1.42.sql | 11 +++ 7 files changed, 93 insertions(+), 44 deletions(-) rename setup/databaseScripts/{create_database_V1.1.41.sql => create_database_V1.1.42.sql} (99%) rename setup/databaseScripts/{drop_database_V1.1.40.sql => drop_database_V1.1.42.sql} (100%) create mode 100755 setup/databaseScripts/update_database_V1.1.42.sql diff --git a/index.php b/index.php index e59ba894..a13eb279 100755 --- a/index.php +++ b/index.php @@ -48,7 +48,7 @@ if (!defined('ABSPATH')) { */ define('GLM_MEMBERS_PLUGIN_VERSION', '2.10.45'); -define('GLM_MEMBERS_PLUGIN_DB_VERSION', '1.1.41'); +define('GLM_MEMBERS_PLUGIN_DB_VERSION', '1.1.42'); // Check if plugin version is not current in WordPress option and if needed updated it if (GLM_MEMBERS_PLUGIN_VERSION != get_option('glmMembersDatabasePluginVersion')) { diff --git a/lib/GlmDataAbstract/DataAbstract.php b/lib/GlmDataAbstract/DataAbstract.php index 31fda89c..650c9a71 100755 --- a/lib/GlmDataAbstract/DataAbstract.php +++ b/lib/GlmDataAbstract/DataAbstract.php @@ -3208,27 +3208,28 @@ $forEdit = true; * after the query takes place. Calling code will have to sort the results to * sort by those target values. * - * @param string $where Optional WHERE clause for selection of entries. Defaults to all. - * @param string $order Optional ORDER BY clause for sorting of results. + * @param string $where Optional WHERE clause for selection of entries. Defaults to all. + * @param string $order Optional ORDER BY clause for sorting of results. * - * Set to "pseudo-random" for Pseudo-Random sorting that uses a session parameter - * to store a custom seed value so all results are in the same randomized order - * for some period of time. See genPseudoRandIdArray() and pseudoRandDataSort(). + * Set to "pseudo-random" for Pseudo-Random sorting that uses a session parameter + * to store a custom seed value so all results are in the same randomized order + * for some period of time. See genPseudoRandIdArray() and pseudoRandDataSort(). * - * Set to "random" to do a simple random sort where that order is not repeated. + * Set to "random" to do a simple random sort where that order is not repeated. * - * @param boolean $fieldVals Optional flag requesting fields contain array of all possible values. - * @param string $idField Optional name of ID field (primary index) to use for result array keys. - * @param integer $start Optional start of list index - * @param integer $limit Optional list length limit + * @param boolean $fieldVals Optional flag requesting fields contain array of all possible values. + * @param string $idField Optional name of ID field (primary index) to use for result array keys. + * @param integer $start Optional start of list index + * @param integer $limit Optional list length limit * @param boolean $prohibitListOptions Prohibit the generation of all options in list or pointer fields - * Can only be set - if false will not turn this option off if it's already been seet. + * Can only be set - if false will not turn this option off if it's already been set. + * @param string $appendSelect Extra select statement to be appended to $this->select. Distance or match for ordering. * * * @return array Array containing results * @access public */ - public function getList($where = '', $order = '', $fieldVals = true, $idField = 'id', $start = false, $limit = false, $prohibitListOptions = false) + public function getList( $where = '', $order = '', $fieldVals = true, $idField = 'id', $start = false, $limit = false, $prohibitListOptions = false, $appendSelect = null ) { $seed = false; @@ -3286,11 +3287,14 @@ $forEdit = true; } + if ( $appendSelect ) { + $this->select .= ', ' . $appendSelect; + } + $sql = "SELECT $this->select FROM $this->table T WHERE true "; - if (trim($where != '')) { $sql .= "AND $where "; diff --git a/models/front/members/list.php b/models/front/members/list.php index facc3b0e..566583e3 100755 --- a/models/front/members/list.php +++ b/models/front/members/list.php @@ -187,6 +187,8 @@ class GlmMembersFront_members_list extends GlmDataMemberInfo $citySearchSelected = false; $haveFeaturedMembers = false; $featuredMembers = false; + $memberSearch = false; + $appendSelect = ''; // Paging Parameters $paging = $this->config['settings']['list_pagination']; // Now this is in management @@ -201,6 +203,7 @@ class GlmMembersFront_members_list extends GlmDataMemberInfo if (isset($_REQUEST['categorySearchMultiple']) && is_array($_REQUEST['categorySearchMultiple'])) { $actionData['request']['category'] = implode(',', $_REQUEST['categorySearchMultiple']); $multiSelectCats = true; + $memberSearch = true; } // Check for URL parameter override of shortcode parameters @@ -209,12 +212,15 @@ class GlmMembersFront_members_list extends GlmDataMemberInfo } if (isset($_REQUEST['memberType'])) { $actionData['request']['type'] = $_REQUEST['memberType']; + $memberSearch = true; } if (isset($_REQUEST['categorySearch'])) { $actionData['request']['category-search'] = $_REQUEST['categorySearch']; + $memberSearch = true; } if (isset($_REQUEST['textSearch'])) { $actionData['request']['text-search'] = $_REQUEST['textSearch']; + $memberSearch = true; } if (isset($_REQUEST['alpha'])) { $actionData['request']['alpha'] = $_REQUEST['alpha']; @@ -295,21 +301,27 @@ class GlmMembersFront_members_list extends GlmDataMemberInfo // Apply any provided text search to name, description, short description, and street address if (trim($actionData['request']['text-search']) != '') { - $textSearch = addslashes(filter_var($actionData['request']['text-search'], FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES)); + $textSearch = addslashes(stripslashes(filter_var($actionData['request']['text-search'], FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES))); $where .= $whereSep." ( - ( - SELECT true - FROM ".GLM_MEMBERS_PLUGIN_DB_PREFIX."members - WHERE id = T.member - AND name LIKE '%$textSearch%' - ) OR - T.descr LIKE '%$textSearch%' OR - T.short_descr LIKE '%$textSearch%' OR - T.addr1 LIKE '%$textSearch%' OR + ( match(member_name) against('$textSearch') ) + OR + ( T.descr SOUNDS LIKE '$textSearch' OR T.descr LIKE '%$textSearch%' ) + OR + T.short_descr LIKE '%$textSearch%' + OR + T.addr1 LIKE '%$textSearch%' + OR T.addr2 LIKE '%$textSearch%' )"; + // City part (on hold) + // OR T.city IN ( + // SELECT id FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "cities + // WHERE name SOUNDS LIKE '%$textSearch%' OR name LIKE '%$textSearch%' + // ) + $appendSelect = " match(member_name) against('$textSearch') as rel_name "; $whereSep = ' AND '; $textSearch = stripslashes($textSearch); + $memberSearch = true; } // Get regions for possible use in search pick list for regions used in active member info records. @@ -865,26 +877,46 @@ class GlmMembersFront_members_list extends GlmDataMemberInfo // Get stats for number of members found matching current selection criteria (includes alpha selection) $filteredMembersFound = $this->getStats(str_replace('T.', '', $where.$alphaWhere)); - // Get member list sorted as specified - switch ($this->config['settings']['list_order_list']) { - - // Pseudo-Random list order - case $this->config['sort_order_numb']['Pseudo-Random']: - ${$resultParam} = $this->getList($where.$alphaWhere, 'pseudo-random', true, 'id', $start, $limit); - break; - - // Random list order - case $this->config['sort_order_numb']['Random']: - ${$resultParam} = $this->getList($where.$alphaWhere, 'random', true, 'id', $start, $limit); - break; - - // Default is alpha-numeric list order - default: - case $this->config['sort_order_numb']['Alpha-Numeric']: - $sortOrder = apply_filters( 'glm-member-db-front-search-query-orderby', 'member_name' ); - ${$resultParam} = $this->getList($where.$alphaWhere, $sortOrder, true, 'id', $start, $limit); - break; + if ( $memberSearch ) { + // echo '
$memberSearch: ' . print_r( $memberSearch, true ) . '
'; + if ( $appendSelect ) { + $order = " rel_name DESC, member_name ASC "; + } else { + $order = " member_name ASC "; + } + // echo '
$order: ' . print_r( $order, true ) . '
'; + ${$resultParam} = $this->getList( + $where.$alphaWhere, // where + $order, // order + true, // fieldVals + 'id', // idField + $start, // start + $limit, // limit + '', // prohibitListOptions + $appendSelect + ); + } else { + // Get member list sorted as specified + switch ($this->config['settings']['list_order_list']) { + + // Pseudo-Random list order + case $this->config['sort_order_numb']['Pseudo-Random']: + ${$resultParam} = $this->getList($where.$alphaWhere, 'pseudo-random', true, 'id', $start, $limit); + break; + + // Random list order + case $this->config['sort_order_numb']['Random']: + ${$resultParam} = $this->getList($where.$alphaWhere, 'random', true, 'id', $start, $limit); + break; + + // Default is alpha-numeric list order + default: + case $this->config['sort_order_numb']['Alpha-Numeric']: + $sortOrder = apply_filters( 'glm-member-db-front-search-query-orderby', 'member_name' ); + ${$resultParam} = $this->getList($where.$alphaWhere, $sortOrder, true, 'id', $start, $limit); + break; + } } } diff --git a/setup/databaseScripts/create_database_V1.1.41.sql b/setup/databaseScripts/create_database_V1.1.42.sql similarity index 99% rename from setup/databaseScripts/create_database_V1.1.41.sql rename to setup/databaseScripts/create_database_V1.1.42.sql index 5134b6e9..847cf026 100755 --- a/setup/databaseScripts/create_database_V1.1.41.sql +++ b/setup/databaseScripts/create_database_V1.1.42.sql @@ -240,7 +240,8 @@ CREATE TABLE {prefix}member_info ( INDEX(lat), INDEX(lon), INDEX(region), - INDEX(county) + INDEX(county), + FULLTEXT KEY member_name (member_name) ); ---- diff --git a/setup/databaseScripts/dbVersions.php b/setup/databaseScripts/dbVersions.php index 0882b86e..745e8f87 100755 --- a/setup/databaseScripts/dbVersions.php +++ b/setup/databaseScripts/dbVersions.php @@ -73,6 +73,7 @@ $glmMembersDbVersions = array( '1.1.39' => array('version' => '1.1.39', 'tables' => 23, 'date' => '09/11/18'), '1.1.40' => array('version' => '1.1.40', 'tables' => 23, 'date' => '09/25/18'), '1.1.41' => array('version' => '1.1.41', 'tables' => 23, 'date' => '10/12/18'), + '1.1.42' => array('version' => '1.1.42', 'tables' => 23, 'date' => '10/29/18'), ); diff --git a/setup/databaseScripts/drop_database_V1.1.40.sql b/setup/databaseScripts/drop_database_V1.1.42.sql similarity index 100% rename from setup/databaseScripts/drop_database_V1.1.40.sql rename to setup/databaseScripts/drop_database_V1.1.42.sql diff --git a/setup/databaseScripts/update_database_V1.1.42.sql b/setup/databaseScripts/update_database_V1.1.42.sql new file mode 100755 index 00000000..769114a2 --- /dev/null +++ b/setup/databaseScripts/update_database_V1.1.42.sql @@ -0,0 +1,11 @@ +-- Gaslight Media Members Database +-- File Created: 2018-10-12 +-- Database Version: 1.1.41 +-- Database Update From Previous Version Script +-- +-- To permit each query below to be executed separately, +-- all queries must be separated by a line with four dashes + +-- Add fulltext index to member name in member_info table +CREATE FULLTEXT INDEX member_name ON {prefix}member_info(member_name); + -- 2.17.1