User tracking mod

Thank you so much for clarifying that! I tried all the different first line suggestions and they all gave this sytax error (with the different UserTrackgID changes of course)

PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE @UserTrackgID <> '0'' at line 1 :: INSERT INTO admin_pages (page_key, language_key, main_page, page_params, menu_key, display_on_menu, sort_order) VALUES ('UserTrackingConfig', 'BOX_TOOLS_USER_TRACKING_CONFIG', 'FILENAME_CONFIGURATION', CONCAT('gID=', @UserTrackgID), 'configuration', 'Y', 10001)

I think that it hates me LOL. I am not in any hurry and I am okay to wait until you have time to review this. I am patient and very appreciative.

Quote:

---

Originally Posted by MyCoolHats

Thank you so much for clarifying that! I tried all the different first line suggestions and they all gave this sytax error (with the different UserTrackgID changes of course)

PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE @UserTrackgID <> '0'' at line 1 :: INSERT INTO admin_pages (page_key, language_key, main_page, page_params, menu_key, display_on_menu, sort_order) VALUES ('UserTrackingConfig', 'BOX_TOOLS_USER_TRACKING_CONFIG', 'FILENAME_CONFIGURATION', CONCAT('gID=', @UserTrackgID), 'configuration', 'Y', 10001)

I think that it hates me LOL. I am not in any hurry and I am okay to wait until you have time to review this. I am patient and very appreciative.

---

Please try this one SQL statement set of commands and report back the result:

Code:

---

SELECT @UserTrackgID := configuration_group_id
FROM configuration_group where configuration_group_title LIKE '%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 (0, 'User Tracking Visitors', 'ZEN_CONFIG_USER_TRACKING', 'true', 'Check the Customers/Guests behaviour ? (each click will be recorded)', @UserTrackgID, 1, '2003-03-03 11:19:26', '2003-02-09 21:20:07', NULL, 'zen_cfg_select_option(array(''true'', ''false''),');

---

Quote:

---

Originally Posted by MyCoolHats

Thank you so much for clarifying that! I tried all the different first line suggestions and they all gave this sytax error (with the different UserTrackgID changes of course)

PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE @UserTrackgID <> '0'' at line 1 :: INSERT INTO admin_pages (page_key, language_key, main_page, page_params, menu_key, display_on_menu, sort_order) VALUES ('UserTrackingConfig', 'BOX_TOOLS_USER_TRACKING_CONFIG', 'FILENAME_CONFIGURATION', CONCAT('gID=', @UserTrackgID), 'configuration', 'Y', 10001)

I think that it hates me LOL. I am not in any hurry and I am okay to wait until you have time to review this. I am patient and very appreciative.

---

Ugh. Bone head stupid mistake on my part... I was trying to insert that menu option if and only if the variable wasn't 0. Problem is that the test method (where clause) doesn't work on an insert, because an insert is a new line. I need to find a more program method to skip the entire line if the value is 0 or run the line if not. So the proper sql format for the "third" line at this point is to omit the where @UserTrackgID <> 0 portion of the statement.

This is. At least twice in the last hour or so that slower would be faster...

Quote:

---

Originally Posted by mc12345678

Please try this one SQL statement set of commands and report back the result:

Code:

---

SELECT @UserTrackgID := configuration_group_id
FROM configuration_group where configuration_group_title LIKE '%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 (0, 'User Tracking Visitors', 'ZEN_CONFIG_USER_TRACKING', 'true', 'Check the Customers/Guests behaviour ? (each click will be recorded)', @UserTrackgID, 1, '2003-03-03 11:19:26', '2003-02-09 21:20:07', NULL, 'zen_cfg_select_option(array(''true'', ''false''),');

---

---

Awesome, no error messages! Statements process just fine and I do not have any errors in my logs. So the only other issue was the first one I had, that you were doing some research on - the display on my user tracking page results. I received your pm and responded. Thank you thank you thank you so much for your continued support!

Quote:

---

Originally Posted by MyCoolHats

Awesome, no error messages! Statements process just fine and I do not have any errors in my logs. So the only other issue was the first one I had, that you were doing some research on - the display on my user tracking page results. I received your pm and responded. Thank you thank you thank you so much for your continued support!

---

Good, okay, so what that indicates for those that have made it this far is that newer SQL servers require a "better" declaration..

For you what this means is that if you start at the last version of the

Code:

---

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

---

Skip the Update statement and the first INSERT (which is what you just did above), then include all of the remaining Insert statements, you should have everything installed as necessary by SQL...

Then there is the question of where your user tracking config option(s) appear. Are they under Tools like they used to be, or are they under Configuration where they should now be... (Didn't see PM, but that's okay as it seems like making progress.)

Okay, this is the newly revised NEW_INSTALL_user_tracking.sql file based on the above testing:

I did find a few more statements that were going to cause a problem similar to what was being seen above, also I corrected the other issue(s) of concern. So this is what I will be including in the new version. I could do a little more if then's to prevent having the duplication issue that was also seen, but the next version really is going to be an autoinstaller so a lot of this shouldn't become an issue. (Famous last words). :)

Thank you for your patience and persistence. Good luck. :)

