Hi David,
Hope all is going well.
I wondered if you could tell me if there is a way to run just category (hierarchy) mapping for all feeds without re-loading the data?
Thanks in advance.
Best regards
Chris
Hi David,
Thanks for the above code which works perfectly.
Best regards
Chris
Hello David,
I have applied the code also.
It saved me a lot of time.
Thank you for your support.
Cheers
Steve
Hi David,
This code help me a lot, thanks.
But when I import a big CSV datafeed then click apply button, it give me a http 500 error within 10s,
I changed connect timeout to 1 hour in these files my.cnf and php.ini, and it seems there is no use.
What should I do to avoid this error?
Thanks!
Hi and welcome to the forum!
I wasn't sure if you configured the maximum execution time in php.ini to zero to enable unlimited time, you could try adding to the top of admin/categories_hierarchy_apply.php just after the opening php tag:
set_time_limit(0);Otherwise the reason for a 500 Internal Server Error is normally reported to your web server's error log which you may have access to via your hosting control panel, or from the command line in a logs folder or at system level for example /var/log/httpd/error_log. If that's the case and an error is being reported but you're not sure where the problem lies; or if there is no output but still not working let me know and I'll check it out further with you...
Cheers,
David.
--
PriceTapestry.com
Hi David, Hope you are doing well.
I was wondering whether you could do something similar for normal category mapping. I can see this meant to update hierarchy category. I mean Category Update Without Feed Re-load
Thanks in advance
Hello Safari,
Sure - in this case, a new field "category_feed" would need to be added to the products table and populated with the unchanged category so that it can be used to apply Category Mapping again without importing the feed. To add the new field run the following dbmod.php script from the top level of the Price Tapestry installation;
<?php
set_time_limit(0);
require("includes/common.php");
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products`
ADD `category_feed` varchar(64) NOT NULL default ''";
database_queryModify($sql,$result);
print "Done.";
?>
Edit includes/admin.php and look for the following comment at line 381:
/* apply category mappings */...and REPLACE with:
/* apply category mappings */
$importRecord["category_feed"] = $importRecord["category"];Next, re-import all feeds to populate the new field for the first time.
Here's the equivalent of the above script for Category Mapping, create and upload as admin/categories_apply.php
<?php
set_time_limit(0);
require("../includes/common.php");
$link1 = mysqli_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,$config_databaseName);
mysqli_set_charset($link1,"utf8");
$link2 = mysqli_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,$config_databaseName);
mysqli_set_charset($link2,"utf8");
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET category=category_feed";
mysqli_query($link1,$sql);
$admin_importCategoryMappings = array();
$sql = "SELECT * FROM `".$config_databaseTablePrefix."categories`";
if (database_querySelect($sql,$rows))
{
foreach($rows as $category)
{
$alternates = explode("\n",$category["alternates"]);
foreach($alternates as $alternate)
{
$alternate = trim($alternate);
$admin_importCategoryMappings[$alternate] = $category["name"];
}
}
}
$sql = "SELECT id,category,category_feed FROM `".$config_databaseTablePrefix."products`";
mysqli_real_query($link1,$sql);
$result = mysqli_use_result($link1);
while($importRecord = mysqli_fetch_assoc($result))
{
$category = $importRecord["category"];
if (isset($admin_importCategoryMappings["=".$importRecord["category_feed"]]))
{
$importRecord["category"] = $admin_importCategoryMappings["=".$importRecord["category_feed"]];
}
else
{
foreach($admin_importCategoryMappings as $k => $v)
{
if (substr($k,0,1) !== "=")
{
$found = 0;
$words = explode(" ",$k);
foreach($words as $word)
{
if ($word)
{
if (strpos($importRecord["category_feed"],$word) !== FALSE) $found++;
}
}
if ($found == count($words))
{
$importRecord["category"] = $v;
break;
}
}
}
}
if ($category != $importRecord["category"])
{
$sql = "UPDATE `".$config_databaseTablePrefix."products`
SET category='".mysqli_real_escape_string($link2,$importRecord["category"])."'
WHERE id='".$importRecord["id"]."'";
mysqli_query($link2,$sql);
}
}
header("Location: categories.php");
exit();
?>
Finally, to add the link to the existing Category Hierarchy admin page, edit admin/categories.php and look for the following code at line 82:
print "</table>";...and REPLACE with:
print "</table>";
print "<p><a href='categories_apply.php'>Apply</a></p>";Hope this helps!
Cheers,
David.
--
PriceTapestry.com
not working, it is running into an error 503 (Service Unavailable) and no categories showing on front-end
Hello Safari,
It may need to be run from the command line - how many products are in your database?
Do the categories appear back correctly after a full import?
Cheers,
David.
--
PriceTapestry.com
Hi,
It may be a memory issue then, I've modified admin/categories_apply.php from the post above now to use buffered queries which should do the trick if that was case...
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
It seems to work well with a commandline. it is currently running.
Hi,
Cool - I posted an update above before just before your last reply so if still no joy you might want to try the updated version which I'll leave in place...
Cheers,
David.
--
PriceTapestry.com
Hi Chris,
This can be done by taking the Category Hierarchy Mapping application code from the import record handler and applying it against the live products table - create a new script containing the following code saved as admin/categories_hierarchy_apply.php:
<?phprequire("../includes/common.php");
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET categoryid='0'";
database_queryModify($sql,$result);
$admin_importCategoryHierarchyMappings = array();
$sql = "SELECT * FROM `".$config_databaseTablePrefix."categories_hierarchy`";
if (database_querySelect($sql,$rows))
{
foreach($rows as $category)
{
$alternates = explode("\n",$category["alternates"]);
foreach($alternates as $alternate)
{
$alternate = trim($alternate);
$admin_importCategoryHierarchyMappings[$alternate] = $category["id"];
}
}
}
$sql = "SELECT id,category FROM `".$config_databaseTablePrefix."products`";
database_querySelect($sql,$importRecords);
foreach($importRecords as $importRecord)
{
if (isset($admin_importCategoryHierarchyMappings["=".$importRecord["category"]]))
{
$importRecord["categoryid"] = $admin_importCategoryHierarchyMappings["=".$importRecord["category"]];
}
else
{
foreach($admin_importCategoryHierarchyMappings as $k => $v)
{
if (substr($k,0,1) !== "=")
{
$found = 0;
$words = explode(" ",$k);
foreach($words as $word)
{
if ($word)
{
if (strpos($importRecord["category"],$word) !== FALSE) $found++;
}
}
if ($found == count($words))
{
$importRecord["categoryid"] = $v;
break;
}
}
}
}
if (isset($importRecord["categoryid"]))
{
$sql = "UPDATE `".$config_databaseTablePrefix."products`
SET categoryid='".$importRecord["categoryid"]."'
WHERE id='".$importRecord["id"]."'";
database_queryModify($sql,$result);
}
}
header("Location: categories_hierarchy.php");
exit();
?>
And then to create a link to apply changes at the bottom of the Category Hierarchy Mapping page, edit admin/categories_hierarchy.php and look for the following code at line 203:
widget_formEnd();...and REPLACE with:
widget_formEnd();print "<p><a href='categories_hierarchy_apply.php'>Apply</a></p>";
Hope this helps!
Cheers,
David.
--
PriceTapestry.com