You are here:  » Category Update Without Feed Re-load


Category Update Without Feed Re-load

Submitted by ChrisNBC on Fri, 2017-09-08 08:03 in

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

Submitted by support on Fri, 2017-09-08 11:03

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:

<?php
  
require("../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

Submitted by ChrisNBC on Tue, 2017-09-12 13:16

Hi David,

Thanks for the above code which works perfectly.

Best regards
Chris

Submitted by stevebi on Thu, 2017-09-14 17:51

Hello David,

I have applied the code also.

It saved me a lot of time.

Thank you for your support.

Cheers

Steve

Submitted by zoder on Mon, 2019-01-07 14:39

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!

Submitted by support on Mon, 2019-01-07 15:06

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

Submitted by safari45 on Fri, 2020-07-24 06:38

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

Submitted by support on Fri, 2020-07-24 07:52

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

Submitted by safari45 on Fri, 2020-07-24 09:04

not working, it is running into an error 503 (Service Unavailable) and no categories showing on front-end

Submitted by support on Fri, 2020-07-24 09:56

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

Submitted by safari45 on Fri, 2020-07-24 10:27

a bit more than 200 000 products

Submitted by safari45 on Fri, 2020-07-24 10:28

Yes categories appear back after full import.

Submitted by support on Fri, 2020-07-24 10:40

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

Submitted by safari45 on Fri, 2020-07-24 10:41

It seems to work well with a commandline. it is currently running.

Submitted by support on Fri, 2020-07-24 10:43

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