Duplicate Products on front end.

Jessie K. Jepsen · October 17, 2012

Im having an issue that just started, in 1.5.1

On the front end of my site, each one of my products is showing 5 times. I dont know where to start with this.. any suggestions? wasn't like this two days ago.

Edited October 17, 2012 by EMPeiTQ (see edit history)

Baltimore · October 24, 2012

I have the same problem. If you or me get a solution please, let us know it! Thank you!

Jessie K. Jepsen · October 24, 2012

I have the same problem. If you or me get a solution please, let us know it! Thank you!

Replace your /classes/Category.php with the code below, and let me know if that fix's it for you.

PLEASE BACKUP YOUR Category.php FILE FIRST

<?php
/*
* 2007-2012 PrestaShop
*
* NOTICE OF LICENSE
*
* This source file is subject to the Open Software License (OSL 3.0)
* that is bundled with this package in the file LICENSE.txt.
* It is also available through the world-wide-web at this URL:
* http://opensource.org/licenses/osl-3.0.php
* If you did not receive a copy of the license and are unable to
* obtain it through the world-wide-web, please send an email
* to [email protected] so we can send you a copy immediately.
*
* DISCLAIMER
*
* Do not edit or add to this file if you wish to upgrade PrestaShop to newer
* versions in the future. If you wish to customize PrestaShop for your
* needs please refer to http://www.prestashop.com for more information.
*
*  @author PrestaShop SA <[email protected]>
*  @copyright  2007-2012 PrestaShop SA
*  @version  Release: $Revision: 7515 $
*  @license    http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
*  International Registered Trademark & Property of PrestaShop SA
*/
class CategoryCore extends ObjectModel
{
public $id;
/** @var integer category ID */
public $id_category;
/** @var string Name */
public $name;
/** @var boolean Status for display */
public $active = 1;
/** @var  integer category position */
public $position;
/** @var string Description */
public $description;
/** @var integer Parent category ID */
public $id_parent;
/** @var integer default Category id */
public $id_category_default;
/** @var integer Parents number */
public $level_depth;
/** @var integer Nested tree model "left" value */
public $nleft;
/** @var integer Nested tree model "right" value */
public $nright;
/** @var string string used in rewrited URL */
public $link_rewrite;
/** @var string Meta title */
public $meta_title;
/** @var string Meta keywords */
public $meta_keywords;
/** @var string Meta description */
public $meta_description;
/** @var string Object creation date */
public $date_add;
/** @var string Object last modification date */
public $date_upd;
/** @var boolean is Category Root */
public $is_root_category;
public $groupBox;
protected static $_links = array();
/**
 * @see ObjectModel::$definition
 */
public static $definition = array(
 'table' => 'category',
 'primary' => 'id_category',
 'multilang' => true,
 'multilang_shop' => true,
 'fields' => array(
  'nleft' =>	 array('type' => self::TYPE_INT, 'validate' => 'isUnsignedInt'),
  'nright' =>    array('type' => self::TYPE_INT, 'validate' => 'isUnsignedInt'),
  'level_depth' =>   array('type' => self::TYPE_INT, 'validate' => 'isUnsignedInt'),
  'active' =>    array('type' => self::TYPE_BOOL, 'validate' => 'isBool', 'required' => true),
  'id_parent' =>    array('type' => self::TYPE_INT, 'validate' => 'isUnsignedInt'),
  'id_shop_default' =>  array('type' => self::TYPE_INT, 'validate' => 'isUnsignedId'),
  'is_root_category' =>  array('type' => self::TYPE_BOOL, 'validate' => 'isBool'),
  'position' =>    array('type' => self::TYPE_INT),
  'date_add' =>    array('type' => self::TYPE_DATE, 'validate' => 'isDate'),
  'date_upd' =>    array('type' => self::TYPE_DATE, 'validate' => 'isDate'),
  // Lang fields
  'name' =>	 array('type' => self::TYPE_STRING, 'lang' => true, 'validate' => 'isCatalogName', 'required' => true, 'size' => 64),
  'link_rewrite' =>   array('type' => self::TYPE_STRING, 'lang' => true, 'validate' => 'isLinkRewrite', 'required' => true, 'size' => 64),
  'description' =>   array('type' => self::TYPE_HTML, 'lang' => true, 'validate' => 'isString'),
  'meta_title' =>   array('type' => self::TYPE_STRING, 'lang' => true, 'validate' => 'isGenericName', 'size' => 128),
  'meta_description' =>  array('type' => self::TYPE_STRING, 'lang' => true, 'validate' => 'isGenericName', 'size' => 255),
  'meta_keywords' =>   array('type' => self::TYPE_STRING, 'lang' => true, 'validate' => 'isGenericName', 'size' => 255),
 ),
);
/** @var string id_image is the category ID when an image exists and 'default' otherwise */
public $id_image = 'default';
protected $webserviceParameters = array(
 'objectsNodeName' => 'categories',
 'hidden_fields' => array('nleft', 'nright', 'groupBox'),
 'fields' => array(
  'id_parent' => array('xlink_resource'=> 'categories'),
  'level_depth' => array('setter' => false),
  'nb_products_recursive' => array('getter' => 'getWsNbProductsRecursive', 'setter' => false),
 ),
 'associations' => array(
  'categories' => array('getter' => 'getChildrenWs', 'resource' => 'category', ),
  'products' => array('getter' => 'getProductsWs', 'resource' => 'product', ),
 ),
);
public function __construct($id_category = null, $id_lang = null, $id_shop = null)
{
 parent::__construct($id_category, $id_lang, $id_shop);
 $this->id_image = ($this->id && file_exists(_PS_CAT_IMG_DIR_.(int)$this->id.'.jpg')) ? (int)$this->id : false;
 $this->image_dir = _PS_CAT_IMG_DIR_;
}
/**
  * Allows to display the category description without HTML tags and slashes
  *
  * @return string
  */
public static function getDescriptionClean($description)
{
 return strip_tags(stripslashes($description));
}
public function add($autodate = true, $null_values = false)
{
 if (!isset($this->level_depth))
  $this->level_depth = $this->calcLevelDepth();
 $ret = parent::add($autodate, $null_values);
 if (Tools::isSubmit('checkBoxShopAsso_category'))
  foreach (Tools::getValue('checkBoxShopAsso_category') as $id_shop => $value)
  {
   $position = Category::getLastPosition((int)$this->id_parent, $id_shop);
   $this->addPosition($position, $id_shop);
  }
 else
  foreach (Shop::getShops(true) as $shop)
  {
   $position = Category::getLastPosition((int)$this->id_parent, $shop['id_shop']);
   if (!$position)
 $position = 1;
   $this->addPosition($position, $shop['id_shop']);
  }
 if (!isset($this->doNotRegenerateNTree) || !$this->doNotRegenerateNTree)
  Category::regenerateEntireNtree();
 $this->updateGroup($this->groupBox);
 Hook::exec('actionCategoryAdd', array('category' => $this));
 return $ret;
}
/**
 * update category positions in parent
 *
 * @param mixed $null_values
 * @return void
 */
public function update($null_values = false)
{
 if ($this->id_parent == $this->id)
  throw new PrestaShopException('a category cannot be it\'s own parent');
 // Update group selection
 $this->updateGroup($this->groupBox);
 $this->level_depth = $this->calcLevelDepth();
 // If the parent category was changed, we don't want to have 2 categories with the same position
 if ($this->getDuplicatePosition())
 {
  $assos = array();
  if (Tools::isSubmit('checkBoxShopAsso_category'))
  {
   $check_box = Tools::getValue('checkBoxShopAsso_category');
   foreach ($check_box as $id_asso_object => $row)
   {
 foreach ($row as $id_shop => $value)
  $assos[] = array('id_object' => (int)$id_asso_object, 'id_shop' => (int)$id_shop);
   }
  }
  foreach ($assos as $shop)
   $this->addPosition(Category::getLastPosition((int)$this->id_parent, $shop['id_shop']), $shop['id_shop']);
 }
 $this->cleanPositions((int)$this->id_parent);
 $ret = parent::update($null_values);
 if (!isset($this->doNotRegenerateNTree) || !$this->doNotRegenerateNTree)
 {
  Category::regenerateEntireNtree();
  $this->recalculateLevelDepth($this->id_category);
 }
 Hook::exec('actionCategoryUpdate', array('category' => $this));
 return $ret;
}
/**
 * @see ObjectModel::toggleStatus()
 */
public function toggleStatus()
{
 $result = parent::toggleStatus();
 Hook::exec('actionCategoryUpdate');
 return $result;
}
/**
 * Recursive scan of subcategories
 *
 * @param integer $max_depth Maximum depth of the tree (i.e. 2 => 3 levels depth)
 * @param integer $current_depth specify the current depth in the tree (don't use it, only for rucursivity!)
 * @param integer $id_lang Specify the id of the language used
 * @param array $excluded_ids_array specify a list of ids to exclude of results
 *
 * @return array Subcategories lite tree
 */
public function recurseLiteCategTree($max_depth = 3, $current_depth = 0, $id_lang = null, $excluded_ids_array = null)
{
 $id_lang = is_null($id_lang) ? Context::getContext()->language->id : (int)$id_lang;
 if (!(int)$id_lang)
  $id_lang = _USER_ID_LANG_;
 $children = array();
 $subcats = $this->getSubCategories($id_lang, true);
 if (($max_depth == 0 || $current_depth < $max_depth) && $subcats && count($subcats))
  foreach ($subcats as &$subcat)
  {
   if (!$subcat['id_category'])
 break;
   else if (!is_array($excluded_ids_array) || !in_array($subcat['id_category'], $excluded_ids_array))
   {
 $categ = new Category($subcat['id_category'], $id_lang);
 $children[] = $categ->recurseLiteCategTree($max_depth, $current_depth + 1, $id_lang, $excluded_ids_array);
   }
  }

 if (is_array($this->description))
  foreach ($this->description as $lang => $description)
   $this->description[$lang] = Category::getDescriptionClean($description);
 else
  $this->description = Category::getDescriptionClean($this->description);

 return array(
  'id' => (int)$this->id_category,
  'link' => Context::getContext()->link->getCategoryLink($this->id, $this->link_rewrite),
  'name' => $this->name,
  'desc'=> $this->description,
  'children' => $children
 );
}
public static function recurseCategory($categories, $current, $id_category = 1, $id_selected = 1)
{
 echo '<option value="'.$id_category.'"'.(($id_selected == $id_category) ? ' selected="selected"' : '').'>'.
 str_repeat(' ', $current['infos']['level_depth'] * 5).stripslashes($current['infos']['name']).'</option>';
 if (isset($categories[$id_category]))
  foreach (array_keys($categories[$id_category]) as $key)
   Category::recurseCategory($categories, $categories[$id_category][$key], $key, $id_selected);
}

/**
 * Recursively add specified category childs to $to_delete array
 *
 * @param array &$to_delete Array reference where categories ID will be saved
 * @param array $id_category Parent category ID
 */
protected function recursiveDelete(&$to_delete, $id_category)
{
 if (!is_array($to_delete) || !$id_category)
  die(Tools::displayError());
 $result = Db::getInstance()->executeS('
 SELECT `id_category`
 FROM `'._DB_PREFIX_.'category`
 WHERE `id_parent` = '.(int)$id_category);
 foreach ($result as $row)
 {
  $to_delete[] = (int)$row['id_category'];
  $this->recursiveDelete($to_delete, (int)$row['id_category']);
 }
}
public function deleteLite()
{
 // Directly call the parent of delete, in order to avoid recursion
 return parent::delete();
}
public function delete()
{
 if ((int)$this->id === 0 || (int)$this->id === 1)
  return false;
 $this->clearCache();
 $all_cat = $this->getAllChildren();
 $all_cat[] = $this;
 foreach ($all_cat as $cat)
 {
  $cat->deleteLite();
  if (!$this->hasMultishopEntries())
  {
   $cat->deleteImage();
   $cat->cleanGroups();
   $cat->cleanAssoProducts();
   // Delete associated restrictions on cart rules
   CartRule::cleanProductRuleIntegrity('categories', array($cat->id));
   Category::cleanPositions($cat->id_parent);
   /* Delete Categories in GroupReduction */
   if (GroupReduction::getGroupReductionByCategoryId((int)$cat->id))
 GroupReduction::deleteCategory($cat->id);
  }
 }

 /* Rebuild the nested tree */
 if (!$this->hasMultishopEntries() && (!isset($this->doNotRegenerateNTree) || !$this->doNotRegenerateNTree))
  Category::regenerateEntireNtree();
 Hook::exec('actionCategoryDelete', array('category' => $this));

 return true;
}
/**
 * Delete several categories from database
 *
 * return boolean Deletion result
 */
public function deleteSelection($categories)
{
 $return = 1;
 foreach ($categories as $id_category)
 {
  $category = new Category($id_category);
  if ($category->isRootCategoryForAShop())
   return false;
  else
   $return &= $category->delete();
 }
 return $return;
}
/**
  * Get the depth level for the category
  *
  * @return integer Depth level
  */
public function calcLevelDepth()
{
 /* Root category */
 if (!$this->id_parent)
  return 0;
 $parent_category = new Category($this->id_parent);
 if (!Validate::isLoadedObject($parent_category))
  throw new PrestaShopException('Parent category does not exist');
 return $parent_category->level_depth + 1;
}
/**
  * Re-calculate the values of all branches of the nested tree
  */
public static function regenerateEntireNtree()
{
 $id = Context::getContext()->shop->id;
 $id_shop = $id ? $id: Configuration::get('PS_SHOP_DEFAULT');
 $categories = Db::getInstance()->executeS('
 SELECT c.`id_category`, c.`id_parent`
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_shop` cs
  ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
 ORDER BY c.`id_parent`, cs.`position` ASC');
 $categories_array = array();
 foreach ($categories as $category)
  $categories_array[$category['id_parent']]['subcategories'][] = $category['id_category'];
 $n = 1;
 if (isset($categories_array[0]) && $categories_array[0]['subcategories'])
  Category::_subTree($categories_array, $categories_array[0]['subcategories'][0], $n);
}
protected static function _subTree(&$categories, $id_category, &$n)
{
 $left = $n++;
 if (isset($categories[(int)$id_category]['subcategories']))
  foreach ($categories[(int)$id_category]['subcategories'] as $id_subcategory)
   Category::_subTree($categories, (int)$id_subcategory, $n);
 $right = (int)$n++;
 Db::getInstance()->execute('
  UPDATE '._DB_PREFIX_.'category
  SET nleft = '.(int)$left.', nright = '.(int)$right.'
  WHERE id_category = '.(int)$id_category.' LIMIT 1
 ');
}
/**
  * Updates level_depth for all children of the given id_category
  *
  * @param integer $id_category parent category
  */
public function recalculateLevelDepth($id_category)
{
 if (!is_numeric($id_category))
  throw new PrestaShopException('id category is not numeric');
 /* Gets all children */
 $categories = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
  SELECT id_category, id_parent, level_depth
  FROM '._DB_PREFIX_.'category
  WHERE id_parent = '.(int)$id_category);
 /* Gets level_depth */
 $level = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
  SELECT level_depth
  FROM '._DB_PREFIX_.'category
  WHERE id_category = '.(int)$id_category);
 /* Updates level_depth for all children */
 foreach ($categories as $sub_category)
 {
  Db::getInstance()->execute('
   UPDATE '._DB_PREFIX_.'category
   SET level_depth = '.(int)($level['level_depth'] + 1).'
   WHERE id_category = '.(int)$sub_category['id_category']);
  /* Recursive call */
  $this->recalculateLevelDepth($sub_category['id_category']);
 }
}
/**
  * Return available categories
  *
  * @param integer $id_lang Language ID
  * @param boolean $active return only active categories
  * @return array Categories
  */
public static function getCategories($id_lang = false, $active = true, $order = true, $sql_filter = '', $sql_sort = '', $sql_limit = '')
{
  if (!Validate::isBool($active))
   die(Tools::displayError());
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
  SELECT *
  FROM `'._DB_PREFIX_.'category` c
  '.Shop::addSqlAssociation('category', 'c').'
  LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON c.`id_category` = cl.`id_category`'.Shop::addSqlRestrictionOnLang('cl').'
  WHERE 1 '.$sql_filter.' '.($id_lang ? 'AND `id_lang` = '.(int)$id_lang : '').'
  '.($active ? 'AND `active` = 1' : '').'
  '.(!$id_lang ? 'GROUP BY c.id_category' : '').'
  '.($sql_sort != '' ? $sql_sort : 'ORDER BY c.`level_depth` ASC, category_shop.`position` ASC').'
  '.($sql_limit != '' ? $sql_limit : '')
 );
 if (!$order)
  return $result;
 $categories = array();
 foreach ($result as $row)
  $categories[$row['id_parent']][$row['id_category']]['infos'] = $row;
 return $categories;
}
public static function getSimpleCategories($id_lang)
{
 return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
 SELECT c.`id_category`, cl.`name`
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category`'.Shop::addSqlRestrictionOnLang('cl').')
 '.Shop::addSqlAssociation('category', 'c').'
 WHERE cl.`id_lang` = '.(int)$id_lang.'
 AND c.`id_category` != '.Configuration::get('PS_ROOT_CATEGORY').'
 GROUP BY c.id_category
 ORDER BY c.`id_category`, category_shop.`position`');
}
public function getShopID()
{
 return $this->id_shop;
}
/**
  * Return current category childs
  *
  * @param integer $id_lang Language ID
  * @param boolean $active return only active categories
  * @return array Categories
  */
public function getSubCategories($id_lang, $active = true)
{
  if (!Validate::isBool($active))
   die(Tools::displayError());
 $groups = FrontController::getCurrentCustomerGroups();
 $sql_groups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
  SELECT c.*, cl.id_lang, cl.name, cl.description, cl.link_rewrite, cl.meta_title, cl.meta_keywords, cl.meta_description
  FROM `'._DB_PREFIX_.'category` c
  '.Shop::addSqlAssociation('category', 'c').'
  LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
   ON (c.`id_category` = cl.`id_category`
   AND `id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
  LEFT JOIN `'._DB_PREFIX_.'category_group` cg
   ON (cg.`id_category` = c.`id_category`)
  WHERE `id_parent` = '.(int)$this->id.'
   '.($active ? 'AND `active` = 1' : '').'
   AND cg.`id_group` '.$sql_groups.'
  GROUP BY c.`id_category`
  ORDER BY `level_depth` ASC, category_shop.`position` ASC
 ');
 foreach ($result as &$row)
 {
  $row['id_image'] = file_exists(_PS_CAT_IMG_DIR_.$row['id_category'].'.jpg') ? (int)$row['id_category'] : Language::getIsoById($id_lang).'-default';
  $row['legend'] = 'no picture';
 }
 return $result;
}
/**
  * Return current category products
  *
  * @param integer $id_lang Language ID
  * @param integer $p Page number
  * @param integer $n Number of products per page
  * @param boolean $get_total return the number of results instead of the results themself
  * @param boolean $active return only active products
  * @param boolean $random active a random filter for returned products
  * @param int $random_number_products number of products to return if random is activated
  * @param boolean $check_access set to false to return all products (even if customer hasn't access)
  * @return mixed Products or number of products
  */
public function getProducts($id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $random = false, $random_number_products = 1, $check_access = true, Context $context = null)
{
 if (!$context)
  $context = Context::getContext();
 if ($check_access && !$this->checkAccess($context->customer->id))
  return false;

 $front = true;
 if (!in_array($context->controller->controller_type, array('front', 'modulefront')))
  $front = false;

 if ($p < 1) $p = 1;
 if (empty($order_by))
  $order_by = 'position';
 else
  /* Fix for all modules which are now using lowercase values for 'orderBy' parameter */
  $order_by = strtolower($order_by);
 if (empty($order_way))
  $order_way = 'ASC';
 if ($order_by == 'id_product' || $order_by == 'date_add' || $order_by == 'date_upd')
  $order_by_prefix = 'p';
 elseif ($order_by == 'name')
  $order_by_prefix = 'pl';
 elseif ($order_by == 'manufacturer')
 {
  $order_by_prefix = 'm';
  $order_by = 'name';
 }
 elseif ($order_by == 'position')
  $order_by_prefix = 'cp';
 if ($order_by == 'price')
  $order_by = 'orderprice';
 if (!Validate::isBool($active) || !Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way))
  die (Tools::displayError());
 $id_supplier = (int)Tools::getValue('id_supplier');
 /* Return only the number of products */
 if ($get_total)
 {
  $sql = 'SELECT COUNT(cp.`id_product`) AS total
 FROM `'._DB_PREFIX_.'product` p
 '.Shop::addSqlAssociation('product', 'p').'
 LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON p.`id_product` = cp.`id_product`
 WHERE cp.`id_category` = '.(int)$this->id.
 ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '').
 ($active ? ' AND product_shop.`active` = 1' : '').
 ($id_supplier ? 'AND p.id_supplier = '.(int)$id_supplier : '');
  return (int)Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
 }
 $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, product_attribute_shop.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`,
 pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`,
 il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default,
 DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(),
 INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).'
  DAY)) > 0 AS new,
 (product_shop.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice
   FROM `'._DB_PREFIX_.'category_product` cp
   LEFT JOIN `'._DB_PREFIX_.'product` p
 ON p.`id_product` = cp.`id_product`
   '.Shop::addSqlAssociation('product', 'p').'
   LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
   ON (p.`id_product` = pa.`id_product`)
   '.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
   '.Product::sqlStock('p', 'product_attribute_shop', false, $context->shop).'
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
 ON (product_shop.`id_category_default` = cl.`id_category`
 AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
   LEFT JOIN `'._DB_PREFIX_.'product_lang` pl
 ON (p.`id_product` = pl.`id_product`
 AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').')
   LEFT JOIN `'._DB_PREFIX_.'image` i
 ON (i.`id_product` = p.`id_product`
 AND i.`cover` = 1)
   LEFT JOIN `'._DB_PREFIX_.'image_lang` il
 ON (i.`id_image` = il.`id_image`
 AND il.`id_lang` = '.(int)$id_lang.')
   LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr
 ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`
 AND tr.`id_country` = '.(int)$context->country->id.'
 AND tr.`id_state` = 0
 AND tr.`zipcode_from` = 0)
   LEFT JOIN `'._DB_PREFIX_.'tax` t
 ON (t.`id_tax` = tr.`id_tax`)
   LEFT JOIN `'._DB_PREFIX_.'tax_lang` tl
 ON (t.`id_tax` = tl.`id_tax`
 AND tl.`id_lang` = '.(int)$id_lang.')
   LEFT JOIN `'._DB_PREFIX_.'manufacturer` m
 ON m.`id_manufacturer` = p.`id_manufacturer`
   WHERE product_shop.`id_shop` = '.(int)$context->shop->id.'
   AND ((product_attribute_shop.id_product_attribute IS NOT NULL OR pa.id_product_attribute IS NULL)
 OR (product_attribute_shop.id_product_attribute IS NULL AND pa.default_on=1))
 AND cp.`id_category` = '.(int)$this->id
 .($active ? ' AND product_shop.`active` = 1' : '')
 .($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '')
 .($id_supplier ? ' AND p.id_supplier = '.(int)$id_supplier : '');
 if ($random === true)
 {
  $sql .= ' ORDER BY RAND()';
  $sql .= ' LIMIT 0, '.(int)$random_number_products;
 }
 else
  $sql .= ' ORDER BY '.(isset($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).'
  LIMIT '.(((int)$p - 1) * (int)$n).','.(int)$n;
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
 if ($order_by == 'orderprice')
  Tools::orderbyPrice($result, $order_way);
 if (!$result)
  return false;
 /* Modify SQL result */
 return Product::getProductsProperties($id_lang, $result);
}
/**
  * Return main categories
  *
  * @param integer $id_lang Language ID
  * @param boolean $active return only active categories
  * @return array categories
  */
public static function getHomeCategories($id_lang, $active = true)
{
 return self::getChildren(Configuration::get('PS_HOME_CATEGORY'), $id_lang, $active);
}
public static function getRootCategory($id_lang = null, Shop $shop = null)
{
 $context = Context::getContext();
 if (is_null($id_lang))
  $id_lang = $context->language->id;
 if (!$shop)
  if (Shop::isFeatureActive() && Shop::getContext() != Shop::CONTEXT_SHOP)
   $shop = new Shop(Configuration::get('PS_SHOP_DEFAULT'));
  else
   $shop = $context->shop;
 else
  return new Category($shop->getCategory(), $id_lang);
 $is_more_than_one_root_category = count(Category::getCategoriesWithoutParent()) > 1;
 if ((!Shop::isFeatureActive() && $is_more_than_one_root_category) ||
  Shop::isFeatureActive() && $is_more_than_one_root_category && Shop::getContext() != Shop::CONTEXT_SHOP)
  $category = Category::getTopCategory($id_lang);
 else
  $category = new Category($shop->getCategory(), $id_lang);
 return $category;
}
/**
 *
 * @param int $id_parent
 * @param int $id_lang
 * @param bool $active
 * @return array
 */
public static function getChildren($id_parent, $id_lang, $active = true, $id_shop = false)
{
 if (!Validate::isBool($active))
  die(Tools::displayError());
 $query = 'SELECT c.`id_category`, cl.`name`, cl.`link_rewrite`, category_shop.`id_shop`
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category`'.Shop::addSqlRestrictionOnLang('cl').')
 '.Shop::addSqlAssociation('category', 'c').'
 WHERE `id_lang` = '.(int)$id_lang.'
 AND c.`id_parent` = '.(int)$id_parent.'
 '.($active ? 'AND `active` = 1' : '').'
 GROUP BY c.`id_category`
 ORDER BY category_shop.`position` ASC';
 return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
}
/**
 * Return an array of all children of the current category
 *
 * @param int $id_lang
 * @return Collection
 */
public function getAllChildren($id_lang = null)
{
 if (is_null($id_lang))
  $id_lang = Context::getContext()->language->id;
 $categories = new Collection('Category', $id_lang);
 $categories->where('nleft', '>', $this->nleft);
 $categories->where('nright', '<', $this->nright);
 return $categories;
}
/**
 * This method allow to return children categories with the number of sub children selected for a product
 *
 * @param int $id_parent
 * @param int $id_product
 * @param int $id_lang
 * @return array
 */
public static function getChildrenWithNbSelectedSubCat($id_parent, $selected_cat, $id_lang, Shop $shop = null, $use_shop_context = true)
{
 if (!$shop)
  $shop = Context::getContext()->shop;
 $id_shop = $shop->id ? $shop->id : Configuration::get('PS_SHOP_DEFAULT');
 $selected_cat = explode(',', str_replace(' ', '', $selected_cat));
 $sql = 'SELECT c.`id_category`, c.`level_depth`, cl.`name`, IF((
  SELECT COUNT(*)
  FROM `'._DB_PREFIX_.'category` c2
  WHERE c2.`id_parent` = c.`id_category`
 ) > 0, 1, 0) AS has_children, '.($selected_cat ? '(
  SELECT count(c3.`id_category`)
  FROM `'._DB_PREFIX_.'category` c3
  WHERE c3.`nleft` > c.`nleft`
  AND c3.`nright` < c.`nright`
  AND c3.`id_category`  IN ('.implode(',', array_map('intval', $selected_cat)).')
 )' : '0').' AS nbSelectedSubCat
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` '.Shop::addSqlRestrictionOnLang('cl', $id_shop).')';
 if (Shop::getContext() == Shop::CONTEXT_SHOP && $use_shop_context)
  $sql .= ' LEFT JOIN `'._DB_PREFIX_.'category_shop` cs ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')';
 $sql .= ' WHERE `id_lang` = '.(int)$id_lang;
 if (Shop::getContext() == Shop::CONTEXT_SHOP && $use_shop_context)
  $sql .= ' AND cs.`id_shop` = '.(int)$shop->id;
 $sql .= ' AND c.`id_parent` = '.(int)$id_parent;
 if (Shop::getContext() == Shop::CONTEXT_SHOP && $use_shop_context)
  $sql .= ' ORDER BY cs.`position` ASC';
 return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
}
/**
  * Copy products from a category to another
  *
  * @param integer $id_old Source category ID
  * @param boolean $id_new Destination category ID
  * @return boolean Duplication result
  */
public static function duplicateProductCategories($id_old, $id_new)
{
 $sql = 'SELECT `id_category`
   FROM `'._DB_PREFIX_.'category_product`
   WHERE `id_product` = '.(int)$id_old;
 $result = Db::getInstance()->executeS($sql);
 $row = array();
 if ($result)
  foreach ($result as $i)
   $row[] = '('.implode(', ', array((int)$id_new, $i['id_category'], '(SELECT tmp.max + 1 FROM (
 SELECT MAX(cp.`position`) AS max
 FROM `'._DB_PREFIX_.'category_product` cp
 WHERE cp.`id_category`='.(int)$i['id_category'].') AS tmp)'
   )).')';
 $flag = Db::getInstance()->execute('
  INSERT INTO `'._DB_PREFIX_.'category_product` (`id_product`, `id_category`, `position`)
  VALUES '.implode(',', $row)
 );
 return $flag;
}
/**
  * Check if category can be moved in another one.
 * The category cannot be moved in a child category.
  *
 * @param integer $id_category current category
  * @param integer $id_parent Parent candidate
  * @return boolean Parent validity
  */
