hey,
I created this little script, not sure if others would appreciate it. I would like to make it a little better if others have some tips.
Basically on adding a category, you'll be presented with a list of possible matches which makes things easier than doing it manually.
categories_configure.php around 43
after:
if ($submit == "Save")
{
if (isset($_SERVER["PHP_AUTH_USER"])) if ($_SERVER["PHP_AUTH_USER"]=="demo") { header("Location: disabled.php"); exit(); }
if (!isset($_POST["alternates"]))
{
$_POST["alternates"] = "";
}
$sql = "UPDATE `".$config_databaseTablePrefix."categories` SET alternates = '".database_safe(widget_posted($_POST["alternates"]))."' WHERE id='".database_safe($id)."'";
database_queryModify($sql,$insertId);
header("Location: categories.php");
exit();
}add
$cat_display = str_replace(" and", "",$category["name"]);
//split categories
list($cat1, $cat2, $cat3, $cat4) = explode(" ", $cat_display);
$where = $cat1 . "%'";
if($cat2)
$where .= " AND category LIKE '%$cat2%'";
if($cat3)
$where .= " AND category LIKE '%$cat3%'";
if($cat4)
$where .= " AND category LIKE '%$cat4%'";
$sql = "SELECT DISTINCT category FROM `".$config_databaseTablePrefix."products` WHERE category LIKE '%$where";
database_querySelect($sql,$categories);then place this:
print "<h4> Possible category matches:</h3>";
foreach($categories as $cat)
{
print "".$cat["category"];
print "<br />";
}anywhere you want really, i placed mine after print "</form>";
My plan is to have a dropdown or something on the category configuration at the first step in order to select a category to map too.
So my next question is, any idea's on how to improve the matching? currently it works pretty well but it does tend to not always pick up all matches.
Cheers
Steve
Ah i had to rework the statement a little bit, but i'm glad you gave me the idea of the FULLTEXT index on the category field :) Much better matching possibilities.
$sql = "SELECT DISTINCT category AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH(category) AGAINST ('".database_safe($category["name"])."') ORDER BY relevance DESC";
database_querySelect($sql,$categories);That seems to work fine. Seems to me like it's performing the same as your above statement?
Thanks Aagain :)
Not sure if i should be making the double post of editing the original, but it seems to be repeating some of the category names. Any idea's why that would be?
Example:
{code saved}
Sometimes more than one instance is added. Of course it doesn't really matter that they're being duplicated, although it could be creating extra work for the db?
Hi Steve,
Have a go with the following;
$sql = "SELECT DISTINCT(category) AS category, MATCH category AGAINST ('".database_safe($category["name"])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH category AGAINST ('".database_safe($category["name"])."') ORDER BY relevance DESC";Cheers,
David.
--
PriceTapestry.com
Same results pretty much, not a problem though, my next step is to add in some jquery magic and be able to select these returned results and make category mapping a little more automated.
Cheers
Steve
Hi Dave,
Is there any way to get a full code to make the category mapping easier? Also in my site the product and category mapping doesnt work for some reason.
Using the same type of codes, can i make product mappings easier? and what has to be done to make the mapping work?
Thanks for your help.
Hello azizjon,
The update to Price Tapestry (more info) that will be released at the start of next week includes a new Product Mapping tool with integrated Product Finder that will make product mapping easier. The basically functionality that a search tool on the right-hand side of the page will enable you to search products and click to move them across the mapping.
I can easily incorporate a similar helper tool to Category Mapping so i'll look into including that also...watch out for an announcement at the start of next week and i'll make sure it's an easy upgrade patch if you have an established / reasonably modified site and don't want to re-install from scratch...
Cheers,
David.
--
PriceTapestry.com
Hi Steve,
The reason I suspect it's not always picking up all possible options is because of the AND requirement for each keyword in the master category. One thing you could do is to add a FULLTEXT index to the category field, and then do your search using this, which will return all possible options, sorted by relevance! To add the index, use the following dbmod.php script:
<?phprequire("includes/common.php");
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products`
CREATE FULLTEXT INDEX category_2 ON (category)";
database_queryModify($sql,$result);
print "Done.";
?>
With that in place, try this as the initial part of your mod:
$sql = "SELECT DISTINCT category,MATCH category AGAINST ('".database_safe($category["name"])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH category AGAINST ('".database_safe($category["name"])."') ORDER BY relevance DESC";database_querySelect($sql,$categories);
Hope this helps!
Cheers,
David.
--
PriceTapestry.com