Initializing My Store's Statistics

[jhixson]

I'm interested in initializing(zeroing) some of the statistical data my store has collected while we've been fooling around setting it up. For example, at the bottom of the main page the footer bar says there have been over 6000 "requests" since a date mid last year. I want to zero that number and reset the date before going public. Likewise the "Best Products Viewed," "Best Products Purchased," and "Customer Orders-Total" data under Reports and even the order numbers.

I have been advised to use something called "phpmyadmin in cpanel" to make these changes, but I'm afraid I need a little more instructions than that. This tool (phpMyAdmin) looks decidedly user-unfriendly and and appears like it could REALLY mess up my store. Doesn't someone have an easier way or at least detailed instructions for the neophyte? Thanks.

[jonthewebb]

hi,

the 'safe' way to do this would be to create a backup using the tool in the oscommerce admin (tools) section. Then edit this file as follows:

1) find where it says something like:

CREATE TABLE `counter` (
`startdate` char(8) default NULL,
`counter` int(12) default NULL
) TYPE=MyISAM;

#
# Dumping data for table `counter`
#

INSERT INTO `counter` VALUES ('20030717', 1995);

change that last line to:

INSERT INTO `counter` VALUES ('20040309', 0);

that will restore the counter to 0 and the date to today.

to restore the products_viewed you will need to edit the last digit of each of the products lines, eg:

CREATE TABLE `products_description` (
`products_id` int(11) NOT NULL auto_increment,
`language_id` int(11) NOT NULL default '1',
`products_name` varchar(64) NOT NULL default '',
`products_description` text,
`products_url` varchar(255) default NULL,
`products_viewed` int(5) default '0',
PRIMARY KEY (`products_id`,`language_id`),
KEY `products_name` (`products_name`)
) TYPE=MyISAM AUTO_INCREMENT=65 ;

#
# Dumping data for table `products_description`
#

INSERT INTO `products_description` VALUES (5, 1, 'Blade Runner - Director\'s Cut', 'Regional Code: 2 (Japan, Europe, Middle East, South Africa).<br>Languages: English, Deutsch.<br>Subtitles: English, Deutsch, Spanish.<br>Audio: Dolby Surround 5.1.<br>Picture Format: 16:9 Wide-Screen.<br>Length: (approx) 112 minutes.<br>Other: Interactive Menus, Chapter Selection, Subtitles (more languages).', 'www.bladerunner.com', 8);

change the final 8 to 0 for every product. Of course then it would be easier to use phpmyadmin as you would just say:

"UPDATE products_description SET products_viewed = 0"

which would change every products_viewed to 0.


After editing the file just restore it using the backup tool again.

It really is worth learning to use phpMyAdmin, it makes life a lot easier. It is one of the best software utilities available in my opinion, it has certainly saved me countless hours work. It is very easy to understand with a small amount of patience and very basic database knowledge.

jon

[jonthewebb]

haha, the forum code changed my final 8 to a smily face!!!!! anyway you should get the idea!!!!!

[Anonymous]

jonthewebb-
I think I'm beginning to get less than ice cold, but I still need more help. You say to make "a backup." A backup of what? I went ahead and made "a backup" per your instructions but downloaded it to my HDD only, rather than leaving it on the server. What I got was a strange looking file with a strange name that only opens in IE and does not seem to be editable. So I don't know how to do your next step which was to edit this file (I think!?). Later on in your instructions you suggest I use phpmyadmin to just say: "UPDATE products_description SET products_viewed = 0." How do I "just say" that in phpmyadmin? Are there instructions for using phpmyadmin that I've missed somewhere? Thanks.

[jhixson]

jonthewebb-
The Guest message immediately above is mine. I forgot to log in before posting. Thanks.

[michael_s]

jonthewebb, you can always click the 'disable smilies' checkbox when you post, or use the 'code' tag to offset your code. Cheers.

[jonthewebb]

you should be able to open the file in a text viewer (notepad, textpad, etc), as long as you choose "no compression". The file extension should be .sql

jw

[jhixson]

jonthewebb-
OK, I'm getting it even more! I'm a Mac user but have found I can open it in Word. Thanks. Now, what about my question about how to "just say" in phpmyadmin?

[Anonymous]

mmmm I wouldn't use Word, chances are when you save it, it will add extra characters and screw it up. Download textpad (www.textpad.com I think) and use that.

About the query, you need to install and set up phpmyadmin.

then open the database you're using, go to the sql query box and type in the query

"UPDATE products_description SET products_viewed = 0"

jon