public static function checkBeforeMove($id_category, $id_parent)
{
 if ($id_category == $id_parent) return false;
 if ($id_parent == Configuration::get('PS_HOME_CATEGORY')) return true;
 $i = (int)$id_parent;
 while (42)
 {
  $result = Db::getInstance()->getRow('SELECT `id_parent` FROM `'._DB_PREFIX_.'category` WHERE `id_category` = '.(int)$i);
  if (!isset($result['id_parent'])) return false;
  if ($result['id_parent'] == $id_category) return false;
  if ($result['id_parent'] == Configuration::get('PS_HOME_CATEGORY')) return true;
  $i = $result['id_parent'];
 }
}
public static function getLinkRewrite($id_category, $id_lang)
{
 if (!Validate::isUnsignedId($id_category) || !Validate::isUnsignedId($id_lang))
  return false;
 if (isset(self::$_links[$id_category.'-'.$id_lang]))
  return self::$_links[$id_category.'-'.$id_lang];
 $result = Db::getInstance()->getRow('
  SELECT cl.`link_rewrite`
  FROM `'._DB_PREFIX_.'category_lang` cl
  WHERE `id_lang` = '.(int)$id_lang.'
  '.Shop::addSqlRestrictionOnLang('cl').'
  AND cl.`id_category` = '.(int)$id_category
 );
 self::$_links[$id_category.'-'.$id_lang] = $result['link_rewrite'];
 return $result['link_rewrite'];
}
public function getLink(Link $link = null)
{
 if (!$link)
  $link = Context::getContext()->link;
 return $link->getCategoryLink($this, $this->link_rewrite);
}
public function getName($id_lang = null)
{
 if (!$id_lang)
 {
  if (isset($this->name[Context::getContext()->language->id]))
   $id_lang = Context::getContext()->language->id;
  else
   $id_lang = (int)Configuration::get('PS_LANG_DEFAULT');
 }
 return isset($this->name[$id_lang]) ? $this->name[$id_lang] : '';
}
/**
  * Light back office search for categories
  *
  * @param integer $id_lang Language ID
  * @param string $query Searched string
  * @param boolean $unrestricted allows search without lang and includes first category and exact match
  * @return array Corresponding categories
  */
