You are here:  » Category mapping


Category mapping

Submitted by wesse249 on Wed, 2016-01-27 09:01 in

Hello David,

I inserted 3100 by category mapping. I use this script to check which categories aren't mapped: http://www.pricetapestry.com/node/4435

Is it possible to do the following trick: Add automaticly all unmapped categories to a category called for example "Z". But i still like to use unmapped script. So i don't know if it still works when all unmapped categories are placed in for example Z category.

Thank you.

Submitted by support on Wed, 2016-01-27 09:30

Hello Jan,

It would be straight forward to create a "Z" category and have any unmapped categories assigned to it, and then for the script from node 4435 to exclude "Z". Firstly, in Category Hierarchy Mapping, create the new top level category "Z".

Then in includes/admin.php, look for the following code at line 452:

    /* check product record for minimum required fields */

...and REPLACE with:

    if (!$importRecord["categoryid"])
    {
      global $category_z_id;
      if (!isset($category_z_id))
      {
        $sql = "SELECT id FROM `".$config_databaseTablePrefix."categories_hierarchy` WHERE name='Z'";
        database_querySelect($sql,$rows);
        $category_z_id = $rows[0]["id"];
      }
      $importRecord["categoryid"] = $category_z_id;
    }
    /* check product record for minimum required fields */

And then in the unmapped script from this comment, look for the following code at line 4:

  $sql = "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` WHERE categoryid='0' ORDER BY category";

...and REPLACE with:

  $sql = "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` WHERE categoryid = (SELECT id AS categoryid FROM `".$config_databaseTablePrefix."categories_hierarchy` WHERE name='Z') ORDER BY category";

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Wed, 2016-01-27 10:40

This works also for category mapping? Then i need to change categories_hierarchy to categories?

Thanks

Submitted by support on Wed, 2016-01-27 10:48

Hello Jan,

It would actually be more complicated for normal Category Mapping since the `category` field is overwritten so by writing "Z" you would lose the list of unmapped categories. A basic Category Mapping list of unmapped categories from feeds is no problem though...

<?php
  
require("includes/common.php");
  
$atoz["items"] = array();
  
$sql "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` WHERE name NOT IN (SELECT name FROM `".$config_databaseTablePrefix."categories`) ORDER BY category";
  if (
database_querySelect($sql,$rows))
  {
    foreach(
$rows as $product)
    {
      if (
$product["category"])
      {
        
$item = array();
        
$item["name"] = $product["category"];
        
$item["href"] = "search.php?q=feedcategory:".urlencode($product["category"]);
        
$atoz["items"][] = $item;
      }
    }
  }
  
$banner["h2"] = "<strong>Unmapped Categories</strong>";
  require(
"html/header.php");
  require(
"html/menu.php");
  require(
"html/searchform.php");
  require(
"html/banner.php");
  require(
"html/atoz.php");
  require(
"html/footer.php");
?>

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Wed, 2016-01-27 12:10

Hello David,

So it is not possible? Because i have the categories in normal category mapping: {link saved}

Or is it possible to hide the the unmapped categories on the {link saved} page? and maybe also the products?

Submitted by support on Wed, 2016-01-27 12:26

Hello Jan,

Hiding unmapped categories on the front-end is no problem, and this leaves them in the database for you to carry on with Category Mapping in the back-end.

To remove from Category A-Z page, edit categories.php and look for the following code at line 70:

      $sql = "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` ORDER BY category";

...and REPLACE with:

      $sql = "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` WHERE category IN (SELECT name AS category FROM `".$config_databaseTablePrefix."categories`) ORDER BY category";

To remove from search filters, edit html/searchfilters.php and look for the following code at line 105:

  $sql1 = "SELECT DISTINCT(category) FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." AND category <> '' ORDER BY category";

...and REPLACE with:

  $sql1 = "SELECT DISTINCT(category) FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." AND category IN (SELECT name AS category FROM `".$config_databaseTablePrefix."categories`) ORDER BY category";

If you are conditionally showing category name on product page, then you can clear the value after the $product_main variable has been set, so in html/product.php look for the following code at line 2:

  $product_main = $product["products"][0];

...and REPLACE with:

  $product_main = $product["products"][0];
  $sql = "SELECT id FROM `".$config_databaseTablePrefix."categories` WHERE name='".database_safe($product_main["category"])."'";
  if (!database_querySelect($sql,$rows))
  {
    $product_main["category"] = "";
  }

For the display code to be conditional (if not already), use something like:

<?php if ($product_main["category"]): ?>
<p>Category: <?php print $product_main["category"]; ?></p>
<?php endif; ?>

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Wed, 2016-01-27 20:27

Hello David,

Works great. Only i don't understand this code:

  $product_main = $product["products"][0];
  $sql = "SELECT id FROM `".$config_databaseTablePrefix."categories` WHERE name='".database_safe($product_main["name"])."'";
  if (!database_querySelect($sql,$rows))
  {
    $product_main["category"] = "";
  }

I have on all my productpages (category:) like this:

    <td class='pt_pr_visit'><a class='button tiny radius success' target='_BLANK' href='<?php print tapestry_buyURL($product_main); ?>'>Ga naar <?php print $p["merchant"]; ?></a><br>
    <p><?php if ($product_main["category"]): ?></a>
    Categorie: <a href='<?php print $config_baseHREF."category/".tapestry_hyphenate($product_main["category"]);?>/'><?php print $product_main["category"]; ?></a></p>
    <p>Bekijk <a href='<?php print $config_baseHREF."merchant/".tapestry_hyphenate($product_main["merchant"]);?>/'>hier</a> het complete assortiment van <?php print $product_main["merchant"]; ?></p>
    <?php endif; ?></td>

but when i add your code all above is gone. also for the mapped products. for unmapped is no problem.

Is it also possible that products which aren't mapped don't be showed under brandnames, sitemap and searchresult?

Thank you very much.

Submitted by support on Thu, 2016-01-28 09:07

Sorry about that Jan, the code at the top of html/product.php should be:

  $product_main = $product["products"][0];
  $sql = "SELECT id FROM `".$config_databaseTablePrefix."categories` WHERE name='".database_safe($product_main["name"])."'";
  if (!database_querySelect($sql,$rows))
  {
    $product_main["category"] = "";
  }

(I had used $product_main["name"] instead of $product_main["category"] - corrected above also...)

Cheers,
David.
--
PriceTapestry.com