Query for Traffic

[colby2152]

I see, well im going to have to retest this and fix it up so expect some time for me to do it.

I am curious to know if you have anything updated?  Thanks.

[ascaland]

Here is the entire num_views function again,

Code Select Expand

function num_views($output_method = 'echo')
{
   global $smcFunc;

   // The specified parent board id
   $idboard = 5;
   // The first date
   $timestamp1 = 0;
   // The second date
   $timestamp2 = 9999999999;
   // The boards
   $boards = array($idboard);

   // Retrieve all child boards, and child boards of child boards who inherit from parent
   // !! SLOW
   $request = $smcFunc['db_query']('',
  'SELECT requestedBoard.id_board
  FROM {db_prefix}boards AS requestedBoard
  INNER JOIN {db_prefix}boards AS childBoard ON (childBoard.id_parent = {int:parentId})
  LEFT JOIN {db_prefix}boards AS childOfChildBoard ON (childOfChildBoard.id_parent IN (childBoard.id_board))
  WHERE requestedBoard.id_board IN (childBoard.id_board) OR
  requestedBoard.id_board IN (childOfChildBoard.id_board)',
  array(
'parentId' => $idboard,
  )
   );

   while ($row = $smcFunc['db_fetch_assoc']($request)) {
   $boards[] = $row['id_board'];
   }

   $smcFunc['db_free_result']($request);

   // Next, retrieve all the topics in the boards where the timestamp is between two dates.
   // Once found, just output num_views
   $request = $smcFunc['db_query']('',
   'SELECT t.num_views
   FROM {db_prefix}topics AS t
   INNER JOIN {db_prefix}messages AS m ON (m.id_msg = t.id_first_msg)
   WHERE t.id_board IN ({array_int:boards_list}) AND m.poster_time BETWEEN {int:date1} AND {int:date2}',
   array(
   'boards_list' => $boards,
   'date1' => $timestamp1,
   'date2' => $timestamp2,
   )
   );

   // Your total number of views in this variable
   $num_views = 0;
   while ($row = $smcFunc['db_fetch_assoc']($request))
   {
   $num_views += $row['num_views'];

   }
   $smcFunc['db_free_result']($request);
   if ($output_method == 'echo')
   {
   echo $num_views;
   }
}

As you can see, the timestamps I set to be for any date you would have stored as dates for topics.

[colby2152]

Thanks.  I did some testing and noticed it counted the views for all topics in one board.  However, I feel there is an issue with the board list.  What I would like it to do is to take one board, count all of the page views (between two timestamps) of all topics in that board, and then perform the same count for all topics in its child boards, and subsequent grandchild boards, and greatgrandchild boards, and great-great-grandchild boards, etc..

[Joker™]

Thanks.  I did some testing and noticed it counted the views for all topics in one board.  However, I feel there is an issue with the board list.  What I would like it to do is to take one board, count all of the page views (between two timestamps) of all topics in that board, and then perform the same count for all topics in its child boards, and subsequent grandchild boards, and greatgrandchild boards, and great-great-grandchild boards, etc..

So you want to have separate view count for all level of boards or counts of all child boards(level 1,2,3,4 so on) added up and shown as a single entity, please clarify it.


Moreover, do you really have that much deep child boards? Picking all those deep child boards, counting all boards views and showing it at very last stage can make a heavy load on server.

[colby2152]

So you want to have separate view count for all level of boards or counts of all child boards(level 1,2,3,4 so on) added up and shown as a single entity, please clarify it.

I want to have the counts of all child boards, and the primary board, for the total views of all topics in those boards between two dates added up for one aggregate number.

Moreover, do you really have that much deep child boards? Picking all those deep child boards, counting all boards views and showing it at very last stage can make a heavy load on server.

Understandable.  Deepest levels that I have are four total levels of boards...

Category -> Board1 -> ChildBoard -> GrandChildBoard -> GreatGrandChildBoard

[Joker™]

I want to have the counts of all child boards, and the primary board, for the total views of all topics in those boards between two dates added up for one aggregate number.

Board1 -> ChildBoard -> GrandChildBoard -> GreatGrandChildBoard

Still whole query is going to be slow.

[colby2152]

How slow?

The numbers from this query are important to me - speed of the report isn't an issue as long as it doesn't overload the server.
If we could cap it off at three levels of child boards then

[ascaland]

