News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Query for Traffic

Started by colby2152, July 07, 2011, 10:39:26 AM

Previous topic - Next topic

colby2152

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!!!

ascaland


colby2152

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!!!

ascaland

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.


colby2152

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!!!

colby2152

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!!!

Joker™

Try this

<?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
Android apps
Medium

How to enable Post Moderation

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

All support seeking PM's get microwaved

colby2152

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!!!

Joker™

Quote from: colby2152 on August 03, 2011, 12:10:23 PM
What are the variables in the code that I need to replace?

Just the ones you have asked for ;)



//board id
$idboard = 1;

// The first date
$timestamp1 = 1310065085;

// The second date
$timestamp2 = 1312367343;
Github Profile
Android apps
Medium

How to enable Post Moderation

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

All support seeking PM's get microwaved

colby2152

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!!!

Joker™

Quote from: colby2152 on August 03, 2011, 09:53:34 PM
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?


QuoteWhat about database IDs?
Which IDs?
Github Profile
Android apps
Medium

How to enable Post Moderation

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

All support seeking PM's get microwaved

colby2152

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!!!

Joker™

Quote from: colby2152 on August 10, 2011, 09:49:49 PM
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
require_once['above_file_name.php']
num_views();



QuoteCan 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.



Quotedb_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
Android apps
Medium

How to enable Post Moderation

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

All support seeking PM's get microwaved

colby2152

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!!!

Joker™

Quote from: colby2152 on August 11, 2011, 10:28:48 AM
Wait, so I should add this code to a php file instead?
Yes.


QuoteAny way that I can have it embedded into a form?
Quote from: Joker™ on August 11, 2011, 09:53:28 AM
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
require_once['above_file_name.php']
num_views();

Github Profile
Android apps
Medium

How to enable Post Moderation

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

All support seeking PM's get microwaved

colby2152

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!!!

colby2152

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!!!

ascaland

Hey Joker, how come the query you posted here,
// 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. :)

Joker™

Quote from: Project Evolution on August 11, 2011, 01:32:24 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
Android apps
Medium

How to enable Post Moderation

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

All support seeking PM's get microwaved

ascaland

<?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

Advertisement: