Positive from Average

I just watched Bruce almighty. Wasn't bad wasn't thaqt great. Light entertainment and Carrey was quite funny in his wacky way. However it had two great little bits.

The first was when Bruce says to god

Can I ask you about free will?

to which God replies

Yes you can, that's the beauty of it

. Brilliant and when Morgan Freeman says it I can almost give it credibility.

The second was a message about changing things yourself. Everyone having the power to do miracles, and by miracles I don't mean magic. Bruce learnt he could make a difference without the powers and later on Anthony spotted some chavs letting down tyres across the road, so I did something.

Nothing big or special, just went over and as I crossed the road they did in the other direction so I followed back and confronted them.

Are you two letting down tyres?

No

Are you sure?

Yes honest.

Are you sure?

Yes, We weren't I promise!

Right

And I left... and they walked off but didn't do it again. Liek I said nothing big but it felt good. Neighbourhood watch here I come :) It's easy not to do things like that and I think I've realised I don't do things I should. That's gonna change.

Converting ShortStats to PostgreSQL

Shaun Inmans rather spiffy shortstats has always looked nice to me but until lately I coudln't run it as I don't use MySQL. So I hacked it and with Shaun's permission here is a guide how.

configuration.php

One small change ass $SI_db['con'] = 0; to the database section to provide a connection resource for all your PostgreSQL actions.

_install.php

1. Change the checking line to use $SI_db['con'] and pg_query so the code reads
   if (@ pg_query($SI_db['con'],"SELECT * FROM $SI_tables[stats]") && @ pg_query($SI_db['con'],"SELECT * FROM $SI_tables[searchterms]"))


2. Change the two queries that add the database to the following
   

   
   $query = "CREATE TABLE ".$SI_tables['stats']." (
   	  id SERIAL,
   	  remote_ip varchar(15) NOT NULL,
   	  country varchar(50) NOT NULL,
   	  domain varchar(255) NOT NULL,
   	  referer varchar(255) NOT NULL,
   	  resource varchar(255) NOT NULL,
   	  user_agent varchar(255) NOT NULL,
   	  platform varchar(50) NOT NULL,
   	  browser varchar(50) NOT NULL,
   	  version varchar(15) NOT NULL,
   	  dt int4 NOT NULL default '0',
   	  PRIMARY KEY (\"id\")
   	  )";
   $query_search = "CREATE TABLE $SI_tables[searchterms] (
   	  id SERIAL,
   	  searchterms varchar(255) NOT NULL,
   	  count int4 NOT NULL default '0',
   	  PRIMARY KEY  (id)
   	  )";
   

   
   


3. 
   Take the query execution out of the if statment at the end as so:
   
$r1 = @ pg_query($SI_db['con'],$query);
$r2 = @ pg_query($SI_db['con'],$query_search);

if ($r1 && $r2)

   

inc.stats.php

One small change, the query at the end should be pg_query($SI_db['con'],$query); using our database connection and PostgreSQL.

functions.php

Again three changes here

1. The connection function SI_pconnect() needs a PostreSQL connection string like
   $SI_db['con'] =@ pg_connect("host=".$SI_db['server']." user=".$SI_db['username']." password=".$SI_db['password']." dbname=".$SI_db['database']); and you can replace the logic after with if ($SI_db['con'] = 0) die($horribly);


2. Every single function needs to be scoured for mysql_query($query); calls and have them replaced with pg_query($SI_db['con'],$query); as well as adding global $SI_db; at the start of every single function.


3. last but not least a to get referers working we need a small change to the SQL in SI_getDomains()
   

   
   SELECT 
       domain, 
       referer, 
       COUNT(*) AS total,
       MAX(dt) as last_access
     FROM 
       $SI_tables[stats]
     WHERE 
       domain !='".
       SI_trimReferer($_SERVER['SERVER_NAME'])."' 
     AND 
       domain!='' 
     GROUP BY 
       domain,referer
     ORDER BY 
       total DESC, 
       last_access DESC
   

   
   

And then your done

Follow the README provided and everything should work fine. If not feel free to email me and if I have time I'll offer help or even send a copy of my working abeit old version of shortstats