Customizing SMF > SMF Coding Discussion

Query for Traffic

(1/19) > >>

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.

Anthony`:
SMF 1.1.x or 2.0?

colby2152:

--- Quote from: Project Evolution on July 12, 2011, 11:16:16 PM ---SMF 1.1.x or 2.0?

--- End quote ---

Thanks for responding... 2.0 RC5

Anthony`:
A bit tricky to wrap my head around, but give this PHP code a try,

--- Code: (PHP) --- // 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);
--- End code ---

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.

Navigation

[0] Message Index

[#] Next page