Jump to content

Help with a sql


iticleonel.len
 Share

Recommended Posts

Hi i have the next code.. but i add a variable por each category parent, but when i ejecute the sql it don't work. can you help me.

 

add to the variable $where the id of each category if is selected.

 

if(isset($category1) && $category1!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category1;
}
if(isset($category2) && $category2!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category2;
}
if(isset($category3) && $category3!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category3;
}
if(isset($category4) && $category4!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category4;
}if(isset($category5) && $category5!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category5;
}if(isset($category6) && $category6!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category6;
}
if(isset($category7) && $category7!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category7;
}

 

And sql consult.

$context = Context::getContext();
 $sql1 = 'SELECT T.* FROM (SELECT DISTINCT product_shop.*, p.`ean13`,  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`, image_shop.`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`)'.
   Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
   LEFT JOIN `'._DB_PREFIX_.'image_lang` il
 ON (image_shop.`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`
   LEFT JOIN `'._DB_PREFIX_.'product_tag` pt ON (product_shop.`id_product` = pt.`id_product`)


   WHERE product_shop.`id_shop` = '.(int)$context->shop->id.'
   AND (pa.id_product_attribute IS NULL OR product_attribute_shop.id_shop='.(int)$context->shop->id.')
   AND (i.id_image IS NULL OR image_shop.id_shop='.(int)$context->shop->id.')
 AND product_shop.`active` = 1'
 .' AND product_shop.`visibility` IN ("both", "catalog")'.$where.') AS T
   WHERE T.`active` = 1 '.$where2.$sqlOrderBy.$limit;
  $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql1);

 

this is the complete code.

 

<?php
include_once('../../config/config.inc.php');
include_once('../../init.php');
include_once('blockadvancedsearch.php');

if (!class_exists('BASClass', false)) {
include _PS_MODULE_DIR_ . 'blockadvancedsearch' . DIRECTORY_SEPARATOR . 'blockadvancedsearch.class.php';
}
if (!class_exists('MPClass', false)) {
include _PS_MODULE_DIR_ . 'megaproduct' . DIRECTORY_SEPARATOR . 'megaproduct.class.php';
}
function array_flatten($array,$field) {
 if (!is_array($array)) {
   return FALSE;
 }
 $result = array();
 foreach ($array as $key => $value) {

  array_push($result, $value[$field]);

 }
 return implode(",", $result);
}
if (Tools::getValue('action') == 'getCategoryValues')
{
$id_category = Tools::getValue('categoryId');
$bas = bascategories::getArrayByCategory($id_category);
$arraySend = array();
$arraySend['attributes'] =  $bas->attributes;
$arraySend['features'] =  $bas->features;
$arraySend['tags'] =  $bas->tags;
$arraySend['suppliers'] =  $bas->suppliers;
$arraySend['manufacturers'] =  $bas->manufacturers;
//$value = $.parseJSON(json_encode(json_encode($aCoders)));
echo json_encode($arraySend);
return;
}
$blockadvancedsearch = new BlockAdvancedSearch();
$html = '';
$category1 = Tools::getValue('category1');
$category2 = Tools::getValue('category2');
$category3 = Tools::getValue('category3');
$category4 = Tools::getValue('category4');
$category5 = Tools::getValue('category5');
$category6 = Tools::getValue('category6');
$category7 = Tools::getValue('category7');
$manufacturer = Tools::getValue('manufacturer');
$supplier = Tools::getValue('supplier');
$filtertext = Tools::getValue('filtertext');
$tag = Tools::getValue('tag');
$minprice = Tools::getValue('minprice');
$maxprice = Tools::getValue('maxprice');
$page = intval(Tools::getValue('page'));
$attributes = Tools::getValue('attributes');
$features = Tools::getValue('features');
$orderby = Tools::getValue('orderby');
$where = '';
$where2 = '';
$sqlOrderBy = '';
$counter = (int)Tools::getValue('counter');
if(isset($orderby) && $orderby!='')
{
if($orderby=="price:asc")
{
 $sqlOrderBy .= ' ORDER BY T.`orderprice` ASC ';
}
else if(($orderby=="price:desc"))
{
 $sqlOrderBy .= ' ORDER BY T.`orderprice` DESC ';
}

}

