首页 > 代码库 > 查询的问题
查询的问题
//在classes文件中的search.php文件里
public static function indexation($full = false, $id_product = false)
{
$db = Db::getInstance();
$dropIndex = false;
if ($id_product)
$full = false;
if ($full)
{
$db->Execute(‘TRUNCATE ‘._DB_PREFIX_.‘search_index‘);
$db->Execute(‘TRUNCATE ‘._DB_PREFIX_.‘search_word‘);
$db->Execute(‘UPDATE ‘._DB_PREFIX_.‘product SET indexed = 0‘);
$dropIndex = true;
}
else
{
// Do it even if you already know the product id on order to be sure that it exists
$products = $db->ExecuteS(‘
SELECT id_product
FROM ‘._DB_PREFIX_.‘product
WHERE ‘.($id_product ? ‘id_product = ‘.(int)$id_product : ‘indexed = 0‘));
$ids = array();
if ($products)
foreach($products AS $product)
$ids[] = (int)$product[‘id_product‘];
if (sizeof($ids))
$db->Execute(‘DELETE FROM ‘._DB_PREFIX_.‘search_index WHERE id_product IN (‘.implode(‘,‘, $ids).‘)‘);
if (count($products) > 2000)
$dropIndex = true;
}
if ($dropIndex)
{
$dropIndex = false;
$result = $db->ExecuteS(‘SHOW INDEX FROM `‘._DB_PREFIX_.‘search_index`‘);
foreach ($result as $row)
if (strtolower($row[‘Key_name‘]) == ‘primary‘)
$dropIndex = true;
if ($dropIndex)
$db->Execute(‘ALTER TABLE ‘._DB_PREFIX_.‘search_index DROP PRIMARY KEY‘);
$dropIndex = true;
}
//var_dump($db);exit;
// Every fields are weighted according to the configuration in the backend
$weightArray = array(
‘pname‘ => Configuration::get(‘PS_SEARCH_WEIGHT_PNAME‘),
‘reference‘ => Configuration::get(‘PS_SEARCH_WEIGHT_REF‘),
‘ean13‘ => Configuration::get(‘PS_SEARCH_WEIGHT_REF‘),
‘upc‘ => Configuration::get(‘PS_SEARCH_WEIGHT_REF‘),
‘description_short‘ => Configuration::get(‘PS_SEARCH_WEIGHT_SHORTDESC‘),
‘description‘ => Configuration::get(‘PS_SEARCH_WEIGHT_DESC‘),
‘cname‘ => Configuration::get(‘PS_SEARCH_WEIGHT_CNAME‘),
‘mname‘ => Configuration::get(‘PS_SEARCH_WEIGHT_MNAME‘),
‘tags‘ => Configuration::get(‘PS_SEARCH_WEIGHT_TAG‘),
‘attributes‘ => Configuration::get(‘PS_SEARCH_WEIGHT_ATTRIBUTE‘),
‘features‘ => Configuration::get(‘PS_SEARCH_WEIGHT_FEATURE‘)
);
// All the product not yet indexed are retrieved
$products = $db->ExecuteS(‘
SELECT p.id_product, pl.id_lang, pl.name pname, p.reference, p.ean13, p.upc, pl.description_short, pl.description, cl.name cname, m.name mname
FROM ‘._DB_PREFIX_.‘product p
LEFT JOIN ‘._DB_PREFIX_.‘product_lang pl ON p.id_product = pl.id_product
LEFT JOIN ‘._DB_PREFIX_.‘category_lang cl ON (cl.id_category = p.id_category_default AND pl.id_lang = cl.id_lang)
LEFT JOIN ‘._DB_PREFIX_.‘manufacturer m ON m.id_manufacturer = p.id_manufacturer
WHERE p.indexed = 0‘, false);
//var_dump($products);exit;
// Those are kind of global variables required to save the processed data in the database every X occurences, in order to avoid overloading MySQL
$countWords = 0;
$countProducts = 0;
$queryArray3 = array();
$productsArray = array();
// Every indexed words are cached into a PHP array
$wordIdsByWord = array();
$wordIds = Db::getInstance()->ExecuteS(‘
SELECT sw.id_word, sw.word, id_lang
FROM ‘._DB_PREFIX_.‘search_word sw‘, false);
$wordIdsByWord = array();
while ($wordId = $db->nextRow($wordIds))
{
if (!isset($wordIdsByWord[$wordId[‘id_lang‘]]))
$wordIdsByWord[$wordId[‘id_lang‘]] = array();
$wordIdsByWord[$wordId[‘id_lang‘]][‘_‘.$wordId[‘word‘]] = (int)$wordId[‘id_word‘];
}
// Now each non-indexed product is processed one by one, langage by langage
while ($product = $db->nextRow($products))
{
$product[‘tags‘] = Search::getTags($db, (int)$product[‘id_product‘], (int)$product[‘id_lang‘]);
$product[‘attributes‘] = Search::getAttributes($db, (int)$product[‘id_product‘], (int)$product[‘id_lang‘]);
$product[‘features‘] = Search::getFeatures($db, (int)$product[‘id_product‘], (int)$product[‘id_lang‘]);
// Data must be cleaned of html, bad characters, spaces and anything, then if the resulting words are long enough, they‘re added to the array
$pArray = array();
//var_dump($product);exit;
foreach ($product AS $key => $value)
if (strncmp($key, ‘id_‘, 2))
{
/********************************************/
//var_dump($value);exit;
//$words = explode(‘ ‘,strtolower($value));
$words = explode(‘ ‘, Search::sanitize($value, (int)$product[‘id_lang‘], true));
//var_dump($words);exit;
/*********************************************/
foreach ($words AS $word)
if (!empty($word))
{
$word = Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH);
if (!isset($pArray[$word]))
$pArray[$word] = 0;
$pArray[$word] += $weightArray[$key];
}
}
// If we find words that need to be indexed, they‘re added to the word table in the database
if (sizeof($pArray))
{
$list = ‘‘;
foreach ($pArray AS $word => $weight)
$list .= ‘\‘‘.$word.‘\‘,‘;
$list = rtrim($list, ‘,‘);
$queryArray = array();
$queryArray2 = array();
foreach ($pArray AS $word => $weight)
if ($weight AND !isset($wordIdsByWord[‘_‘.$word]))
{
$queryArray[] = ‘(‘.(int)$product[‘id_lang‘].‘,\‘‘.pSQL($word).‘\‘)‘;
$queryArray2[] = ‘\‘‘.pSQL($word).‘\‘‘;
$wordIdsByWord[$product[‘id_lang‘]][‘_‘.$word] = 0;
}
if (count($queryArray))
{
// The words are inserted...
$db->Execute(‘
INSERT IGNORE INTO ‘._DB_PREFIX_.‘search_word (id_lang, word)
VALUES ‘.implode(‘,‘,$queryArray));
$sql = ‘INSERT IGNORE INTO ‘._DB_PREFIX_.‘search_word (id_lang, word)
VALUES ‘.implode(‘,‘,$queryArray);
// ...then their IDs are retrieved and added to the cache
$addedWords = $db->ExecuteS(‘
SELECT sw.id_word, sw.word
FROM ‘._DB_PREFIX_.‘search_word sw
WHERE sw.word IN (‘.implode(‘,‘, $queryArray2).‘)
AND sw.id_lang = ‘.(int)$product[‘id_lang‘].‘
LIMIT ‘.count($queryArray2));
foreach ($addedWords AS $wordId)
$wordIdsByWord[$product[‘id_lang‘]][‘_‘.$wordId[‘word‘]] = (int)$wordId[‘id_word‘];
}
}
foreach ($pArray AS $word => $weight)
{
if (!$weight)
continue;
if (!isset($wordIdsByWord[$product[‘id_lang‘]][‘_‘.$word]))
continue;
if (!$wordIdsByWord[$product[‘id_lang‘]][‘_‘.$word])
continue;
$queryArray3[] = ‘(‘.(int)$product[‘id_product‘].‘,‘.(int)$wordIdsByWord[$product[‘id_lang‘]][‘_‘.$word].‘,‘.(int)$weight.‘)‘;
// Force save every 200 words in order to avoid overloading MySQL
if (++$countWords % 200 == 0)
Search::saveIndex($queryArray3);
}
if (!in_array($product[‘id_product‘], $productsArray))
$productsArray[] = (int)$product[‘id_product‘];
// Force save every 50 products in order to avoid overloading MySQL
if (++$countProducts % 50 == 0)
Search::setProductsAsIndexed($productsArray);
}
// One last save is done at the end in order to save what‘s left
Search::saveIndex($queryArray3);
Search::setProductsAsIndexed($productsArray);
// If it has been deleted, the index is created again once the indexation is done
if (!$dropIndex)
{
$dropIndex = true;
$result = $db->ExecuteS(‘SHOW INDEX FROM `‘._DB_PREFIX_.‘search_index`‘);
foreach ($result as $row)
if (strtolower($row[‘Key_name‘]) == ‘primary‘)
$dropIndex = false;
}
if ($dropIndex)
$db->Execute(‘ALTER TABLE `‘._DB_PREFIX_.‘search_index` ADD PRIMARY KEY (`id_word`, `id_product`)‘);
Configuration::updateValue(‘PS_NEED_REBUILD_INDEX‘, 0);
return true;
}
search入库的部分在此方法中，如果，$words数据时空的话查看下面的方法
public static function sanitize($string, $id_lang, $indexation = false)
{
$string = Tools::strtolower(strip_tags($string));
$string = html_entity_decode($string, ENT_NOQUOTES, ‘utf-8‘);
$string = preg_replace(‘/([‘.PREG_CLASS_NUMBERS.‘]+)[‘.PREG_CLASS_PUNCTUATION.‘]+(?=[‘.PREG_CLASS_NUMBERS.‘])/u‘, ‘\1‘, $string);
/************2014-09-25***************/
//$string = preg_replace(‘/[‘.PREG_CLASS_SEARCH_EXCLUDE.‘]+/u‘,‘ ‘, $string);
$string = str_replace(‘,‘, ‘‘, $string);
$string = str_replace(‘.‘, ‘‘, $string);
$string = str_replace(‘/>‘, ‘‘, $string);
$string = str_replace(‘:‘, ‘ ‘, $string);
//var_dump($string);exit;
/****************end*********************/
if ($indexation)
$string = preg_replace(‘/[._-]+/‘, ‘‘, $string);
else
{
$string = preg_replace(‘/[._]+/‘, ‘‘, $string);
$string = ltrim(preg_replace(‘/([^ ])-/‘, ‘$1‘, ‘ ‘.$string));
$string = preg_replace(‘/[._]+/‘, ‘‘, $string);
$string = preg_replace(‘/[^\s]-+/‘, ‘‘, $string);
}
$blacklist = Configuration::get(‘PS_SEARCH_BLACKLIST‘, (int)$id_lang);
if (!empty($blacklist))
{
$string = preg_replace(‘/(?<=\s)(‘.$blacklist.‘)(?=\s)/Su‘, ‘‘, $string);
$string = preg_replace(‘/^(‘.$blacklist.‘)(?=\s)/Su‘, ‘‘, $string);
$string = preg_replace(‘/(?<=\s)(‘.$blacklist.‘)$/Su‘, ‘‘, $string);
$string = preg_replace(‘/^(‘.$blacklist.‘)$/Su‘, ‘‘, $string);
}
if (!$indexation)
{
$alias = new Alias(NULL, $string);
if (Validate::isLoadedObject($alias))
$string = $alias->search;
}
if ($indexation)
{
$minWordLen = (int)Configuration::get(‘PS_SEARCH_MINWORDLEN‘);
if ($minWordLen > 1)
{
$minWordLen -= 1;
$string = preg_replace(‘/(?<=\s)[^\s]{1,‘.$minWordLen.‘}(?=\s)/Su‘, ‘ ‘, $string);
$string = preg_replace(‘/^[^\s]{1,‘.$minWordLen.‘}(?=\s)/Su‘, ‘‘, $string);
$string = preg_replace(‘/(?<=\s)[^\s]{1,‘.$minWordLen.‘}$/Su‘, ‘‘, $string);
$string = preg_replace(‘/^[^\s]{1,‘.$minWordLen.‘}$/Su‘, ‘‘, $string);
}
}
$string = trim(preg_replace(‘/\s+/‘, ‘ ‘, $string));
return $string;
}
查询的问题
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。