Advertisement:

Author Topic: Query for Traffic  (Read 12792 times)

Offline colby2152

  • Sr. Member
  • ****
  • Posts: 872
  • Gender: Male
    • ProFSL
Query for Traffic
« on: July 07, 2011, 10:39:26 AM »
I want to have a query built, or some other mechanism, that counts the total number of page views of all of the posts in all child boards of all child boards of a given board between two dates.

If dates is an unattainable qualifier then I could do without that.
ProFSL.com - Pro Fantasy Sports Leagues - Advanced fantasy sports leagues, contests, free chances at cash/prizes, and sports discussion.

Need a server? I am happy to advocate this hosting solution - hands on customer service and reliability is relieving!!!

Offline Anthony`

  • Sophist Member
  • *****
  • Posts: 1,347
  • Gender: Male
  • Formerly Project Evolution
Re: Query for Traffic
« Reply #1 on: July 12, 2011, 11:16:16 PM »
SMF 1.1.x or 2.0?

Offline colby2152

  • Sr. Member
  • ****
  • Posts: 872
  • Gender: Male
    • ProFSL
Re: Query for Traffic
« Reply #2 on: July 14, 2011, 11:40:46 PM »
SMF 1.1.x or 2.0?

Thanks for responding... 2.0 RC5
ProFSL.com - Pro Fantasy Sports Leagues - Advanced fantasy sports leagues, contests, free chances at cash/prizes, and sports discussion.

Need a server? I am happy to advocate this hosting solution - hands on customer service and reliability is relieving!!!

Offline Anthony`

  • Sophist Member
  • *****
  • Posts: 1,347
  • Gender: Male
  • Formerly Project Evolution
Re: Query for Traffic
« Reply #3 on: July 16, 2011, 07:40:41 PM »
A bit tricky to wrap my head around, but give this PHP code a try,
Code: (PHP) [Select]
// The specified parent board id
$boardId = 1;
// The first date
$timestamp1 = 1310526542;
// The second date
$timestamp2 = 1310526550;

// Retrieve all child boards who inherit from parent board, then select those who inherit from the previous boards
$request = $smcFunc['db_query']('',
'SELECT 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' => $boardId,
)
);

$boards = array();
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 topic.num_views FROM {db_prefix}topics AS topic
INNER JOIN {db_prefix}messages AS msg ON (msg.id_topic = topic.id_topic)
WHERE topic.id_board IN (' . implode(',', $boards) . ') AND msg.poster_time BETWEEN {int:date1} AND {int:date2}',
array(
'date1' => $timestamp1, 'date2' => $timestamp2,
)
);

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

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

Timestamps can be generated using PHP here (the ones I have are generated times from July 13, 2011, 04:09:02 AM and July 13, 2011, 04:09:48 AM),
http://php.net/manual/en/function.time.php

Hopefully you understand how to get the timestamps.


Offline colby2152

  • Sr. Member
  • ****
  • Posts: 872
  • Gender: Male
    • ProFSL
Re: Query for Traffic
« Reply #4 on: August 02, 2011, 10:08:35 PM »
Thanks for the help... long time coming, but I will test this out shortly.
ProFSL.com - Pro Fantasy Sports Leagues - Advanced fantasy sports leagues, contests, free chances at cash/prizes, and sports discussion.

Need a server? I am happy to advocate this hosting solution - hands on customer service and reliability is relieving!!!

Offline colby2152

  • Sr. Member
  • ****
  • Posts: 872
  • Gender: Male
    • ProFSL
Re: Query for Traffic
« Reply #5 on: August 02, 2011, 10:09:35 PM »
Tested it with error...
Database error in block code. Please check the code.

How do I make it into a form?
ProFSL.com - Pro Fantasy Sports Leagues - Advanced fantasy sports leagues, contests, free chances at cash/prizes, and sports discussion.

Need a server? I am happy to advocate this hosting solution - hands on customer service and reliability is relieving!!!

Offline Joker™

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 5,830
  • Gender: Male
Re: Query for Traffic
« Reply #6 on: August 03, 2011, 06:33:01 AM »
Try this

Code: [Select]
<?php

require_once('SSI.php');

num_views();

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

// The specified parent board id
$idboard 1;
// The first date
$timestamp1 1310065085;
// The second date
$timestamp2 1312367343;