if(isset($manufacturer) && $manufacturer!=0)
{
$where.= ' AND p.`id_manufacturer`='.(int)$manufacturer; 
}
if(isset($supplier) && $supplier!=0)
{
$where.= ' AND p.`id_supplier`='.(int)$supplier; 
}
if(isset($filtertext) && $filtertext!='')
{
$where.= ' AND pl.`name` LIKE \'%'.$filtertext.'%\'';
}
if(isset($minprice) && $minprice!='')
{
$where2.= ' AND T.`orderprice`>='.(int)$minprice;
}
if(isset($maxprice) && $maxprice!='')
{
$where2.= ' AND T.`orderprice`<='.(int)$maxprice;
}
if(isset($tag) && $tag!='')
{
$where.= ' AND pt.`id_tag`='.$tag;
}
$numproductshow = intval(Configuration::get('BAS_NUMPRODUCTSHOW'));
$limitup = $numproductshow;
$limit = '';
if(isset($numproductshow) && $numproductshow!=0)
{
$limit.= ' LIMIT '.$page*$numproductshow.','.$limitup;
}
$id_lang =  (int)(Context::getContext()->cookie->id_lang);
$products = null;
$result = null;
//START
if(isset($category1) && $category1!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category1;
}
if(isset($category2) && $category2!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category2;
}
if(isset($category3) && $category3!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category3;
}
if(isset($category4) && $category4!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category4;
}if(isset($category5) && $category5!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category5;
}if(isset($category6) && $category6!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category6;
}
if(isset($category7) && $category7!=0)
{
$where.= ' AND cp.`id_category`='.(int)$category7;
}
if(isset($attributes) && $attributes!='')
{
$arrayAttributes = explode("_", $attributes);

$sql = 'SELECT fp.`id_product_attribute`, count(id_attribute)
FROM `'._DB_PREFIX_.'product_attribute_combination`  fp WHERE 1=1 AND id_attribute IN ('.str_replace('_',',',$attributes).') GROUP BY fp.`id_product_attribute` HAVING COUNT(id_attribute) = '.sizeof($arrayAttributes);

$combinations = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);

if(isset($combinations) && sizeof($combinations))
{
 $combinationsid = array_flatten($combinations,'id_product_attribute');
 $products = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
 SELECT DISTINCT pa.`id_product`
 FROM `'._DB_PREFIX_.'product_attribute` pa  WHERE pa.`id_product_attribute` IN ('.$combinationsid.')');
 $where.= ' AND p.`id_product` IN ('.array_flatten($products,'id_product').') ';
}
else
{
 $where.= ' AND p.`id_product` IN () ';
}
}
if(isset($features) && $features!='')
{
$arrayFeatures = explode("_", $features);

$sql = 'SELECT fp.`id_product`, count(id_feature_value)
FROM `'._DB_PREFIX_.'feature_product`  fp WHERE id_feature_value IN ('.str_replace('_',',',$features).') GROUP BY fp.`id_product` HAVING COUNT(id_feature_value) = '.sizeof($arrayFeatures);
$products = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);

if(isset($products) && sizeof($products))
{
 $productsid = array_flatten($products,'id_product');

 $where.= ' AND p.`id_product` IN ('.$productsid.') ';
}
else
{
 $where.= ' AND p.`id_product` IN () ';
}

}

