Create new page with data from DB

[tinybibiya]

Hi,

 

I want to create a page that is only limit to a certain customer group.

 

I wish to create a page like CMS page, but i want to grab data from the DB and it will auto display in that page.

I want to create a page for my dropshippers to see the stock list, so they don't have to go in to each product listing to check on the stock availability. 

 

The current table that I have in my SQL Manager is as below:

 

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

And I wish to make it display in a nice table. Example:

 

Romper Color

1. White     2S   2M   2L

2. Cream   2S   4M   5L

etc.

..

.

.

 

Can anyone help the basic to get data from the DB and post in front office?

[yaniv14]

Just create another controller to get a new custom page.

You can start by making a copy of a simple controller like 'controllers/front/SitemapController.php' and name it SomethingController.php

 

Inside the initcontent function you can place you code and add all variables to smarty and to your new template.

 

Once you are done you can configure friendly url to the new controller in the BO under seo & url.

 

Good luck