User tracking mod

[Brent]

1.3.6.1
I just tried to install and am getting an sql error. I dropped the table several times and tried to install the patch via both Myphp and the Zen tool with the same result.

Get the same error on 2 sites. Both are 1.3.6 zen and 1.3.6.1 of the mod.

I will also assume this is the reason there is no options under cinfig for this mod.
--------------------------------
SQL query:

INSERT INTO configuration_group( `configuration_group_id` , `configuration_group_title` , `configuration_group_description` , `sort_order` , `visible` )
VALUES ( 999, 'User Tracking Config', 'User Tracking', 31, 1 ) ;

MySQL said: Documentation
#1062 - Duplicate entry '999' for key 1
----------------------------------------

Im a database dummy and need a little guidance as to whether its an error in the supplied sql or if its me.

I'm in the same club as you when it comes to databases but I vaguely remember coming across this issue somewhere in the old archives.

I think another mod you have installed is already using 999. Possibly the old Admin Levels? I know there are issues between those two if you are using the olds version.

It had something to do with the way the original User Tracking Code was written.

Please don't quote me on this! As nobody else has replied yet I thought I may as well post as it gives you something to go on.

[a_berezin]

Use this sql-path to install user tracking

DROP TABLE IF EXISTS user_tracking;
CREATE TABLE user_tracking (
  customer_id int(11) default NULL,
  click_id int(11) default NULL,
  full_name varchar(64) NOT NULL default '',
  session_id varchar(128) NOT NULL default '',
  ip_address varchar(15) NOT NULL default '',
  time_entry varchar(14) NOT NULL default '',
  time_last_click varchar(14) NOT NULL default '',
  last_page_url varchar(254) NOT NULL default '',
  referer_url varchar(254) NOT NULL default '',
  page_desc varchar(64) NOT NULL default '',
  KEY session_id (session_id),
  KEY time_entry (time_entry)
) TYPE=MyISAM;

SET @t4=0;
SELECT (@t4:=configuration_group_id) as t4 
FROM configuration_group
WHERE configuration_group_title= 'User Tracking Configuration';
DELETE FROM configuration WHERE configuration_group_id = @t4;
DELETE FROM configuration_group WHERE configuration_group_id = @t4;

INSERT INTO configuration_group VALUES (NULL, 'User Tracking Configuration', 'Set User Tracking Options', '1', '1');
UPDATE configuration_group SET sort_order = last_insert_id() WHERE configuration_group_id = last_insert_id();

SET @t4=0;
SELECT (@t4:=configuration_group_id) as t4 
FROM configuration_group
WHERE configuration_group_title= 'User Tracking';