$days = (int)Tools::getValue('days');
if(isset($days))
{
$day = Tools::getValue('day');
$month = Tools::getValue('month');
$year = Tools::getValue('year');
$date_string = $year.'-'.$month.'-'.$day;
$date_start = date('Y-m-d', strtotime($date_string));


$id_timeslot = (int)Tools::getValue('id_timeslot');

if($days==1)
{
 $date_end = $date_start;
}
else
{
 $date_end = date("Y-m-d", strtotime($date_start ."+".$days." days"));
}

$megaproducts = MPClass::getAllMegaProducts();
$mpdays = megaproductdays::getDaysByDates($date_start,$date_end);
$arrayMPIds = array();

foreach($megaproducts as $mp)
{
 $id_product = $mp['id_product'];
 $arrayMPIds[$id_product]=$id_product;
}
$mpc = new megaproductcart();
$mpc->id_product = $id_product;

$mpc->date_start = $date_start;
$mpc->days = $days;
$mpc->id_timeslot = $id_timeslot;
foreach($megaproducts as $mp)
{
 $id_product = $mp['id_product'];

 $addmp = true;
 $mpc->id_product = $id_product;
 $mpc->config = $mp['config'];

 if($mpdays)
 {
  foreach($mpdays as $days)
  {
   if($days['id_product']==$mp['id_product'])
   {
 if($days['type']==1)
 {
  //unset($arrayMPIds[$id_product]);
  //$addmp = false;
  //break;
  for($i=0;$i<$days;$i++)
  {
   $timestamp = strtotime($date_start ."+".$i." days");
   if($timestamp >= strtotime($days['date_start']) && $timestamp < strtotime($days['date_end']))
   {
    unset($arrayMPIds[$id_product]);
    $addmp = false;
    break;
   }
  }
 }
 elseif($days['type']==2)
 {
  if($days<$days['value'])
  {
   unset($arrayMPIds[$id_product]);
   $addmp = false;
   break;
  }
 }
   }

  }
 }
 $service_number = 1;
 if(isset($mp['config']['service_number']))
 {
  $service_number= (int)$mp['config']['service_number'];
 }
 if($addmp && $service_number>0)
 {
  $freenumbers = $mpc->getFreeNumbers();
  if(sizeof($freenumbers)>0)
  {
   $addmp = true;
  }
  else
  {
   unset($arrayMPIds[$id_product]);
   $addmp = false;
  }
 }

 if($id_timeslot!=0 && $addmp)
 {
  $tls= megaproductslotproduct::getTimeSlotByProduct($id_product,$id_timeslot);
  if($tls && sizeof($tls)>0)
  {
   $returnerror = false;
   foreach($tls as $ts)
   {
 $arrayDays = explode('-',$ts['days']);
 //for($j=0;$j<$days;$j++)
 //{
  $j =0;
  $timestamp = strtotime($date_start ."+".$j." days");
  $numberday = getDate($timestamp);
  if(in_array($numberday['wday'],$arrayDays))
  {
   $returnerror = true;
  }

 //}
   }
   if(!$returnerror)
   {
 unset($arrayMPIds[$id_product]);
 $addmp = false;
   }
  }
 }
// if($addmp)
 // $arrayMPIds[$id_product]=$id_product;
}
if(isset($arrayMPIds) && sizeof($arrayMPIds))
{
 $productsid = implode(',',$arrayMPIds);

 $where.= ' AND p.`id_product` IN ('.$productsid.') ';
}
else
{
 $where.= ' AND p.`id_product` IN () ';
}
}