Code:

---

  INSERT INTO admin_pages (page_key, language_key, main_page, page_params, menu_key, display_on_menu, sort_order) VALUES ('UserTracking', 'BOX_TOOLS_USER_TRACKING', 'FILENAME_USER_TRACKING', '', 'tools', 'Y', 10000);  SELECT @UserTrackgID := configuration_group_id  FROM configuration_group where configuration_group_title LIKE '%User Tracking%'; IF @UserTrackgID != 0 THEN
  INSERT INTO admin_pages (page_key, language_key, main_page, page_params, menu_key, display_on_menu, sort_order) VALUES ('UserTrackingConfig', 'BOX_TOOLS_USER_TRACKING_CONFIG', 'FILENAME_CONFIGURATION', CONCAT('gID=', @UserTrackgID), 'configuration', 'Y', 10001); END IF;
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 '' ) ENGINE=MyISAM;  SELECT @UserTrackgID := configuration_group_id  FROM configuration_group where configuration_group_title LIKE '%User Tracking%'; DELETE FROM configuration where configuration_group_id = @UserTrackgID and @UserTrackgID != 0;  INSERT INTO configuration_group (`configuration_group_id`, `configuration_group_title`, `configuration_group_description`, `sort_order`, `visible`) VALUES (0, 'User Tracking Config', 'User Tracking', '', 1); SELECT @UserTrackgID := configuration_group_id  FROM configuration_group where configuration_group_title LIKE '%User Tracking%'; UPDATE configuration_group SET sort_order = @UserTrackgID WHERE configuration_group_id = @UserTrackgID and @UserTrackgID != 0; INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking Visitors', 'ZEN_CONFIG_USER_TRACKING', 'true', 'Check the Customers/Guests behaviour ? (each click will be recorded)', @UserTrackgID, 1, '2003-03-03 11:19:26', '2003-02-09 21:20:07', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (ADMIN)', 'ADMIN_CONFIG_USER_TRACKING', 'true', 'Check the ADMINs behaviour ? (each click will be recorded)', @UserTrackgID, 2, '2003-03-03 11:19:26', '2003-02-09 21:20:07', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) 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)', @UserTrackgID, 10, '2003-03-04 23:08:38', '2003-02-09 21:20:07', NULL, NULL); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) 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 !', @UserTrackgID, 15, '2003-03-03 11:19:13', '2003-02-09 21:20:07', NULL, NULL); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Admin User Can Delete)', 'CONFIG_USER_TRACKING_ADMIN_CAN_DELETE', 'true', 'Allow Record Deletion to be Active?<br/>Setting this to true will override ENTRY and SESSION purges.<br/>Default <b>true</b><br/>', @UserTrackgID, 25, '2013-11-09 11:19:26', '2013-11-09 11:19:26', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Admin User Can Delete Historical Data)', 'CONFIG_USER_TRACKING_ADMIN_CAN_DELETE_RECORDS', 'false', 'Allow Record Deletion of records older than now - the purge duration set below.<br/>This value is ignored if Admin User Can Delete is set to true.  Otherwise set this to true to allow deletion of visits.<br/>Default <b>false</b>.<br/>', @UserTrackgID, 26, '2013-11-09 11:19:26', '2013-11-09 11:19:26', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Admin User Can Delete SESSIONS)', 'CONFIG_USER_TRACKING_ADMIN_CAN_DELETE_SESSIONS', 'false', 'Allow SESSION Deletion to be Active?<br/>This setting is ignored if Admin User Can Delete is set to true.<br/>Default <b>false</b><br/>', @UserTrackgID, 27, '2013-11-09 11:19:26', '2013-11-09 11:19:26', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Admin User Can Delete IP)', 'CONFIG_USER_TRACKING_ADMIN_CAN_DELETE_IP', 'false', 'Allow Deletion of records that match the identified IP address?<br/>This setting is ignored if Admin User Can Delete is set to true.<br/>Default <b>false</b><br/>', @UserTrackgID, 28, '2013-11-09 11:19:26', '2013-11-09 11:19:26', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Purge this Number)', 'CONFIG_USER_TRACKING_PURGE_NUMBER', '3', 'What is the number associated with purging before the current date/time?<br/><br/>An example would be to choose 3 here and units associated with days to delete data greater than 3 days before today.<br/>', @UserTrackgID, 30, '2013-11-09 23:08:38', '2013-11-09 23:08:38', NULL, NULL); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Purge Units)', 'CONFIG_USER_TRACKING_PURGE_UNITS', '1440', 'Pick the units associate with the periodicity to allow purging data.<br/><br/>60) Hours<br/>1440) Days<br/>10080) Weeks<br/>43200) Months (Based on 30 days)<br/>', @UserTrackgID, 31, '2013-11-09 23:08:38', '2013-11-09 23:08:38', NULL, 'zen_cfg_select_option(array(''60'', ''1440'',''10080'',''43200''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Type of User Interaction to Record)', 'CONFIG_USER_TRACKING_TRACK_TYPE_RECORD', '1', 'Type of user tracking to record?<br/><br/>1 - All visitors.<br/>2 - Visitors views where sessions have been started.<br/>3 - All users except bots/spiders ( requires Configuration->Sessions->Prevent Spider Sessions->true)  (Don\'t know if this works with Zen-Cart versions older than 1.2.6d)<br/><br/>Related to above: If you set Force Cookie Use->true, then at the first user entry, sessions do not start!!! And in variants 2 and 3, user-tracking will not have started. In this case you lose one click and do not log the refferal. But if this user is a returning user and has an old/previous zen cookie the session started and your tracking system will collect this info. So, the result beforehand is not logged or will not be known.<br/>', @UserTrackgID, 40, '2013-11-09 11:19:26', '2013-11-09 11:19:26', NULL, 'zen_cfg_select_option(array(''1'', ''2'',''3''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) 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)', @UserTrackgID, 50, '2003-03-03 11:19:13', '2003-03-11 11:40:01', NULL, NULL); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Show Product Category when tracking product clicks)', 'ZEN_CONFIG_SHOW_USER_TRACKING_CATEGORY', 'true', 'Show Product Category when tracking product clicks', @UserTrackgID, 60, '2006-12-05 11:19:26', '2006-12-05 21:20:07', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Version Installed)', 'CONFIG_USER_TRACKING_VERSION', '1.5', 'Shows the version number associated with user tracking and should be updated with each upgrade', @UserTrackgID, 1000, '2013-11-10 04:19:26', '2013-11-18 04:19:26', NULL, NULL);