public static function searchByName($id_lang, $query, $unrestricted = false)
{
 if ($unrestricted === true)
  return Db::getInstance()->getRow('
   SELECT c.*, cl.*
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
 ON (c.`id_category` = cl.`id_category`'.Shop::addSqlRestrictionOnLang('cl').')
   WHERE `name` LIKE \''.pSQL($query).'\'
  ');
 else
  return Db::getInstance()->executeS('
   SELECT c.*, cl.*
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
 ON (c.`id_category` = cl.`id_category`
 AND `id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
   WHERE `name` LIKE \'%'.pSQL($query).'%\'
   AND c.`id_category` != '.(int)Configuration::get('PS_HOME_CATEGORY')
  );
}
/**
  * Retrieve category by name and parent category id
  *
  * @param integer $id_lang Language ID
  * @param string  $category_name Searched category name
  * @param integer $id_parent_category parent category ID
  * @return array Corresponding category
  */
public static function searchByNameAndParentCategoryId($id_lang, $category_name, $id_parent_category)
{
 return Db::getInstance()->getRow('
  SELECT c.*, cl.*
  FROM `'._DB_PREFIX_.'category` c
  LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
   ON (c.`id_category` = cl.`id_category`
   AND `id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
  WHERE `name`  LIKE \''.pSQL($category_name).'\'
   AND c.`id_category` != '.(int)Configuration::get('PS_HOME_CATEGORY').'
   AND c.`id_parent` = '.(int)$id_parent_category
 );
}
/**
 * Get Each parent category of this category until the root category
 *
 * @param integer $id_lang Language ID
 * @return array Corresponding categories
 */
public function getParentsCategories($id_lang = null)
{
 $context = Context::getContext();
 if (is_null($id_lang))
  $id_lang = $context->language->id;
 $categories = null;
 $id_current = $this->id;
 if (count(Category::getCategoriesWithoutParent()) > 1 && Configuration::get('PS_MULTISHOP_FEATURE_ACTIVE') && count(Shop::getShops(true, null, true)) != 1)
  $context->shop->id_category = Category::getTopCategory()->id;
 elseif (!$context->shop->id)
  $context->shop = new Shop(Configuration::get('PS_SHOP_DEFAULT'));
 $id_shop = $context->shop->id;
 while (true)
 {
  $sql = '
  SELECT c.*, cl.*
  FROM `'._DB_PREFIX_.'category` c
  LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
   ON (c.`id_category` = cl.`id_category`
   AND `id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')';
  if (Shop::isFeatureActive() && Shop::getContext() == Shop::CONTEXT_SHOP)
   $sql .= '
  LEFT JOIN `'._DB_PREFIX_.'category_shop` cs
   ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')';
  $sql .= '
  WHERE c.`id_category` = '.(int)$id_current;
  if (Shop::isFeatureActive() && Shop::getContext() == Shop::CONTEXT_SHOP)
   $sql .= '
   AND cs.`id_shop` = '.(int)$context->shop->id;
  $root_category = Category::getRootCategory();
  if (Shop::isFeatureActive() && Shop::getContext() == Shop::CONTEXT_SHOP &&
   (!Tools::isSubmit('id_category') ||
 (int)Tools::getValue('id_category') == (int)$root_category->id_category ||
 (int)$root_category->id_category == (int)$context->shop->id_category))
   $sql .= '
 AND c.`id_parent` != 0';
  $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
  if (isset($result[0]))
   $categories[] = $result[0];
  else if (!$categories)
   $categories = array();
  if (!$result || ($result[0]['id_category'] == $context->shop->id_category))
   return $categories;
  $id_current = $result[0]['id_parent'];
 }
}
/**
* Specify if a category already in base
*
* @param $id_category Category id
* @return boolean
*/
public static function categoryExists($id_category)
{
 $row = Db::getInstance()->getRow('
 SELECT `id_category`
 FROM '._DB_PREFIX_.'category c
 WHERE c.`id_category` = '.(int)$id_category);
 return isset($row['id_category']);
}
public function cleanGroups()
{
 Db::getInstance()->execute('DELETE FROM `'._DB_PREFIX_.'category_group` WHERE `id_category` = '.(int)$this->id);
}

public function cleanAssoProducts()
{
 Db::getInstance()->execute('DELETE FROM `'._DB_PREFIX_.'category_product` WHERE `id_category` = '.(int)$this->id);
}
public function addGroups($groups)
{
 foreach ($groups as $group)
 {
  $row = array('id_category' => (int)$this->id, 'id_group' => (int)$group);
  Db::getInstance()->insert('category_group', $row);
 }
}
public function getGroups()
{
 $groups = array();
 $result = Db::getInstance()->executeS('
  SELECT cg.`id_group`
  FROM '._DB_PREFIX_.'category_group cg
  WHERE cg.`id_category` = '.(int)$this->id
 );
 foreach ($result as $group)
  $groups[] = $group['id_group'];
 return $groups;
}
public function addGroupsIfNoExist($id_group)
{
 $groups = $this->getGroups();
 if (!in_array((int)$id_group, $groups))
  return $this->addGroups(array((int)$id_group));
 else
  return false;
}
/**
 * checkAccess return true if id_customer is in a group allowed to see this category.
 *
 * @param mixed $id_customer
 * @access public
 * @return boolean true if access allowed for customer $id_customer
 */
public function checkAccess($id_customer)
{
 if (!$id_customer)
 {
  $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
   SELECT ctg.`id_group`
   FROM '._DB_PREFIX_.'category_group ctg
   WHERE ctg.`id_category` = '.(int)$this->id.' AND ctg.`id_group` = 1
  ');
 } else {
  $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
   SELECT ctg.`id_group`
   FROM '._DB_PREFIX_.'category_group ctg
   INNER JOIN '._DB_PREFIX_.'customer_group cg on (cg.`id_group` = ctg.`id_group` AND cg.`id_customer` = '.(int)$id_customer.')
   WHERE ctg.`id_category` = '.(int)$this->id
  );
 }
 if ($result && isset($result['id_group']) && $result['id_group'])
  return true;
 return false;
}
/**
 * Update customer groups associated to the object
 *
 * @param array $list groups
 */
public function updateGroup($list)
{
 $this->cleanGroups();
 if ($list && !empty($list))
  $this->addGroups($list);
 else
  $this->addGroups(array(Configuration::get('PS_UNIDENTIFIED_GROUP'), Configuration::get('PS_GUEST_GROUP'), Configuration::get('PS_CUSTOMER_GROUP')));
}
public static function setNewGroupForHome($id_group)
{
 if (!(int)$id_group)
  return false;
 return Db::getInstance()->execute('
  INSERT INTO `'._DB_PREFIX_.'category_group`
  VALUES ('.(int)Context::getContext()->shop->getCategory().', '.(int)$id_group.')
 ');
}
public function updatePosition($way, $position)
{
 $id = Context::getContext()->shop->id;
 $id_shop = $id ? $id: Configuration::get('PS_SHOP_DEFAULT');
 if (!$res = Db::getInstance()->executeS('
  SELECT cp.`id_category`, cs.`position`, cp.`id_parent`
  FROM `'._DB_PREFIX_.'category` cp
  LEFT JOIN `'._DB_PREFIX_.'category_shop` cs
   ON (cp.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
  WHERE cp.`id_parent` = '.(int)$this->id_parent.'
  ORDER BY cs.`position` ASC'
 ))
  return false;
 foreach ($res as $category)
  if ((int)$category['id_category'] == (int)$this->id)
   $moved_category = $category;
 if (!isset($moved_category) || !isset($position))
  return false;
 // < and > statements rather than BETWEEN operator
 // since BETWEEN is treated differently according to databases
 $result = (Db::getInstance()->execute('
  UPDATE `'._DB_PREFIX_.'category_shop` cs
  LEFT JOIN `'._DB_PREFIX_.'category` c
   ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
  SET cs.`position`= cs.`position` '.($way ? '- 1' : '+ 1').'
  WHERE cs.`position`
  '.($way
   ? '> '.(int)$moved_category['position'].' AND cs.`position` <= '.(int)$position
   : '< '.(int)$moved_category['position'].' AND cs.`position` >= '.(int)$position).'
  AND c.`id_parent`='.(int)$moved_category['id_parent'])
 && Db::getInstance()->execute('
  UPDATE `'._DB_PREFIX_.'category_shop` cs
  LEFT JOIN `'._DB_PREFIX_.'category` c
   ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
  SET cs.`position` = '.(int)$position.'
  WHERE c.`id_parent` = '.(int)$moved_category['id_parent'].'
  AND c.`id_category`='.(int)$moved_category['id_category']));
 Hook::exec('actionCategoryUpdate');
 return $result;
}
/**
 * cleanPositions keep order of category in $id_category_parent,
 * but remove duplicate position. Should not be used if positions
 * are clean at the beginning !
 *
 * @param mixed $id_category_parent
 * @return boolean true if succeed
 */
public static function cleanPositions($id_category_parent = null)
{
 if ($id_category_parent === null)
  return;
 $return = true;
 $id = Context::getContext()->shop->id;
 $id_shop = $id ? $id : Configuration::get('PS_SHOP_DEFAULT');
 $result = Db::getInstance()->executeS('
  SELECT c.`id_category`
  FROM `'._DB_PREFIX_.'category` c
  LEFT JOIN `'._DB_PREFIX_.'category_shop` cs
   ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
  WHERE c.`id_parent` = '.(int)$id_category_parent.'
  ORDER BY cs.`position`
 ');
 $count = count($result);
 for ($i = 0; $i < $count; $i++)
 {
  $sql = '
   UPDATE `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_shop` cs
 ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
   SET cs.`position` = '.(int)$i.'
   WHERE c.`id_parent` = '.(int)$id_category_parent.'
   AND c.`id_category` = '.(int)$result[$i]['id_category'];
  $return &= Db::getInstance()->execute($sql);
 }
 return $return;
}
/** this function return the number of category + 1 having $id_category_parent as parent.
 *
 * @todo rename that function to make it understandable (getNewLastPosition for example)
 * @param int $id_category_parent the parent category
 * @param int $id_shop
 * @return int
 */
public static function getLastPosition($id_category_parent, $id_shop)
{
 return (int)(Db::getInstance()->getValue('
 SELECT MAX(cs.`position`)
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_shop` cs
  ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
 WHERE c.`id_parent` = '.(int)$id_category_parent) + 1);
}
public static function getUrlRewriteInformations($id_category)
{
 return Db::getInstance()->executeS('
  SELECT l.`id_lang`, c.`link_rewrite`
  FROM `'._DB_PREFIX_.'category_lang` AS c
  LEFT JOIN  `'._DB_PREFIX_.'lang` AS l ON c.`id_lang` = l.`id_lang`
  WHERE c.`id_category` = '.(int)$id_category.'
  AND l.`active` = 1'
 );
}
/**
 * Return nleft and nright fields for a given category
 *
 * @since 1.5.0
 * @param int $id
 * @return array
 */
public static function getInterval($id)
{
 $sql = 'SELECT nleft, nright, level_depth
   FROM '._DB_PREFIX_.'category
   WHERE id_category = '.(int)$id;
 if (!$result = Db::getInstance()->getRow($sql))
  return false;
 return $result;
}
/**
 * Check if current category is a child of shop root category
 *
 * @since 1.5.0
 * @param Shop $shop
 * @return bool
 */
public function inShop(Shop $shop = null)
{
 if (!$shop)
  $shop = Context::getContext()->shop;
 if (!$interval = Category::getInterval($shop->getCategory()))
  return false;
 return ($this->nleft >= $interval['nleft'] && $this->nright <= $interval['nright']);
}

public static function inShopStatic($id_category, Shop $shop = null)
{
 if (!$shop || !is_object($shop))
  $shop = Context::getContext()->shop;
 if (!$interval = Category::getInterval($shop->getCategory()))
  return false;
 $row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('SELECT nleft, nright FROM `'._DB_PREFIX_.'category` WHERE id_category = '.(int)$id_category);
 return ($row['nleft'] >= $interval['nleft'] && $row['nright'] <= $interval['nright']);
}
public function getChildrenWs()
{
 $id = Context::getContext()->shop->id;
 $id_shop = $id ? $id : Configuration::get('PS_SHOP_DEFAULT');
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
 SELECT c.`id_category` as id
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_shop` cs
  ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
 WHERE c.`id_parent` = '.(int)$this->id.'
 AND c.`active` = 1
 ORDER BY cs.`position` ASC');
 return $result;
}
public function getProductsWs()
{
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
 SELECT cp.`id_product` as id
 FROM `'._DB_PREFIX_.'category_product` cp
 WHERE cp.`id_category` = '.(int)$this->id.'
 ORDER BY `position` ASC');
 return $result;
}
/**
 * Search for another category with the same parent and the same position
 *
 * @return array first category found
 */
public function getDuplicatePosition()
{
 $id = Context::getContext()->shop->id;
 $id_shop = $id ? $id : Configuration::get('PS_SHOP_DEFAULT');
 return Db::getInstance()->getRow('
 SELECT c.`id_category` as id
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_shop` cs
  ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
 WHERE c.`id_parent` = '.(int)$this->id_parent.'
 AND cs.`position` = '.(int)$this->position.'
 AND c.`id_category` != '.(int)$this->id);
}
public function getWsNbProductsRecursive()
{
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
  SELECT COUNT(distinct(id_product)) as nb_product_recursive
  FROM  `'._DB_PREFIX_.'category_product`
  WHERE id_category IN (
   SELECT c2.id_category
   FROM `'._DB_PREFIX_.'category` c2
   '.Shop::addSqlAssociation('category', 'c2').'
   WHERE c2.nleft > '.(int)$this->nleft.'
 AND c2.nright < '.(int)$this->nright.'
 AND c2.active = 1
   UNION SELECT '.(int)$this->id.'
  )
 ');
 if (!$result)
  return -1;
 return $result[0]['nb_product_recursive'];
}
/**
 *
 * @param Array $ids_category
 * @param int $id_lang
 * @return Array
 */
public static function getCategoryInformations($ids_category, $id_lang = null)
{
 if ($id_lang === null)
  $id_lang = Context::getContext()->language->id;
 if (!is_array($ids_category) || !count($ids_category))
  return;
 $categories = array();
 $results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
  SELECT c.`id_category`, cl.`name`, cl.`link_rewrite`, cl.`id_lang`
  FROM `'._DB_PREFIX_.'category` c
  LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category`'.Shop::addSqlRestrictionOnLang('cl').')
  '.Shop::addSqlAssociation('category', 'c').'
  WHERE cl.`id_lang` = '.(int)$id_lang.'
  AND c.`id_category` IN ('.implode(',', array_map('intval', $ids_category)).')
 ');
 foreach ($results as $category)
  $categories[$category['id_category']] = $category;
 return $categories;
}
/**
 * @param $id_shop
 * @return bool
 */
public function isParentCategoryAvailable($id_shop)
{
 $id = Context::getContext()->shop->id;
 $id_shop = $id ? $id : Configuration::get('PS_SHOP_DEFAULT');
 return (bool)Db::getInstance()->getValue('
 SELECT c.`id_category`
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_shop` cs
  ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
 WHERE cs.`id_shop` = '.(int)$id_shop.'
 AND c.`id_parent` = '.(int)$this->id_parent);
}
/**
 * Add association between shop and cateogries
 * @param int $id_shop
 * @return bool
 */
public function addShop($id_shop)
{
 $data = array();
 if (!$id_shop)
 {
  foreach (Shop::getShops(false) as $shop)
   if (!$this->existsInShop($shop['id_shop']))
 $data[] = array(
  'id_category' => (int)$this->id,
  'id_shop' => (int)$shop['id_shop'],
 );
 }
 else if (!$this->existsInShop($id_shop))
  $data[] = array(
   'id_category' => (int)$this->id,
   'id_shop' => (int)$id_shop,
  );
 return Db::getInstance()->insert('category_shop', $data);
}
public static function getRootCategories($id_lang = null, $active = true)
{
 if (!$id_lang)
  $id_lang = Context::getContext()->language->id;
 return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
 SELECT DISTINCT(c.`id_category`), cl.`name`
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (cl.`id_category` = c.`id_category` AND cl.`id_lang`='.(int)$id_lang.')
 WHERE `is_root_category` = 1
 '.(($active) ? 'AND `active` = 1': ''));
}
public static function getCategoriesWithoutParent()
{
 return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
 SELECT DISTINCT c.*
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND cl.`id_lang` = '.(int)Context::getContext()->language->id.')
 WHERE `level_depth` = 1
 ');
}
public function isRootCategoryForAShop()
{
 return (bool)Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
 SELECT `id_shop`
 FROM `'._DB_PREFIX_.'shop`
 WHERE `id_category` = '.(int)$this->id);
}
/**
 * @static
 * @param null $id_lang
 * @return Category
 */
public static function getTopCategory($id_lang = null)
{
 if (is_null($id_lang))
  $id_lang = Context::getContext()->language->id;
 $id_category = Db::getInstance()->getValue('
 SELECT `id_category`
 FROM `'._DB_PREFIX_.'category`
 WHERE `id_parent` = 0');
 return new Category($id_category, $id_lang);
}
public function addPosition($position, $id_shop = null)
{
 $return = true;
 if (is_null($id_shop))
 {
  if (Shop::getContext() != Shop::CONTEXT_SHOP)
   foreach (Shop::getContextListShopID() as $id_shop)
 $return &= Db::getInstance()->execute('
  INSERT INTO `'._DB_PREFIX_.'category_shop` (`id_category`, `id_shop`, `position`) VALUES
  ('.(int)$this->id.', '.(int)$id_shop.', '.(int)$position.')
  ON DUPLICATE KEY UPDATE `position` = '.(int)$position);
  else
  {
   $id = Context::getContext()->shop->id;
   $id_shop = $id ? $id : Configuration::get('PS_SHOP_DEFAULT');
   $return &= Db::getInstance()->execute('
 INSERT INTO `'._DB_PREFIX_.'category_shop` (`id_category`, `id_shop`, `position`) VALUES
 ('.(int)$this->id.', '.(int)$id_shop.', '.(int)$position.')
 ON DUPLICATE KEY UPDATE `position` = '.(int)$position);
  }
 }
 else
  $return &= Db::getInstance()->execute('
  INSERT INTO `'._DB_PREFIX_.'category_shop` (`id_category`, `id_shop`, `position`) VALUES
  ('.(int)$this->id.', '.(int)$id_shop.', '.(int)$position.')
  ON DUPLICATE KEY UPDATE `position` = '.(int)$position);
 return $return;
}
public static function getShopsByCategory($id_category)
{
 return Db::getInstance()->executeS('
  SELECT `id_shop`
  FROM `'._DB_PREFIX_.'category_shop`
  WHERE `id_category` = '.(int)$id_category);
}
/**
* Update categories for a shop
*
* @param string $categories Categories list to associate a shop
* @param string $id_shop Categories list to associate a shop
* @return array Update/insertion result
*/
public static function updateFromShop($categories, $id_shop)
{
 $shop = new Shop($id_shop);
 // if array is empty or if the default category is not selected, return false
 if (empty($categories) || !in_array($shop->id_category, $categories))
  return false;
 // delete categories for this shop
 Category::deleteCategoriesFromShop($id_shop);
 // and add $categories to this shop
 return Category::addToShop($categories, $id_shop);
}
/**
 * Delete category from shop $id_shop
 * @param int $id_shop
 * @return bool
 */
public function deleteFromShop($id_shop)
{
 return Db::getInstance()->execute(
  'DELETE FROM `'._DB_PREFIX_.'category_shop`
  WHERE `id_shop` = '.(int)$id_shop.'
  AND id_category = '.(int)$this->id.''
 );
}
/**
 * Delete every categories
 * @return bool
 */
public static function deleteCategoriesFromShop($id_shop)
{
 return Db::getInstance()->execute('
 DELETE FROM `'._DB_PREFIX_.'category_shop` WHERE `id_shop` = '.(int)$id_shop.'
 ');
}
/**
 * Add some categories to a shop
 * @param array $categories
 * @return bool
 */
public static function addToShop(array $categories, $id_shop)
{
 if (!is_array($categories))
  return false;
 $sql = '
 INSERT INTO `'._DB_PREFIX_.'category_shop` (`id_category`, `id_shop`) VALUES';
 $tab_categories = array();
 foreach ($categories as $id_category)
 {
  $tab_categories[] = new Category($id_category);
  $sql .= '("'.(int)$id_category.'", "'.(int)$id_shop.'"),';
 }
 // removing last comma to avoid SQL error
 $sql = substr($sql, 0, strlen($sql) - 1);
 $return = Db::getInstance()->execute($sql);
 // we have to update position for every new entries
 foreach ($tab_categories as $category)
  $category->addPosition(Category::getLastPosition($category->id_parent, $id_shop), $id_shop);
 return $return;
}
public function existsInShop($id_shop)
{
 return (bool)Db::getInstance()->getValue('
  SELECT `id_category`
  FROM `'._DB_PREFIX_.'category_shop`
  WHERE `id_category` = '.(int)$this->id.'
  AND `id_shop` = '.(int)$id_shop);
}
}

Baltimore · October 24, 2012

Thank you very much Jessie!! I get show the products only one time in categories and home page. Now the products appears duplicated only in Top sellers, this is very strange. I believe that i made the actualization in bad way. But thank you again, now i have one less problem!

Jessie K. Jepsen · October 24, 2012

Thank you very much Jessie!! I get show the products only one time in categories and home page. Now the products appears duplicated only in Top sellers, this is very strange. I believe that i made the actualization in bad way. But thank you again, now i have one less problem!

I don't use the top sellers, so I am sorry.. I will look into it and see if I can figure anything out for you.

If someone else does first please post it for Baltimore..

acfccfsignals · October 25, 2012

I've had a play by removing resetting and reinstalling but have so far been unable to fix it. I will see if I have a backup of a working file but won't be able to test it until Friday.

Jessie K. Jepsen · October 30, 2012

Also, if you replaced your category.php with the code above and it fixed it, but sometimes on searches its still doing only on some items and not all, replace your /Classes/Search.php code with the following below and it might fix it like it did for me.

ALWAYS BACK UP YOUR FILES AND DATABASE FIRST

<?php
/*
* 2007-2012 PrestaShop
*
* NOTICE OF LICENSE
*
* This source file is subject to the Open Software License (OSL 3.0)
* that is bundled with this package in the file LICENSE.txt.
* It is also available through the world-wide-web at this URL:
* http://opensource.org/licenses/osl-3.0.php
* If you did not receive a copy of the license and are unable to
* obtain it through the world-wide-web, please send an email
* to [email protected] so we can send you a copy immediately.
*
* DISCLAIMER
*
* Do not edit or add to this file if you wish to upgrade PrestaShop to newer
* versions in the future. If you wish to customize PrestaShop for your
* needs please refer to http://www.prestashop.com for more information.
*
*  @author PrestaShop SA <[email protected]>
*  @copyright  2007-2012 PrestaShop SA
*  @version  Release: $Revision: 7489 $
*  @license    http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
*  International Registered Trademark & Property of PrestaShop SA
*/
define('PS_SEARCH_MAX_WORD_LENGTH', 15);
/* Copied from Drupal search module, except for \x{0}-\x{2f} that has been replaced by \x{0}-\x{2c}\x{2e}-\x{2f} in order to keep the char '-' */
define('PREG_CLASS_SEARCH_EXCLUDE',
'\x{0}-\x{2c}\x{2e}-\x{2f}\x{3a}-\x{40}\x{5b}-\x{60}\x{7b}-\x{bf}\x{d7}\x{f7}\x{2b0}-'.
'\x{385}\x{387}\x{3f6}\x{482}-\x{489}\x{559}-\x{55f}\x{589}-\x{5c7}\x{5f3}-'.
'\x{61f}\x{640}\x{64b}-\x{65e}\x{66a}-\x{66d}\x{670}\x{6d4}\x{6d6}-\x{6ed}'.
'\x{6fd}\x{6fe}\x{700}-\x{70f}\x{711}\x{730}-\x{74a}\x{7a6}-\x{7b0}\x{901}-'.
'\x{903}\x{93c}\x{93e}-\x{94d}\x{951}-\x{954}\x{962}-\x{965}\x{970}\x{981}-'.
'\x{983}\x{9bc}\x{9be}-\x{9cd}\x{9d7}\x{9e2}\x{9e3}\x{9f2}-\x{a03}\x{a3c}-'.
'\x{a4d}\x{a70}\x{a71}\x{a81}-\x{a83}\x{abc}\x{abe}-\x{acd}\x{ae2}\x{ae3}'.
'\x{af1}-\x{b03}\x{b3c}\x{b3e}-\x{b57}\x{b70}\x{b82}\x{bbe}-\x{bd7}\x{bf0}-'.
'\x{c03}\x{c3e}-\x{c56}\x{c82}\x{c83}\x{cbc}\x{cbe}-\x{cd6}\x{d02}\x{d03}'.
'\x{d3e}-\x{d57}\x{d82}\x{d83}\x{dca}-\x{df4}\x{e31}\x{e34}-\x{e3f}\x{e46}-'.
'\x{e4f}\x{e5a}\x{e5b}\x{eb1}\x{eb4}-\x{ebc}\x{ec6}-\x{ecd}\x{f01}-\x{f1f}'.
'\x{f2a}-\x{f3f}\x{f71}-\x{f87}\x{f90}-\x{fd1}\x{102c}-\x{1039}\x{104a}-'.
'\x{104f}\x{1056}-\x{1059}\x{10fb}\x{10fc}\x{135f}-\x{137c}\x{1390}-\x{1399}'.
'\x{166d}\x{166e}\x{1680}\x{169b}\x{169c}\x{16eb}-\x{16f0}\x{1712}-\x{1714}'.
'\x{1732}-\x{1736}\x{1752}\x{1753}\x{1772}\x{1773}\x{17b4}-\x{17db}\x{17dd}'.
'\x{17f0}-\x{180e}\x{1843}\x{18a9}\x{1920}-\x{1945}\x{19b0}-\x{19c0}\x{19c8}'.
'\x{19c9}\x{19de}-\x{19ff}\x{1a17}-\x{1a1f}\x{1d2c}-\x{1d61}\x{1d78}\x{1d9b}-'.
'\x{1dc3}\x{1fbd}\x{1fbf}-\x{1fc1}\x{1fcd}-\x{1fcf}\x{1fdd}-\x{1fdf}\x{1fed}-'.
'\x{1fef}\x{1ffd}-\x{2070}\x{2074}-\x{207e}\x{2080}-\x{2101}\x{2103}-\x{2106}'.
'\x{2108}\x{2109}\x{2114}\x{2116}-\x{2118}\x{211e}-\x{2123}\x{2125}\x{2127}'.
'\x{2129}\x{212e}\x{2132}\x{213a}\x{213b}\x{2140}-\x{2144}\x{214a}-\x{2b13}'.
'\x{2ce5}-\x{2cff}\x{2d6f}\x{2e00}-\x{3005}\x{3007}-\x{303b}\x{303d}-\x{303f}'.
'\x{3099}-\x{309e}\x{30a0}\x{30fb}\x{30fd}\x{30fe}\x{3190}-\x{319f}\x{31c0}-'.
'\x{31cf}\x{3200}-\x{33ff}\x{4dc0}-\x{4dff}\x{a015}\x{a490}-\x{a716}\x{a802}'.
'\x{E000}-\x{F8FF}\x{FB29}\x{FD3E}-\x{FD3F}\x{FDFC}-\x{FDFD}'.
'\x{fd3f}\x{fdfc}-\x{fe6b}\x{feff}-\x{ff0f}\x{ff1a}-\x{ff20}\x{ff3b}-\x{ff40}'.
'\x{ff5b}-\x{ff65}\x{ff70}\x{ff9e}\x{ff9f}\x{ffe0}-\x{fffd}');
define('PREG_CLASS_NUMBERS',
'\x{30}-\x{39}\x{b2}\x{b3}\x{b9}\x{bc}-\x{be}\x{660}-\x{669}\x{6f0}-\x{6f9}'.
'\x{966}-\x{96f}\x{9e6}-\x{9ef}\x{9f4}-\x{9f9}\x{a66}-\x{a6f}\x{ae6}-\x{aef}'.
'\x{b66}-\x{b6f}\x{be7}-\x{bf2}\x{c66}-\x{c6f}\x{ce6}-\x{cef}\x{d66}-\x{d6f}'.
'\x{e50}-\x{e59}\x{ed0}-\x{ed9}\x{f20}-\x{f33}\x{1040}-\x{1049}\x{1369}-'.
'\x{137c}\x{16ee}-\x{16f0}\x{17e0}-\x{17e9}\x{17f0}-\x{17f9}\x{1810}-\x{1819}'.
'\x{1946}-\x{194f}\x{2070}\x{2074}-\x{2079}\x{2080}-\x{2089}\x{2153}-\x{2183}'.
'\x{2460}-\x{249b}\x{24ea}-\x{24ff}\x{2776}-\x{2793}\x{3007}\x{3021}-\x{3029}'.
'\x{3038}-\x{303a}\x{3192}-\x{3195}\x{3220}-\x{3229}\x{3251}-\x{325f}\x{3280}-'.
'\x{3289}\x{32b1}-\x{32bf}\x{ff10}-\x{ff19}');
define('PREG_CLASS_PUNCTUATION',
'\x{21}-\x{23}\x{25}-\x{2a}\x{2c}-\x{2f}\x{3a}\x{3b}\x{3f}\x{40}\x{5b}-\x{5d}'.
'\x{5f}\x{7b}\x{7d}\x{a1}\x{ab}\x{b7}\x{bb}\x{bf}\x{37e}\x{387}\x{55a}-\x{55f}'.
'\x{589}\x{58a}\x{5be}\x{5c0}\x{5c3}\x{5f3}\x{5f4}\x{60c}\x{60d}\x{61b}\x{61f}'.
'\x{66a}-\x{66d}\x{6d4}\x{700}-\x{70d}\x{964}\x{965}\x{970}\x{df4}\x{e4f}'.
'\x{e5a}\x{e5b}\x{f04}-\x{f12}\x{f3a}-\x{f3d}\x{f85}\x{104a}-\x{104f}\x{10fb}'.
'\x{1361}-\x{1368}\x{166d}\x{166e}\x{169b}\x{169c}\x{16eb}-\x{16ed}\x{1735}'.
'\x{1736}\x{17d4}-\x{17d6}\x{17d8}-\x{17da}\x{1800}-\x{180a}\x{1944}\x{1945}'.
'\x{2010}-\x{2027}\x{2030}-\x{2043}\x{2045}-\x{2051}\x{2053}\x{2054}\x{2057}'.
'\x{207d}\x{207e}\x{208d}\x{208e}\x{2329}\x{232a}\x{23b4}-\x{23b6}\x{2768}-'.
'\x{2775}\x{27e6}-\x{27eb}\x{2983}-\x{2998}\x{29d8}-\x{29db}\x{29fc}\x{29fd}'.
'\x{3001}-\x{3003}\x{3008}-\x{3011}\x{3014}-\x{301f}\x{3030}\x{303d}\x{30a0}'.
'\x{30fb}\x{fd3e}\x{fd3f}\x{fe30}-\x{fe52}\x{fe54}-\x{fe61}\x{fe63}\x{fe68}'.
'\x{fe6a}\x{fe6b}\x{ff01}-\x{ff03}\x{ff05}-\x{ff0a}\x{ff0c}-\x{ff0f}\x{ff1a}'.
'\x{ff1b}\x{ff1f}\x{ff20}\x{ff3b}-\x{ff3d}\x{ff3f}\x{ff5b}\x{ff5d}\x{ff5f}-'.
'\x{ff65}');
/**
* Matches all CJK characters that are candidates for auto-splitting
* (Chinese, Japanese, Korean).
* Contains kana and BMP ideographs.
*/
define('PREG_CLASS_CJK', '\x{3041}-\x{30ff}\x{31f0}-\x{31ff}\x{3400}-\x{4db5}\x{4e00}-\x{9fbb}\x{f900}-\x{fad9}');
class SearchCore
{
public static function sanitize($string, $id_lang, $indexation = false)
{
 $string = Tools::strtolower(strip_tags($string));
 $string = html_entity_decode($string, ENT_NOQUOTES, 'utf-8');
 $string = preg_replace('/(['.PREG_CLASS_NUMBERS.']+)['.PREG_CLASS_PUNCTUATION.']+(?=['.PREG_CLASS_NUMBERS.'])/u', '\1', $string);
 $string = preg_replace('/['.PREG_CLASS_SEARCH_EXCLUDE.']+/u', ' ', $string);
 if ($indexation)
  $string = preg_replace('/[._-]+/', '', $string);
 else
 {
  $string = preg_replace('/[._]+/', '', $string);
  $string = ltrim(preg_replace('/([^ ])-/', '$1', ' '.$string));
  $string = preg_replace('/[._]+/', '', $string);
  $string = preg_replace('/[^\s]-+/', '', $string);
 }
 $blacklist = Configuration::get('PS_SEARCH_BLACKLIST', $id_lang);
 if (!empty($blacklist))
 {
  $string = preg_replace('/(?<=\s)('.$blacklist.')(?=\s)/Su', '', $string);
  $string = preg_replace('/^('.$blacklist.')(?=\s)/Su', '', $string);
  $string = preg_replace('/(?<=\s)('.$blacklist.')$/Su', '', $string);
  $string = preg_replace('/^('.$blacklist.')$/Su', '', $string);
 }
 if (!$indexation)
 {
  $words = explode(' ', $string);
  $processed_words = array();
  // search for aliases for each word of the query
  foreach ($words as $word)
  {
   $alias = new Alias(null, $word);
   if (Validate::isLoadedObject($alias))
 $processed_words[] = $alias->search;
   else
 $processed_words[] = $word;
  }
  $string = implode(' ', $processed_words);
 }
 if ($indexation)
 {
  $minWordLen = (int)Configuration::get('PS_SEARCH_MINWORDLEN');
  if ($minWordLen > 1)
  {
   $minWordLen -= 1;
   $string = preg_replace('/(?<=\s)[^\s]{1,'.$minWordLen.'}(?=\s)/Su', ' ', $string);
   $string = preg_replace('/^[^\s]{1,'.$minWordLen.'}(?=\s)/Su', '', $string);
   $string = preg_replace('/(?<=\s)[^\s]{1,'.$minWordLen.'}$/Su', '', $string);
   $string = preg_replace('/^[^\s]{1,'.$minWordLen.'}$/Su', '', $string);
  }
 }
 $string = trim(preg_replace('/\s+/', ' ', $string));
 return $string;
}
public static function find($id_lang, $expr, $page_number = 1, $page_size = 1, $order_by = 'position',
 $order_way = 'desc', $ajax = false, $use_cookie = true, Context $context = null)
{
 if (!$context)
  $context = Context::getContext();
 $db = Db::getInstance(_PS_USE_SQL_SLAVE_);
 // Only use cookie if id_customer is not present
 if ($use_cookie)
  $id_customer = $context->customer->id;
 else
  $id_customer = 0;
 // TODO : smart page management
 if ($page_number < 1) $page_number = 1;
 if ($page_size < 1) $page_size = 1;
 if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way))
  return false;
 $intersect_array = array();
 $score_array = array();
 $words = explode(' ', Search::sanitize($expr, $id_lang));
 foreach ($words as $key => $word)
  if (!empty($word) && strlen($word) >= (int)Configuration::get('PS_SEARCH_MINWORDLEN'))
  {
   $word = str_replace('%', '\\%', $word);
   $word = str_replace('_', '\\_', $word);
   $intersect_array[] = 'SELECT si.id_product
 FROM '._DB_PREFIX_.'search_word sw
 LEFT JOIN '._DB_PREFIX_.'search_index si ON sw.id_word = si.id_word
 WHERE sw.id_lang = '.(int)$id_lang.'
  AND sw.id_shop = '.$context->shop->id.'
  AND sw.word LIKE
 '.($word[0] == '-'
  ? ' \''.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).'%\''
  : '\''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\''
 );
   if ($word[0] != '-')
 $score_array[] = 'sw.word LIKE \''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'';
  }
  else
   unset($words[$key]);
 if (!count($words))
  return ($ajax ? array() : array('total' => 0, 'result' => array()));
 $score = '';
 if (count($score_array))
  $score = ',(
   SELECT SUM(weight)
   FROM '._DB_PREFIX_.'search_word sw
   LEFT JOIN '._DB_PREFIX_.'search_index si ON sw.id_word = si.id_word
   WHERE sw.id_lang = '.(int)$id_lang.'
 AND sw.id_shop = '.$context->shop->id.'
 AND si.id_product = p.id_product
 AND ('.implode(' OR ', $score_array).')
  ) position';
 $sql = 'SELECT cp.`id_product`
   FROM `'._DB_PREFIX_.'category_group` cg
   INNER JOIN `'._DB_PREFIX_.'category_product` cp ON cp.`id_category` = cg.`id_category`
   INNER JOIN `'._DB_PREFIX_.'category` c ON cp.`id_category` = c.`id_category`
   INNER JOIN `'._DB_PREFIX_.'product` p ON cp.`id_product` = p.`id_product`
   '.Shop::addSqlAssociation('product', 'p', false).'
   WHERE c.`active` = 1
 AND product_shop.`active` = 1
 AND product_shop.`visibility` IN ("both", "search")
 AND product_shop.indexed = 1
 AND cg.`id_group` '.(!$id_customer ?  '= 1' : 'IN (
  SELECT id_group FROM '._DB_PREFIX_.'customer_group
  WHERE id_customer = '.(int)$id_customer.'
 )');
 $results = $db->executeS($sql);
 $eligible_products = array();
 foreach ($results as $row)
  $eligible_products[] = $row['id_product'];
 foreach ($intersect_array as $query)
 {
  $eligible_products2 = array();
  foreach ($db->executeS($query) as $row)
   $eligible_products2[] = $row['id_product'];
  $eligible_products = array_intersect($eligible_products, $eligible_products2);
  if (!count($eligible_products))
   return ($ajax ? array() : array('total' => 0, 'result' => array()));
 }
 $eligible_products = array_unique($eligible_products);
 $product_pool = '';
 foreach ($eligible_products as $id_product)
  if ($id_product)
   $product_pool .= (int)$id_product.',';
 if (empty($product_pool))
  return ($ajax ? array() : array('total' => 0, 'result' => array()));
 $product_pool = ((strpos($product_pool, ',') === false) ? (' = '.(int)$product_pool.' ') : (' IN ('.rtrim($product_pool, ',').') '));
 if ($ajax)
 {
  $sql = 'SELECT DISTINCT p.id_product, pl.name pname, cl.name cname,
  cl.link_rewrite crewrite, pl.link_rewrite prewrite '.$score.'
 FROM '._DB_PREFIX_.'product p
 INNER JOIN `'._DB_PREFIX_.'product_lang` pl ON (
  p.`id_product` = pl.`id_product`
  AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
 )
 '.Shop::addSqlAssociation('product', 'p').'
 INNER JOIN `'._DB_PREFIX_.'category_lang` cl ON (
  product_shop.`id_category_default` = cl.`id_category`
  AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').'
 )
 WHERE p.`id_product` '.$product_pool.'
 ORDER BY position DESC LIMIT 10';
  return $db->executeS($sql);
 }
 if (strpos($order_by, '.') > 0)
 {
  $order_by = explode('.', $order_by);
  $order_by = pSQL($order_by[0]).'.`'.pSQL($order_by[1]).'`';
 }
 $alias = '';
 if ($order_by == 'price')
  $alias = 'product_shop.';
 $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
   pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
   tax.`rate`, i.`id_image`, il.`legend`, m.`name` manufacturer_name '.$score.',
   DATEDIFF(
 p.`date_add`,
 DATE_SUB(
  NOW(),
  INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY
 )
   ) > 0 new
   FROM '._DB_PREFIX_.'product p
   '.Shop::addSqlAssociation('product', 'p').'
   INNER JOIN `'._DB_PREFIX_.'product_lang` pl ON (
 p.`id_product` = pl.`id_product`
 AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
   )
   LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`
 AND tr.`id_country` = '.(int)$context->country->id.'
 AND tr.`id_state` = 0)
   LEFT JOIN `'._DB_PREFIX_.'tax` tax ON (tax.`id_tax` = tr.`id_tax`)
   LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
   LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
   LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
   '.Product::sqlStock('p', 0).'
   WHERE p.`id_product` '.$product_pool.'
   '.($order_by ? 'ORDER BY  '.$alias.$order_by : '').($order_way ? ' '.$order_way : '').'
   LIMIT '.(int)(($page_number - 1) * $page_size).','.(int)$page_size;
 $result = $db->executeS($sql);
 $sql = 'SELECT COUNT(*)
   FROM '._DB_PREFIX_.'product p
   '.Shop::addSqlAssociation('product', 'p').'
   INNER JOIN `'._DB_PREFIX_.'product_lang` pl ON (
 p.`id_product` = pl.`id_product`
 AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
   )
   LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`
 AND tr.`id_country` = '.(int)Context::getContext()->country->id.'
   AND tr.`id_state` = 0)
   LEFT JOIN `'._DB_PREFIX_.'tax` tax ON (tax.`id_tax` = tr.`id_tax`)
   LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
   LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
   LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
   WHERE p.`id_product` '.$product_pool;
 $total = $db->getValue($sql);
 if (!$result)
  $result_properties = false;
 else
  $result_properties = Product::getProductsProperties((int)$id_lang, $result);
 return array('total' => $total,'result' => $result_properties);
}
public static function getTags($db, $id_product, $id_lang)
{
 $tags = '';
 $tagsArray = $db->executeS('
 SELECT t.name FROM '._DB_PREFIX_.'product_tag pt
 LEFT JOIN '._DB_PREFIX_.'tag t ON (pt.id_tag = t.id_tag AND t.id_lang = '.(int)$id_lang.')
 WHERE pt.id_product = '.(int)$id_product);
 foreach ($tagsArray as $tag)
  $tags .= $tag['name'].' ';
 return $tags;
}
public static function getAttributes($db, $id_product, $id_lang)
{
 if (!Combination::isFeatureActive())
  return '';
 $attributes = '';
 $attributesArray = $db->executeS('
 SELECT al.name FROM '._DB_PREFIX_.'product_attribute pa
 INNER JOIN '._DB_PREFIX_.'product_attribute_combination pac ON pa.id_product_attribute = pac.id_product_attribute
 INNER JOIN '._DB_PREFIX_.'attribute_lang al ON (pac.id_attribute = al.id_attribute AND al.id_lang = '.(int)$id_lang.')
 '.Shop::addSqlAssociation('product_attribute', 'pa').'
 WHERE pa.id_product = '.(int)$id_product);
 foreach ($attributesArray as $attribute)
  $attributes .= $attribute['name'].' ';
 return $attributes;
}
public static function getFeatures($db, $id_product, $id_lang)
{
 if (!Feature::isFeatureActive())
  return '';
 $features = '';
 $featuresArray = $db->executeS('
 SELECT fvl.value FROM '._DB_PREFIX_.'feature_product fp
 LEFT JOIN '._DB_PREFIX_.'feature_value_lang fvl ON (fp.id_feature_value = fvl.id_feature_value AND fvl.id_lang = '.(int)$id_lang.')
 WHERE fp.id_product = '.(int)$id_product);
 foreach ($featuresArray as $feature)
  $features .= $feature['value'].' ';
 return $features;
}
protected static function getProductsToIndex($total_languages, $id_product = false, $limit = 50)
{
 // Adjust the limit to get only "whole" products, in every languages (and at least one)
 $max_possibilities = $total_languages * count(Shop::getShops(true));
 $limit = max(1, floor($limit / $max_possibilities) * $max_possibilities);
 return Db::getInstance()->executeS('
  SELECT p.id_product, pl.id_lang, pl.id_shop, pl.name pname, p.reference, p.ean13, p.upc,
   pl.description_short, pl.description, cl.name cname, m.name mname
  FROM '._DB_PREFIX_.'product p
  LEFT JOIN '._DB_PREFIX_.'product_lang pl
   ON p.id_product = pl.id_product
  '.Shop::addSqlAssociation('product', 'p').'
  LEFT JOIN '._DB_PREFIX_.'category_lang cl
   ON (cl.id_category = product_shop.id_category_default AND pl.id_lang = cl.id_lang AND cl.id_shop = product_shop.id_shop)
  LEFT JOIN '._DB_PREFIX_.'manufacturer m
   ON m.id_manufacturer = p.id_manufacturer
  WHERE product_shop.indexed = 0
  AND product_shop.visibility IN ("both", "search")
  '.($id_product ? 'AND p.id_product = '.(int)$id_product : '').'
  LIMIT '.(int)$limit
 );
}
public static function indexation($full = false, $id_product = false)
{
 $db = Db::getInstance();
 if ($id_product)
  $full = false;
 if ($full)
 {
  $db->execute('TRUNCATE '._DB_PREFIX_.'search_index');
  $db->execute('TRUNCATE '._DB_PREFIX_.'search_word');
  ObjectModel::updateMultishopTable('Product', array('indexed' => 0), '1');
 }
 else
 {
  // Do it even if you already know the product id in order to be sure that it exists and it needs to be indexed
  $products = $db->executeS('
   SELECT p.id_product
   FROM '._DB_PREFIX_.'product p
   '.Shop::addSqlAssociation('product', 'p').'
   WHERE product_shop.visibility IN ("both", "search")
   AND '.($id_product ? 'p.id_product = '.(int)$id_product : 'product_shop.indexed = 0')
  );
  $ids = array();
  if ($products)
   foreach ($products as $product)
 $ids[] = (int)$product['id_product'];
  if (count($ids))
   $db->execute('DELETE FROM '._DB_PREFIX_.'search_index WHERE id_product IN ('.implode(',', $ids).')');
 }
 // Every fields are weighted according to the configuration in the backend
 $weight_array = array(
  'pname' => Configuration::get('PS_SEARCH_WEIGHT_PNAME'),
  'reference' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
  'ean13' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
  'upc' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
  'description_short' => Configuration::get('PS_SEARCH_WEIGHT_SHORTDESC'),
  'description' => Configuration::get('PS_SEARCH_WEIGHT_DESC'),
  'cname' => Configuration::get('PS_SEARCH_WEIGHT_CNAME'),
  'mname' => Configuration::get('PS_SEARCH_WEIGHT_MNAME'),
  'tags' => Configuration::get('PS_SEARCH_WEIGHT_TAG'),
  'attributes' => Configuration::get('PS_SEARCH_WEIGHT_ATTRIBUTE'),
  'features' => Configuration::get('PS_SEARCH_WEIGHT_FEATURE')
 );
 // Those are kind of global variables required to save the processed data in the database every X occurrences, in order to avoid overloading MySQL
 $count_words = 0;
 $query_array3 = array();
 $products_array = array();
 // Every indexed words are cached into a PHP array
 $word_ids = Db::getInstance()->executeS('
  SELECT id_word, word, id_lang, id_shop
  FROM '._DB_PREFIX_.'search_word');
 $word_ids_by_word = array();
 foreach ($word_ids as $word_id)
 {
  if (!isset($word_ids_by_word[$word_id['id_shop']][$word_id['id_lang']]))
   $word_ids_by_word[$word_id['id_shop']][$word_id['id_lang']] = array();
  $word_ids_by_word[$word_id['id_shop']][$word_id['id_lang']]['_'.$word_id['word']] = (int)$word_id['id_word'];
 }
 // Retrieve the number of languages
 $total_languages = count(Language::getLanguages(false));
 // Products are processed 50 by 50 in order to avoid overloading MySQL
 while (($products = Search::getProductsToIndex($total_languages, $id_product, 50)) && (count($products) > 0))
 {
  // Now each non-indexed product is processed one by one, langage by langage
  foreach ($products as $product)
  {
   $product['tags'] = Search::getTags($db, (int)$product['id_product'], (int)$product['id_lang']);
   $product['attributes'] = Search::getAttributes($db, (int)$product['id_product'], (int)$product['id_lang']);
   $product['features'] = Search::getFeatures($db, (int)$product['id_product'], (int)$product['id_lang']);
   // Data must be cleaned of html, bad characters, spaces and anything, then if the resulting words are long enough, they're added to the array
   $product_array = array();
   foreach ($product as $key => $value)
 if (strncmp($key, 'id_', 3))
 {
  $words = explode(' ', Search::sanitize($value, (int)$product['id_lang'], true));
  foreach ($words as $word)
   if (!empty($word))
   {
    $word = Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH);
    // Remove accents
    $word = Tools::replaceAccentedChars($word);
    if (!isset($product_array[$word]))
	 $product_array[$word] = 0;
    $product_array[$word] += $weight_array[$key];
   }
 }
   // If we find words that need to be indexed, they're added to the word table in the database
   if (count($product_array))
   {
 $query_array = $query_array2 = array();
 foreach ($product_array as $word => $weight)
  if ($weight && !isset($word_ids_by_word['_'.$word]))
  {
   $query_array[$word] = '('.(int)$product['id_lang'].', '.(int)$product['id_shop'].', \''.pSQL($word).'\')';
   $query_array2[] = '\''.pSQL($word).'\'';
   $word_ids_by_word[$product['id_shop']][$product['id_lang']]['_'.$word] = 0;
  }
 if ($query_array2)
 {
  $existing_words = $db->executeS('
  SELECT DISTINCT word FROM '._DB_PREFIX_.'search_word
   WHERE word IN ('.implode(',', $query_array2).')
  AND id_lang = '.(int)$product['id_lang'].'
  AND id_shop = '.(int)$product['id_shop']);
  foreach ($existing_words as $data)
   unset($query_array[Tools::replaceAccentedChars($data['word'])]);
 }
 if (count($query_array))
 {
  // The words are inserted...
  $db->execute('
  INSERT IGNORE INTO '._DB_PREFIX_.'search_word (id_lang, id_shop, word)
  VALUES '.implode(',', $query_array));
 }
 if (count($query_array2))
 {
  // ...then their IDs are retrieved and added to the cache
  $added_words = $db->executeS('
  SELECT sw.id_word, sw.word
  FROM '._DB_PREFIX_.'search_word sw
  WHERE sw.word IN ('.implode(',', $query_array2).')
  AND sw.id_lang = '.(int)$product['id_lang'].'
  AND sw.id_shop = '.(int)$product['id_shop'].'
  LIMIT '.count($query_array2));
  // replace accents from the retrieved words so that words without accents or with differents accents can still be linked
  foreach ($added_words as $word_id)
   $word_ids_by_word[$product['id_shop']][$product['id_lang']]['_'.Tools::replaceAccentedChars($word_id['word'])] = (int)$word_id['id_word'];
 }
   }
   foreach ($product_array as $word => $weight)
   {
 if (!$weight)
  continue;
 if (!isset($word_ids_by_word[$product['id_shop']][$product['id_lang']]['_'.$word]))
  continue;
 if (!$word_ids_by_word[$product['id_shop']][$product['id_lang']]['_'.$word])
  continue;
 $query_array3[] = '('.(int)$product['id_product'].','.
  (int)$word_ids_by_word[$product['id_shop']][$product['id_lang']]['_'.$word].','.(int)$weight.')';
 // Force save every 200 words in order to avoid overloading MySQL
 if (++$count_words % 200 == 0)
  Search::saveIndex($query_array3);
   }
   if (!in_array($product['id_product'], $products_array))
 $products_array[] = (int)$product['id_product'];
  }
  Search::setProductsAsIndexed($products_array);
  // One last save is done at the end in order to save what's left
  Search::saveIndex($query_array3);
 }
 return true;
}
protected static function setProductsAsIndexed(&$products)
{
 if (count($products))
  ObjectModel::updateMultishopTable('Product', array('indexed' => 1), 'a.id_product IN ('.implode(',', $products).')');
}
/** $queryArray3 is automatically emptied in order to be reused immediatly */
protected static function saveIndex(&$queryArray3)
{
 if (count($queryArray3))
  Db::getInstance()->execute(
   'INSERT INTO '._DB_PREFIX_.'search_index (id_product, id_word, weight)
   VALUES '.implode(',', $queryArray3).'
   ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight)'
 );
 $queryArray3 = array();
}
public static function searchTag($id_lang, $tag, $count = false, $pageNumber = 0, $pageSize = 10, $orderBy = false, $orderWay = false,
 $useCookie = true, Context $context = null)
{
 if (!$context)
  $context = Context::getContext();
 // Only use cookie if id_customer is not present
 if ($useCookie)
  $id_customer = (int)$context->customer->id;
 else
  $id_customer = 0;
 if (!is_numeric($pageNumber) || !is_numeric($pageSize) || !Validate::isBool($count) || !Validate::isValidSearch($tag)
 || $orderBy && !$orderWay || ($orderBy && !Validate::isOrderBy($orderBy)) || ($orderWay && !Validate::isOrderBy($orderWay)))
  return false;
 if ($pageNumber < 1) $pageNumber = 1;
 if ($pageSize < 1) $pageSize = 10;
 $id = Context::getContext()->shop->id;
 $id_shop = $id ? $id : Configuration::get('PS_SHOP_DEFAULT');
 if ($count)
 {
  $sql = 'SELECT COUNT(DISTINCT pt.`id_product`) nb
 FROM `'._DB_PREFIX_.'product` p
 '.Shop::addSqlAssociation('product', 'p').'
 LEFT JOIN `'._DB_PREFIX_.'product_tag` pt ON (p.`id_product` = pt.`id_product`)
 LEFT JOIN `'._DB_PREFIX_.'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = '.(int)$id_lang.')
 LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
 LEFT JOIN `'._DB_PREFIX_.'category_shop` cs ON (cp.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
 LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = cp.`id_category`)
 WHERE product_shop.`active` = 1
  AND cs.`id_shop` = '.(int)Context::getContext()->shop->id.'
  AND cg.`id_group` '.(!$id_customer ?  '= 1' : 'IN (
   SELECT id_group FROM '._DB_PREFIX_.'customer_group
   WHERE id_customer = '.(int)$id_customer.')').'
  AND t.`name` LIKE \'%'.pSQL($tag).'%\'';
  return (int)Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
 }
 $sql = 'SELECT DISTINCT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description_short`, pl.`link_rewrite`, pl.`name`,
 tax.`rate`, i.`id_image`, il.`legend`, m.`name` manufacturer_name, 1 position,
 DATEDIFF(
  p.`date_add`,
  DATE_SUB(
   NOW(),
   INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY
  )
 ) > 0 new
   FROM `'._DB_PREFIX_.'product` p
   INNER JOIN `'._DB_PREFIX_.'product_lang` pl ON (
 p.`id_product` = pl.`id_product`
 AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
   )
   '.Shop::addSqlAssociation('product', 'p', false).'
   LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
   LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
   LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`
 AND tr.`id_country` = '.(int)$context->country->id.'
 AND tr.`id_state` = 0)
   LEFT JOIN `'._DB_PREFIX_.'tax` tax ON (tax.`id_tax` = tr.`id_tax`)
   LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
   LEFT JOIN `'._DB_PREFIX_.'product_tag` pt ON (p.`id_product` = pt.`id_product`)
   LEFT JOIN `'._DB_PREFIX_.'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = '.(int)$id_lang.')
   LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
   LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = cp.`id_category`)
   LEFT JOIN `'._DB_PREFIX_.'category_shop` cs ON (cg.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
   '.Product::sqlStock('p', 0).'
   WHERE product_shop.`active` = 1
 AND cs.`id_shop` = '.(int)Context::getContext()->shop->id.'
 AND cg.`id_group` '.(!$id_customer ?  '= 1' : 'IN (
  SELECT id_group FROM '._DB_PREFIX_.'customer_group
  WHERE id_customer = '.(int)$id_customer.')').'
 AND t.`name` LIKE \'%'.pSQL($tag).'%\'
   ORDER BY position DESC'.($orderBy ? ', '.$orderBy : '').($orderWay ? ' '.$orderWay : '').'
   LIMIT '.(int)(($pageNumber - 1) * $pageSize).','.(int)$pageSize;
 if (!$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))
  return false;
 return Product::getProductsProperties((int)$id_lang, $result);
}
}

nathanielleee · November 1, 2012

yeah i was able to fix both of those thanks to your help (search and featured) but still having issues when dealing with new and best selling pages. Im guessing the coding is quite different since they are basing their query on a totally different thing such as purchasing or dates. Any help here will be great. Im still picking at it to see what i can do so if i find out something ill let you know

nathanielleee · November 7, 2012

Has anyone yet been able to solve the duplicates in "Specials" or "Top Sellers" Im still getting double products

Jessie K. Jepsen · November 7, 2012

Has anyone yet been able to solve the duplicates in "Specials" or "Top Sellers" Im still getting double products

Sorry, I have not had a chance to look at those. I will do so this weekend and see what I can figure out. I activated the links for those and I have the same issue also.

If anyone else fix's it let us know..

nathanielleee · November 8, 2012

so far ive checked out the tables and nothing is duplicated in product_shop. so totally confused. cant launch this new version without fixing this issue

nathanielleee · November 9, 2012

Ok by doing some minor Edits on the classes/Prodcut.php file, i was able to get my sales items back to normal. Still facing duplicates on top sellers though but i dont really need that page anyhow.

Quick question: i transfered my customers to another database and all seemed well until they tried logging in. Anyone have this issue with tranfering customers? it seems everything in the backend is fine but their passcodes no longer work.

Ace mcputer · November 12, 2012

I had this same problem "duplicate products front & back end" in 1.5 and found that the catalog > products> edit >prices >tax rule was pointing to the incorrect tax.

Once I had pointed to correct tax the duplicates disappeared.

Hope this helps someone

nathanielleee · November 29, 2012

Another fix is the Image_shop table usually has duplicate entries in this instance. You can run a query and delete the duplicates and that fixes the issue.

carelessoul · January 2, 2013

I'm having the same problem. I already replaced the Category.php file but the problem is still there.

Edited January 2, 2013 by carelessoul (see edit history)

lowang · January 7, 2013

I've used CSV import in PS 1.5 to import my products, I've also selected to import photos using product photos url column.

My first attempt to correct it was to add GROUP BY statement to query on product page, in PS 1.5.3.1 I've changed classes/Category.php and added in line 645 in function getProducts just before ORDER BY statements:

$sql .= ' GROUP BY p.id_product';

But then I've started to investigate that query against my data and it I've found inconsistency problem. I guess that import feature has error causing each product photo to be marked as a cover, causing duplicated in sql query to fetch products on category page. So executing following code:

UPDATE image_shop SET cover = 0;
UPDATE image SET cover =0;
UPDATE `image` i JOIN (SELECT id_product, id_image FROM  `image` GROUP BY id_product) AS x USING(id_image) SET i.cover = 1 ;
UPDATE `image_shop` i JOIN (SELECT id_product, id_image FROM  `image` GROUP BY id_product) AS x USING(id_image) SET i.cover = 1 ;

resolved the problem for me and I was able to remove that GROUP BY from Category.php completely restoring default file.

It turns out that I've still had duplicates on main page in Featured products, again it was due tu data inconsistency, I've executed following query to find duplicates:

SELECT count(*) cnt, id_image, id_shop FROM `image_shop` WHERE `cover` = 1 group by id_image,id_shop having cnt > 1

and removed duplicate occurences from that table

Edited January 7, 2013 by lowang (see edit history)

H4818 · February 18, 2013

I had this issue after upgrading to 1.5.3.1...

I resolved it by executing the following queries on the database - duplicates were removed from ALL product listings including specials, top sellers and new products:

CREATE TABLE `ps_image_shop_foo` (
`id_image` INT( 11 ) UNSIGNED NOT NULL,
`id_shop` INT( 11 ) UNSIGNED NOT NULL,
`cover` tinyint(1) NOT NULL,
KEY (`id_image`, `id_shop`, `cover`),
KEY `id_shop` (`id_shop`)
) ENGINE=InnoDb  DEFAULT CHARSET=utf8;

INSERT INTO ps_image_shop_foo SELECT DISTINCT * FROM ps_image_shop;

delete from ps_image_shop;

INSERT INTO ps_image_shop SELECT DISTINCT * FROM ps_image_shop_foo;

Arafat Jahan · March 5, 2013

THANKS A LOT, This solve my problem,

On my category page all product was comes 3/4 times repeated

BTW exactly where and which function you edit? this will help me specific about the problem,

THUS THANKS AGAIN MY PROBLEM WAS SOLVED!!

Thanks,

Arafat

ZuZulik · March 26, 2013

Thanks H4818, problem solved.

alimotorolla · April 6, 2013

hi jessie and other friends

*******

thanks to H4818 for leading me to this solution

*******

i had a problem just like yours in 1.5 upgrade and when i imported .csv files.

i tried to write a script to import product directly from my own mysql db to presta db, but the problem appeared so.

then i searched and searched and searched and not found any reason to this issue

but finally i found a solution which at least worked for me.

************ solution ************

table: ps_image & ps_image_shop

field: cover

this field should be (1) for first image of unique product and (0) for other images of that unique product.

if you set this field to (1) for all images of one product prestashop will be confused and the list of products will be overwritten by multiple cover image products.

pay attention that repetitions are equal to number of images.

************ solution ************

thanks in advance to all new ideas...

Edited April 6, 2013 by alimotorolla (see edit history)

jfpts · May 13, 2013

I had the same problem, because I duplicated tax rule for the same country. So, it was duplicating products on all listing.

When I deleted this country tax rule, the problem disapeared.

harveygwa · May 21, 2014

i had this problem don't want to go into to much detail but when i updated it turned out it put the featured product module on the home page twice... i disabled and re-enabled the module and it fixed the problem...

hope this helps.

Blejzer · May 6, 2015

I had this same problem "duplicate products front & back end" in 1.5 and found that the catalog > products> edit >prices >tax rule was pointing to the incorrect tax.

Once I had pointed to correct tax the duplicates disappeared.

Hope this helps someone

This fixed my problem. Thank you!

isabella_brown · May 8, 2015

Hi Jessie K. Jepsen,

Thanks for giving here this code.I try it too and now I am able to get where is actually the problem.

Thanks again.

rahees1234 · May 11, 2015

Categories are listing twice in the shop's home page

Iam using Presashop theme named 'Megashop'. I have created a shop and inserted different product categories. But the problem is the categories were displaying twice. Do any one have similar problems. if found please help me to fix this problem.

Ace mcputer · May 11, 2015

Ace mcputer, on 12 Nov 2012 - 4:29 PM, said:

I had this same problem "duplicate products front & back end" in 1.5 and found that the catalog > products> edit >prices >tax rule was pointing to the incorrect tax.
Once I had pointed to correct tax the duplicates disappeared.
Hope this helps someone

Hi it should be a simple to check this.

rahees1234 · May 11, 2015

Hi

Thx for the reply. But it not fixex . My shop url is http://chip-mall.in/call4all_presta/prestashop/callus/index.php .

Duplicate Products on front end.

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites