Long category names

Submitted by Panos on Mon, 2016-04-04 20:50 in Feed Management and Administration

Hello,
once again i seek your support for an issue regarding long category names. In my data feeds i have really long category names which are chopped off when stored in database. This actually results in not being able to perform a category hierarchy mapping (more specifically a reverse per store mapping). Although the mapping is saved when i visit the relative category page in frontend no products appear (products though exist and are discoverable only by search). I guess this issue has to do with the length of the corresponding field in mysql which is set to 64. Got any clue?..well i bet you have :)

thanks so so much in advance,
Panos

Hello Panos, The reason for

Submitted by support on Tue, 2016-04-05 08:09

Hello Panos,

The reason for the 64 character size is because the category field is included as part of a multiple field index for search performance, however, with the index remaining in place it is normally OK to use a size of 150 characters, so to apply this change create and run the following dbmod.php script:

<?php
  set_time_limit(0);
  require("includes/common.php");
  $config_databaseDebugMode = TRUE;
  $sql = "ALTER TABLE `".$config_databaseTablePrefix."products`
            CHANGE `category` `category` VARCHAR(150) NOT NULL default ''";
  database_queryModify($sql,$result);
  print "Done.";
?>

The above sets database debug mode to on so that if the maximum key length is exceeded you will see the warning and can try a shorter length.

Alternatively, for a niche site where the performance benefit from the index would be negligible anyway, or if you are using Category Hierarchy Mapping, then you could drop the index and that would permit any length of `category` field required, so to apply this change, create and run the following dbmod.php script:

<?php
  require("includes/common.php");
  $sql = "DROP INDEX search_name_category_price_id ON `".$config_databaseTablePrefix."products`";
  database_queryModify($sql,$result);
  print "Done.";
?>

Cheers,
David.
--
PriceTapestry.com

David thanks a lot for the

Submitted by Panos on Wed, 2016-04-06 21:43

David thanks a lot for the solutions provided. In fact solution number 1 didnt work out for me cause the max char length can be set max to 75 which doesn't play out for me well. So i went with option 2 since im using reverse mapping. Thanks so much again.

Panos