CODEWITHSUNDEEP
phpmysql
Grant
Grant

Reputation: 2441

Daily page ratio MySQL query

I am using a cookie to track website traffic, which is then written to a database.

I am trying to get the daily ratio of my landing page index.php and the average of all the other pages to display in a Chart.

I am struggling with the syntax of the MySQL query.

I want 2 values from the query: $row->day and $pageRatio and to show these values for the past 30 days.

My database consists of one table called tracking with the following fields: id, cid, referrerDomain, referrer, page, ip, reverseIP, userAgent, lang, date

The page field contains the web page names eg: index.php, other.php etc.

$sql = "SELECT (
                 SELECT COUNT(`page`) FROM `tracking`
                 WHERE `page` = 'index.php'
                 AND DISTINCT(`date`)
                 GROUP BY DATE(`date`) ) AS indexCount,

             (
             SELECT COUNT(`page`) FROM `tracking`
             WHERE `page` != 'index.php' 
             AND `date`
             BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY)
             AND NOW()
             GROUP BY DATE(`date`) ) AS otherPageCount,

                 DATE(`date`) AS day
                 FROM `tracking`
                 WHERE `date`
                 BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY)
                 AND NOW()
                 GROUP BY DATE(`date`)";

      $sqlprep = $conn->prepare($sql); 

      if($sqlprep->execute()) {
                $completedAmount = 0;
          while($row = $sqlprep->fetch(PDO::FETCH_OBJ)){

                $pageRatio = $row->indexCount / $row->otherPageCount;

                echo "['" . $row->day . "', " . $pageRatio . "],\n";

           }

         }

The solution I found is an accepted answer here: How do I combine these 3 queries into one MySQL query?

SELECT DATE(`date`),
       COUNT(`page`) AS indexCount, 
       SUM(`page` = 'index.php') as idx_count, 
       SUM(`page` <> 'index.php') as not_idx_count
FROM `tracking`
WHERE `date` BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY) AND NOW()
GROUP BY DATE(`date`)

Upvotes: 1

Views: 320

Answers (1)

vijaykumar
vijaykumar

Reputation: 4806

May be this will also work

SELECT DATE(`date`),
       COUNT(`page`) AS indexCount, 
       SUM(`page` = 'index.php') as idx_count, 
       SUM(`page` <> 'index.php') as not_idx_count
       TIMESTAMPDIFF(DAY,date,now()) as days
FROM `tracking` GROUP BY DATE(`date`) having days <30

Upvotes: 1

Related Questions