You are here:  » Unmapped category list


Unmapped category list

Submitted by karolisgo on Fri, 2012-01-06 12:22 in

Hi David,

I started categories mapping and now I am lost :)
Do you know any idea maybe it is possible get a list of all categories that are unmapped?

With products, you gave an idea to create extra column and mark mapped=1, how about categories, is it possible too?

Thanks,
Karolis

Submitted by support on Fri, 2012-01-06 13:37

Hi Karolis,

Here's a version of the categories A-Z which will show unmapped categories only, save it as something like categoriesunmapped.php and upload to and browse to from the top level of your Price Tapestry installation:

<?php
  
require("includes/common.php");
  
$atoz["items"] = array();
  
$sql "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` ORDER BY category";
  if (
database_querySelect($sql,$rows1))
  {
    
$rows2 = array();
    foreach(
$rows1 as $row)
    {
      
$rows2[$row["category"]] = $row;
    }
    
$sql "SELECT * FROM `".$config_databaseTablePrefix."categories`";
    
database_querySelect($sql,$rows3);
    foreach(
$rows3 as $row)
    {
      unset(
$rows2[$row["name"]]);
    }
    foreach(
$rows2 as $product)
    {
      if (
$product["category"])
      {
        
$item = array();
        
$item["name"] = $product["category"];
        if (
$config_useRewrite)
        {
          
$item["href"] = $config_baseHREF."category/".urlencode(tapestry_hyphenate($product["category"]))."/";
        }
        else
        {
          
$item["href"] = $config_baseHREF."search.php?q=category:".urlencode($product["category"]).":";
        }
        
$atoz["items"][] = $item;
      }
    }
  }
  
$banner["h2"] = "<strong>".translate("Category")." A-Z</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 karolisgo on Fri, 2012-01-06 13:42

Thanks David. What is more, I want to add extra fields to mapped categories. Like: multilanguage names, descriptions, etc. Which file is the best to do this? Because I see that category.php has nothing with mapped categories.

Submitted by support on Fri, 2012-01-06 13:53

Hi,

Yes mapping is purely an import time operation - after that it is just the 'category' field in the products table that is referred to.

What you could do very easily would be to implement almost exactly the same as the additional merchant info mod described in this thread - simply use a folder /categoryinfo/ and replace each instance of "merchant" with "category" in the modification and that should do the trick - you can then add as much category info HTML as required in the included /categoryinfo/ files...

Cheers,
David.
--
PriceTapestry.com

Submitted by karolisgo on Fri, 2012-01-06 14:01

I understand this, but I am not sure if this can solve multilanguage case. Maybe I can have several .html pages like, marchant_en.html, merchant_fr.html and so on.

Moving forwad, what about Mapped Products, is it same problem or I can add extra columns in database table _productmap?

Submitted by support on Fri, 2012-01-06 15:22

Hi Karlis,

Sure - you could easily modify the /merchantinfo/ method to pull in different versions depending on language. With a mechanism in place to identify language - presumably a variable e.g. $language, then simply replace

  $merchantinfoFilename = "merchantinfo/".$product["merchant"].".html";

with:

  $merchantinfoFilename = "merchantinfo/".$product["merchant"]."_".$language.".html";

Exactly the same with /categoryinfo/ of course.

For product - yes one way would be to add additional description override fields - the current description override is stored as "description" in the `productsmap` table so here you could add `description_en`, `description_fr` etc.

You would also need similar fields in the products table for which to import them into - see the dbmod.php script in the extra indexed fields thread here for how to add the fields.

Finally, you'd need to duplicate the code where the description override is set - within includes/admin.php search on the keyword "description" where you'll find each relevant section to copy; plus similar in admin/productsmap_configure.php...

Hope this points you in the right direction!

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Sat, 2016-01-09 11:08

Hello,

How can i use this unmapped.php also for category hierarchie mapping?

Thank you.

Jan Roel

Submitted by support on Sat, 2016-01-09 11:37

Hello Jan,

The Reverse Mapping tool without merchant filter applied an "Unmapped only" checked is will display the complete list of unmapped feed categories, however if it would be handy to study the actual products associated with those feed categories as would be possible with the above then first of all create an alternative to the above script as follows:

<?php
  
require("includes/common.php");
  
$atoz["items"] = array();
  
$sql "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` WHERE categoryid='0' 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");
?>

And then to add a search operator for feedcategory: edit search.php and look for the following code at line 230:

      case "bw":

...and REPLACE with:

      case "feedcategory":
        $where = "category = '".database_safe($parts[1])."'";
        $sql = "SELECT SQL_CALC_FOUND_ROWS id,COUNT(id) AS numMerchants,MIN(price) as minPrice FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." GROUP BY search_name";
        $orderBySelection = $orderByDefault;
        break;
      case "bw":

You'll then be able to browse the products associated with unmapped feed categories using the links generated by the above script...

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Sat, 2016-01-09 11:44

Hello david,

