Is it possible to make the search in Category Hierarchy boolean?
Hi David,
I'm using a helper2.php you created, based on my request to add individual products as well as categories in Category Hierarchy, plus order by merchant, product name then category. Here's that code:
$sql = "SELECT DISTINCT(".$field."),merchant FROM `".$config_databaseTablePrefix."products` WHERE ".$field." LIKE '%".database_safe($q)."%' ORDER BY merchant,".$field;
database_querySelect($sql,$rows);
foreach($rows as $row)
{
print "<option value='".htmlspecialchars($row[$field],ENT_QUOTES,$config_charset)."'>".htmlspecialchars($row["merchant"],ENT_QUOTES,$config_charset)." - ".htmlspecialchars($row[$field],ENT_QUOTES,$config_charset)."</option>";
}
$sql = "SELECT DISTINCT(category),name,merchant FROM `".$config_databaseTablePrefix."products` WHERE name LIKE '%".database_safe($q)."%' ORDER BY merchant,name";
database_querySelect($sql,$rows);
foreach($rows as $row)
{
print "<option value='".htmlspecialchars($row["name"],ENT_QUOTES,$config_charset)."'>".htmlspecialchars($row["merchant"],ENT_QUOTES,$config_charset)." - ".htmlspecialchars($row["name"],ENT_QUOTES,$config_charset)." - ".htmlspecialchars($row[$field],ENT_QUOTES,$config_charset)."</option>";
}Cheers,
Retro135
Hi,
Sure - first (if not applied already from other mods), create a FULLTEXT index on the `category` field with the following dbmod.php script;
<?php
set_time_limit(0);
require("includes/common.php");
$sql = "CREATE FULLTEXT INDEX category_2
ON `".$config_databaseTablePrefix."products` (category)";
database_queryModify($sql,$result);
print "Done.";
?>
Then have a go with;
$sql = "SELECT DISTINCT(".$field."),merchant FROM `".$config_databaseTablePrefix."products` WHERE MATCH ".$field." AGAINST ('".database_safe($q)."' IN BOOLEAN MODE) ORDER BY merchant,".$field;
database_querySelect($sql,$rows);
foreach($rows as $row)
{
print "<option value='".htmlspecialchars($row[$field],ENT_QUOTES,$config_charset)."'>".htmlspecialchars($row["merchant"],ENT_QUOTES,$config_charset)." - ".htmlspecialchars($row[$field],ENT_QUOTES,$config_charset)."</option>";
}
$sql = "SELECT DISTINCT(category),name,merchant FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($q)."' IN BOOLEAN MODE) ORDER BY merchant,name";
database_querySelect($sql,$rows);
foreach($rows as $row)
{
print "<option value='".htmlspecialchars($row["name"],ENT_QUOTES,$config_charset)."'>".htmlspecialchars($row["merchant"],ENT_QUOTES,$config_charset)." - ".htmlspecialchars($row["name"],ENT_QUOTES,$config_charset)." - ".htmlspecialchars($row[$field],ENT_QUOTES,$config_charset)."</option>";
}You can then use full MySQL boolean mode fulltet queries in the search box;
http://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi,
Sure, first you would need to add a FULLTEXT index to the `category` field. To do this, run the following dbmod.php script in the top level folder of your installation, and then delete the file:
<?phpset_time_limit(0);
require("includes/common.php");
$sql = "CREATE FULLTEXT INDEX category_2
ON `".$config_databaseTablePrefix."products` (category)";
database_queryModify($sql,$result);
print "Done.";
?>
Then edit helper.php (a single script that provides the search functionality for all mapping features) and look for the following code at line 22:
$sql = "SELECT DISTINCT(".$field.") FROM `".$config_databaseTablePrefix."products` WHERE ".$field." LIKE '%".database_safe($q)."%' ORDER BY ".$field." LIMIT 6";...and REPLACE with:
if ($field=="category"){
$sql = "SELECT DISTINCT(".$field.") FROM `".$config_databaseTablePrefix."products` WHERE MATCH category AGAINST ('%".database_safe($q)."%' IN BOOLEAN MODE) ORDER BY ".$field." LIMIT 6";
}
else
{
$sql = "SELECT DISTINCT(".$field.") FROM `".$config_databaseTablePrefix."products` WHERE ".$field." LIKE '%".database_safe($q)."%' ORDER BY ".$field." LIMIT 6";
}
You'll then be able to use all the supported operators of a MySQL FULLTEXT boolean mode search on the configuration page for a Category Hierarchy Mapping entry;
https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html
Cheers,
David.
--
PriceTapestry.com