Jump to content

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


Emil_RDW

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.