---

Quote:

---

Originally Posted by mc12345678

Okay, this is the newly revised NEW_INSTALL_user_tracking.sql file based on the above testing:

I did find a few more statements that were going to cause a problem similar to what was being seen above, also I corrected the other issue(s) of concern. So this is what I will be including in the new version. I could do a little more if then's to prevent having the duplication issue that was also seen, but the next version really is going to be an autoinstaller so a lot of this shouldn't become an issue. (Famous last words). :)

Thank you for your patience and persistence. Good luck. :)

Code:

---

  INSERT INTO admin_pages (page_key, language_key, main_page, page_params, menu_key, display_on_menu, sort_order) VALUES ('UserTracking', 'BOX_TOOLS_USER_TRACKING', 'FILENAME_USER_TRACKING', '', 'tools', 'Y', 10000);  SELECT @UserTrackgID := configuration_group_id  FROM configuration_group where configuration_group_title LIKE '%User Tracking%'; IF @UserTrackgID != 0 THEN
  INSERT INTO admin_pages (page_key, language_key, main_page, page_params, menu_key, display_on_menu, sort_order) VALUES ('UserTrackingConfig', 'BOX_TOOLS_USER_TRACKING_CONFIG', 'FILENAME_CONFIGURATION', CONCAT('gID=', @UserTrackgID), 'configuration', 'Y', 10001); END IF;
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 '' ) ENGINE=MyISAM;  SELECT @UserTrackgID := configuration_group_id  FROM configuration_group where configuration_group_title LIKE '%User Tracking%'; DELETE FROM configuration where configuration_group_id = @UserTrackgID and @UserTrackgID != 0;  INSERT INTO configuration_group (`configuration_group_id`, `configuration_group_title`, `configuration_group_description`, `sort_order`, `visible`) VALUES (0, 'User Tracking Config', 'User Tracking', '', 1); SELECT @UserTrackgID := configuration_group_id  FROM configuration_group where configuration_group_title LIKE '%User Tracking%'; UPDATE configuration_group SET sort_order = @UserTrackgID WHERE configuration_group_id = @UserTrackgID and @UserTrackgID != 0; INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking Visitors', 'ZEN_CONFIG_USER_TRACKING', 'true', 'Check the Customers/Guests behaviour ? (each click will be recorded)', @UserTrackgID, 1, '2003-03-03 11:19:26', '2003-02-09 21:20:07', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (ADMIN)', 'ADMIN_CONFIG_USER_TRACKING', 'true', 'Check the ADMINs behaviour ? (each click will be recorded)', @UserTrackgID, 2, '2003-03-03 11:19:26', '2003-02-09 21:20:07', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) 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)', @UserTrackgID, 10, '2003-03-04 23:08:38', '2003-02-09 21:20:07', NULL, NULL); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) 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 !', @UserTrackgID, 15, '2003-03-03 11:19:13', '2003-02-09 21:20:07', NULL, NULL); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Admin User Can Delete)', 'CONFIG_USER_TRACKING_ADMIN_CAN_DELETE', 'true', 'Allow Record Deletion to be Active?<br/>Setting this to true will override ENTRY and SESSION purges.<br/>Default <b>true</b><br/>', @UserTrackgID, 25, '2013-11-09 11:19:26', '2013-11-09 11:19:26', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Admin User Can Delete Historical Data)', 'CONFIG_USER_TRACKING_ADMIN_CAN_DELETE_RECORDS', 'false', 'Allow Record Deletion of records older than now - the purge duration set below.<br/>This value is ignored if Admin User Can Delete is set to true.  Otherwise set this to true to allow deletion of visits.<br/>Default <b>false</b>.<br/>', @UserTrackgID, 26, '2013-11-09 11:19:26', '2013-11-09 11:19:26', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Admin User Can Delete SESSIONS)', 'CONFIG_USER_TRACKING_ADMIN_CAN_DELETE_SESSIONS', 'false', 'Allow SESSION Deletion to be Active?<br/>This setting is ignored if Admin User Can Delete is set to true.<br/>Default <b>false</b><br/>', @UserTrackgID, 27, '2013-11-09 11:19:26', '2013-11-09 11:19:26', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Admin User Can Delete IP)', 'CONFIG_USER_TRACKING_ADMIN_CAN_DELETE_IP', 'false', 'Allow Deletion of records that match the identified IP address?<br/>This setting is ignored if Admin User Can Delete is set to true.<br/>Default <b>false</b><br/>', @UserTrackgID, 28, '2013-11-09 11:19:26', '2013-11-09 11:19:26', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Purge this Number)', 'CONFIG_USER_TRACKING_PURGE_NUMBER', '3', 'What is the number associated with purging before the current date/time?<br/><br/>An example would be to choose 3 here and units associated with days to delete data greater than 3 days before today.<br/>', @UserTrackgID, 30, '2013-11-09 23:08:38', '2013-11-09 23:08:38', NULL, NULL); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Purge Units)', 'CONFIG_USER_TRACKING_PURGE_UNITS', '1440', 'Pick the units associate with the periodicity to allow purging data.<br/><br/>60) Hours<br/>1440) Days<br/>10080) Weeks<br/>43200) Months (Based on 30 days)<br/>', @UserTrackgID, 31, '2013-11-09 23:08:38', '2013-11-09 23:08:38', NULL, 'zen_cfg_select_option(array(''60'', ''1440'',''10080'',''43200''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Type of User Interaction to Record)', 'CONFIG_USER_TRACKING_TRACK_TYPE_RECORD', '1', 'Type of user tracking to record?<br/><br/>1 - All visitors.<br/>2 - Visitors views where sessions have been started.<br/>3 - All users except bots/spiders ( requires Configuration->Sessions->Prevent Spider Sessions->true)  (Don\'t know if this works with Zen-Cart versions older than 1.2.6d)<br/><br/>Related to above: If you set Force Cookie Use->true, then at the first user entry, sessions do not start!!! And in variants 2 and 3, user-tracking will not have started. In this case you lose one click and do not log the refferal. But if this user is a returning user and has an old/previous zen cookie the session started and your tracking system will collect this info. So, the result beforehand is not logged or will not be known.<br/>', @UserTrackgID, 40, '2013-11-09 11:19:26', '2013-11-09 11:19:26', NULL, 'zen_cfg_select_option(array(''1'', ''2'',''3''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) 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)', @UserTrackgID, 50, '2003-03-03 11:19:13', '2003-03-11 11:40:01', NULL, NULL); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Show Product Category when tracking product clicks)', 'ZEN_CONFIG_SHOW_USER_TRACKING_CATEGORY', 'true', 'Show Product Category when tracking product clicks', @UserTrackgID, 60, '2006-12-05 11:19:26', '2006-12-05 21:20:07', NULL, 'zen_cfg_select_option(array(''true'', ''false''),'); INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('User Tracking (Version Installed)', 'CONFIG_USER_TRACKING_VERSION', '1.5', 'Shows the version number associated with user tracking and should be updated with each upgrade', @UserTrackgID, 1000, '2013-11-10 04:19:26', '2013-11-18 04:19:26', NULL, NULL);

---

---

This fixed all my issues and everything resolved working perfectly! Thank you so much!

Quote:

---

Originally Posted by MyCoolHats

This fixed all my issues and everything resolved working perfectly! Thank you so much!

---

Whew! Glad that it worked. Wouldn't have known that there was an issue without the report. As stated in the middle of the message storm, this was a result of the formatting of the SQL statements and them not all containing the field associated with the data being inserted. I forget the proper term for the type of SQL being discussed (mysqli?) But, with the rewrite of the statements (although I didn't modify the dates to be relative to the date of execution of the statement) the new install code is now compatible with both the older and newer versions of mysql it would ppear.

