A script will not write to DB (at least half of it)

[Emil_RDW]

Hello,

 

I'm working on implementing a small script on a website where the activity and hits on the website will be available to see on the Admin back end.

 

For some reason I can only get half of it to work and currently can only see the hits counter but not the actual activity because for some reason the table in the DB will not populate with what the user is doing at the moment.  I should add that this is all on a PHP website for auctions (much like ebay).

 

So basic run down on the script:

 

 

I create the tables:

 

CREATE TABLE `site_whos_online` (
  `id` bigint(20) NOT NULL auto_increment,
  `location` varchar(200) NOT NULL default '',
  `time` bigint(20) NOT NULL default '0',
  `ipaddress` text NOT NULL,
  `specific` varchar(255) NOT NULL default '0',
  `hostname` varchar(255) NOT NULL default '',
  `userid` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `site_hit_counter` (
  `id` bigint(20) NOT NULL auto_increment,
  `ipaddress` varchar(255) NOT NULL default '',
  `hostname` varchar(255) NOT NULL default '',
  `time` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

in the main site funcitons.php file I add

 

 

function mygetSqlNumber($sqlQuery) {
$query=@mysql_query($sqlQuery);
$result=@mysql_num_rows($query);
@mysql_free_result($query);
return $result;
}
function update_wol($location,$timenow,$ipaddress,$specific,$hostname,$userid) {
$dupe = mygetSqlNumber("SELECT id FROM site_whos_online WHERE ipaddress = '".$ipaddress."'");
if (!$dupe) {	
$update = mysql_query("INSERT INTO " . DB_PREFIX . "whos_online 
(location,time,ipaddress,specific,hostname,userid) VALUES ('" . $location . "', '" . $timenow . "', '" . $ipaddress . "', '" . $specific . "', '" . $hostname . "', '" . $userid . "')");
} else {
$update = mysql_query("UPDATE site_whos_online SET location='".$location."', time='".$timenow."', specific='".$specific."', hostname='".$hostname."', userid='".$userid."' WHERE ipaddress = '".$ipaddress."'");
}
}
function update_hit($ipaddress,$hostname) {
$date = time();
$update = mysql_query("INSERT INTO " . DB_PREFIX . "hit_counter 
(ipaddress,hostname,time) VALUES ('" . $ipaddress . "', '" . $hostname . "', '" . $date . "')");
}

 

To track the activity and figure out where the user is I added this to the global header of the site:

 

$location = substr($_SERVER["PHP_SELF"],1);
$ipaddress = $_SERVER['REMOTE_ADDR'];
$timenow = time();
$hostname = $_SERVER['HTTP_USER_AGENT'];
$userid = $session->value('user_id');
if ($location == "auction_details.php") { $specific = $_REQUEST['auction_id']; }
if ($location == "shop.php") { $specific = $_REQUEST['user_id']; }
if ($location == "categories.php") { $specific = $_REQUEST['parent_id']; }
if ($location == "about_me.php") { $specific = $_REQUEST['user_id']; }
if ($location == "wanted_details.php") { $specific = $_REQUEST['wanted_ad_id']; }
if ($location == "profile.php") { $specific = $_REQUEST['user_id']; }
if ($location == "user_reputation.php") { $specific = $_REQUEST['user_id']; }
if ($location == "other_items.php") { $specific = $_REQUEST['owner_id']; }
if ($location == "auction_search.php") { 
if ($_REQUEST['basic_search']) { $specific = $_REQUEST['basic_search']; }
if ($_REQUEST['keywords_search']) { $specific = $_REQUEST['keywords_search']; }
}
if ($location == "edit_item.php") { $specific = $_REQUEST['auction_id']; }
$update = update_wol($location,$timenow,$ipaddress,$specific,$hostname,$userid);
$botspy = update_hit($ipaddress,$hostname);

 

But this is where it all grinds to a halt at least for the activity not being recorded into the DB.  The hit counter runs fine.  When I first ran the script it ran beautifully and as soon as I refreshed the page the activity went away and I haven't been able to figure out why it's table is always empty in the DB

 

Can anybody spot any errors?

 

Thank you for any help!

[smoseley]

Before we get into the details, I would offer up that maybe you should reconsider the architecture...

 

Your two tables should be:

 

user_sessions:

• id - autoincrement id
  
• phpsessid - the phpsessid
  
• user_id - nullable reference to your users table (so you can track sessions for logged-in users)
  
• ip_address - the user's ip address for the session
  
• hostname - the user's hostname (if applicable)
  
• created - TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
  
• last_updated - TIMESTAMP - updated to NOW() on page load - this will help you track your session length
  

 

user_session_actions:

• id - autoincrement id
  
• user_session_id - reference to the user_sessions table
  
• request_uri - the request uri
  
• request_data - the request parameters ($_REQUEST)
  
• created - TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP - the request timestamp
  

 

This schema will provide you much better organized information.