Implemented MySQL queries to re-create stats table

Status
Not open for further replies.

Rodolfo

Chevereto Developer
Chevereto Staff
If for some reason your stats are not accurate or displaying wrong data, run the following queries to re-create the stats table:

Code:
TRUNCATE TABLE `%table_prefix%stats`;

INSERT INTO `%table_prefix%stats` (stat_id, stat_date_gmt, stat_type) VALUES ("1", NULL, "total") ON DUPLICATE KEY UPDATE stat_type=stat_type;

UPDATE `%table_prefix%stats` SET
stat_images = (SELECT IFNULL(COUNT(*),0) FROM `%table_prefix%images`),
stat_albums = (SELECT IFNULL(COUNT(*),0) FROM `%table_prefix%albums`),
stat_users = (SELECT IFNULL(COUNT(*),0) FROM `%table_prefix%users`),
stat_image_views = (SELECT IFNULL(SUM(image_views),0) FROM `%table_prefix%images`),
stat_disk_used = (SELECT IFNULL(SUM(image_size) + SUM(image_thumb_size) + SUM(image_medium_size),0) FROM `%table_prefix%images`)
WHERE stat_type = "total";

INSERT INTO `%table_prefix%stats` (stat_type, stat_date_gmt, stat_images, stat_image_views, stat_disk_used)
SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_images, sb.stat_image_views, sb.stat_disk_used
FROM (SELECT "date" AS stat_type, DATE(image_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_images, SUM(image_views) AS stat_image_views, SUM(image_size + image_thumb_size + image_medium_size) AS stat_disk_used FROM `%table_prefix%images` GROUP BY DATE(image_date_gmt)) AS sb
ON DUPLICATE KEY UPDATE stat_images = sb.stat_images;

INSERT INTO `%table_prefix%stats` (stat_type, stat_date_gmt, stat_users)
SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_users
FROM (SELECT "date" AS stat_type, DATE(user_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_users FROM `%table_prefix%users` GROUP BY DATE(user_date_gmt)) AS sb
ON DUPLICATE KEY UPDATE stat_users = sb.stat_users;

INSERT INTO `%table_prefix%stats` (stat_type, stat_date_gmt, stat_albums)
SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_albums
FROM (SELECT "date" AS stat_type, DATE(album_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_albums FROM `%table_prefix%albums` GROUP BY DATE(album_date_gmt)) AS sb
ON DUPLICATE KEY UPDATE stat_albums = sb.stat_albums;

UPDATE `%table_prefix%users` SET user_content_views = COALESCE((SELECT SUM(image_views) FROM `%table_prefix%images` WHERE image_user_id = user_id GROUP BY user_id), "0");
Note: You need to change %table_prefix% with the table prefix that matches your database.
 

dale2015

Network license
License owner
looks like this number is cached or not updated
or fix didn't worked out
cuz now everything else showing 0
 

dale2015

Network license
License owner
nevermind
i fixed..

first statement will not work correctly on the live server
this one: INSERT INTO `chv_stats` (stat_id, stat_date_gmt, stat_type) VALUES (1, NULL, 'total') ON DUPLICATE KEY UPDATE stat_type=stat_type;
u ignoring the fact that users has live databases
 

Rodolfo

Chevereto Developer
Chevereto Staff
Guys, at least give me the output returned by the queries. I'm not a wizard.

I tested this one in two websites and it worked fine.
 
Status
Not open for further replies.
Top