User tracking mod

[mc12345678]

The above sql is not working in zen cart 1.5.3

Thanks.

Please elaborate. The ZC version does not affect the operability of a sql statement. Perhaps if a table doesn't exist or aa specific field doesn't exist any longer, but there hasn't been anything so drastic in the change of ZC versions in the 1.5.x series.

How was the above sql attempted to be applied, what was the system response, what error logs were generated in the logs folder?

[jjone]

I executed uninstall sql and then i copied your above sql for new install.

This is the error i got, WARNING: An Error occurred, please refresh the page and try again.

[mc12345678]

I executed uninstall sql and then i copied your above sql for new install.

This is the error i got, WARNING: An Error occurred, please refresh the page and try again.

And as a result of that error, an error log would be generated. Please provide the resulting error log that can be found in the logs folder for ZC >= 1.5.1 or in the cache folder for ZC <=1.5.0 if the sql was placed in the sql patches area of the ZC admin.

[mc12345678]

And as a result of that error, an error log would be generated. Please provide the resulting error log that can be found in the logs folder for ZC >= 1.5.1 or in the cache folder for ZC <=1.5.0 if the sql was placed in the sql patches area of the ZC admin.

I have tested it again this time in ZC 1.5.3. The SQL statements themselves are not an issue, but the comments that were added using // are... Haven't identified what if any acceptable commenting is available. So much for trying to provide assistive comments to make the SQL easier to troubleshoot. I just need to package this to do autoinstallation and do away with the manual install and complications that can occur from the SQL part..

Soooo.. For those that have joined us on installing this user_tracking modification, until a new version is posted, the above new_install sql found at: http://www.zen-cart.com/showthread.p...83#post1254683
should omit any line that begins with // as it will generate an error at least in ZC 1.5.3...

[gilby]

# For single line comments

-- Also for single line, must be followed by space/control character

/*
C-style multiline comment
*/

[mc12345678]

# For single line comments

-- Also for single line, must be followed by space/control character

/*
C-style multiline comment
*/

Gilby,

Thank you for the sliver of glimmering light to my day today.

Take three...

Below is the new_install.sql SQL that should be used modified from the downloaded package. It includes comments (correctly formatted thanks to Gilby) to help if/when issues arise while running it.

# 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;  
/* The above 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;  
/* The above line 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''),');

[jjone]

perfect! i executed uninstall sql first and then executed your above sql again. It worked !. Thank you.

[CaptainFacePalm]

Hi I have been trying to get this plugin to work I installed in but could not get it to work with the new SQL so I went to un install and put the uninstall SQL and got the following error
1146 Table 'smlhygie_admin.admin_pages' doesn't exist
in:
[DELETE FROM admin_pages WHERE page_key = 'UserTracking';]
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.

[mc12345678]

Hi I have been trying to get this plugin to work I installed in but could not get it to work with the new SQL so I went to un install and put the uninstall SQL and got the following error
1146 Table 'smlhygie_admin.admin_pages' doesn't exist
in:
[DELETE FROM admin_pages WHERE page_key = 'UserTracking';]
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.

The sqlis written to be run from the ZC admin through sql install interface. If run from myphpadmin, will need to include the table prefix if thereis one.

[mc12345678]

If that doesn't correct the problem, please post more information about the problem(s) identified and the method(s) taken to cause the problem(s).