Emil_RDW Posted July 14, 2012 Share Posted July 14, 2012 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! Quote Link to comment Share on other sites More sharing options...
smoseley Posted July 15, 2012 Share Posted July 15, 2012 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.