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.
SMF 1.1.x or 2.0?
A bit tricky to wrap my head around, but give this PHP code a try,
// 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.
Thanks for the help... long time coming, but I will test this out shortly.
Tested it with error...
Database error in block code. Please check the code.
How do I make it into a form?
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;
}
}
?>
What are the variables in the code that I need to replace?
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;
Hmmm, I didn't edit that code and it threw an error. What about database IDs?
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?
Well, where do I place this code? Can I place it in a Simple Portal PHP block? db_prefix.... does that change?
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.
Wait, so I should add this code to a php file instead? Any way that I can have it embedded into a form?
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();
Does above_file_name change to the name of my file? Is that at the beginning or end of the file?
Alright, I have output!
How do I set up a PHP form to enter the three variables? Board ID, Start Time, End Time
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. :)
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 ;).
<?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
I created an HTML form that sends data to the PHP file and produces output. I have no interest in breaking down the timestamps into seconds, minutes, or hours. I am interested in looking at the record of traffic between two dates (assume time is midnight). How can I enter two such dates and have them converted to the timestamp?
Quote from: Project Evolution on August 11, 2011, 01:37:25 PM
There we go, the code looks awesome. :D
Thanks a lot.
Joker hands over a drink (soft one :P)
Quote from: colby2152 on August 11, 2011, 01:48:40 PM
I created an HTML form that sends data to the PHP file and produces output. I have no interest in breaking down the timestamps into seconds, minutes, or hours. I am interested in looking at the record of traffic between two dates (assume time is midnight). How can I enter two such dates and have them converted to the timestamp?
You can use PHP's
strtotime() function to convert a formatted string to time.
http://cz.php.net/manual/en/function.strtotime.php
Latest code gives error....
Database error, given array of integer values is empty. (boards_list)
Quote from: colby2152 on August 11, 2011, 01:57:51 PM
Latest code gives error....
Database error, given array of integer values is empty. (boards_list)
This is most likely because in your original post you said child boards of child boards. So in this case picture this format,
Parent Board 1 -> Child Board 1 ->
Child Board of Child 1, Child Board of Child 2 Child Board 2 ->
Child Board of Child 1The underlined board is the ID you need to set, while the boards in bold are those which will have the number of views and replies counted.
Got it... what I really want is in your example..
Parent Board 1 -> Child Board 1 -> Child Board of Child 1, Child Board of Child 2 -> Grandchild Board of Child Board 1
Child Board 2 -> Child Board of Child 1
So, board ID, all boards underneath it plus that board
Well thats something quite different than what you wanted in your original post.. So im going to need time to rewrite a part of it.
Quote from: Project Evolution on August 11, 2011, 02:22:27 PM
Well thats something quite different than what you wanted in your original post.. So im going to need time to rewrite a part of it.
My apologies for the miscommunication. I truly appreciate the help you are providing here. What site(s) do you run?
Quote from: colby2152 on August 11, 2011, 02:06:16 PM
Parent Board 1 -> Child Board 1 -> Child Board of Child 1, Child Board of Child 2 -> Grandchild Board of Child Board 1
Child Board 2 -> Child Board of Child 1
Are you sure you want to go that deep as I'm pretty sure it's going to place some considerable amount of load on your database if you have nice number of posts.
Off topic question, on what sort of hosting are you running your forum?
I dont hose any major sites, my customization site has been offline though. :(
By the way, here is the updated code, give it a shot.
<?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, 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' => $boardId,
)
);
$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;
}
}
?>
Error...
No database selected
Hmm well thats an odd one.. This error only came up when you applied the above code? If that was the case, please try the updated code above.
Quote from: Project Evolution on August 11, 2011, 04:55:12 PM
Hmm well thats an odd one.. This error only came up when you applied the above code? If that was the case, please try the updated code above.
What was updated? Will try it later when I have access.
Quote from: colby2152 on August 12, 2011, 02:41:35 PM
Quote from: Project Evolution on August 11, 2011, 04:55:12 PM
Hmm well thats an odd one.. This error only came up when you applied the above code? If that was the case, please try the updated code above.
What was updated? Will try it later when I have access.
I accidentally missed a db_prefix in the query.
An Error Has Occurred!
Wrong value type sent to the database. Array of integers expected. (boards_list)
I noticed the variable names got changed up from the original codes, so from the last block of code I posted,
'parentId' => $boardId,
'parentId' => $idboard,
Thanks... I had changed the ID in my variables but not in the code. I will test the numbers out, but right now I have results!
Initial testing shows some issues. For example, I test on a parent board which has no topics and the result is zero. However, the results of its child boards is greater than zero. The code should give at leas the sum of the child boards.
I also tested on a board in which there were several posts two days ago. I used a date span that would cover August 10th through today and the result was zero. Please note that I am utilizing the strtotime function where inputs in the HTML form are in the form of DD Month YYYY (example 14 August 2011).
I see, well im going to have to retest this and fix it up so expect some time for me to do it.
Quote from: Project Evolution on August 14, 2011, 10:38:14 PM
I see, well im going to have to retest this and fix it up so expect some time for me to do it.
No problem... I appreciate it and would look forward to working with you for some paid coding opportunities.
Quote from: Project Evolution on August 14, 2011, 10:38:14 PM
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.
Here is the entire num_views function again,
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.
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..
Quote from: colby2152 on August 18, 2011, 10:43:36 AM
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.
Quote from: Joker™ on August 18, 2011, 10:53:46 AM
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.
Quote from: Joker™ on August 18, 2011, 10:53:46 AM
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
Quote from: colby2152 on August 18, 2011, 11:02:28 AM
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.
Quote from: colby2152 on August 18, 2011, 11:02:28 AM
Board1 -> ChildBoard -> GrandChildBoard -> GreatGrandChildBoard
Still whole query is going to be slow.
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
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.
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.
Quote from: colby2152 on August 18, 2011, 12:40:53 PM
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?
Quote from: Project Evolution on August 18, 2011, 12:46:02 PM
Quote from: colby2152 on August 18, 2011, 12:40:53 PM
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.
Quote from: colby2152 on August 18, 2011, 12:49:33 PM
Quote from: Project Evolution on August 18, 2011, 12:46:02 PM
Quote from: colby2152 on August 18, 2011, 12:40:53 PM
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?
Total result for parent board was zero whereas a child board had 365 for the same dates
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?
Quote from: Project Evolution on August 18, 2011, 01:18:28 PM
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
OMG WHY DOESNT THIS CRAP WORK
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.
Quote from: Project Evolution on August 18, 2011, 02:05:55 PM
OMG WHY DOESNT THIS CRAP WORK
Haha... well you were able to collect all hits for one particular board, so that is a start!
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?
Quote from: Project Evolution on August 18, 2011, 02:36:20 PM
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...
<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;
}
}
?>
You actually forgot to update the query, so could you replace,
// 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,
)
);
With,
// 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,
)
);
Wow... not sure how that happened. That fixed the issue with the child board. Let me check on ChildofChild and ChildofChildofChild boards.
Hmm, still not working appropriately. I am using test board = 9 (http://www.profsl.com/smf/index.php?board=9.0) for July. It has total views of 9926 with the following results for its child boards:
10 FGM Rules 0
11 FGM Official Rosters 0
12 FGM Transactions 6922
152 Updated on Fantrax 96
13 Completed Moves 6384
14 Invalid Moves 382
Board IDs 10 and 11 definitely have more than zero.
I then went up to the parent level and its total was 11000 which suggest that it is only looking at its board and then the child boards.
By the looks of the individual results it looks like the results arent even correct?
Quote from: Project Evolution on August 18, 2011, 03:36:46 PM
By the looks of the individual results it looks like the results arent even correct?
I tabulated on another board to make sure they were correct in counting one board.
I dont know what to tell you, but I really cant figure out why this wont work properly. Im convinced it does work properly because its fine on my forum, although its not counting huge results, you might have to wait for someone more experienced with SQL or some sort of idea to getting this working.
Quote from: Project Evolution on August 18, 2011, 03:51:52 PM
I dont know what to tell you, but I really cant figure out why this wont work properly. Im convinced it does work properly because its fine on my forum, although its not counting huge results, you might have to wait for someone more experienced with SQL or some sort of idea to getting this working.
Thanks for helping me out with this. I will see if someone else, say Jester, chimes in on this thread as I do more testing.
Quote from: colby2152 on August 18, 2011, 04:03:27 PM
Quote from: Project Evolution on August 18, 2011, 03:51:52 PM
I dont know what to tell you, but I really cant figure out why this wont work properly. Im convinced it does work properly because its fine on my forum, although its not counting huge results, you might have to wait for someone more experienced with SQL or some sort of idea to getting this working.
Thanks for helping me out with this. I will see if someone else, say Jester, chimes in on this thread as I do more testing.
I think I meant Joker.
Quote from: colby2152 on August 19, 2011, 08:26:44 AM
Quote from: colby2152 on August 18, 2011, 04:03:27 PM
Quote from: Project Evolution on August 18, 2011, 03:51:52 PM
I dont know what to tell you, but I really cant figure out why this wont work properly. Im convinced it does work properly because its fine on my forum, although its not counting huge results, you might have to wait for someone more experienced with SQL or some sort of idea to getting this working.
Thanks for helping me out with this. I will see if someone else, say Jester, chimes in on this thread as I do more testing.
I think I meant Joker.
Someone called me :o. How can I help here, as I've already said multiple times that what you are looking for is very unhealthy for servers.
This isn't code that will continously run. It is a report that I would run once a month. What I need is for the function to be able to count the number of total views of all topics (between two dates) on a given board (input board ID) and all child boards, all subsequent child boards, and the next level of child boards.
Quote from: colby2152 on August 19, 2011, 10:33:30 AM
This isn't code that will continously run. It is a report that I would run once a month. What I need is for the function to be able to count the number of total views of all topics (between two dates) on a given board (input board ID) and all child boards, all subsequent child boards, and the next level of child boards.
Although I don't see any issue with "Project Evolution's" code, still if you say, I'll try to look into it by sunday most probably.
Joker use Saturday to flush his mind from workplace garbage.
Quote from: Joker™ on August 19, 2011, 05:03:50 PM
Quote from: colby2152 on August 19, 2011, 10:33:30 AM
This isn't code that will continously run. It is a report that I would run once a month. What I need is for the function to be able to count the number of total views of all topics (between two dates) on a given board (input board ID) and all child boards, all subsequent child boards, and the next level of child boards.
Although I don't see any issue with "Project Evolution's" code, still if you say, I'll try to look into it by sunday most probably.
Joker use Saturday to flush his mind from workplace garbage.
Thanks.
FYI, I validated that the current code works for three levels. I tried with a fourth level and it counts everything but the last level of child boards. I assume this should be an easy fix? Thanks!
Well if your sure the results are correct and you want another level counted, try this one out,
// 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))
LEFT JOIN {db_prefix}boards AS childOfChildOfChildBoard ON (childOfChildOfChildBoard.id_parent IN (childOfChildBoard.id_board))
WHERE requestedBoard.id_board IN (childBoard.id_board) OR
requestedBoard.id_board IN (childOfChildBoard.id_board) OR
requestedBoard.id_board IN (childOfChildOfChildBoard.id_board)',
array(
'parentId' => $idboard,
)
);
These queries make me cringe...
Thanks... I will test that out. If you want to cringe more then I suppose this will do...
The next and final thing I want to do is match it to my HTML form, so that it runs the script for up to 100 different board IDs on one set of timestamps. I basically want to create a looping process with new output for each iteration in the loop. I could do this with multiple forms, but how do I set the timestamp values to be dependent upon master inputs (researching)?
Simply just loop through the queries with a different id each time, nothing complicated but I can tell you it most likely will screw up your server if your thinking 100 different ids. I would assume anything even over 10 (or less!) would do some damage!
Quote from: Project Evolution on August 20, 2011, 04:08:54 PM
Simply just loop through the queries with a different id each time, nothing complicated but I can tell you it most likely will screw up your server if your thinking 100 different ids. I would assume anything even over 10 (or less!) would do some damage!
Temporary damage? I suppose I can check the server load as I perform this. Keep in mind that this is a report only I can access and I would do it rather infrequently.
Quote from: colby2152 on August 20, 2011, 04:09:46 PM
Quote from: Project Evolution on August 20, 2011, 04:08:54 PM
Simply just loop through the queries with a different id each time, nothing complicated but I can tell you it most likely will screw up your server if your thinking 100 different ids. I would assume anything even over 10 (or less!) would do some damage!
Temporary damage? I suppose I can check the server load as I perform this. Keep in mind that this is a report only I can access and I would do it rather infrequently.
Even so, 100 board ids resulting in 200 expensive queries is something I wouldnt even consider. Im no expert on servers or anything but that doesnt sound good to me.
Quote from: Project Evolution on August 20, 2011, 04:16:46 PM
Quote from: colby2152 on August 20, 2011, 04:09:46 PM
Quote from: Project Evolution on August 20, 2011, 04:08:54 PM
Simply just loop through the queries with a different id each time, nothing complicated but I can tell you it most likely will screw up your server if your thinking 100 different ids. I would assume anything even over 10 (or less!) would do some damage!
Temporary damage? I suppose I can check the server load as I perform this. Keep in mind that this is a report only I can access and I would do it rather infrequently.
It seems like it does a similar query to the total unread posts or new replies to your posts links that are part of SMF. It appears the increase the load slightly by about 0.20. If this were something that my users had access to and would use frequently then I would think twice about it, but it is just a reporting tool used by yours truly.
Even so, 100 board ids resulting in 200 expensive queries is something I wouldnt even consider. Im no expert on servers or anything but that doesnt sound good to me.
Updated code worked for four levels! Thanks for everything. Now, I just need to figure out how to change my HTML form, so that it uses one set of time stamps for X board ID inputs.
I know what this is not doing correctly. It is counting total number of page views between two timestamps on the post-dates rather than the view-dates. This still provides some useful information, but is there a way to tailor the code so that I can see total page views during a time period?
I believe that isnt possible.
Quote from: Project Evolution on August 20, 2011, 07:20:26 PM
I believe that isnt possible.
Fair enough. While that is what I want... I can make do without it by taking monthly reports.
One more request as I have explored several options on how to do this. I want to change the board ID input, so that it is a series of board IDs, separated by commas like this: 1, 45, 542, 209, 1031, 78
The PHP script loops through the inputs, providing output for each board ID in the same format, so: Views(1), Views(45), Views(542), Views(209), Views(1031), Views(78)
It is essentially the same script but with a parent script that loops through the base script N times and saves the outputs in an array until the looping is finished. The resulting output is the array of saved numbers, separated by commas.
I did that in one of my mods.
http://custom.simplemachines.org/mods/index.php?mod=2975
I would highly recommend you to read more about arrays on php.net. The power of arrays is simply amazing. I used them in one of the scripts at my workplace and the output was simply astonishing.
Do you think you could lend a hand with updating the code?
Not any soon. I'll try to find some time for this, but no promises :).
Quote from: colby2152 on August 26, 2011, 03:33:15 PM
One more request as I have explored several options on how to do this. I want to change the board ID input, so that it is a series of board IDs, separated by commas like this: 1, 45, 542, 209, 1031, 78
The PHP script loops through the inputs, providing output for each board ID in the same format, so: Views(1), Views(45), Views(542), Views(209), Views(1031), Views(78)
It is essentially the same script but with a parent script that loops through the base script N times and saves the outputs in an array until the looping is finished. The resulting output is the array of saved numbers, separated by commas.
Bump... any developer who is reading this. I am tired of manually counting this board by board. I do it for a couple hundred boards! I would like someone to update the script that it can take multiple board IDs, separated by comma. I need this completed after this weekend.
I will pay!
Quote from: Project Evolution on May 23, 2012, 10:36:26 PM
Quote from: colby2152 on May 23, 2012, 09:08:40 PM
Quote from: colby2152 on April 25, 2012, 10:18:31 PM
I will pay!
Any takers?
Does this include boards past the 4th level of child boards?
No. I have specifically set my forum to not have child board past such a level. With zero being the first board in a category, I do not have any child board beyond level four.