[Solved] Custom SQL table in Controller php for custom page

[tinybibiya]

Hi,

 

I have created StocklistController.php in /public_html/controllers/front

I also have created stocklist.tpl in /public_html/themes/[my_theme]

 

It suppose to display the values at tpl but is not showing. What am I missing?

 

php:

<?php
class StocklistControllerCore extends FrontController
{
public $php_self = 'stocklist';

public function initContent()
{
  parent::initContent();

$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('SELECT 
    (cp.position + 1) no,
    cl.name tb_category,
    MAX(IF(fp.id_feature = 11, fvl.value, NULL)) tb_type,
    p.reference tb_sku,
    MAX(IF(fp.id_feature = 13, fvl.value, NULL)) prod_color_name,
    MAX(IF(a.id_attribute = 4, pq.quantity, NULL)) free_size,    
    MAX(IF(a.id_attribute = 1, pq.quantity, NULL)) s_size,
    MAX(IF(a.id_attribute = 2, pq.quantity, NULL)) m_size,
    MAX(IF(a.id_attribute = 3, pq.quantity, NULL)) l_size,
    MAX(IF(a.id_attribute = 25, pq.quantity, NULL)) xl_size
FROM ps_product p 
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) 
LEFT JOIN ps_feature_product fp ON (pa.id_product = fp.id_product)
LEFT JOIN ps_feature_value_lang fvl ON (fp.id_feature_value = fvl.id_feature_value)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute) 
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) 
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute a ON (pac.id_attribute = a.id_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
LEFT JOIN ps_category_product cp ON (p.id_category_default = cp.id_category AND p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_shop cs ON (cp.id_category = cs.id_category)
WHERE pl.id_lang = 1 
AND (p.id_category_default = 15 OR p.id_category_default = 31 OR p.id_category_default = 17 OR p.id_category_default = 16) 
AND (fp.id_feature = 13 OR fp.id_feature = 11)
GROUP BY p.id_product
ORDER BY c.id_parent, cs.position,cp.position,  a.position');

$quantitytable = array();
	foreach ($result as $row)
	{
  		$quantitytable[] = array(
			"no" => $row['no'],
			"tb_category" => $row['tb_category'],
			"tb_type" => $row['tb_type'],
			"tb_sku" => $row['tb_sku'],
			"prod_color_name" => $row['prod_color_name'],
    			"free_size" => $row['free_size'], 
			"s_size" => $row['s_size'],
			"m_size" => $row['m_size'],
			"l_size" => $row['l_size'],
			"xl_size" => $row['xl_size']	
		);
	}
	
	$this->context->smarty->assign("quantitytable", $quantitytable);

    	$this->setTemplate(_PS_THEME_DIR_.'stocklist.tpl');
}
}

tpl:

<h1>Testpage Test123</h1>
<p>Testing</p>

<ul>
{foreach $quantitytable as $item}
	<li>{$item.s_size}</li>
{/foreach}
</ul>

When I use this table no problem, can see the value in tpl:

$result = Db::getInstance()->ExecuteS('SELECT id_product, id_product_attribute, quantity FROM ps_stock_available WHERE id_product_attribute <> "0"');

$quantitytable = array();
	foreach ($result as $row)
	{
  		$quantitytable[] = array(
			"id_product" => $row['id_product'],
			"id_prod_attr" => $row['id_product_attribute'],
    			"quantity" => $row['quantity']  		
		);
	}
	

	$this->context->smarty->assign("quantitytable", $quantitytable);

I managed to display all values.

Edited June 1, 2016 by tinybibiya (see edit history)

[tinybibiya]

Solve:

 

Need to change in BO Preference -> Performance:

 

Recompile templates if the files have been updated