Use MsAccess as MySql frontend

 

Horses for courses, where MySql, PHP and HTML are native to the web, PHP/HTML suck as table GUI,no offense, I just got extremely frustrated with it today. Another way to get the data off the web onto my desktop is using FTP/ODBC and enclosing MySql tables in my MsAccess desktop database. It has it perks but it works. If you want to try it check with your host (if you are not self hosting) if your host allows external access to your mysql server. For local servers it works flawless.

Download the odbc connector

Over at Dev.MySql they muist have heard my screams of agony an programmed an ODBC (open database connectivity) driver. Windows supports the ODBC standards hence in Office you can use external databases if you install an odbc driver for the database type.

Create the system datasource

Once the driver is installed you can add the MySql database as open database to Windows (odbc works via the operating system) and link the tables in MsAccess. Start by opening the Windows configuration screen and open System manager, where you can access the ODBC Datasources and open the ODBC Administrator screen :

Pick the tab System DSN and choose Add… to select a driver : the new MySql ODBC Driver.

Then comes the magic bit : when you click Finish, the MySql driver pops up its own configuration screen :

The data you fill in is the same login data used in WordPress wp-config.php : the mysql database host, login and password, and what database to select. When you are done, you can make more than one ODBC Sources so you can add every WordPress install you have on the web to it, provided you have access to the mysql server. A lot of hosts do not actually allow it, but mine do.

So far so good, the Datasource is now known in the Windows operating system, now we go to MsAccess.

Enclose the tables in MsAccess

 

Under External Data pick ODBC Database :

In the screen that opens, pick the Link data option :

And in the next screen pick the tab with machine data sources : this picture shows both my new localhost database and the juust.org database so I will pick that one (it is in Rotterdam somewhere on a server)

When I pick that one the driver kicks in and connects to the remote database, retrieving a list with tables for me to link into the database, for the occasion I will link the Ak_Twitter table of the WordPress Twitter Tools plugin that has my most recent Tweets :

And click okay and Voila :

The tables are linked

Note: if you get an error “server has gone away, ( ask your host to) set the connection timeout to 300 or more

 

 

 

 

google trends crosstab

I was playing with Google Trends using another blog. Of the seven or eight posts I tried, five made it to the details page, which gave me some traffic. I am too lazy to follow 100 entries during the day, so I want a simple report on how trends develop the past day like this :

google trends watch

Now how do I get that done ?

First I make a cronjob that runs a routine to pull the Google trends xml-feed in every hour and store it in a database. I add a string for the period (year-month-day-hour).

That gives me a table like this one :

DATE SEARCHID POS
2008120101 baboon 78
2008120101 monkey 13
2008120102 baboon 98
2008120102 monkey 5
2008120103 monkey 3

I want an output

2008120101 2008120102 2008120103
baboon 78 98
monkey 13 5 3

The way to do that is a crosstable query, also called a pivot table .

MySql pivot table

I have to make a routine to turn that base table into a table with the dates as column, and per phrase the position in the correct column. I want a complete row, so I take the highest period from the table and the lowest, and from that intrapolate the other periods.

  1. function make_periods($begin, $end) {
  2. $BeginTime=mktime(substr($begin, 8, 2),0,0, substr($begin,4,2), substr($begin,6,2), substr($begin,0,4));
  3. $EndTime=mktime(substr($end,8,2),0,0, substr($end,4,2), substr($end,6,2), substr($end,0,4));
  4. //divide the difference by 60minutes*60seconds
  5. $periods = ($EndTime$BeginTime) / 3600;
  6. //make a row of hour-periods with "+N hour",
  7. for($i=0;$i< ($periods+1);$i++) $myperiods[] = strftime('%Y%m%d%H', strtotime("+$i hour", $BeginTime));
  8. //return an array with all periods
  9. //that are to be the column headers in the crosstable
  10.  return $myperiods;
  11. }

At some point I have to clean up the table, and I want the data to fit in one page,
so I will use 24 periods

  1. $plength=25;
  2. if(count($dates)>$plength) {
  3.  for($j=0;$j< ($plength+1);$j++) $usedates[] = $dates[count($dates) ($plength$j)];
  4. } else {
  5. //note : this one needs some work.
  6.      $usedates[]=$dates;
  7. }

