Matmazella
03.05.2006, 21:04
Delete search_stopwords from your searchtables
Now you want to remove the thousands of entries of the blacklisted searwords from your searchtables. And after that you should optmize the two tables.I have included an example script, that will do this. It is not pretty, but it works (for me). I have mySQL 3.23 and php 4.1, you might need to make modifications if you have a different setup.
After you have run the script, you should delete it from the server (although it should not be dangerous to run it twice).
That's it. Hopefully this works for you.
[syntax="php"]<?php
//***** reduce_my_searchtables_with_stopwords.php ****//
define('IN_PHPBB', true);
$phpbb_root_path = './';
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'common.'.$phpEx);
include($phpbb_root_path . 'includes/functions_search.'.$phpEx);
// Start session management
$userdata = session_pagestart($user_ip, PAGE_SEARCH);
init_userprefs($userdata);
// End session management
$stopwords_array = file($phpbb_root_path . 'language/lang_' . $board_config['default_lang'] . "/search_stopwords.txt");
$liste='';
foreach($stopwords_array as $curr_word)
{
$liste .= ( ( $liste != '' ) ? ', ' : '' ) ."'".trim($curr_word)."'";
}
$sql = "SELECT word_id
FROM " . SEARCH_WORD_TABLE . "
WHERE word_text IN ($liste)";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Could not obtain common word list', '', __LINE__, __FILE__, $sql);
}
$common_word_id = '';
while ( $row = $db->sql_fetchrow($result) )
{
$common_word_id .= ( ( $common_word_id != '' ) ? ', ' : '' ) . $row['word_id'];
}
if ($common_word_id=='') message_die(GENERAL_ERROR,'None of the words in the list are in your search_tables.<br>Note: This could also mean the list is empty');
//echo '>'.trim($curr_word)."<<br>";
//echo $liste .'<br>'. $common_word_id;
//exit;
$sql = "DELETE FROM " . SEARCH_WORD_TABLE . "
WHERE word_id IN ($common_word_id)";
if ( !$db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Could not delete word match entry', '', __LINE__, __FILE__, $sql);
}
$sql = "OPTIMIZE TABLE " . SEARCH_WORD_TABLE;
if ( !$db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Could not optimize', '', __LINE__, __FILE__, $sql);
}
$sql = "DELETE FROM " . SEARCH_MATCH_TABLE . "
WHERE word_id IN ($common_word_id)";
if ( !$db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Could not delete word match entry', '', __LINE__, __FILE__, $sql);
}
$sql = "OPTIMIZE TABLE " . SEARCH_MATCH_TABLE;
if ( !$db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Could not pütimize', '', __LINE__, __FILE__, $sql);
}
message_die(GENERAL_MESSAGE,'<b>Done!</b><br><br>The following list-entries have been removed from your searchtables:'.$liste);
//echo $liste .'<br>'. $common_word_id;
?>[/syntax]