Query for Traffic

[colby2152]

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?

[Joker™]

There we go, the code looks awesome.

Thanks a lot.

Joker hands over a drink (soft one )

[ascaland]

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

[colby2152]

Latest code gives error....

Database error, given array of integer values is empty. (boards_list)

[ascaland]

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 1

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

[colby2152]

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

[ascaland]

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.

[colby2152]

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?

[Joker™]

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?

[ascaland]

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.

Code Select Expand

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

?>

[colby2152]

Error...

No database selected

[ascaland]

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.

[colby2152]

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.

[ascaland]

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.

[colby2152]

An Error Has Occurred!
Wrong value type sent to the database. Array of integers expected. (boards_list)

[ascaland]

I noticed the variable names got changed up from the original codes, so from the last block of code I posted,

Code (Replace) Select Expand

'parentId' => $boardId,

Code (With) Select Expand

'parentId' => $idboard,

[colby2152]

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!

[colby2152]

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

[ascaland]

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

[colby2152]

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.