… and delete all older records :

  1. //make the periods
  2. $dates=array();
  3. $dates = make_periods($begin, $end);
  4.  
  5. //use the $dates array, see if I have more than 24 periods
  6. if(count($dates) > 24) {
  7. //get period number-last-minus-24
  8. //delete all records before that period
  9.   mysql_query("DELETE FROM `serp_trends`  WHERE `date`< '".$dates[count($dates)-24] ."'", $link);
  10. }

…that keeps the table small enough for quick querying. I have 24 periods to make columns, how do I get the position as value in each column ? I read the ‘wizard’ article on mysql pivot tables and that sounded difficult.

I don’t get it, but I got it running :

  • group the records by phrase (“searchid”)
  • use the array of periods as columns
  • see if the phrase has a position in a specific period,
    • if so, take the position as value (it only has one per period)
    • otherwise take 0 as value
  • sum the values
  • name the column “T”+period
  1. $usedates=$dates;
  2. $CROSTAB = "SELECT `searchid`, AVG(`pos`) AS AP ";
  3.  for($j=0;$j<count ($usedates);$j++) {
  4.   $CROSTAB.=", SUM(IF(date='".$usedates[$j]."', pos, 0)) AS T".$usedates[$j];
  5.  }
  6. $CROSTAB .= " FROM `serp_trends` GROUP BY `searchid` ORDER BY AP ASC";

I take the average position from the grouped records, order it ascending and output that as table, first the two columns phrase (searchid) and average position, then the periods as column names.

  1. $link=connect_data();
  2.  
  3. $result=mysql_query($CROSTAB, $link) or die(mysql_error());
  4.  
  5. //add the zebra class marker for mootool
  6. $CTB.= '<table class="zebra" cellpadding="0" cellspacing="0"><tbody>';
  7.  
  8. //make the header row
  9. $CTB.='<tr><td>avg</td><td>search</td>';
  10.  
  11. for($j=0;$j<count ($usedates);$j++) {
  12.   $CTB.='<td >'.substr($usedates[$j], -2).'';
  13. }
  14. $CTB.='</count></tr>';
  15.  
  16. //output the crosstable query result
  17. //which has the same format
  18. //searchid, average position, period(01)-period(24).
  19.  
  20. while($row=mysql_fetch_assoc($result)) {
  21.  $CTB.='<tr><td>'.number_format($row['AP'], '0', '0', '1').'</td><td>'.$row['searchid'].'</td>';
  22.  for($j=0;$j<count ($usedates);$j++) {
  23.   $v=$row['T'.$usedates[$j]];
  24.   if($v=='0') {
  25.    $CTB.='<td>'; } else {
  26.    $CTB.='<td>'.$v.'</td>'; }
  27.  }
  28.  $CTB.='</count></tr>';
  29. }
  30. $CTB.='</tbody></table>';

When I echo $CTB, the crosstable, this comes out :

google trends watch

Seeing rows per phrase means I can check a lot easier which trends are running, rising, dropping, or even returning. And that was what I wanted.

I zipped the files for download.

how to keep a traffic log with mysql and php

A rather obsolete post. I hacked a quick referrer tracking script together because I wanted to see how Googlebot indexed my serp tool pages. I can use Google analytics or phpMyVisites, but that is not as rewarding as keeping my own logs.

