title: Store user agents in mysql database with php
body: <p>I am successfully trying to store statistics in a mysql database. I made a tracker class which checks the URL and updates the hits in the mysql row 'hits'. However i want to expand my stats with more info like browser /os stuff like that. Now whenever i have a request to my tracker i make a new entry in my user_agents database and i was wondering if this is wise, and how my database structure should be built instead.</p>
  
  <p>I need to mention that the statistics are for different users, so user-x has it's own hits and browser info, and user-y also.</p>
  
  <p>Mysql Database Structure</p>
  
  <pre><code>    == lumbr_stats
  
      |//**id**//|bigint(20)|Nee|
      |active|int(11)|Nee|
      |account|varchar(255)|Nee|
      |date|datetime|Nee|
      |ip|varchar(255)|Nee|
      |hits|bigint(20)|Nee|
      |theme|bigint(20)|Nee|
  
      == lumbr_themes
      |//**id**//|bigint(20)|Nee|
      |active|int(11)|Nee|
      |slug|varchar(255)|Ja|NULL
      |name|varchar(255)|Nee|
      |description|text|Ja|NULL
  
  
      == lumbr_user_agents
      |//**id**//|bigint(20)|Nee|
      |name|varchar(255)|Nee|
      |version|int(11)|Nee|
      |stat_user|bigint(20)|Nee|
  </code></pre>
  
  <p>Tracker class .php</p>
  
  <pre><code>    &lt;?php 
      require_once(__DIR__ . '/inc/database.php');
      require_once(__DIR__ . '/lib/KLogger.php');
  
      Class ThemeStatsTracker
      {
          public $log;
  
          private $theme_name;
          private $theme_user;
          private $theme_name_db_id;
          private $theme_user_db_id;
  
          public function __construct()
          {
              // Setup Log 
              $this-&gt;log = KLogger::instance(dirname(__FILE__).'/log/', KLogger::DEBUG);
  
              // Process URI variables
              $uri = explode('/',$_SERVER['REQUEST_URI']);    
              $this-&gt;theme_name = (!empty($uri[1])) ? $uri[1] : NULL;
              $this-&gt;theme_user = (!empty($uri[2])) ? $uri[2] : NULL;
  
              // Handle the Request
              $this-&gt;database_connect();
              if($this-&gt;validate_theme()){
                  if($this-&gt;user_entry_exists()){
                      $this-&gt;user_entry_update(); 
                  }else{
                      $this-&gt;user_entry_create();
                  }   
              }
  
              // Always serve an image as response
              $this-&gt;serve_image();
          }
  
          private function validate_theme()
          {
              $query  = sprintf("SELECT id FROM ".DB_PREFIX."themes WHERE slug='%s' LIMIT 1", mysql_real_escape_string($this-&gt;theme_name));
              $result = mysql_query($query);
              if (!$result){
                  $this-&gt;log-&gt;logError(__FUNCTION__ . ' FAIL: ' . $query . ' BECAUSE: ' . mysql_error());
                  return FALSE;
              }
              if(mysql_num_rows($result)==0){
                  $this-&gt;log-&gt;logError(__FUNCTION__ . ' Theme name ' . $this-&gt;theme_name . ' NOT found');
                  return FALSE;
              }else{
                  $this-&gt;log-&gt;logInfo(__FUNCTION__ . ' Theme name ' . $this-&gt;theme_name . ' found');
                  $this-&gt;theme_name_db_id = mysql_result($result,0);
                  return TRUE;
              }
          }
  
          private function user_entry_exists()
          {
              $query  = sprintf("SELECT id FROM ".DB_PREFIX."stats WHERE account='%s' LIMIT 1", mysql_real_escape_string($this-&gt;theme_user));
              $result = mysql_query($query);
              if (!$result){
                  $this-&gt;log-&gt;logError(__FUNCTION__ . ' FAIL: ' . $query . ' BECAUSE: ' . mysql_error());
                  return FALSE;
              }
              if(mysql_num_rows($result)==0){
                  $this-&gt;log-&gt;logInfo(__FUNCTION__ . ' New user ' . $this-&gt;theme_user);
                  return FALSE;
              }else{
                  $this-&gt;log-&gt;logInfo(__FUNCTION__ . ' Existing user ' . $this-&gt;theme_user);
                  $this-&gt;theme_user_db_id = mysql_result($result,0);
                  return TRUE;
              }   
          }
  
          private function user_entry_create()
          {
              $query  = sprintf("INSERT INTO ".DB_PREFIX."stats (id,active,account,date,ip,hits,theme) VALUES ('','1','%s',NOW(),'".$_SERVER['REMOTE_ADDR']."','1','".$this-&gt;theme_name_db_id."')", mysql_real_escape_string($this-&gt;theme_user));
              $result = mysql_query($query);
              $insertid = mysql_insert_id();
  
              if (!$result){
                  $this-&gt;log-&gt;logError(__FUNCTION__ . ' FAIL: ' . $query . ' BECAUSE: ' . mysql_error());
                  return FALSE;
              }
  
              $browser = $this-&gt;detect_browser();
              $query2 = "INSERT INTO ".DB_PREFIX."user_agents (id,name,version,stat_user) VALUES ('','".$browser['name']."','".$browser['version']."','".$insertid."')";
  
              $result2 = mysql_query($query2);
              if (!$result2){
                  $this-&gt;log-&gt;logError(__FUNCTION__ . ' FAIL: ' . $query2 . ' BECAUSE: ' . mysql_error());
                  return FALSE;
              }
  
              $this-&gt;log-&gt;logNotice(__FUNCTION__ . ' New user created with id  ' . mysql_insert_id());
              return TRUE;
          }
  
          private function user_entry_update()
          {
              $query  = sprintf("UPDATE ".DB_PREFIX."stats SET date=NOW(),ip='".$_SERVER['REMOTE_ADDR']."',hits=hits+1 WHERE id='".$this-&gt;theme_user_db_id."' LIMIT 1");
              $result = mysql_query($query);
              if (!$result){
                  $this-&gt;log-&gt;logError(__FUNCTION__ . ' FAIL: ' . $query . ' BECAUSE: ' . mysql_error());
                  return FALSE;
              }
  
              $browser = $this-&gt;detect_browser();
              $query2 = "INSERT INTO ".DB_PREFIX."user_agents (id,name,version,stat_user) VALUES ('','".$browser['name']."','".$browser['version']."','".$this-&gt;theme_user_db_id."')";
  
              $result2 = mysql_query($query2);
              if (!$result2){
                  $this-&gt;log-&gt;logError(__FUNCTION__ . ' FAIL: ' . $query2 . ' BECAUSE: ' . mysql_error());
                  return FALSE;
              }
  
              $this-&gt;log-&gt;logNotice(__FUNCTION__ . ' User with id ' . $this-&gt;theme_user_db_id . 'updated');
              return TRUE;
          }
  
          private function serve_image()
          {
              header("Content-type: image/gif");
              header("Content-length: 43");
              $fp = fopen("php://output","wb");
              fwrite($fp,"GIF89a\x01\x00\x01\x00\x80\x00\x00\xFF\xFF",15);
              fwrite($fp,"\xFF\x00\x00\x00\x21\xF9\x04\x01\x00\x00\x00\x00",12);
              fwrite($fp,"\x2C\x00\x00\x00\x00\x01\x00\x01\x00\x00\x02\x02",12);
              fwrite($fp,"\x44\x01\x00\x3B",4);
              fclose($fp);
          }
  
          private function detect_browser() 
          {
              $ua = strtolower($_SERVER['HTTP_USER_AGENT']);
  
              if(preg_match('/(chromium)[ \/]([\w.]+)/', $ua))
                  $browser = 'chromium';
              elseif(preg_match('/(chrome)[ \/]([\w.]+)/', $ua))
                  $browser = 'chrome';
              elseif(preg_match('/(safari)[ \/]([\w.]+)/', $ua))
                  $browser = 'safari';
              elseif(preg_match('/(opera)[ \/]([\w.]+)/', $ua))
                  $browser = 'opera';
              elseif(preg_match('/(msie)[ \/]([\w.]+)/', $ua))
                  $browser = 'msie';
              elseif(preg_match('/(mozilla)[ \/]([\w.]+)/', $ua))
                  $browser = 'mozilla';
  
              preg_match('/('.$browser.')[ \/]([\w]+)/', $ua, $version);
  
              return array('name'=&gt;$browser,'version'=&gt;$version[2]);
          }
  
  
          private function database_connect()
          {
              $con = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);
              if(!$con){
                  $this-&gt;log-&gt;logError(__FUNCTION__ . ' FAIL: ' . $query . ' BECAUSE: ' . mysql_error());
                  return FALSE;
              }
              $select = mysql_select_db(DB_NAME, $con);
              if (!$select){
                  $this-&gt;log-&gt;logError(__FUNCTION__ . ' FAIL: ' . $query . ' BECAUSE: ' . mysql_error());
                  return FALSE;
              }
          }       
      }
  
      $stats = new ThemeStatsTracker();
  </code></pre>