if (Tools::getValue('action') == 'getProducts')
{

if(_PS_VERSION_<'1.5')
{
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
 SELECT T.* FROM (SELECT DISTINCT p.*, 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,
  (p.`price` * IF(tax.`rate`,((100 + (tax.`rate`))/100),1)) AS orderprice
 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.')
 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 (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
										 AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
										    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`) WHERE p.`active` = 1 '.$where.') AS T
 WHERE T.`active` = 1 '.$where2.$sqlOrderBy.$limit);

}
elseif(_PS_VERSION_<'1.6')
{

 $context = Context::getContext();
 $sql1 = 'SELECT T.* FROM (SELECT DISTINCT product_shop.*, p.`ean13`,  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`, image_shop.`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`)'.
   Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
   LEFT JOIN `'._DB_PREFIX_.'image_lang` il
 ON (image_shop.`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`
   LEFT JOIN `'._DB_PREFIX_.'product_tag` pt ON (product_shop.`id_product` = pt.`id_product`)


   WHERE product_shop.`id_shop` = '.(int)$context->shop->id.'
   AND (pa.id_product_attribute IS NULL OR product_attribute_shop.id_shop='.(int)$context->shop->id.')
   AND (i.id_image IS NULL OR image_shop.id_shop='.(int)$context->shop->id.')
 AND product_shop.`active` = 1'
 .' AND product_shop.`visibility` IN ("both", "catalog")'.$where.') AS T
   WHERE T.`active` = 1 '.$where2.$sqlOrderBy.$limit;
  $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql1);
}


if ($counter==0)
{
 $sql = '
  SELECT COUNT(DISTINCT T.`id_product`) nb FROM (SELECT p.`id_product` 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.')
  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 (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
										  AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
											 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`) WHERE p.`active` = 1 '.$where.') AS T
  WHERE 1=1  '.$where2;
  //HERE
  $resultCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
  $counter = isset($resultCount['nb']) ? $resultCount['nb'] : 0;

}
$products = Product::getProductsProperties($id_lang, $result);

$smarty = Context::getContext()->smarty;
if(sizeof($products))
{
 $smarty->assign('products', $products);
 $pagenext = $page+1;

 /*
 if(isset($category) && $category!=0)
 {
  $smarty->assign('path', Tools::getPath((int)($category)));
  $html .= $smarty->display(_PS_THEME_DIR_.'breadcrumb.tpl');
 }*/

 $html .= $smarty->display(_PS_THEME_DIR_.'product-list.tpl');
 // Add Pagination
 $html .= '<div class="pagination" id="pagination">
 <ul class="pagination">';


 $disabledprev ="";
 $disablednext ="";
 $pageprevious = $page-1;
 $pagenext = $page+1;
 $totalpage = 1;
 if($numproductshow!=0)
  $totalpage = ceil($counter/$numproductshow);
 if($page!=0)
 {
  $html.='<li onclick="loadAdvancedSearch(true,'.$pageprevious.','.$counter.')" id="pagination_previous"><span>'.$blockadvancedsearch->getL('Previous').'</span></li>';
 }
 else
 {
  $html.='<li class="disabled"  id="pagination_previous"><span>'.$blockadvancedsearch->getL('Previous').'</span></li>';
 }


 for ($i = 0; $i <= $totalpage-1; $i++)
 {
  $x = $i+1;
  if($i==$page)
  {
   $html .= '<li class="current"><span>'.$x.'</span></li>';
  }
  else
  {
   $html .= '<li onclick="loadAdvancedSearch(true,'.$i.','.$counter.')"><span>'.$x.'</span></li>';
  }
 }
 if($counter > $numproductshow*$pagenext)
 {
  $html.= '<li onclick="loadAdvancedSearch(true,'.$pagenext.','.$counter.')"  id="pagination_next"><span>'.$blockadvancedsearch->getL('Next').'</span></a></li>';
 }
 else
 {
  $html.= '<li class="disabled"  id="pagination_next"><span>'.$blockadvancedsearch->getL('Next').'</span></a></li>';
 }
 $html.= '</ul></div>';
}
}
else if (Tools::getValue('action') == 'getComboProducts')
{
$sql = 'SELECT T.* FROM (SELECT DISTINCT p.*, 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,
  (p.`price` * IF(tax.`rate`,((100 + (tax.`rate`))/100),1)) AS orderprice
 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.')
 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 (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
										 AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
										    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`) WHERE p.`active` = 1 '.$where.') AS T
 WHERE T.`active` = 1 '.$where2.$sqlOrderBy;
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);





$products = Product::getProductsProperties($id_lang, $result);

if(sizeof($products))
{
  foreach($products as $prod)
  {
   $html .= '<option value="'.$prod['link'].'">'.$prod['name'].'</option>';
  }
}
}
else if (Tools::getValue('action') == 'getSubCategories')
{
$subcategory = Tools::getValue('subcategory');
$category = new Category($subcategory);
$categories = $category->getSubCategories($id_lang,true);

if(sizeof($categories))
{
  $html .= '<option value="0">'.$blockadvancedsearch->getL('Select').'</option>';
  foreach($categories as $cat)
  {
   $html .= '<option value="'.$cat['id_category'].'">'.$cat['name'].'</option>';
  }
}
}
else
{
$html .= '';
}
echo $html;

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More