INSERT INTO configuration (configuration_id, configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES (NULL, 'User Tracking Visitors', 'ZEN_CONFIG_USER_TRACKING', 'true', 'Check the Customers/Guests behaviour? (each click will be recorded)', @t4, 1, now(), now(), NULL, 'zen_cfg_select_option(array(\'true\', \'false\'),'),
(NULL, 'User Tracking (ADMIN)', 'ADMIN_CONFIG_USER_TRACKING', 'true', 'Check the ADMINs behaviour? (each click will be recorded)', @t4, 2, now(), now(), NULL, 'zen_cfg_select_option(array(\'true\', \'false\'),'),
(NULL, 'User Tracking (exclude IP-Addresses)', 'CONFIG_USER_TRACKING_EXCLUDED', 'your IPs', 'Do NOT record these IP Addresses<br />(like webmaster/owners/company IPs)<br /><br />Examples:<br />192.168.1.1<br />192.168.1.0/255.255.255.0<br />192.168.1.0/24<br /><br />Or any combination of these separated with a comma:<br />192.168.1.1<b>,</b>192.168.2.0/255.255.255.0<b>,</b>192.168.3.0/24<b>,</b>192.168.1.2<br /><br />', @t4, 10, now(), now(), NULL, NULL),
(NULL, 'User Tracking (Session Limit)', 'CONFIG_USER_TRACKING_SESSION_LIMIT', '50', 'Displaying the latest # sessions of this 24 hour period.<br />(SET to 999999 for unlimited per 24 hour period)<br />NOTE:<br />Watch your space !', @t4, 15, now(), now(), NULL, NULL),
(NULL, 'User Tracking (your favorite WHOIS URL)', 'USER_TRACKING_WHOIS_URL', 'http://www.dnsstuff.com/tools/whois.ch?ip=', 'Enter your favorite WHOIS tracking site<br />(the IP will follow automatically after this url)', @t4, 50, now(), now(), NULL, NULL),
(NULL, 'User Tracking (HostLookup)', 'CONFIG_USER_TRACKING_HOST_LOOKUP', 'true', 'Check the hostname of each IP address? (may cause slower display of UT data)', @t4, 20, now(), now(), NULL, 'zen_cfg_select_option(array(\'true\', \'false\'),');

[JeffD]

The sql is written to be used in the patch tool only. You would need to edit the sql and add the table prefix you are using to execute in PHPMyAdmin. The error you are seeing is probably okay as this would happen if the configuration group already exists which indicates you execute the sql twice.

You should see the config options under the Configuration menu. You didn't say if everything else is working so I'll assume it is working okay for you? Let us know if not and we will try to help?

Happy holidays!
Jeff

[inksale]

I used the SQL listed above and did not get any installation errors, however, it is still not working.

With the original SQL as well as this new one the configuration tool has no options to choose from.

On the user tracking page I am now getting this message.

1054 Unknown column 'customers_host_address' in 'field list'
in:
[select customer_id, full_name, ip_address, time_entry, time_last_click, last_page_url, page_desc, session_id, referer_url, customers_host_address from user_tracking where time_entry > 1166936400 and time_entry < 1167022800 order by time_last_click desc]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

[JeffD]

It appears that you have an older version of the user_tracking table and a newer version of the user tracking code installed. Please download the user tracking contrib again and execute the SQL for creating the user_tracking table which looks like:

DROP TABLE IF EXISTS user_tracking;
CREATE TABLE user_tracking (
  `customer_id` int(11) default NULL,
  `click_id` int(11) default NULL,
  `full_name` varchar(64) NOT NULL default '',
  `session_id` varchar(32) NOT NULL default '',
  `ip_address` varchar(15) NOT NULL default '',
  `time_entry` varchar(14) NOT NULL default '',
  `time_last_click` varchar(14) NOT NULL default '',
  `last_page_url` varchar(128) NOT NULL default '',
  `referer_url` varchar(254) NOT NULL default '',
  `page_desc` varchar(64) NOT NULL default '',
  `customers_host_address` varchar(64) NOT NULL default ''
) TYPE=MyISAM;

This should correct the problem. Let us know if not?

Good Luck!
Jeff

[inksale]

Just to keep this straight,
I dropped the table I had installed,
DL the contribution
Re-installed all the files by over righting them.
Installed the SQL included with the contribution.

The included SQL still gave me the original errors and there are no options in the config.

I dropped the table and reinstalled using the SQL listed above By Jeff.

The tracking now works but there are still no options in the configuration tool.

[JeffD]

That is very odd that you would get the same SQL error (I assume you mean duplicate key 999) since the SQL if executed properly deletes this key?

DELETE FROM configuration where configuration_group_id = '999';

Can you clarify this?

Also, when you say:

still no options in the configuration tool

Are you saying the Configuration->User Tracking Config menu choice is missing?

-or-

Do you mean when you select Configuration->User Tracking Config there are not six configuration switches listed?

Let me know and I will help?

Jeff

[qhome]

If you'd like a more "collision proof" approach, you could use:

DELETE FROM configuration where configuration_group_id = '999';
DELETE FROM configuration_group where configuration_group_id = '999';
DELETE FROM configuration_group where configuration_group_title like '&#37;User Tracking%';
DELETE FROM configuration where configuration_title like '%User Tracking%';
INSERT INTO configuration_group (`configuration_group_id`, `configuration_group_title`, `configuration_group_description`, `sort_order`, `visible`) VALUES ('', 'User Tracking Config', 'User Tracking', 31, 1);

SELECT @gid := configuration_group_id
FROM configuration_group where configuration_group_title LIKE '%User Tracking%';

INSERT INTO configuration VALUES ('', 'User Tracking (ADMIN)', 'ADMIN_CONFIG_USER_TRACKING', 'true', 'Check the ADMINs behaviour ? (each click will be recorded)', @gid, 2, '2003-03-03 11:19:26', '2003-02-09 21:20:07', NULL, 'zen_cfg_select_option(array(''true'', ''false''),');
INSERT INTO configuration VALUES ('', 'User Tracking (exclude this IP-Address)', 'CONFIG_USER_TRACKING_EXCLUDED', 'your IP', 'Do NOT record this IP Address<br>(like webmaster/owners/Beta-testers)', @gid, 10, '2003-03-04 23:08:38', '2003-02-09 21:20:07', NULL, NULL);
INSERT INTO configuration VALUES ('', 'User Tracking (Session Limit)', 'CONFIG_USER_TRACKING_SESSION_LIMIT', '50', 'Displaying the latest # sessions of this 24 hour period.<br>(SET to 999999 for unlimited per 24 hour period)<br>NOTE:<BR>Watch you space !', @gid, 15, '2003-03-03 11:19:13', '2003-02-09 21:20:07', NULL, NULL);
INSERT INTO configuration VALUES ('', 'User Tracking (your favorite WHOIS URL)', 'USER_TRACKING_WHOIS_URL', 'http://www.dnsstuff.com/tools/whois.ch?ip=', 'Put here you favorite WHOIS tracking site<br>(the IP will follow automaticly after this url)', @gid, 50, '2003-03-03 11:19:13', '2003-03-11 11:40:01', NULL, NULL);
INSERT INTO configuration VALUES ('', 'User Tracking Visitors', 'ZEN_CONFIG_USER_TRACKING', 'true', 'Check the Customers/Guests behaviour ? (each click will be recorded)', @gid, 1, '2003-03-03 11:19:26', '2003-02-09 21:20:07', NULL, 'zen_cfg_select_option(array(''true'', ''false''),');

That will actually let the database decide on the next Configuration Group ID number, then copy that number to a variable "gid" and use that in place of hardcoding 999.

[qhome]

Cancel that. It looks like UserTracking code uses a hardcoded 999 for the user_tracking_config.php file. You would need to change a lot of the hardcoding in the php code to set this variable properly.

[Woodymon]

@inksale: I had the same/similar issues as you report (I reported my solutions previously in the thread).

Originally the mod was designed to display "User Tracking Configuration" under Configuration menu. However during the later development history for the mod, the SQL was revised to display BOTH "User Tracking" and "User Tracking Configuration" under the Tools menus, which I think is the cause of many issues, with those who have installed a previous version and with thosw who have installed Admin Profiles mod.

Note that the Admin Profiles mod (from Kuroi) plays funny with User Tracking (or vice versa). In Kuroi's latest versions of Admin Profiles he provides some patch code and instructions to install to help if you have Admin Profiles installed. But I found that workaround did not solve my issue completely.

Then DrByte posted to this thread some work-arounds for the User Tracking Configuration menu issue, but that too was not a complete solution for me as I still had some issues of the User Tracking Configuration menu item sometimes not displaying, depending on which admin menu page I was viewing (dynamic disappearing ghost menus).

After all that my solution was to install the SQL patch from Andrew (a_berezin). The result of that is "User Tracking Configuration" correctly displays under the "Configuration" menu, just as the original User Tracking mod was designed to do. And most importantly all the menu items correctly show up fine on the User Tracking Configuration page.

Also be sure to edit your Admin Profiles "permissions" to allow display of the "User Tracking Configuration" menu items for whichever admin user you want the menu displayed.

P.S. You might want to also check out the Admin Profiles support thread. I believe there was some discussion of this issue there also.

Woody