// Retrieve all child boards who inherit from parent board, then select those who inherit from the previous boards
$request $smcFunc['db_query'](''
'SELECT b.id_board FROM {db_prefix}boards AS b
WHERE b.id_parent = {int:parentId}'
,
array(
'parentId' => $idboard,
)
);

$boards = array();
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_topic = t.id_topic)
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;
}
}

?>
Github Profile
My Mods
How to enable Post Moderation
Paid Support
Elkarte


"For the wise man looks into space and he knows there is no limited dimensions." - Laozi

All support seeking PM's get microwaved

Offline colby2152

  • Sr. Member
  • ****
  • Posts: 872
  • Gender: Male
    • ProFSL
Re: Query for Traffic
« Reply #7 on: August 03, 2011, 12:10:23 PM »
What are the variables in the code that I need to replace?
ProFSL.com - Pro Fantasy Sports Leagues - Advanced fantasy sports leagues, contests, free chances at cash/prizes, and sports discussion.

Need a server? I am happy to advocate this hosting solution - hands on customer service and reliability is relieving!!!

Offline Joker™

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 5,830
  • Gender: Male
Re: Query for Traffic
« Reply #8 on: August 03, 2011, 12:21:50 PM »
What are the variables in the code that I need to replace?

Just the ones you have asked for ;)


Code: [Select]
//board id
$idboard = 1;

// The first date
$timestamp1 = 1310065085;

// The second date
$timestamp2 = 1312367343;
Github Profile
My Mods
How to enable Post Moderation
Paid Support
Elkarte


"For the wise man looks into space and he knows there is no limited dimensions." - Laozi

All support seeking PM's get microwaved

Offline colby2152

  • Sr. Member
  • ****
  • Posts: 872
  • Gender: Male
    • ProFSL
Re: Query for Traffic
« Reply #9 on: August 03, 2011, 09:53:34 PM »
Hmmm, I didn't edit that code and it threw an error.  What about database IDs?
ProFSL.com - Pro Fantasy Sports Leagues - Advanced fantasy sports leagues, contests, free chances at cash/prizes, and sports discussion.

Need a server? I am happy to advocate this hosting solution - hands on customer service and reliability is relieving!!!

Offline Joker™

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 5,830
  • Gender: Male
Re: Query for Traffic
« Reply #10 on: August 04, 2011, 09:42:18 AM »
Hmmm, I didn't edit that code and it threw an error.
When you go to a doctor you don't tell him that you are ill, you tell him about your exact illness issue. Similarly, when a code doesn't work you are supposed to tell the coder about

- What didn't worked exacty
- What was the error


Have you changed the board id as per your forum requirements?
Have you changed the time variables as per your forum requirement?


Quote
What about database IDs?
Which IDs?
Github Profile
My Mods
How to enable Post Moderation
Paid Support
Elkarte


"For the wise man looks into space and he knows there is no limited dimensions." - Laozi

All support seeking PM's get microwaved

Offline colby2152

  • Sr. Member
  • ****
  • Posts: 872
  • Gender: Male
    • ProFSL
Re: Query for Traffic
« Reply #11 on: August 10, 2011, 09:49:49 PM »
Well, where do I place this code?  Can I place it in a Simple Portal PHP block?  db_prefix.... does that change?
ProFSL.com - Pro Fantasy Sports Leagues - Advanced fantasy sports leagues, contests, free chances at cash/prizes, and sports discussion.

Need a server? I am happy to advocate this hosting solution - hands on customer service and reliability is relieving!!!

Offline Joker™

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 5,830
  • Gender: Male
Re: Query for Traffic
« Reply #12 on: August 11, 2011, 09:53:28 AM »
Well, where do I place this code
The code posted above gives you complete freedom to show the count result anywhere.

Just call this file and function in the the template file you want to show the result.

Like
Code: [Select]
require_once['above_file_name.php']
num_views();


Quote
Can I place it in a Simple Portal PHP block?
No idea, I think on this someone else can answer you better as I've never used the portals.



Quote
db_prefix.... does that change?
SMF is going to pick your DB prefix itself by inbuilt functions.

Joker loves SMF coz of these reasons ;D.
Github Profile
My Mods
How to enable Post Moderation
Paid Support
Elkarte