I start with a mysql table LOG to store the traffic data

  1. CREATE TABLE IF NOT EXISTS `log` (
  2.   `id` bigint(11) NOT NULL auto_increment,
  3.   `ip` varchar(20) NOT NULL,
  4.   `page` varchar(120) NOT NULL,
  5.   `visitpage` varchar(120) NOT NULL,
  6.   `visitdate` varchar(25) NOT NULL,
  7.   `agent` varchar(200) NOT NULL,
  8.   `protocol` varchar(10) NOT NULL,
  9.   `url` varchar(120) NOT NULL,
  10.   `visitpath` varchar(80) NOT NULL,
  11.   PRIMARY KEY  (`id`)
  12. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

on every page I track, I start with

  1. session_start();
  2. log_hit();

The function log_hit() relies on the php $_SERVER array that holds all data the web server collects:

  1. function log_hit();
  2.         $ip   = $_SERVER['REMOTE_ADDR'];
  3.  
  4. //if it is my own ip, exit the routine
  5.  if($ip =="my.own.ip.here") return;
  6.  
  7. //what page is my visitor at ?
  8.  $page  = "http://".$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF'];
  9.  
  10. //what page did he come from ?
  11.  $visitpage  = $_SERVER['HTTP_REFERER'];
  12.  
  13.  $visitdate  = date("YmdHi");
  14.  
  15. //what user-string is it (iow: is it googlebot and pals or an actual visitor ?)
  16.  $agent      = $_SERVER["HTTP_USER_AGENT"];        
  17.  
  18. //the rest i dont use but i'll throw them in just for fun :
  19.  $protocol   = $_SERVER['SERVER_PROTOCOL'];
  20.  $method    = $_SERVER['REQUEST_METHOD'];
  21.  $url           = $_SERVER['REQUEST_URI'];
  22.  $visitpath   = parse_url($page, PHP_URL_PATH);
  23.  
  24.  if(!$ip) {} else {
  25. //if I have an Ip, register the hit :
  26. //connect to mysql table
  27.   $m=connect_data();
  28.  
  29. //and store it in the LOG table
  30.   $myqry="INSERT INTO `serp_log` (
  31.  `ip` ,`page` ,`visitpage` ,`visitdate` ,`agent` ,`protocol` ,`url` ,`visitpath` ) VALUES (
  32.  '".$ip."', '".$page."', '".$visitpage."', '".$visitdate."', '".$agent."', '".$protocol."', '".$url."', '".$visitpath."')";
  33.   mysql_query($myqry, $m) or die('store log :'.mysql_error());
  34.   mysql_close($m);
  35. }

the connect_data() function is standard mysql connection

  1. function connect_data() {
  2.   $DB_USER =  "user";
  3.   $DB_PASSWORD = "password";
  4.   $DB_HOST = "server";
  5.   $DB_DATA = "database";
  6.   $link =  mysql_connect($DB_HOST, $DB_USER, $DB_PASSWORD) or $error = mysql_error();
  7.   if (!$link) {
  8.       return $error;
  9.   }  
  10.      mysql_select_db($DB_DATA, $link) or $error = mysql_error();
  11.   return $link;
  12.  }

It is far from perfect, but it works. It helps check the crawl-rate of pages. You can filter out the useragent ‘Googlebot’ and run a query grouping useragent by date and see what times Googlebot comes by.

  1. //66.249.72.143 is GoogleBot
  2. check_traffic_by_IP('66.249.72.143' );
  3.  
  4. function check_traffic_by_IP($thisIP) {
  5.  $m = connect_data();
  6.  $qry = "SELECT * from `log` WHERE `ip` = '".$thisIP."' GROUP BY `visitdate`";
  7.  $lst = mysql_query($qry, $m);
  8.  echo '<table><tbody>';
  9.  while($row=mysql_fetch_assoc($lst)) {
  10.   echo '<tr><td>'.$row['visitdate'].'</td></tr>';
  11.  }
  12.  echo '</tbody></table>';
  13. }

…or exclude all records if the UserAgent indicates it was a Bot, and get a listing of organic beings visiting the pages :

  1. function check_real_traffic() {
  2.  $m = connect_data();
  3.  
  4. //the bots
  5.  $bots = array('googlebot', 'anonymouse');
  6.  
  7.  $qry = "SELECT * from `log`";
  8.  $lst = mysql_query($qry, $m);
  9.  
  10.  echo '<table><tbody>';
  11.  
  12.  while($row=mysql_fetch_assoc($lst)) {
  13.  
  14. //check if the entries in the bots-array
  15. //occur in the stored useragent string
  16.   $traffic=-1;
  17.   for($i=0;$i<count ($bots);$i++) {
  18.    if(strpos(strtolower($row['agent']), $bots[$i])>0) $traffic+=1;
  19.   }
  20.  
  21. //if it's not a bot (then the $traffic var is still -1), output the info
  22.   if($traffic==-1) {
  23.                echo '<tr><td>'.$row['visitdate'].'</td><td>'.$row['ip'].'</td><td>'.$row['page'].'</td></tr>';
  24.                 }
  25.  }
  26.  echo '</count></tbody></table>';
  27. }

…that shows ‘organic’ visitors.

other resources
tek-tips.com/ Limit Requests per day by IP

phpMyVisites open source free referrer and traffic analysis