Basically the function above counts as deep as 2 child boards from the parent which makes it to GrandChildBoard, and I made sure it worked perfectly as I tested it a ton of times and triple checked the results. If I were to make this recursively into n amount of child boards, I can assure you it gets slow past 2, because I already noticed running the query was slow even at 2. But I will modify the function again, just make sure this is the last request.

[colby2152]

I tested it for a board with two child boards.  One of the child boards has a view count of 365 for the timespan.  The other child board has zeo.  The parent board also has zero instead of 365.

[ascaland]

I tested it for a board with two child boards.  One of the child boards has a view count of 365 for the timespan.  The other child board has zeo.  The parent board also has zero instead of 365.

Does the parent board have any topics?

[colby2152]

I tested it for a board with two child boards.  One of the child boards has a view count of 365 for the timespan.  The other child board has zeo.  The parent board also has zero instead of 365.

Does the parent board have any topics?

No topics in the parent board, so it appears it is only counting the primary [parent] board.

[ascaland]

I tested it for a board with two child boards.  One of the child boards has a view count of 365 for the timespan.  The other child board has zeo.  The parent board also has zero instead of 365.

Does the parent board have any topics?

No topics in the parent board, so it appears it is only counting the primary [parent] board.

So what was the total result when you ran the script?

[colby2152]

Total result for parent board was zero whereas a child board had 365 for the same dates

[ascaland]

It should just be outputting a single entire result, are you sure this is being used correctly? Can I have a link to the site your using this on? Is there a form your using which I can use or are you just editting the file?

[colby2152]

It should just be outputting a single entire result, are you sure this is being used correctly? Can I have a link to the site your using this on? Is there a form your using which I can use or are you just editting the file?

Forum: http://www.profsl.com/smf/index.php?action=forum
Traffic Form: http://www.profsl.com/smf/traffic.html

[ascaland]

OMG WHY DOESNT THIS CRAP WORK

[Illori]

there have been posts before of people attempting this stuff and without a major impact to your server it is not possible, do some searching.

[colby2152]

OMG WHY DOESNT THIS CRAP WORK

Haha... well you were able to collect all hits for one particular board, so that is a start!

[ascaland]

It just boggles my mind why this wont work since its working perfectly on my testing site. Can you show me the entire PHP script (traffic.php) you currently have on your site?

[colby2152]

It just boggles my mind why this wont work since its working perfectly on my testing site. Can you show me the entire PHP script (traffic.php) you currently have on your site?

No problem...

Code Select Expand

<html>
<body>

<?php

require_once('SSI.php');

num_views();

function num_views($output_method = 'echo')
{
global $smcFunc;

// The specified parent board id
//$idboard = 1;
$idboard = $_POST['idboard'];
// The first date
//$timestamp1 = 1310065085;
$timestamp1 = strtotime($_POST['timestamp1']);
// The second date
//$timestamp2 = 1312767343;
$timestamp2 = strtotime($_POST['timestamp2']);


 // Retrieve all child boards who inherit from parent board, include those, then select those who inherit from the previous boards
                // It is possible to have duplicate board ids, however that isnt a problem here
  $request = $smcFunc['db_query']('', 
 'SELECT childBoards.id_board, requestedBoards.id_board 
 FROM {db_prefix}boards AS requestedBoards
 INNER JOIN {db_prefix}boards AS childBoards ON (childBoards.id_parent = {int:parentId})
 WHERE requestedBoards.id_parent = childBoards.id_board',
  array(
  'parentId' => $idboard,
  )
  );

  $boards = array($idboard);
  while ($row = $smcFunc['db_fetch_assoc']($request)) {
  $boards[] = $row['id_board'];
  }

  $smcFunc['db_free_result']($request);

  // Next, retrieve all the topics in the boards where the timestamp is between two dates.
  // Once found, just output num_views
  $request = $smcFunc['db_query']('', 
  'SELECT t.num_views 
  FROM {db_prefix}topics AS t
  INNER JOIN {db_prefix}messages AS m ON (m.id_msg = t.id_first_msg)
  WHERE t.id_board IN ({array_int:boards_list}) AND m.poster_time BETWEEN {int:date1} AND {int:date2}',
  array(
  'boards_list' => $boards,
  'date1' => $timestamp1,
  'date2' => $timestamp2,
  )
  );

  // Your total number of views in this variable
  $num_views = 0;
  while ($row = $smcFunc['db_fetch_assoc']($request))
  {
  $num_views += $row['num_views'];

  }
  $smcFunc['db_free_result']($request);
  if ($output_method == 'echo')
  {
  echo $num_views;
  }
}

?>