MySQL Forums
Forum List  »  Newbie

Help with a query please!
Posted by: Greg Hanley
Date: February 02, 2015 12:03AM

Hi Everyone,

I'm a very new "newbie" to MYSQL and I am sure that somebody will be able to help me sort this simple issue out.

I have been implementing a Prestashop website for a friend of mine (prestashop.com)> Prestashop is based on PHP with a MYSQL backend.

It keeps track of the number of times a "page" is viewed in a table called ps_page_viewed....so far so good. I want to display this number on the actual web page so that people can clearly see how popular a web page is.

The structure of the table is as follows:

(`id_page`, `id_shop_group`, `id_shop`, `id_date_range`, `counter`)

Where "id_page" is the internal id number of the web page
and "counter" is the number of times the web page has been viewed....

I'm pretty sure that the other fields are really relevant except that "id_date_range" maybe..


When I run the following query via the Cpanel MySql administration panel, I seem to get the totals that I am after.

SELECT id_page, (SUM(counter)) AS Total
FROM ps_page_viewed
GROUP BY id_page


I am trying to implement a suggestion by another experienced Prestashop user who uses the following query:

public static function getTotalViewed($id_product){
$view1 = 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 = \'product\' AND p.`id_object` = '.intval($id_product).'');
return isset($view1['total']) ? $view1['total'] : 0;
}

The problem with the above is that whilst it provides me with a number, the number is usually considerably less than what my query shows for specific product id's.

Prestashop also has back office reporting function that enables me to "see", via the back office, how many page views a specific web page has had and the numbers I am seeing there are closer to the number that I am seeing in my query.


As Prestashop is open source, I tried to find the query that it uses in the back office to create the "page views" number and I think it is in the following query:


public function getTotalViewed($id_product)
{
$date_between = ModuleGraph::getDateBetween();
$sql = 'SELECT SUM(pv.`counter`) AS total
FROM `'._DB_PREFIX_.'page_viewed` pv
LEFT JOIN `'._DB_PREFIX_.'date_range` dr ON pv.`id_date_range` = dr.`id_date_range`
LEFT JOIN `'._DB_PREFIX_.'page` p ON pv.`id_page` = p.`id_page`
LEFT JOIN `'._DB_PREFIX_.'page_type` pt ON pt.`id_page_type` = p.`id_page_type`
WHERE pt.`name` = \'product\'
'.Shop::addSqlRestriction(false, 'pv').'
AND p.`id_object` = '.(int)$id_product.'
AND dr.`time_start` BETWEEN '.$date_between.'
AND dr.`time_end` BETWEEN '.$date_between;
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
return isset($result['total']) ? $result['total'] : 0;
}

Ok, here is my question: how do I put together a query that follows the same format as the following:

public static function getTotalViewed($id_product){
$view1 = 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 = \'product\' AND p.`id_object` = '.intval($id_product).'');
return isset($view1['total']) ? $view1['total'] : 0;
}

that will return the correct numbers???

I hope that I haven't confused everyone!

Thanks for any help you can provide me as I have been trying to work this out but it's finally gotten the better of me!

Regards
Greg

Options: ReplyQuote


Subject
Written By
Posted
Help with a query please!
February 02, 2015 12:03AM
February 03, 2015 06:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.