Again, thank you for pointing out the issue. Remember to periodically clear your history (lthoughi am considering adding a feature to do this automatically as based on the history to maintain, but don't expect that part too soon. ) :)

Huh, wonder why no one said that the new_install_user_tracking.sql that I posted a few posts back was no good... I have a newer new_install_user_tracking.sql that includes some commentary and assistance and also has been simplified a little. I was trying to do too much with a new install before. Hopefully will have an auto installer soon. Again the below is basically for a new install only and will not be of much help otherwise.

Code:

---

//If this script has been run before, this line is likely to cause an error. Check logs folder (ZC V >=1.5.1) or cache folder (ZC V <= 1.5.0)  If this is the SQL command that caused an error, it may be removed or commented.
INSERT INTO admin_pages (page_key, language_key, main_page, menu_key, display_on_menu, sort_order) VALUES ('UserTracking', 'BOX_TOOLS_USER_TRACKING', 'FILENAME_USER_TRACKING', 'tools', 'Y', 10000);

//If this script has been run before, this line is likely to cause an error. Check logs folder (ZC V >=1.5.1) or cache folder (ZC V <= 1.5.0)  If this is the SQL command that caused an error, it may be removed or commented.
INSERT INTO configuration_group (`configuration_group_title`, `configuration_group_description`, `visible`) VALUES ('User Tracking Config', 'User Tracking', 1);

//Do not remove these SQL lines.  They are needed for further processing.
SELECT @UserTrackgID := configuration_group_id
FROM configuration_group where configuration_group_title = 'User Tracking Config';
UPDATE configuration_group SET sort_order = @UserTrackgID WHERE configuration_group_id = @UserTrackgID AND configuration_group_id != 0;

//If this script has been run before, this line is likely to cause an error. Check logs folder (ZC V >=1.5.1) or cache folder (ZC V <= 1.5.0)  If this is the SQL command that caused an error, it may be removed or commented.
INSERT INTO admin_pages (page_key, language_key, main_page, page_params, menu_key, display_on_menu, sort_order) VALUES ('UserTrackingConfig', 'BOX_TOOLS_USER_TRACKING_CONFIG', 'FILENAME_CONFIGURATION', CONCAT('gID=', @UserTrackgID), 'configuration', 'Y', @UserTrackgID);

//DROP TABLE IF EXISTS user_tracking;  //This line is not necessary on a new install as there is no expectation of another plugin
// naming the table the same.  If however, the below line is a problem, then either the above DROP TABLE should be uncommented
// or the below CREATE TABLE should be commented using double forward slashes.  This will depend on whether the table exists by running this previously or if another application has created this table.
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 ''
) ENGINE=MyISAM;