"For the wise man looks into space and he knows there is no limited dimensions." - Laozi

All support seeking PM's get microwaved

Offline colby2152

  • Sr. Member
  • ****
  • Posts: 872
  • Gender: Male
    • ProFSL
Re: Query for Traffic
« Reply #13 on: August 11, 2011, 10:28:48 AM »
Wait, so I should add this code to a php file instead?  Any way that I can have it embedded into a form?
ProFSL.com - Pro Fantasy Sports Leagues - Advanced fantasy sports leagues, contests, free chances at cash/prizes, and sports discussion.

Need a server? I am happy to advocate this hosting solution - hands on customer service and reliability is relieving!!!

Offline Joker™

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 5,830
  • Gender: Male
Re: Query for Traffic
« Reply #14 on: August 11, 2011, 11:39:02 AM »
Wait, so I should add this code to a php file instead?
Yes.


Quote
Any way that I can have it embedded into a form?
The code posted above gives you complete freedom to show the count result anywhere.

Just call this file and function in the the template file you want to show the result.

Like
Code: [Select]
require_once['above_file_name.php']
num_views();
Github Profile
My Mods
How to enable Post Moderation
Paid Support
Elkarte


"For the wise man looks into space and he knows there is no limited dimensions." - Laozi

All support seeking PM's get microwaved

Offline colby2152

  • Sr. Member
  • ****
  • Posts: 872
  • Gender: Male
    • ProFSL
Re: Query for Traffic
« Reply #15 on: August 11, 2011, 01:21:56 PM »
Does above_file_name change to the name of my file?  Is that at the beginning or end of the file?
ProFSL.com - Pro Fantasy Sports Leagues - Advanced fantasy sports leagues, contests, free chances at cash/prizes, and sports discussion.

Need a server? I am happy to advocate this hosting solution - hands on customer service and reliability is relieving!!!

Offline colby2152

  • Sr. Member
  • ****
  • Posts: 872
  • Gender: Male
    • ProFSL
Re: Query for Traffic
« Reply #16 on: August 11, 2011, 01:26:03 PM »
Alright, I have output!

How do I set up a PHP form to enter the three variables?  Board ID, Start Time, End Time
ProFSL.com - Pro Fantasy Sports Leagues - Advanced fantasy sports leagues, contests, free chances at cash/prizes, and sports discussion.

Need a server? I am happy to advocate this hosting solution - hands on customer service and reliability is relieving!!!

Offline Anthony`

  • Sophist Member
  • *****
  • Posts: 1,347
  • Gender: Male
  • Formerly Project Evolution
Re: Query for Traffic
« Reply #17 on: August 11, 2011, 01:32:24 PM »
Hey Joker, how come the query you posted here,
Code: [Select]
// Retrieve all child boards who inherit from parent board, then select those who inherit from the previous boards
$request = $smcFunc['db_query']('',
'SELECT b.id_board FROM {db_prefix}boards AS b
WHERE b.id_parent = {int:parentId}',
array(
'parentId' => $idboard,
)
);

Is so different from mine? You only output child boards of a given board, not child boards of child boards. :)

Offline Joker™

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 5,830
  • Gender: Male
Re: Query for Traffic
« Reply #18 on: August 11, 2011, 01:35:01 PM »
You only output child boards of a given board, not child boards of child boards. :)
I must have missed the point there. If you like you can re-modify it ;).
Github Profile
My Mods
How to enable Post Moderation
Paid Support
Elkarte


"For the wise man looks into space and he knows there is no limited dimensions." - Laozi

All support seeking PM's get microwaved

Offline Anthony`

  • Sophist Member
  • *****
  • Posts: 1,347
  • Gender: Male
  • Formerly Project Evolution
Re: Query for Traffic
« Reply #19 on: August 11, 2011, 01:37:25 PM »
Code: [Select]
<?php

require_once('SSI.php');

num_views();

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

 // The specified parent board id
 $idboard 1;
 // The first date
 $timestamp1 1310065085;
 // The second date
 $timestamp2 1312367343;

// Retrieve all child boards who inherit from parent board, then select those who inherit from the previous boards
 $request $smcFunc['db_query'](''
'SELECT 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' => $boardId,
 )
 );

 $boards = array();
 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_topic = t.id_topic)
 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;
 }
}

?>

There we go, the code looks awesome. :D