Display personalized SQL request

Resolved unkedeuxke
(@unkedeuxke)

13 years ago

Hello,

I created those requests to count visitors (group by 10 minutes ranges) of the current day, of the last day, of the 7 last days, of the 30 last days, like that :

// Visiteurs du jour
SELECT COUNT(*)
FROM
(SELECT t.ip, LEFT(DATE_FORMAT(FROM_UNIXTIME(t.dt),”%Y%m%d%H%i” ), 11) AS intervalle
FROM wp_slim_stats t
GROUP BY intervalle) r
WHERE r.intervalle > LEFT(DATE_FORMAT(NOW(),”%Y%m%d%H%i” ), 8)

// Visiteurs de la veille
SELECT COUNT(*)
FROM
(SELECT t.ip, LEFT(DATE_FORMAT(FROM_UNIXTIME(t.dt),”%Y%m%d%H%i” ), 11) AS intervalle
FROM wp_slim_stats t
GROUP BY intervalle) r
WHERE r.intervalle > LEFT(DATE_FORMAT(NOW() – INTERVAL 1 DAY,”%Y%m%d%H%i” ), 8) AND r.intervalle < LEFT(DATE_FORMAT(NOW(),”%Y%m%d%H%i” ), 8)

// Visiteurs des 7 derniers jours
SELECT COUNT(*)
FROM
(SELECT t.ip, LEFT(DATE_FORMAT(FROM_UNIXTIME(t.dt),”%Y%m%d%H%i” ), 11) AS intervalle
FROM wp_slim_stats t
GROUP BY intervalle) r
WHERE r.intervalle > LEFT(DATE_FORMAT(NOW() – INTERVAL 7 DAY,”%Y%m%d%H%i” ), 8) AND r.intervalle < LEFT(DATE_FORMAT(NOW(),”%Y%m%d%H%i” ), 8)

// Visiteurs des 30 derniers jours
SELECT COUNT(*)
FROM
(SELECT t.ip, LEFT(DATE_FORMAT(FROM_UNIXTIME(t.dt),”%Y%m%d%H%i” ), 11) AS intervalle
FROM wp_slim_stats t
GROUP BY intervalle) r
WHERE r.intervalle > LEFT(DATE_FORMAT(NOW() – INTERVAL 30 DAY,”%Y%m%d%H%i” ), 8) AND r.intervalle < LEFT(DATE_FORMAT(NOW(),”%Y%m%d%H%i” ), 8)

// Visiteurs total
SELECT COUNT(*)
FROM
(SELECT t.ip, LEFT(DATE_FORMAT(FROM_UNIXTIME(t.dt),”%Y%m%d%H%i” ), 11) AS intervalle
FROM wp_slim_stats t
GROUP BY intervalle) r

My question : how can I display the results on my page by using shortcodes (so use personnalized SQL code) ?
I see something like : “[slimstat f=’custom’ filterby=’MYSQLREQUEST’]” but it does’nt work…

Thanks a lot

http://wordpress.org/extend/plugins/wp-slimstat-shortcodes/

Viewing 8 replies - 1 through 8 (of 8 total)

Plugin Author Jason Crouse
(@coolmann)

13 years ago

You may want to use WP SlimStat’s API (documentation is coming soon, I promise) to be forward compatible with possible changes to the db structure. For example, to get the number of visitors in the last 30 seconds, you can just use:

wp_slimstat_db::count_records("t1.visit_id > 0 AND t1.dt > UNIX_TIMESTAMP()-300", 't1.id', false)

Where
– the first parameter is the ‘type’ of pageviews you want to count (in this case only humans, who have visit_id > 0)
– the second param is the group by clause

Or you can use the corresponding shortcode to do the same.

Thread Starter unkedeuxke
(@unkedeuxke)

13 years ago

OK, and how can I use it on my widget ?

Just write <?php wp_slimstat_db::count_records(“t1.visit_id > 0 AND t1.dt > UNIX_TIMESTAMP()-300”, ‘t1.id’, false); ?> ?

Because it doesn’t work… Thanks to help me with an example 🙂

Thread Starter unkedeuxke
(@unkedeuxke)

13 years ago

I succeed with that code :

<?php
require_once(WP_PLUGIN_DIR.’/wp-slimstat/admin/view/wp-slimstat-db.php’);
wp_slimstat_db::init();
?>

Visiteurs en ligne : <?php echo wp_slimstat_db::count_records(“t1.visit_id > 0 AND t1.dt > UNIX_TIMESTAMP()-600”, ‘t1.id’, false); ?>

But the problem is that at each refresh of the page, the number increases of 1. It’s not a 10 last minutes visitors counter :/

Thread Starter unkedeuxke
(@unkedeuxke)

13 years ago

Another element to be careful of :
If a person is on the website for 18 minutes, I want to count as 2 visits, because 1 visit is considered as 10 minutes time

Plugin Author Jason Crouse
(@coolmann)

13 years ago

I think I’m not understanding what is a “10 last minute visitor” 🙁 I thought you needed the number of visitors in the last 10 minutes…

Plugin Author Jason Crouse
(@coolmann)

13 years ago

You should start by changing the session duration in your settings from 30 to 10 minutes… And then count how many different visit_id you have in a given timeframe, if I understand correctly what you need 🙂

In the meanwhile a vote for my plugin would be a nice way to say thank you!

Thread Starter unkedeuxke
(@unkedeuxke)

13 years ago

I just want :
– the visitors currently on the website (so those in the last 10 minutes)
– the visitors who were on the website on the last day (so if somebody visited the website 3 times yesterday it’s 3 visits, and if somebody stayed 30 minutes it’s counted as 3 visits too)

Is it clearer ? sorry i’m french ^^

Thread Starter unkedeuxke
(@unkedeuxke)

13 years ago

Any idea please ?

Viewing 8 replies - 1 through 8 (of 8 total)

The topic ‘Display personalized SQL request’ is closed to new replies.