Title: Display personalized SQL request Last modified: August 20, 2016 --- # Display personalized SQL request * Resolved [unkedeuxke](https://wordpress.org/support/users/unkedeuxke/) * (@unkedeuxke) * [13 years ago](https://wordpress.org/support/topic/display-personalized-sql-request/) * 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/](http://wordpress.org/extend/plugins/wp-slimstat-shortcodes/) Viewing 8 replies - 1 through 8 (of 8 total) * Plugin Author [Jason Crouse](https://wordpress.org/support/users/coolmann/) * (@coolmann) * [13 years ago](https://wordpress.org/support/topic/display-personalized-sql-request/#post-3608578) * 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](https://wordpress.org/support/users/unkedeuxke/) * (@unkedeuxke) * [13 years ago](https://wordpress.org/support/topic/display-personalized-sql-request/#post-3608584) * OK, and how can I use it on my widget ? * Just write 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](https://wordpress.org/support/users/unkedeuxke/) * (@unkedeuxke) * [13 years ago](https://wordpress.org/support/topic/display-personalized-sql-request/#post-3608588) * I succeed with that code : * * Visiteurs en ligne : 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](https://wordpress.org/support/users/unkedeuxke/) * (@unkedeuxke) * [13 years ago](https://wordpress.org/support/topic/display-personalized-sql-request/#post-3608591) * 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](https://wordpress.org/support/users/coolmann/) * (@coolmann) * [13 years ago](https://wordpress.org/support/topic/display-personalized-sql-request/#post-3608664) * 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](https://wordpress.org/support/users/coolmann/) * (@coolmann) * [13 years ago](https://wordpress.org/support/topic/display-personalized-sql-request/#post-3608665) * 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](https://wordpress.org/support/users/unkedeuxke/) * (@unkedeuxke) * [13 years ago](https://wordpress.org/support/topic/display-personalized-sql-request/#post-3608666) * 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](https://wordpress.org/support/users/unkedeuxke/) * (@unkedeuxke) * [13 years ago](https://wordpress.org/support/topic/display-personalized-sql-request/#post-3608698) * Any idea please ? Viewing 8 replies - 1 through 8 (of 8 total) The topic ‘Display personalized SQL request’ is closed to new replies. * ![](https://s.w.org/plugins/geopattern-icon/wp-slimstat-shortcodes.svg) * [WP SlimStat Shortcodes](https://wordpress.org/plugins/wp-slimstat-shortcodes/) * [Support Threads](https://wordpress.org/support/plugin/wp-slimstat-shortcodes/) * [Active Topics](https://wordpress.org/support/plugin/wp-slimstat-shortcodes/active/) * [Unresolved Topics](https://wordpress.org/support/plugin/wp-slimstat-shortcodes/unresolved/) * [Reviews](https://wordpress.org/support/plugin/wp-slimstat-shortcodes/reviews/) * 8 replies * 2 participants * Last reply from: [unkedeuxke](https://wordpress.org/support/users/unkedeuxke/) * Last activity: [13 years ago](https://wordpress.org/support/topic/display-personalized-sql-request/#post-3608698) * Status: resolved