//DELETE FROM configuration where configuration_group_id = @UserTrackgID and @UserTrackgID != 0;  // This is not necessary for a new installation as this information is not already entered in the database; however, if while attempting to run this query, there may be an error at the insert below and therefore this may need to be uncommented to move forward with it.  Be advised, this will remove all options that are currently on the User Trackings configuration menu.

//If this script has been run before, one or more of the below lines are likely to cause an error. Check logs folder (ZC V >=1.5.1) or cache folder (ZC V <= 1.5.0). The line(s) below up to the SQL command that caused the error may be removed or commented out.

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES ('User Tracking Visitors', 'ZEN_CONFIG_USER_TRACKING', 'true', 'Check the Customers/Guests behaviour? (each click will be recorded)', @UserTrackgID, 1, NOW(), NULL, 'zen_cfg_select_option(array(''true'', ''false''),');
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES ('User Tracking (ADMIN)', 'ADMIN_CONFIG_USER_TRACKING', 'true', 'Check the ADMINs behaviour? (each click will be recorded)', @UserTrackgID, 2, NOW(), NULL, 'zen_cfg_select_option(array(''true'', ''false''),');
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) 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)', @UserTrackgID, 10, NOW(), NULL, NULL);
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) 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 !', @UserTrackgID, 15, NOW(), NULL, NULL);
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES ('User Tracking (Admin User Can Delete)', 'CONFIG_USER_TRACKING_ADMIN_CAN_DELETE', 'true', 'Allow Record Deletion to be Active?<br/>Setting this to true will override ENTRY and SESSION purges.<br/>Default <b>true</b><br/>', @UserTrackgID, 25, NOW(), NULL, 'zen_cfg_select_option(array(''true'', ''false''),');
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES ('User Tracking (Admin User Can Delete Historical Data)', 'CONFIG_USER_TRACKING_ADMIN_CAN_DELETE_RECORDS', 'false', 'Allow Record Deletion of records older than now - the purge duration set below.<br/>This value is ignored if Admin User Can Delete is set to true.  Otherwise set this to true to allow deletion of visits.<br/>Default <b>false</b>.<br/>', @UserTrackgID, 26, NOW(), NULL, 'zen_cfg_select_option(array(''true'', ''false''),');
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES ('User Tracking (Admin User Can Delete SESSIONS)', 'CONFIG_USER_TRACKING_ADMIN_CAN_DELETE_SESSIONS', 'false', 'Allow SESSION Deletion to be Active?<br/>This setting is ignored if Admin User Can Delete is set to true.<br/>Default <b>false</b><br/>', @UserTrackgID, 27, NOW(), NULL, 'zen_cfg_select_option(array(''true'', ''false''),');
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES ('User Tracking (Admin User Can Delete IP)', 'CONFIG_USER_TRACKING_ADMIN_CAN_DELETE_IP', 'false', 'Allow Deletion of records that match the identified IP address?<br/>This setting is ignored if Admin User Can Delete is set to true.<br/>Default <b>false</b><br/>', @UserTrackgID, 28, NOW(), NULL, 'zen_cfg_select_option(array(''true'', ''false''),');
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES ('User Tracking (Purge this Number)', 'CONFIG_USER_TRACKING_PURGE_NUMBER', '3', 'What is the number associated with purging before the current date/time?<br/><br/>An example would be to choose 3 here and units of days to delete data greater than 3 days before today.<br/>', @UserTrackgID, 30, NOW(), NULL, NULL);
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES ('User Tracking (Purge Units)', 'CONFIG_USER_TRACKING_PURGE_UNITS', '1440', 'Pick the units associate with the periodicity to allow purging data.<br/>', @UserTrackgID, 31, NOW(), NULL, 'zen_cfg_select_option(array(array(''id''=>''60'', ''text''=>''Hours''), array(''id''=>''1440'', ''text''=>''Days''),array(''id''=>''10080'', ''text''=>''Weeks''),array(''id''=>''43200'', ''text''=>''Months (Based on 30 days)'')),');
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES ('User Tracking (Type of User Interaction to Record)', 'CONFIG_USER_TRACKING_TRACK_TYPE_RECORD', '1', 'Type of user tracking to record?<br/><br/>1 - All visitors.<br/>2 - Visitors views where sessions have been started.<br/>3 - All users except bots/spiders ( requires Configuration->Sessions->Prevent Spider Sessions->true)  (Don\'t know if this works with Zen-Cart versions older than 1.2.6d)<br/><br/>Related to above: If you set Force Cookie Use->true, then at the first user entry, sessions do not start!!! And in variants 2 and 3, user-tracking will not have started. In this case you lose one click and do not log the referal. But if this user is a returning user and has an old/previous zen cookie the session started and your tracking system will collect this info. So, the result beforehand is not logged or will not be known.<br/>', @UserTrackgID, 40, NOW(), NULL, 'zen_cfg_select_option(array(''1'', ''2'',''3''),');
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) 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)', @UserTrackgID, 50, NOW(), NULL, NULL);
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES ('User Tracking (Show Product Category when tracking product clicks)', 'ZEN_CONFIG_SHOW_USER_TRACKING_CATEGORY', 'true', 'Show Product Category when tracking product clicks', @UserTrackgID, 60, NOW(), NULL, 'zen_cfg_select_option(array(''true'', ''false''),');
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES ('User Tracking (Version Installed)', 'CONFIG_USER_TRACKING_VERSION', '1.5', 'Shows the version number associated with user tracking and should be updated with each upgrade', @UserTrackgID, 1000, NOW(), NULL, 'zen_cfg_select_option(array(''CONFIG_USER_TRACKING_VERSION''),');

---

The above sql is not working in zen cart 1.5.3

Thanks.