Create database table using php

[tinybibiya]

Hi,

 

I'm using PS 1.6. I've managed to create a new blank page using php. 

 

My files are:

 

1) root/controller/front/StocklistController.php

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


public function initContent()
{
  parent::initContent();
  $this->setTemplate(_PS_THEME_DIR_.'stocklist.tpl');
}
}

2) root/themes/*my theme*/stocklist.tpl

 

{include file="$tpl_dir./breadcrumb.tpl"}
{include file="$tpl_dir./errors.tpl"}
<h1>Testpage</h1>

Now, I want to know how to grab data from database. I already have a nice table using the SQL manager module in the BO as seen in picture attachment. How to translate this query into php format? I may need to output Reference #, Name, Size Variation, and Quantity into the front end tpl file.

SELECT 
    p.id_product as 'ID',
    GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as 'Category',
    pa.reference as 'Reference #',
    pl.name as 'Name',
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as 'Size Variation',
    pq.quantity as 'Quantity'
FROM ps_product p 
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) 
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_lang pal ON (pac.id_attribute = pal.id_attribute)
LEFT JOIN ps_category_product cp ON (p.id_category_default = cp.id_category)
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)
WHERE pl.id_lang = 1 
AND pal.id_lang = 1
AND p.id_category_default = 15
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute

Thanks in advance

 

tinybibiya

 

[bellini13]

There are numerous examples of this is the Prestashop code that you can follow.  Open the Product class and you can see how its done

[tinybibiya]

There are numerous examples of this is the Prestashop code that you can follow.  Open the Product class and you can see how its done

 

OK.

Can I put this code into controller php?

 

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


public function initContent()
{
  parent::initContent();
  $this->setTemplate(_PS_THEME_DIR_.'stocklist.tpl');
  
  $sql = 'SELECT p.'id_product', GROUP_CONCAT(DISTINCT(cl.'name') SEPARATOR ","), pa.'reference', pl.'name', GROUP_CONCAT(DISTINCT(pal.'name') SEPARATOR ", "), pq.'quantity'
FROM `'._DB_PREFIX_.'product' p
LEFT JOIN `'._DB_PREFIX_.'product_attribute' pa ON p.'id_product' = pa.'id_product'
LEFT JOIN `'._DB_PREFIX_.'stock_available' pq ON p.'id_product' = pq.'id_product' AND pa.'id_product_attribute' = pq.'id_product_attribute'
LEFT JOIN `'._DB_PREFIX_.'product_lang' pl ON (p.'id_product' = pl.'id_product'
LEFT JOIN `'._DB_PREFIX_.'product_attribute_combination' pac ON (pa.'id_product_attribute' = pac.'id_product_attribute'
LEFT JOIN `'._DB_PREFIX_.'attribute_lang pal' ON (pac.'id_attribute' = pal.'id_attribute'
LEFT JOIN `'._DB_PREFIX_.'category_product' cp ON (p.'id_category_default' = cp.'id_category'
LEFT JOIN `'._DB_PREFIX_.'category_lang' cl ON (cp.'id_category' = cl.'id_category'
LEFT JOIN `'._DB_PREFIX_.'category' c ON cp.'id_category' = c.'id_category'
WHERE pl.'id_lang' = 1
AND pal.'id_lang' = 1
AND p.'id_category_default' = 15
GROUP BY 'pa.reference'
ORDER BY 'p.id_product', 'pac.id_attribute'


$res = Db::getInstance()->executeS($sql);


}
}

If I am doing the controller part right, how can I display the whole table at Front Office stocklist.tpl?