Recent Topics

Statistics on the sidebar

Started by on Jun 17, 2005 – Contents updated: Jun 17, 2005

Jun 17, 2005 21:43    

So I wanted something that could tell me (and the readers) some stats about the blog. LIke total number of words, total number of posts, total images posted etc.

So I thought I could use this to dive into php a little bit, and I'm quite pleased with the results. However, I'm sure I made a ton of stupid mistakes or things that could be done alot better otherwise. So let me hear about it :-)

Heres the output as I currently have it on my blog, just so you have an idea. Maybe somebody finds this useful.

During it's 28 days of existence, this blog has achieved the following:

* 52504 letters
* 10781 words
* 37 posts
* 291 words per post
* 4.87 letters/word
* 11 posts that contain images
* 15 images posted in total
* 2.06 posts per day
* 7 registered users

Longest Post:
Inker's Diary ......(1387 words)
Shortest Post:
Timetables li...(23 words)
Post with most Images:
Lost and Earthqua...(3)

The Bloggers seem to love the letter "e" which was written a whooping 5578 times. But they also seem fond of "a" which they used 4858 times.

On the other hand, the "z" doesn't get much love. It was used a mere 19 times. "q" isn't off much better either though, having been used only 67 times.

Add the following to wherever you want the statistics to show up in the _main.php (I have them underneath the calendar in the sidebar).
Also, as you can see you can easly change what it reads out. So switch things around freely.

You should probably stay away from the part that gets letter statistics if you have a bigger blog or lots of hits. I'm pretty sure this operation is very expensive, so simply comment that part out.

Code