The only thing i want to do is check which categories i have to map manually. I there this code okai for. So which code isn' t map automaticly.

Submitted by support on Sat, 2016-01-09 11:51

Hello Jan,

That _should_ be the case with the above - the list is obtained using a WHERE clause of "categoryid = '0'" so it will just be those categories that currently have not triggered any existing Keyword match or =exact match alternative entries - let me know if you're still not sure of course - I will be online just a little longer this morning but will follow up in detail as soon as I am back online properly on Monday AM...

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Sat, 2016-01-09 14:57

Works great thank you very much!

Submitted by wesse249 on Sat, 2016-03-19 08:50

Hello David,

The unmapped option works great. But i have 2 things:

1 On my homepage i have this:

$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` ORDER BY views DESC LIMIT 24";

The problem is that this code show also the products from unmapped categories. How can i solve this?

2 products from unmapped categories are also showed in the searchresult. How can i solve this?

Thank you very much.

Jan ROel

Submitted by support on Sat, 2016-03-19 16:43

Hello Jan,

For Featured Products, use:

$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE categoryid <> '0' ORDER BY views DESC LIMIT 24";

And to restrict search results in general to mapped in products only, in search.php look for the following code at line 20:

  $priceWhere = "";

...and REPLACE with:

  $priceWhere = " AND categoryid <> '0' ";

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Mon, 2016-03-21 08:14

Hello David,

There are no products showed up when i ad this featured code in index.php

Thanks Jan Roel

Submitted by support on Mon, 2016-03-21 09:55

Hello Jan,

That's strange - could you try with database debug mode enabled temporarily by changing line 6 in config.advanced.php as follows;

  $config_databaseDebugMode = TRUE;

...then re-load home page and let me know if an extended MySQL error message is displayed, or otherwise of course...

Thanks,

David.
--
PriceTapestry.com

Submitted by wesse249 on Mon, 2016-03-21 10:32

Hello David,

There is no MySQL error message is displayed.

Gr Jan Roel

Submitted by support on Mon, 2016-03-21 10:57

Hello Jan,

I just double checked everything on my server, please could you revert to;

$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` ORDER BY views DESC LIMIT 24";

...and confirm that without the WHERE clause you are seeing Featured Products again?

Thanks,
David.
--
PriceTapestry.com

Submitted by wesse249 on Mon, 2016-03-21 10:59

Hello David,

Yes with line i see the products again.

Submitted by support on Mon, 2016-03-21 11:08

Thanks Jan,

If you could perhaps try once again using;

$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE categoryid > 0 ORDER BY views DESC LIMIT 24";

(just in case character / copy or paste issue previously)

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Mon, 2016-03-21 12:54

Hello,

Doesn't work. Could this post maybe give a problem?

Thanks

Submitted by support on Mon, 2016-03-21 12:59

Hello Jan,

Please could you email me the modified index.php and I'll check it out further with you...

Thanks,
David.
--
PriceTapestry.com

Submitted by wesse249 on Mon, 2017-01-30 17:47

Hello David,

I use this unmapped.php code on my website.

<?php
  
require("includes/common.php");
  
$atoz["items"] = array();
  
$sql "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` WHERE categoryid='0' 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/menu.php");
  require(
"html/searchform.php");
  require(
"html/banner.php");
  require(
"html/atoz.php");
  require(
"html/footer.php");
?>

But when i click on the category i can't see which products in it. Is there a solution how i can see this?

{link saved}

Thank you.

Jan Roel

Submitted by support on Mon, 2017-01-30 18:05

Hello Jan,

That looks like the modification described to search.php in this comment - which adds support for the feedcategory: search operator, is not in place on this installation - that should be all it is...

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Tue, 2017-01-31 13:50

Hello David,

I change it to

And then to add a search operator for feedcategory: edit search.php and look for the following code at line 230:

case "bw":

...and REPLACE with:

case "feedcategory":
$where = "category = '".database_safe($parts[1])."'";
$sql = "SELECT SQL_CALC_FOUND_ROWS id,COUNT(id) AS numMerchants,MIN(price) as minPrice FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." GROUP BY search_name";
$orderBySelection = $orderByDefault;
break;
case "bw":

But nothing shows up. See http://wasmachinevoordeel.nl/unmapped.php

Is the problem maybe category_mapped = '1' ?

Thank you

Submitted by support on Tue, 2017-01-31 14:52

Hello Jan,

Yes - category_mapped mod that I was helping you with separately will be the issue - in this case, simply remove the use of $priceWhere in the SQL - so instead of the above replacement, use:

  case "feedcategory":
    $where = "category = '".database_safe($parts[1])."'";
    $sql = "SELECT SQL_CALC_FOUND_ROWS id,COUNT(id) AS numMerchants,MIN(price) as minPrice FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY search_name";
    $orderBySelection = $orderByDefault;
    break;
  case "bw":

Cheers,
David.
--
PriceTapestry.com