Prestashop CMS page view counter

Question

I'm struggling with CMS page view counter.

I'm add new function to classes/controller/FrontController.php like below

public static function getCMSViewed($id_cms){
    $viewcms = Db::getInstance()->getRow('SELECT SUM(pv.counter) AS total 
        FROM '._DB_PREFIX_.'page_viewed pv
        LEFT JOIN '._DB_PREFIX_.'cms c ON pv.id_page = c.id_cms 
        LEFT JOIN '._DB_PREFIX_.'page p ON pv.id_page = p.id_page
        LEFT JOIN '._DB_PREFIX_.'page_type pt ON p.id_page_type = pt.id_page_type
        WHERE pt.name = \'cms\'');
        return isset($viewcms['total']) ? $viewcms['total'] : 0;
}

and in cms.tpl file i have add {FrontController::getCMSViewed(Tools::getValue('id_cms'))}

For now the counter is "working" but showing on all Prestashop CMS pages the same increased value of one of the pages with ID=15

Symeon Quimby Symeon Quimby · Accepted Answer · 2017-03-23T00:44:11

I see you are passing a CMS id into your function, but I do not see you using it anywhere. You also have the join on cms matching against columns id_page and id_cms. Which means your query is not going to grab the count you expect. You want something closer to this.

public static function getCMSViewed($id_cms){
$viewcms = Db::getInstance()->getRow('SELECT pv.counter AS total 
    FROM '._DB_PREFIX_.'page_viewed pv
    LEFT JOIN '._DB_PREFIX_.'page p ON pv.id_page = p.id_page
    LEFT JOIN '._DB_PREFIX_.'page_type pt ON p.id_page_type = pt.id_page_type
    WHERE pt.name = \'cms\' AND p.`id_object` = '.intval($id_cms) );
    return isset($viewcms['total']) ? $viewcms['total'] : 0;
}

The table page holds the id of whatever page it is referencing in the id_object column, so you want to match the $id_cms you pass in against p.id_object. With that match the sql can return the count directly from the page_viewed table. No need to join in the cms table at all.

Prestashop CMS page view counter

1 Answers