<!-- ====================================== HACK =========================================== -->
<!-- =================================== Statistics =================================== -->
<?php
  $wordcount;
  $postscount;
  $avgwordcount;
  $avgpostday;
  $countusers;
  $countpostswithimg;
  $countimages;
  $daysold;
 
  $longestcount;
  $longestlink;
  $longesttitle;
  
  $shortestcount;
  $shortestlink;
  $shortesttitle;
 
  $maximages;
  $maximagesTitle;
  $maximagesLink;
 
  $countmaxletter;
  $maxletter;
  $countsecondmaxletter;
  $maxsecondletter;
 
  $countfewletter;
  $fewletter;
  $countseconedfewletter;
  $fewsecondletter;
 
  $allpostsContent;
  $countletters;
 
  // getting wordcount
  $results = mysql_query('select sum(post_wordcount) from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $wordcount = mysql_result($results , 0);
  
 
  // getting postcount
  $results = mysql_query('select count(post_wordcount) from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $postscount = mysql_result($results , 0);
 
 
  // getting average wordcount
  $results = mysql_query('select avg(post_wordcount) from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $avgwordcount = round (mysql_result($results , 0));
 
 
  // getting average posts/day
  $results = mysql_query('select DATEDIFF(CURRENT_DATE(),(select min(post_issue_date) from evo_posts))');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $daysold = mysql_result($results, 0);
  $avgpostday = round($postscount/$daysold,2);
 
  // getting usercount
  $results = mysql_query('select count(ID) from evo_users');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $countuserst = mysql_result($results , 0);
 
  // getting longest post
  $results = mysql_query('SELECT post_wordcount, id, post_title from evo_posts order by post_wordcount desc LIMIT 1');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $longestcount = mysql_result($results , 0,0);
  $longestlink = '/blog/?p='.mysql_result($results , 0,1);
  $longesttitle = mysql_result($results , 0,2);
  if(strlen($longesttitle) > 20){
    $longesttitle = substr($longesttitle, 0, 17).'...';
  }
 
  // getting shortest post
  $results = mysql_query('SELECT post_wordcount, id, post_title from evo_posts order by post_wordcount asc LIMIT 1');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $shortestcount = mysql_result($results , 0,0);
  $shortestlink = '/blog/?p='.mysql_result($results , 0,1);
  $shortesttitle = mysql_result($results , 0,2);
  if(strlen($shortesttitle) > 20){
    $shortesttitle = substr($shortesttitle, 0, 17).'...';
  }
 
  
  // getting no of posts with images
  // uncomment this if you disable the no of images block below (this way the no of posts with images will still work)
  /*
  $results = mysql_query('select count(id) from evo_posts where post_content like \'%<img%\'');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $countpostswithimg = mysql_result($results , 0);
  */
 
  // getting no of Images and no of posts with images
  // NOTE: this is a very expensive operation and I'm sure there are smarter ways to do this, so use with care
  $results = mysql_query('select post_content, id, post_title  from evo_posts where post_content like \'%<img%\'');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $maximages = 0;
  $countpostswithimg = mysql_num_rows($results);
  for ($i = 0; $i < $countpostswithimg; $i++)
  {
    $thisamount = substr_count(mysql_result($results , $i,0),'<img ');
    $countimages += $thisamount;
    if($thisamount>$maximages)
    {  
      $maximages = $thisamount;
      $maximagesTitle = mysql_result($results , $i,2);
      if(strlen($maximagesTitle) > 20){
        $maximagesTitle = substr($maximagesTitle, 0, 17).'...';
      }
      $maximagesLink = '/blog/?p='.mysql_result($results ,$i,1);
    }
  }
 
  // we also get stats of letters........very very expensive operation, i dont recommand this
 
  $results = mysql_query('select LOWER(post_content) from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  for ($i = 0; $i < mysql_num_rows($results); $i++)
  {
    $allpostsContent = $allpostsContent.mysql_result($results , $i);
  }
  // we loop through the alphabet
  for ($i=97; $i<=122; $i++) {
    $x = chr($i);
    $thisamount = substr_count($allpostsContent,$x);
    //echo $x.' - '.$thisamount.'<BR />';
    $countletters += $thisamount;
    if($thisamount>$countmaxletter)
    {
      $countsecondmaxletter = $countmaxletter;
      $maxsecondletter = $maxletter;
      $maxletter = $x;
      $countmaxletter = $thisamount;
    }
    else if($thisamount<$countfewletter)
    {
      $countseconedfewletter = $countfewletter;
      $fewsecondletter = $fewletter;
      $fewletter = $x;
      $countfewletter = $thisamount;      
    }
    if($i==97){
      $countfewletter  = $thisamount;
      $fewletter = $x;
    }
  }
 
// <!-- ====================================== Echo Statistics =========================================== -->
 
  echo 'During it\'s '.$daysold.' days of existence, this blog has achieved the following:<ul>';
  if($countletters>0){
    echo  '<li>'.$countletters.' letters';
  }
 
  echo  '<li>'.$wordcount.' words';
  echo  '<li>'.$postscount.' posts';
  echo  '<li>'.$avgwordcount.' words per post';
 
  if($countletters>0){
    echo  '<li>'.round($countletters/$wordcount,2).' letters/word';  
  }
 
  echo  '<li>'.$countpostswithimg.' posts that contain images';
  echo  '<li>'.$countimages.' images posted in total';
  echo  '<li>'.$avgpostday.' posts per day';
  echo  '<li>'.$countuserst.' registered users';
  echo '</ul><BR />';
  echo  'Longest Post:<BR /> <a href="'.$longestlink.'">'.$longesttitle.'('.$longestcount.' words)</a><BR />';
  echo  'Shortest Post:<BR /> <a href="'.$shortestlink.'">'.$shortesttitle.'('.$shortestcount.' words)</a><BR />';
  echo  'Post with most Images:<BR /> <a href="'.$maximagesLink.'">'.$maximagesTitle.'('.$maximages.')</a><BR />';
  if($countletters>0){
    echo  '<BR />The Bloggers seem to love the letter "'.$maxletter.'" which was written a whooping '.$countmaxletter.' times. But they also seem fond of "'.$maxsecondletter.'" which they used '.$countsecondmaxletter.' times.<BR />';
    echo  '<BR />On the other hand, the "'.$fewletter.'" doesn\'t get much love. It was used a mere '.$countfewletter.' times. "'.$fewsecondletter.'" isn\'t off much better either though, having been used only '.$countseconedfewletter.' times.<BR />';
  }
?>
<!-- ====================================== END HACK =========================================== -->

Jun 17, 2005 21:59

It would probably be cheaper to do the following:

1. Create a table in your database with the necessary statistics. Call it evo_poststats or something. It could have two columns: "Stat" and "Value"

2. Whenever a post is saved, re-compute these values, and insert them in the database.

Code

INSERT INTO evo_poststats(stat, value) VALUES( 'most_used_letter', 'e');
INSERT INTO evo_poststats(stat, value) VALUES( 'least_used_letter', 'z');
...

3. In your skin, just read them out of the table, with something like this:

Code

<?php
$most_used_letter = $DB->get_var( "select value from evo_poststats where stat = 'most_used_letter'", 'value');
?>The most used letter is: <?php echo $most_used_letter ?>

For step 2, I believe you can do this in 0.9.2 with the a plugin that fires during the OnPostSave event.

Jun 17, 2005 22:05

Hmm, your probably right. I guess I'll try to do that tomorrow. Care to elaborate where I might find that OnPostSave event?

Jun 18, 2005 14:41

hey again,

I have done some more work and added some more statistics (most used word, longest used word, etc.)

Is there a way to have mysql return to me some of these things, or do I have to calculate that stuff in php? I realize that's it's very expensive, so I was playing around with sql, but tbh im not really that good at it.

So what I want mysql to do is:
look at the data of every row from evo_posts.post_content and then return to me the top say 10 words (seperated by " ")?

what I do right now is:

Code

select LOWER(post_content) from evo_posts

then in php I loop through the array and add it all to one long var:

Code

for ($i = 0; $i < mysql_num_rows($results); $i++)
{
  $allpostsContent = $allpostsContent.mysql_result($results , $i);
}

then I explode the array and tell it I only want unique values:

Code

$words = array_unique(explode(' ',$allpostsContent));

then I go through each value of $words and call

Code

$thisamount = substr_count($allpostsContent,' '.$words[$i]);

to find out the number of occurences and then compare them to the top one so far, etc.

for the letters I use a similar method:

Code

for ($i=97; $i<=122; $i++) {
    $x = chr($i);
    $thisamount = substr_count($allpostsContent,$x);
    $countletters += $thisamount;
    if($thisamount>$countmaxletter)
    {
      $countsecondmaxletter = $countmaxletter;
      $maxsecondletter = $maxletter;
      $maxletter = $x;
      $countmaxletter = $thisamount;
    }
    else if($thisamount>$countsecondmaxletter)
    {
      $countsecondmaxletter = $thisamount;
      $maxsecondletter = $x;  
    }
    else if($thisamount<$countfewletter)
    {
      $countseconedfewletter = $countfewletter;
      $fewsecondletter = $fewletter;
      $fewletter = $x;
      $countfewletter = $thisamount;      
    }
    else if($thisamount<$countseconedfewletter)
    {
      $countseconedfewletter = $thisamount;
      $fewsecondletter = $x;  
    }
    if($i==97){
      $countfewletter  = $thisamount;
      $fewletter = $x;
    }
 
    // here we can actually write the stats for every letter.
    // echo $x.' - '.$thisamount.'<BR />';
  }

But I already notice that this is slow, and it'll only get slower the more content there is.

Somebody tell me that I can have mysql do all that for me and return to me?

I looked at the event stuff and I realize this is probably the best way to do it so I am gonna look into it. I'm using 0.9.0.11 and not 9.2 But even if its all only calculated on an update (say edit/write/delete post) doing it this way is still rather expensive, and I can see this really going downhill once I have a few hundred posts.

Thank you guys once again

Jun 18, 2005 22:11

I think I could improve speed somewhat and I also added a bunch of more statistics. This is fun, seeing which words and letters are used how often, lol

heres my current output:
BTW: posts (such as longest post) link to the individual post and top words all link to the search, so you can click on it and find all posts that have that word in it.

Statistics

* 19 Days of Existence
* 106958 characters
* 72826 letters
* 15319 spaces
* 12666 words
* 4053 unique words
* 41 posts
* 309 words per post
* 5.75 letters/word
* 14 posts that contain images
* 20 images posted in total
* 2.16 posts per day
* 7 registered users
* Friday, June 17, 2005 4 post were posted, which is record
* "kontingenztheorie" is the longest used word (17 characters)

Longest Post:
Inker's Diary ......(1387 words)
Shortest Post:
Timetables li...(23 words)
Post with most Images:
Lost and Earthqua...(3)

Top 10 Words:

* the (506)
* to (336)
* and (298)
* of (156)
* we (151)
* in (147)
* that (139)
* it (124)
* for (112)
* was (108)

Top 10 Words with 5+ letters:

* daniel (68)
* really (21)
* called (19)
* around (18)
* getting (18)
* something (17)
* another (16)
* before (16)
* presentation (16)
* actually (15)

Top 10 Words with 10+ letters:

* presentation (16)
* documentation (9)
* programming (7)
* introduction (4)
* interesting (3)
* anticipated (2)
* appointment (2)
* b2evolution (2)
* certificate (2)
* requirements (2)

Complete Letter Statistics:

* A (7250),
* E (7124),
* S (6861),
* T (6785),
* N (5424),
* O (4531),
* I (4179),
* R (4094),
* L (3899),
* P (3196),
* H (2897),
* D (2671),
* C (2428),
* M (2025),
* U (1884),
* G (1567),
* W (1492),
* F (1048),
* Y (960),
* B (825),
* K (669),
* V (538),
* X (212),
* J (121),
* Q (120),
* Z (26),

Statistics took 0.117 seconds to calculate!

heres the new code if anybody wants it:

Code

<!-- =================================== Statistics =================================== -->
<?php
 
// Disable statistics
  $starttime = microtime();
  $wordcount;
  $postscount;
  $avgwordcount;
  $avgpostday;
  $countusers;
  $countpostswithimg;
  $countimages;
  $daysold;
 
  $longestcount;
  $longestlink;
  $longesttitle;
  
  $shortestcount;
  $shortestlink;
  $shortesttitle;
 
  $maximages;
  $maximagesTitle;
  $maximagesLink;
 
  $allpostsContent;
  $countletters;
 
  $countcharacters;
 
// getting wordcount
  $results = mysql_query('select sum(post_wordcount) from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $wordcount = mysql_result($results , 0);
 
// getting character
  $results = mysql_query('select sum(CHAR_LENGTH(post_content)) from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $countcharacters = mysql_result($results , 0);
  
  
 
// getting postcount
  $results = mysql_query('select count(*) from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $postscount = mysql_result($results , 0);
 
 
// getting average wordcount
  $results = mysql_query('select avg(post_wordcount) from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $avgwordcount = round (mysql_result($results , 0));
 
 
// getting average posts/day
  $results = mysql_query('select DATEDIFF(CURRENT_DATE(),(select min(post_issue_date) from evo_posts))');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $daysold = mysql_result($results, 0);
  $avgpostday = round($postscount/$daysold,2);
 
// getting usercount
  $results = mysql_query('select count(*) from evo_users');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $countuserst = mysql_result($results , 0);
 
// getting longest post
  $results = mysql_query('SELECT post_wordcount, id, post_title from evo_posts order by post_wordcount desc LIMIT 1');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $longestcount = mysql_result($results , 0,0);
  $longestlink = '/blog/?p='.mysql_result($results , 0,1);
  $longesttitle = mysql_result($results , 0,2);
  if(strlen($longesttitle) > 20){
    $longesttitle = substr($longesttitle, 0, 17).'...';
  }
 
// getting shortest post
  $results = mysql_query('SELECT post_wordcount, id, post_title from evo_posts order by post_wordcount asc LIMIT 1');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $shortestcount = mysql_result($results , 0,0);
  $shortestlink = '/blog/?p='.mysql_result($results , 0,1);
  $shortesttitle = mysql_result($results , 0,2);
  if(strlen($shortesttitle) > 20){
    $shortesttitle = substr($shortesttitle, 0, 17).'...';
  }
 
  
// getting no of posts with images
// uncomment this if you disable the no of images block below (this way the no of posts with images will still work)
  
  //$results = mysql_query('select count(*) from evo_posts where post_content like \'%<img%\'');
  //if (!$results ) {
     //  echo 'Error getting statistics';
  //}
  //$countpostswithimg = mysql_result($results , 0);
  
 
// getting no of Images and no of posts with images
// NOTE: this is a very expensive operation and I'm sure there are smarter ways to do this, so use with care
  $results = mysql_query('select post_content, id, post_title  from evo_posts where post_content like \'%<img%\'');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $maximages = 0;
  $countpostswithimg = mysql_num_rows($results);
  for ($i = 0; $i < $countpostswithimg; $i++)
  {
    $thisamount = substr_count(mysql_result($results , $i,0),'<img ');
    $countimages += $thisamount;
    if($thisamount>$maximages)
    {  
      $maximages = $thisamount;
      $maximagesTitle = mysql_result($results , $i,2);
      if(strlen($maximagesTitle) > 20){
        $maximagesTitle = substr($maximagesTitle, 0, 17).'...';
      }
      $maximagesLink = '/blog/?p='.mysql_result($results ,$i,1);
    }
  }
 
// we also get stats of letters........very very expensive operation, i dont recommand this
  
  $letters;
  $results = mysql_query('select LOWER(post_content) from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  for ($i = 0; $i < mysql_num_rows($results); $i++)
  {
    $allpostsContent = $allpostsContent.mysql_result($results , $i);
  }
  // we loop through the alphabet
  for ($i=97; $i<=122; $i++) {
    $x = chr($i);
    $thisamount = substr_count($allpostsContent,$x);
    $countletters+=$thisamount;
    $letters[$i-97][0] = strtoupper($x);
    $letters[$i-97][1] = $thisamount;
  }
  $nospaces = substr_count($allpostsContent,' ');
 
  foreach($letters as $res)
         $sortAux[] = $res[1];
  array_multisort($sortAux, SORT_DESC, $letters);
 
  $words = array_unique(explode(' ',$allpostsContent));
  
  $longwordlength;
  $longword;
  $amount = 0;
  $wordsandamount;
  $longwordsandamount;
  $verylongwordsandamount;
// we loop through each word
  for ($i=0; $i<sizeof($words); $i++) {
    $thislength = strlen($words[$i]);
    if($thislength>1 && stristr($words[$i],'-')==null && stristr($words[$i],'"')==null && stristr($words[$i],'=')==null && stristr($words[$i],'class')==null && stristr($words[$i],'<')==null && stristr($words[$i],',')==null && stristr($words[$i],'/')==null && stristr($words[$i],'.')==null)
    {
      $amount ++;
      $thisamount = substr_count($allpostsContent,' '.$words[$i].' ');
      $thisamount += substr_count($allpostsContent,' '.$words[$i].'\'');
      $wordsandamount[$i][0] = $words[$i];
      $wordsandamount[$i][1] = $thisamount;
      if($thislength>$longwordlength)
      {
        $longword = $words[$i];
        $longwordlength = $thislength;
      }
      if($thislength>10)
      {
        $verylongwordsandamount[$i][0] = $words[$i];
        $verylongwordsandamount[$i][1] = $thisamount;
      }
      if($thislength>5)
      {
        $longwordsandamount[$i][0] = $words[$i];
        $longwordsandamount[$i][1] = $thisamount;  
      }
 
    // here we can actually write the stats for every word.
    // echo $words[$i].' - '.$thisamount.'<BR />';
    }
  }
  foreach($wordsandamount as $res)
         $sortAux1[] = $res[1];
  array_multisort($sortAux1, SORT_DESC, $wordsandamount);
  foreach($longwordsandamount as $res)
         $sortAux2[] = $res[1];
  array_multisort($sortAux2, SORT_DESC, $longwordsandamount);
  foreach($verylongwordsandamount as $res)
         $sortAux3[] = $res[1];
  array_multisort($sortAux3, SORT_DESC, $verylongwordsandamount);
 
// getting date with most posts
  $results = mysql_query('select count(*) as amount, UNIX_TIMESTAMP(post_issue_date) from evo_posts group by date(post_issue_date) order by amount desc limit 1');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $dateMostPostsCount = mysql_result($results , 0,0);
  $dateMostPosts = Date("l, F j, Y",mysql_result($results,0,1));
 
// <!-- ====================================== Echo Statistics =========================================== -->
 
  echo '<h3 class="sideItemTitle"><img src="img/icon.png" /> Statistics</h3><BR /><ul>';
  echo  '<li>'.$daysold.' Days of Existence';
  echo  '<li>'.$countcharacters.' characters';
  if($countletters>0) echo  '<li>'.$countletters.' letters';
  echo  '<li>'.$nospaces.' spaces';
  echo  '<li>'.$wordcount.' words';
  
  if(sizeof($words)>0) echo  '<li>'.sizeof($words).' unique words';
  echo  '<li>'.$postscount.' posts';
  echo  '<li>'.$avgwordcount.' words per post';
  if($countletters>0) echo  '<li>'.round($countletters/$wordcount,2).' letters/word';  
 
  echo  '<li>'.$countpostswithimg.' posts that contain images';
  echo  '<li>'.$countimages.' images posted in total';
  echo  '<li>'.$avgpostday.' posts per day';
  echo  '<li>'.$countuserst.' registered users';
  echo  '<li>'.$dateMostPosts.' '.$dateMostPostsCount.' post were posted, which is record';
 
    
  //if($wordsandamount[0][1]>0) echo  '<li><a href="/blog/index.php/all?s='.$wordsandamount[0][0].'&sentence=AND&submit=Search">"'.$wordsandamount[0][0].'"</a> was used '.$wordsandamount[0][1].' times';
  //if($wordsandamount[1][1]>0) echo  '<li><a href="/blog/index.php/all?s='.$wordsandamount[1][0].'&sentence=AND&submit=Search">"'.$wordsandamount[1][0].'"</a> was used '.$wordsandamount[1][1].' times';
  if($longwordlength>0) echo  '<li><a href="/blog/index.php/all?s='.$longword.'&sentence=AND&submit=Search">"'.$longword.'"</a> is the longest used word ('.$longwordlength.' characters)';
  echo '</ul><BR />';
  echo  'Longest Post:<BR /> <a href="'.$longestlink.'">'.$longesttitle.'('.$longestcount.' words)</a><BR />';
  echo  'Shortest Post:<BR /> <a href="'.$shortestlink.'">'.$shortesttitle.'('.$shortestcount.' words)</a><BR />';
  echo  'Post with most Images:<BR /> <a href="'.$maximagesLink.'">'.$maximagesTitle.'('.$maximages.')</a><BR />';
  
  echo '<BR />Top 10 Words:<ul>';
  for ($i=0; $i<10; $i++) {
    echo '<li><a href="/blog/index.php/all?s='.$wordsandamount[$i][0].'&sentence=AND&submit=Search">'.$wordsandamount[$i][0].'</a> ('.$wordsandamount[$i][1].')';
  }
  echo '</ul><BR />Top 10 Words with 5+ letters:<ul>';
  for ($i=0; $i<10; $i++) {
    echo '<li><a href="/blog/index.php/all?s='.$longwordsandamount[$i][0].'&sentence=AND&submit=Search">'.$longwordsandamount[$i][0].'</a> ('.$longwordsandamount[$i][1].')';
  }
  if(count($verylongwordsandamount)>10)
    $tmp = 'Top 10';
  else
    $tmp = count($verylongwordsandamount);
  echo '</ul><BR />'.$tmp.' Words with 10+ letters:<ul>';
  for ($i=0; $i<10 && $i < count($verylongwordsandamount); $i++) {
    echo '<li><a href="/blog/index.php/all?s='.$verylongwordsandamount[$i][0].'&sentence=AND&submit=Search">'.$verylongwordsandamount[$i][0].'</a> ('.$verylongwordsandamount[$i][1].')';
  }
  echo '</ul>';
  
  if($letters[0][1]>0){
    /*echo  '<BR />The Bloggers seem to love the letter "'.$letters[0][0].'" which was written a whooping '.$letters[0][1].' times. But they also seem fond of "'.$letters[1][0].'" which they used '.$letters[1][1].' times.<BR />';
    echo  '<BR />On the other hand, the "'.$letters[25][0].'" doesn\'t get much love. It was used a mere '.$letters[25][1].' times. "'.$letters[24][0].'" isn\'t off much better either though, having been used only '.$letters[24][1].' times.<BR />';
    */$stats = '<ul>';
    for($i=0; $i<sizeof($letters); $i++) {
      $stats.='<li>'.$letters[$i][0].' ('.$letters[$i][1].'),<BR />';
    }
    echo '<BR />Complete Letter Statistics:<BR /><small>'.$stats.'</ul></small><BR />';
  }
  $starttime = round(abs(microtime()-$starttime),3);
  echo '</ul>';
  echo '<BR />Statistics took '.$starttime.' seconds to calculate!';
 
?>
<!-- ====================================== END HACK =========================================== -->

Jun 23, 2005 21:42

I like the idea of this, but where exactly is a good place to put it? I can't find where the calender is in b2evocore/_main.php and I don't want to mess anything up.

Jun 24, 2005 16:59

put it in your skins _main.php. I use kubrick2evo :-)

BTW I've improved this a bit, so that now it only calculates letter/word statistics when a post is added/edited/deleted. I ended up inserting some lines of code into admin/edit_actions.php. The "downside" is that it requires two new database tables. But at least now it doesnt have to calculate all this stuff all the time.

added some more stats too (of course you can simply comment out whichever ones you dont wanna have - if you do so, make sure you also comment out the logic, and not just the echo stuff).
Output I get now:

Latest:

* That flight to SD...
* Not Again
* Nice
* C++ skillz
* Schedule update 2
* Beautiful morning
* 2412
* Visa bill
* Finally
* presentation tomorr...

Statistics

* 25 Days of existence
* 119750 characters
* 14550 words
* 54 posts
* 269 words per post
* 19 posts that contain images
* 26 images posted in total
* 2.16 posts per day
* 7 registered users
* Thursday, June 23, 2005 4 post were posted, which is record
* 3 days without any post!
* "kontingenztheorie" is the longest used word (17 characters)

Longest Post:

* Inker's Diary ...... (1387 words)

Shortest Post:

* Timetables li... (23 words)

Post with most Images:

* Lost and Earthqua... (3)

Random Word from Posts:

* has (9)

Random long Word from Posts:

* acquantance (1)

Top 10 Words:

* the (602)
* i (494)
* to (382)
* and (343)
* a (241)
* it (200)
* we (194)
* of (190)
* in (181)
* that (173)

Top 10 Words with 5+ letters:

* daniel (87)
* about (85)
* there (50)
* still (49)
* first (42)
* though (38)
* after (37)
* think (37)
* which (35)
* since (33)

Top 10 Words with 10+ letters:

* presentation (22)
* documentation (20)
* programming (11)
* everything (9)
* apparently (8)
* introduction (6)
* development (5)
* interesting (5)
* conference (5)
* downloaded (3)

Complete Letter Statistics:

* E (8110)
* A (7930)
* T (7660)
* S (7398)
* N (6001)
* O (5228)
* I (4798)
* R (4560)
* L (4307)
* P (3368)
* H (3334)
* D (2995)
* C (2644)
* M (2251)
* U (2122)
* G (1777)
* W (1702)
* F (1210)
* Y (1104)
* B (972)
* K (757)
* V (618)
* X (240)
* J (134)
* Q (133)
* Z (28)

Statistics took 0.076 seconds to calculate!

Note that words (all of them) have a link that searches for posts with it, so you can immediatly see where the word occured. Obviously posts also have a link.

to create the two tables run this sql script:

Code

CREATE TABLE `evo_custom_stats_letters` (
  `Id` int(11) NOT NULL auto_increment,
  `letter` char(1) default NULL,
  `lettercount` int(11) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
CREATE TABLE `evo_custom_stats_words` (
  `Id` int(11) NOT NULL auto_increment,
  `word` varchar(30) default NULL,
  `wordcount` int(11) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Insert this into admin/edit_actions.php under echo '<div class="panelinfo">'; (should be line 510)
(actually you have some freedom where to put it)

Code

/////////////////////////////////////////////////////////////////////////////////////////////////////
// HACK
 
 
// we also get stats of letters........very very expensive operation, i dont recommand this
  echo "Statistics:<BR />";  
  $letters;
  $selectStatement = 'LOWER(post_content)';
  $selectStatement= 'REPLACE('.$selectStatement.',"(","")';
  $selectStatement= 'REPLACE('.$selectStatement.',",","")';
  $selectStatement= 'REPLACE('.$selectStatement.',";","")';
  $selectStatement= 'REPLACE('.$selectStatement.',"."," ")';
  $selectStatement= 'REPLACE('.$selectStatement.',"!","")';
  $selectStatement= 'REPLACE('.$selectStatement.',":"," ")';
  $selectStatement= 'REPLACE('.$selectStatement.',"?","")';
  $selectStatement= 'REPLACE('.$selectStatement.',")","")';
  $selectStatement= 'REPLACE('.$selectStatement.',"/"," ")';
  $selectStatement= 'REPLACE('.$selectStatement.',"-"," ")';
  $selectStatement= 'REPLACE('.$selectStatement.',"_"," ")';
  $selectStatement= 'REPLACE('.$selectStatement.',"\n"," ")';
  $selectStatement= 'REPLACE('.$selectStatement.',"  "," ")';
  
  //echo $selectStatement;
  $results = mysql_query('select '.$selectStatement.' from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  for ($i = 0; $i < mysql_num_rows($results); $i++)
  {  
    $allpostsContent = $allpostsContent.mysql_result($results , $i);
  }
 
  //mysql_query ("delete from evo_custom_stats_letters)";
 
  echo "Updating letter statistics...<BR />";
  // we loop through the alphabet
  for ($i=97; $i<=122; $i++) {
    $x = chr($i);
    $thisamount = substr_count($allpostsContent,$x);    
    mysql_query ('update evo_custom_stats_letters set lettercount='.$thisamount.' where letter="'.$x.'"');
 
    //if(mysql_affected_rows()<1)
    //{
    //  //echo 'insert into evo_custom_stats_letters (lettercount, letter) values('.$thisamount.', "'.$x.'")';
    //  mysql_query ('insert into evo_custom_stats_letters (lettercount, letter) values('.$thisamount.', "'.$x.'")');
    //  echo 'Letter '.$x.' not present, inserting instead of updating...<BR />';
      
    //}
  }
  echo "Letter statistics updated!<BR />";
 
 
  $allpostsContent = str_ireplace('\'s', '', $allpostsContent);
  $allpostsContent = str_ireplace('\'re', '', $allpostsContent);
  $allpostsContent = str_ireplace('\'r', '', $allpostsContent);
 
  $words = array_unique(explode(' ',$allpostsContent));
  $amount = 0;
 
  echo "Deleting word statistics...<BR />";
  mysql_query ("delete from evo_custom_stats_words");
  echo mysql_affected_rows()."...words in statistic<BR />";
// we loop through each word
  for ($i=0; $i<sizeof($words); $i++) {
    $thislength = strlen($words[$i]);
    if($thislength>0 && stristr($words[$i],'-')==null && stristr($words[$i],chr(13))==null && stristr($words[$i],'"')==null && stristr($words[$i],'=')==null && stristr($words[$i],'class')==null && stristr($words[$i],'<')==null && stristr($words[$i],',')==null && stristr($words[$i],'/')==null && stristr($words[$i],'.')==null)
    {  
      $thisamount = substr_count($allpostsContent,' '.$words[$i].' ');
      $thisamount += substr_count($allpostsContent,' '.$words[$i].'\'');
 
    mysql_query ('insert into evo_custom_stats_words (word, wordcount) values ("'.$words[$i].'",'.$thisamount.')');
    }
  }
  echo "OK, word statistics generated!<BR />";
  echo "All done with Statistics!<BR />";
 
 
/////////////////////////////////////////////////////////////////////////////////////////////////////

And this in your skin's _main.php where you want the stats to show up:

Code

<!-- ====================================== HACK =========================================== -->
<!-- =================================== Statistics =================================== -->
<?php
 
// Disable statistics
  $starttime = microtime();
  $wordcount;
  $postscount;
  $avgwordcount;
  $avgpostday;
  $countusers;
  $countpostswithimg;
  $countimages;
  $daysold;
 
  $longestcount;
  $longestlink;
  $longesttitle;
  
  $shortestcount;
  $shortestlink;
  $shortesttitle;
 
  $maximages;
  $maximagesTitle;
  $maximagesLink;
 
  $allpostsContent;
  $countletters;
 
  $countcharacters;
 
// getting wordcount
  $results = mysql_query('select sum(post_wordcount) from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $wordcount = mysql_result($results , 0);
 
// getting character
  $results = mysql_query('select sum(CHAR_LENGTH(post_content)) from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $countcharacters = mysql_result($results , 0);
  
  
 
// getting postcount
  $results = mysql_query('select count(*) from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $postscount = mysql_result($results , 0);
 
 
// getting average wordcount
  $results = mysql_query('select avg(post_wordcount) from evo_posts');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $avgwordcount = round (mysql_result($results , 0));
 
 
// getting average posts/day
  $results = mysql_query('select DATEDIFF(CURRENT_DATE(),(select min(post_issue_date) from evo_posts))');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $daysold = mysql_result($results, 0);
  $avgpostday = round($postscount/$daysold,2);
 
// getting usercount
  $results = mysql_query('select count(*) from evo_users');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $countuserst = mysql_result($results , 0);
 
// getting longest post
  $results = mysql_query('SELECT post_wordcount, id, post_title from evo_posts order by post_wordcount desc LIMIT 1');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $longestcount = mysql_result($results , 0,0);
  $longestlink = '/blog/?p='.mysql_result($results , 0,1);
  $longesttitle = mysql_result($results , 0,2);
  if(strlen($longesttitle) > 20){
    $longesttitle = substr($longesttitle, 0, 17).'...';
  }
 
// getting shortest post
  $results = mysql_query('SELECT post_wordcount, id, post_title from evo_posts order by post_wordcount asc LIMIT 1');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $shortestcount = mysql_result($results , 0,0);
  $shortestlink = '/blog/?p='.mysql_result($results , 0,1);
  $shortesttitle = mysql_result($results , 0,2);
  if(strlen($shortesttitle) > 20){
    $shortesttitle = substr($shortesttitle, 0, 17).'...';
  }
 
  
// getting no of posts with images
// uncomment this if you disable the no of images block below (this way the no of posts with images will still work)
  
  //$results = mysql_query('select count(*) from evo_posts where post_content like \'%<img%\'');
  //if (!$results ) {
     //  echo 'Error getting statistics';
  //}
  //$countpostswithimg = mysql_result($results , 0);
  
 
// getting no of Images and no of posts with images
// NOTE: this is a very expensive operation and I'm sure there are smarter ways to do this, so use with care
  $results = mysql_query('select post_content, id, post_title  from evo_posts where post_content like \'%<img%\'');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $maximages = 0;
  $countpostswithimg = mysql_num_rows($results);
  for ($i = 0; $i < $countpostswithimg; $i++)
  {
    $thisamount = substr_count(mysql_result($results , $i,0),'<img ');
    $countimages += $thisamount;
    if($thisamount>$maximages)
    {  
      $maximages = $thisamount;
      $maximagesTitle = mysql_result($results , $i,2);
      if(strlen($maximagesTitle) > 20){
        $maximagesTitle = substr($maximagesTitle, 0, 17).'...';
      }
      $maximagesLink = '/blog/?p='.mysql_result($results ,$i,1);
    }
  }
$results = mysql_query('select word, CHAR_LENGTH(word) from evo_custom_stats_words order by CHAR_LENGTH(word) desc limit 1');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $longwordlength = mysql_result($results , 0,1);
  $longword = mysql_result($results , 0,0);
 
$results = mysql_query('select word, wordcount from evo_custom_stats_words ORDER BY RAND() limit 1');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $randwordcount = mysql_result($results , 0,1);
  $randword = mysql_result($results , 0,0);
 
$results = mysql_query('select word, wordcount from evo_custom_stats_words where CHAR_LENGTH(word)>10 order by rand() limit 1');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $randlongwordcount = mysql_result($results , 0,1);
  $randlongword = mysql_result($results , 0,0);
 
$results = mysql_query('select word, wordcount from evo_custom_stats_words order by wordcount desc limit 10;');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  for ($i=0; $i<mysql_num_rows($results) && $i<10; $i++) {
    $wordsandamount[$i][0] = mysql_result($results , $i,0);
    $wordsandamount[$i][1] = mysql_result($results , $i,1);
  }
 
$results = mysql_query('select word, wordcount from evo_custom_stats_words where CHAR_LENGTH(word)>4 order by wordcount desc limit 10;');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  for ($i=0; $i<mysql_num_rows($results) && $i<10; $i++) {
    $longwordsandamount[$i][0] = mysql_result($results , $i,0);
    $longwordsandamount[$i][1] = mysql_result($results , $i,1);
  }
 
$results = mysql_query('select word, wordcount from evo_custom_stats_words where CHAR_LENGTH(word)>9 order by wordcount desc limit 10;');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  for ($i=0; $i<mysql_num_rows($results) && $i<10; $i++) {
    $verylongwordsandamount[$i][0] = mysql_result($results , $i,0);
    $verylongwordsandamount[$i][1] = mysql_result($results , $i,1);
  }
 
$results = mysql_query('select upper(letter), lettercount from evo_custom_stats_letters order by lettercount desc');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  for ($i=0; $i<mysql_num_rows($results); $i++) {
    $letters[$i][0] = mysql_result($results , $i,0);
    $letters[$i][1] = mysql_result($results , $i,1);
  }
 
 
 
// getting date with most posts
  $results = mysql_query('select count(*) as amount, UNIX_TIMESTAMP(post_issue_date) from evo_posts group by date(post_issue_date) order by amount desc limit 1');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  $dateMostPostsCount = mysql_result($results , 0,0);
  $dateMostPosts = Date("l, F j, Y",mysql_result($results,0,1));
 
// getting titles of last n posts
  $n = 10;
  $results = mysql_query('select post_title, id from evo_posts order by post_issue_date desc limit '.$n);
  if (!$results ) {
       echo 'Error getting statistics';
  }
  for ($i=0; $i<$n; $i++) {
    $lastPosts[$i][0] = mysql_result($results , $i,0);
    $lastPosts[$i][1] = mysql_result($results , $i,1);
  }
  
 
  
  $results = mysql_query('select date(post_issue_date), date(post_issue_date + interval 1 day) from evo_posts group by date(post_issue_date)');
  if (!$results ) {
       echo 'Error getting statistics';
  }
  for ($i=0; $i<mysql_num_rows($results)-1; $i++)
  {
    for ($i=0; $i<mysql_num_rows($results)-1; $i++)
     {
      $nextdate = mysql_result($results , $i,1);
      if($nextdate != mysql_result($results , $i+1,0))  
        $countDatesNoPosts++;    
    }    
  }
  
 
 
 
// <!-- ====================================== Echo Statistics =========================================== -->
 
  echo '<h3 class="sideItemTitle"><img src="img/icon.png" /> Latest:</h3><BR /><ul>';
  for ($i=0; $i<10 && $i < count($verylongwordsandamount); $i++) {
    $title = $lastPosts[$i][0];
    if(strlen($lastPosts[$i][0]) > 25){
      $title = $lastPosts[$i][0];
      $lastPosts[$i][0] = substr($lastPosts[$i][0], 0, 24).'...';
    }
    echo '<li><a href="/blog/?p='.$lastPosts[$i][1].'" title="'.$title.'">'.$lastPosts[$i][0].'</a>';
  }
  echo '</ul>';
  
 
  echo '<h3 class="sideItemTitle"><img src="img/icon.png" /> Statistics</h3><BR /><ul>';
  echo  '<li>'.$daysold.' Days of existence';
  echo  '<li>'.$countcharacters.' characters';
  if($countletters>0) echo  '<li>'.$countletters.' letters';
  //echo  '<li>'.$nospaces.' spaces';
  echo  '<li>'.$wordcount.' words';
  
  if(sizeof($words)>0) echo  '<li>'.sizeof($words).' unique words';
  echo  '<li>'.$postscount.' posts';
  echo  '<li>'.$avgwordcount.' words per post';
  if($countletters>0) echo  '<li>'.round($countletters/$wordcount,2).' letters/word';  
 
  echo  '<li>'.$countpostswithimg.' posts that contain images';
  echo  '<li>'.$countimages.' images posted in total';
  echo  '<li>'.$avgpostday.' posts per day';
  echo  '<li>'.$countuserst.' registered users';
  echo  '<li>'.$dateMostPosts.' '.$dateMostPostsCount.' post were posted, which is record';
  echo  '<li>'.$countDatesNoPosts.' days without any post!';
    
  //if($wordsandamount[0][1]>0) echo  '<li><a href="/blog/index.php/all?s='.$wordsandamount[0][0].'&sentence=AND&submit=Search">"'.$wordsandamount[0][0].'"</a> was used '.$wordsandamount[0][1].' times';
  //if($wordsandamount[1][1]>0) echo  '<li><a href="/blog/index.php/all?s='.$wordsandamount[1][0].'&sentence=AND&submit=Search">"'.$wordsandamount[1][0].'"</a> was used '.$wordsandamount[1][1].' times';
  if($longwordlength>0) echo  '<li><a href="/blog/index.php/all?s='.$longword.'&sentence=AND&submit=Search">"'.$longword.'"</a> is the longest used word ('.$longwordlength.' characters)';
  echo '</ul><BR />';
  echo  'Longest Post:<BR /><ul><li> <a href="'.$longestlink.'">'.$longesttitle.' ('.$longestcount.' words)</li></ul></a>';
  echo  'Shortest Post:<BR /><ul><li> <a href="'.$shortestlink.'">'.$shortesttitle.' ('.$shortestcount.' words)</li></ul></a>';
  echo  'Post with most Images:<BR /><ul><li> <a href="'.$maximagesLink.'">'.$maximagesTitle.' ('.$maximages.')</li></ul></a>';
  
  echo  '<BR />Random Word from Posts:<BR /><ul><li><a href="/blog/index.php/all?s='.$randword.'&sentence=AND&submit=Search">'.$randword.'</a> ('.$randwordcount.')</li></ul>';
 
  echo  'Random long Word from Posts:<BR /><ul><li><a href="/blog/index.php/all?s='.$randlongword.'&sentence=AND&submit=Search">'.$randlongword.'</a> ('.$randlongwordcount.')</li></ul>';
 
  echo '<BR />Top 10 Words:<ul>';
  for ($i=0; $i<10; $i++) {
    echo '<li><a href="/blog/index.php/all?s='.$wordsandamount[$i][0].'&sentence=AND&submit=Search">'.$wordsandamount[$i][0].'</a> ('.$wordsandamount[$i][1].')';
  }
  echo '</ul><BR />Top 10 Words with 5+ letters:<ul>';
  for ($i=0; $i<10; $i++) {
    echo '<li><a href="/blog/index.php/all?s='.$longwordsandamount[$i][0].'&sentence=AND&submit=Search">'.$longwordsandamount[$i][0].'</a> ('.$longwordsandamount[$i][1].')';
  }
  if(count($verylongwordsandamount)>9)
    $tmp = 'Top 10';
  else
    $tmp = count($verylongwordsandamount);
  echo '</ul><BR />'.$tmp.' Words with 10+ letters:<ul>';
  for ($i=0; $i<10 && $i < count($verylongwordsandamount); $i++) {
    echo '<li><a href="/blog/index.php/all?s='.$verylongwordsandamount[$i][0].'&sentence=AND&submit=Search">'.$verylongwordsandamount[$i][0].'</a> ('.$verylongwordsandamount[$i][1].')';
  }
  echo '</ul>';
  
  if($letters[0][1]>0){
    //echo  '<BR />The Bloggers seem to love the letter "'.$letters[0][0].'" which was written a whooping '.$letters[0][1].' times. But they also seem fond of "'.$letters[1][0].'" which they used '.$letters[1][1].' times.<BR />';
    //echo  '<BR />On the other hand, the "'.$letters[25][0].'" doesn\'t get much love. It was used a mere '.$letters[25][1].' times. "'.$letters[24][0].'" isn\'t off much better either though, having been used only '.$letters[24][1].' times.<BR />';
    $stats = '<ul>';
    for($i=0; $i<sizeof($letters); $i++) {
      $stats.='<li>'.$letters[$i][0].' ('.$letters[$i][1].')<BR />';
    }
    echo '<BR />Complete Letter Statistics:<BR /><small>'.$stats.'</ul></small><BR />';
  }
  
  $starttime = round(abs(microtime()-$starttime),3);
  echo '</ul>';
  echo '<BR />Statistics took '.$starttime.' seconds to calculate!';
 
?>
<!-- ====================================== END HACK =========================================== -->

Jun 24, 2005 17:14

That's really cool, inker!

Thanks for the write up.

Jun 27, 2005 22:56

np

Given my lack of experience with php, I'm sure theres a better/cleaner way to do this though.

Any advice?

For instance, I'm not sure how big a burder the many sql request are. maybe this could be streamlined by using only few request, but with more selects, like request all possible items at once (more complicated select statements), etc.?

Seeing how our blog is basically internal to family and friends only, I dont have to be very concerned about performance. So it works quite fine for me.

Laterz

Sep 09, 2005 15:59

Hey inker,

I´m using your hack Posted: Sat Jun 18, 2005 16:11

I put your hack exactly behind the "normal" statistik. It doesn´t work correct cause I got the following error:

Code

Error getting statistics
Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /srv/www/htdocs/web25/html/sniglesde/skins/_stats.php on line 182
 
Warning: Division by zero in /srv/www/htdocs/web25/html/sniglesde/skins/_stats.php on line 183
Error getting statistics
Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /srv/www/htdocs/web25/html/sniglesde/skins/_stats.php on line 328
 
Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /srv/www/htdocs/web25/html/sniglesde/skins/_stats.php on line 329

My _stat.php:

Code

<?php
  /**
   * This is the template that displays stats for a blog
   *
   * This file is not meant to be called directly.
   * It is meant to be called by an include in the _main.php template.
   * To display the stats, you should call a stub AND pass the right parameters
   * For example: /blogs/index.php?disp=stats
   *
   * b2evolution - {@link http://b2evolution.net/}
   * Released under GNU GPL License - {@link http://b2evolution.net/about/license.html}
   * @copyright (c)2003-2004 by Francois PLANQUE - {@link http://fplanque.net/}
   *
   * @package evoskins
   */
  if( !defined('DB_USER') ) die( 'Please, do not access this page directly.' );
 
  if( $disp == 'stats' )
 
        { ?>
 
  <div class="statbloc"><h3><?php echo T_('Last referers') ?>:</h3>
  <?php refererList(30, 'global', 1, 1, 'no', '', ($blog > 1) ? $blog : '');
  if( count( $res_stats ) ) { ?>
  <ul>
    <?php foreach( $res_stats as $row_stats ) { ?>
      <li><a href="<?php stats_referer() ?>"><?php stats_basedomain() ?></a></li>
    <?php } // End stat loop ?>
  </ul>
  <?php } ?>
  </div>
 
  <div class="statbloc">
  <h3><?php echo T_('Top referers') ?>:</h3>
  <?php refererList(30, 'global', 0, 0, 'no', 'baseDomain', ($blog > 1) ? $blog : '', false);
  if( count( $res_stats ) ) { ?>
  <ol>
    <?php foreach( $res_stats as $row_stats ) { ?>
      <li><a href="<?php stats_referer() ?>"><?php stats_basedomain() ?></a></li>
    <?php } // End stat loop ?>
  </ol>
  <?php } ?>
  </div>
 
  <div class="statbloc" style="clear: left;">
  <h3><?php echo T_('Last refering searches') ?>:</h3>
  <?php refererList(30, 'global', 1, 1, 'search', '', ($blog > 1) ? $blog : '');
  if( count( $res_stats ) ) { ?>
  <ul>
    <?php foreach( $res_stats as $row_stats ) { ?>
      <li><a href="<?php stats_referer() ?>"><?php stats_search_keywords() ?></a></li>
    <?php } // End stat loop ?>
  </ul>
  <?php } ?>
  </div>
 
  <div class="statbloc">
  <h3><?php echo T_('Top refering engines') ?>:</h3>
  <?php refererList(30, 'global', 1, 1, 'search', 'baseDomain', ($blog > 1) ? $blog : '', true);
  if( count( $res_stats ) ) { ?>
  <table class="invisible">
    <?php foreach( $res_stats as $row_stats ) { ?>
      <tr>
        <td class="right">•</td>
        <td><a href="<?php stats_referer() ?>"><?php stats_basedomain() ?></a></td>
        <td class="right"><?php stats_hit_percent() ?></td>
      </tr>
    <?php } // End stat loop ?>
  </table>
  <?php } ?>
  </div>
 
  <div class="statbloc">
  <h3><?php echo T_('Top Indexing Robots') ?>:</h3>
  <?php refererList(30, 'global', 1, 1, 'robot', 'hit_user_agent', ($blog > 1) ? $blog : '', true, true);
  if( count( $res_stats ) ) { ?>
  <table class="invisible">
    <?php foreach( $res_stats as $row_stats ) { ?>
      <tr>
        <td class="right">•</td>
        <td><?php stats_referer('<a href="', '">') ?><?php stats_user_agent( true ) ?><?php stats_referer('', '</a>', false) ?></td>
        <td class="right"><?php stats_hit_percent() ?></td>
      </tr>
    <?php } // End stat loop ?>
  </table>
  <?php } ?>
  </div>
 
 
  <div class="statbloc">
  <h3><?php echo T_('Top Aggregators') ?>:</h3>
  <?php refererList(30, 'global', 1, 1, 'rss', 'hit_user_agent', ($blog > 1) ? $blog : '', true, true);
  if( count( $res_stats ) ) { ?>
  <table class="invisible">
    <?php if( count( $res_stats ) ) foreach( $res_stats as $row_stats ) { ?>
      <tr>
        <td class="right">•</td>
        <td><?php stats_user_agent('robots,aggregators') ?> </td>
        <td class="right"><?php stats_hit_percent() ?></td>
      </tr>
    <?php } // End stat loop ?>
  </table>
  <?php } ?>
  </div>
 
  <div class="clear"></div>
 
  <?php
  }
 
 
 
 
<!-- =================================== Statistics =================================== -->
{
 
// Disable statistics
   $starttime = microtime();
   $wordcount;
   $postscount;
   $avgwordcount;
   $avgpostday;
   $countusers;
   $countpostswithimg;
   $countimages;
   $daysold;
 
   $longestcount;
   $longestlink;
   $longesttitle;
    
   $shortestcount;
   $shortestlink;
   $shortesttitle;
 
   $maximages;
   $maximagesTitle;
   $maximagesLink;
 
   $allpostsContent;
   $countletters;
 
   $countcharacters;
 
// getting wordcount
   $results = mysql_query('select sum(post_wordcount) from evo_posts');
   if (!$results ) {
         echo 'Error getting statistics';
   }
   $wordcount = mysql_result($results , 0);
 
// getting character
   $results = mysql_query('select sum(CHAR_LENGTH(post_content)) from evo_posts');
   if (!$results ) {
         echo 'Error getting statistics';
   }
   $countcharacters = mysql_result($results , 0);
    
    
 
// getting postcount
   $results = mysql_query('select count(*) from evo_posts');
   if (!$results ) {
         echo 'Error getting statistics';
   }
   $postscount = mysql_result($results , 0);
 
 
// getting average wordcount
   $results = mysql_query('select avg(post_wordcount) from evo_posts');
   if (!$results ) {
         echo 'Error getting statistics';
   }
   $avgwordcount = round (mysql_result($results , 0));
 
 
// getting average posts/day
   $results = mysql_query('select DATEDIFF(CURRENT_DATE(),(select min(post_issue_date) from evo_posts))');
   if (!$results ) {
         echo 'Error getting statistics';
   }
   $daysold = mysql_result($results, 0);
   $avgpostday = round($postscount/$daysold,2);
 
// getting usercount
   $results = mysql_query('select count(*) from evo_users');
   if (!$results ) {
         echo 'Error getting statistics';
   }
   $countuserst = mysql_result($results , 0);
 
// getting longest post
   $results = mysql_query('SELECT post_wordcount, id, post_title from evo_posts order by post_wordcount desc LIMIT 1');
   if (!$results ) {
         echo 'Error getting statistics';
   }
   $longestcount = mysql_result($results , 0,0);
   $longestlink = '/?p='.mysql_result($results , 0,1);
   $longesttitle = mysql_result($results , 0,2);
   if(strlen($longesttitle) > 20){
      $longesttitle = substr($longesttitle, 0, 17).'...';
   }
 
// getting shortest post
   $results = mysql_query('SELECT post_wordcount, id, post_title from evo_posts order by post_wordcount asc LIMIT 1');
   if (!$results ) {
         echo 'Error getting statistics';
   }
   $shortestcount = mysql_result($results , 0,0);
   $shortestlink = '/?p='.mysql_result($results , 0,1);
   $shortesttitle = mysql_result($results , 0,2);
   if(strlen($shortesttitle) > 20){
      $shortesttitle = substr($shortesttitle, 0, 17).'...';
   }
 
    
// getting no of posts with images
// uncomment this if you disable the no of images block below (this way the no of posts with images will still work)
    
   //$results = mysql_query('select count(*) from evo_posts where post_content like \'%<img%\'');
   //if (!$results ) {
      //   echo 'Error getting statistics';
   //}
   //$countpostswithimg = mysql_result($results , 0);
    
 
// getting no of Images and no of posts with images
// NOTE: this is a very expensive operation and I'm sure there are smarter ways to do this, so use with care
   $results = mysql_query('select post_content, id, post_title  from evo_posts where post_content like \'%<img%\'');
   if (!$results ) {
         echo 'Error getting statistics';
   }
   $maximages = 0;
   $countpostswithimg = mysql_num_rows($results);
   for ($i = 0; $i < $countpostswithimg; $i++)
   {
      $thisamount = substr_count(mysql_result($results , $i,0),'<img ');
      $countimages += $thisamount;
      if($thisamount>$maximages)
      {    
         $maximages = $thisamount;
         $maximagesTitle = mysql_result($results , $i,2);
         if(strlen($maximagesTitle) > 20){
            $maximagesTitle = substr($maximagesTitle, 0, 17).'...';
         }
         $maximagesLink = '/?p='.mysql_result($results ,$i,1);
      }
   }
 
// we also get stats of letters........very very expensive operation, i dont recommand this
    
   $letters;
   $results = mysql_query('select LOWER(post_content) from evo_posts');
   if (!$results ) {
         echo 'Error getting statistics';
   }
   for ($i = 0; $i < mysql_num_rows($results); $i++)
   {
      $allpostsContent = $allpostsContent.mysql_result($results , $i);
   }
   // we loop through the alphabet
   for ($i=97; $i<=122; $i++) {
      $x = chr($i);
      $thisamount = substr_count($allpostsContent,$x);
      $countletters+=$thisamount;
      $letters[$i-97][0] = strtoupper($x);
      $letters[$i-97][1] = $thisamount;
   }
   $nospaces = substr_count($allpostsContent,' ');
 
   foreach($letters as $res)
           $sortAux[] = $res[1];
   array_multisort($sortAux, SORT_DESC, $letters);
 
   $words = array_unique(explode(' ',$allpostsContent));
    
   $longwordlength;
   $longword;
   $amount = 0;
   $wordsandamount;
   $longwordsandamount;
   $verylongwordsandamount;
// we loop through each word
   for ($i=0; $i<sizeof($words); $i++) {
      $thislength = strlen($words[$i]);
      if($thislength>1 && stristr($words[$i],'-')==null && stristr($words[$i],'"')==null && stristr($words[$i],'=')==null && stristr($words[$i],'class')==null && stristr($words[$i],'<')==null && stristr($words[$i],',')==null && stristr($words[$i],'/')==null && stristr($words[$i],'.')==null)
      {
         $amount ++;
         $thisamount = substr_count($allpostsContent,' '.$words[$i].' ');
         $thisamount += substr_count($allpostsContent,' '.$words[$i].'\'');
         $wordsandamount[$i][0] = $words[$i];
         $wordsandamount[$i][1] = $thisamount;
         if($thislength>$longwordlength)
         {
            $longword = $words[$i];
            $longwordlength = $thislength;
         }
         if($thislength>10)
         {
            $verylongwordsandamount[$i][0] = $words[$i];
            $verylongwordsandamount[$i][1] = $thisamount;
         }
         if($thislength>5)
         {
            $longwordsandamount[$i][0] = $words[$i];
            $longwordsandamount[$i][1] = $thisamount;    
         }
 
      // here we can actually write the stats for every word.
      // echo $words[$i].' - '.$thisamount.'<BR />';
      }
   }
   foreach($wordsandamount as $res)
           $sortAux1[] = $res[1];
   array_multisort($sortAux1, SORT_DESC, $wordsandamount);
   foreach($longwordsandamount as $res)
           $sortAux2[] = $res[1];
   array_multisort($sortAux2, SORT_DESC, $longwordsandamount);
   foreach($verylongwordsandamount as $res)
           $sortAux3[] = $res[1];
   array_multisort($sortAux3, SORT_DESC, $verylongwordsandamount);
 
// getting date with most posts
   $results = mysql_query('select count(*) as amount, UNIX_TIMESTAMP(post_issue_date) from evo_posts group by date(post_issue_date) order by amount desc limit 1');
   if (!$results ) {
         echo 'Error getting statistics';
   }
   $dateMostPostsCount = mysql_result($results , 0,0);
   $dateMostPosts = Date("l, F j, Y",mysql_result($results,0,1));
 
// <!-- ====================================== Echo Statistics =========================================== -->
 
   echo '<h3 class="sideItemTitle">Weitere Statistiken</h3><BR /><ul>';
   echo  '<li>'.$daysold.' Days of Existence';
   echo  '<li>'.$countcharacters.' characters';
   if($countletters>0) echo  '<li>'.$countletters.' letters';
   echo  '<li>'.$nospaces.' spaces';
   echo  '<li>'.$wordcount.' words';
    
   if(sizeof($words)>0) echo  '<li>'.sizeof($words).' unique words';
   echo  '<li>'.$postscount.' posts';
   echo  '<li>'.$avgwordcount.' words per post';
   if($countletters>0) echo  '<li>'.round($countletters/$wordcount,2).' letters/word';    
 
   echo  '<li>'.$countpostswithimg.' posts that contain images';
   echo  '<li>'.$countimages.' images posted in total';
   echo  '<li>'.$avgpostday.' posts per day';
   echo  '<li>'.$countuserst.' registered users';
   echo  '<li>'.$dateMostPosts.' '.$dateMostPostsCount.' post were posted, which is record';
 
      
   //if($wordsandamount[0][1]>0) echo  '<li><a href="/index.php/all?s='.$wordsandamount[0][0].'&sentence=AND&submit=Search">"'.$wordsandamount[0][0].'"</a> was used '.$wordsandamount[0][1].' times';
   //if($wordsandamount[1][1]>0) echo  '<li><a href="/index.php/all?s='.$wordsandamount[1][0].'&sentence=AND&submit=Search">"'.$wordsandamount[1][0].'"</a> was used '.$wordsandamount[1][1].' times';
   if($longwordlength>0) echo  '<li><a href="/index.php/all?s='.$longword.'&sentence=AND&submit=Search">"'.$longword.'"</a> is the longest used word ('.$longwordlength.' characters)';
   echo '</ul><BR />';
   echo  'Longest Post:<BR /> <a href="'.$longestlink.'">'.$longesttitle.'('.$longestcount.' words)</a><BR />';
   echo  'Shortest Post:<BR /> <a href="'.$shortestlink.'">'.$shortesttitle.'('.$shortestcount.' words)</a><BR />';
   echo  'Post with most Images:<BR /> <a href="'.$maximagesLink.'">'.$maximagesTitle.'('.$maximages.')</a><BR />';
    
   echo '<BR />Top 10 Words:<ul>';
   for ($i=0; $i<10; $i++) {
      echo '<li><a href="/index.php/all?s='.$wordsandamount[$i][0].'&sentence=AND&submit=Search">'.$wordsandamount[$i][0].'</a> ('.$wordsandamount[$i][1].')';
   }
   echo '</ul><BR />Top 10 Words with 5+ letters:<ul>';
   for ($i=0; $i<10; $i++) {
      echo '<li><a href="/index.php/all?s='.$longwordsandamount[$i][0].'&sentence=AND&submit=Search">'.$longwordsandamount[$i][0].'</a> ('.$longwordsandamount[$i][1].')';
   }
   if(count($verylongwordsandamount)>10)
      $tmp = 'Top 10';
   else
      $tmp = count($verylongwordsandamount);
   echo '</ul><BR />'.$tmp.' Words with 10+ letters:<ul>';
   for ($i=0; $i<10 && $i < count($verylongwordsandamount); $i++) {
      echo '<li><a href="/index.php/all?s='.$verylongwordsandamount[$i][0].'&sentence=AND&submit=Search">'.$verylongwordsandamount[$i][0].'</a> ('.$verylongwordsandamount[$i][1].')';
   }
   echo '</ul>';
    
   if($letters[0][1]>0){
      /*echo  '<BR />The Bloggers seem to love the letter "'.$letters[0][0].'" which was written a whooping '.$letters[0][1].' times. But they also seem fond of "'.$letters[1][0].'" which they used '.$letters[1][1].' times.<BR />';
      echo  '<BR />On the other hand, the "'.$letters[25][0].'" doesn\'t get much love. It was used a mere '.$letters[25][1].' times. "'.$letters[24][0].'" isn\'t off much better either though, having been used only '.$letters[24][1].' times.<BR />';
      */$stats = '<ul>';
      for($i=0; $i<sizeof($letters); $i++) {
         $stats.='<li>'.$letters[$i][0].' ('.$letters[$i][1].'),<BR />';
      }
      echo '<BR />Complete Letter Statistics:<BR /><small>'.$stats.'</ul></small><BR />';
   }
   $starttime = round(abs(microtime()-$starttime),3);
   echo '</ul>';
   echo '<BR />Diese Statsitik zu erstellen hat '.$starttime.' Sekunden benötigt!';
 
}
?>

Any idea what the error is? All statistics you can find here http://www.snigles.de/index.php?disp=stats
Nice weekend from joachim

Sep 09, 2005 16:26

What version of MySQL are you running?

This is where your problem is:

PHP

// getting average posts/day 
   $results mysql_query('select DATEDIFF(CURRENT_DATE(),(select min(post_issue_date) from evo_posts))'); 
   if (!$results ) { 
         echo 'Error getting statistics'
   } 
   $daysold mysql_result($results0); 
   $avgpostday round($postscount/$daysold,2);

DATEDIFF is a command in MySQL.

I had the same problem as i was running 4.0.22

DATEDIFF() was added in MySQL 4.1.1.

Comment this part out and it should go away.

Jon[/b]

Sep 09, 2005 16:30

I notice the hack has the stuff in _main but joachim has it in _stats. Could be an issue?

Sep 09, 2005 17:47

yeah, its probably that function then. TBH I don't know, as I stated earlier I'm not exactly a PHP god :-) and I havent played with it for a while either.

So I'm sorry.... :-(

Sep 09, 2005 18:35

I kicked it out from my _stat.php but still errors. does it man I only can use it in the _main.php?

I have PHP 4.2x

so long
joachim

Sep 09, 2005 19:21

My bad.

I edited my post above.

It is not a PHP problem, it's a MySQL problem

DATEDIFF() was added in MySQL 4.1.1.

I have played with this hack on "_main.php"

as well as it's own file. So anywhere you put it should be fine
as long as it can connect to the blog database

Jon


Form is loading...

b2evolution CCMS – This forum is powered by b2evolution CMS, a complete engine for your website.