Mysidia Adoptables Support Forum  

Home Community Mys-Script Creative Off-Topic
Go Back   Mysidia Adoptables Support Forum > Mysidia Adoptables > Questions and Supports

Notices

Reply
 
Thread Tools Display Modes
  #1  
Old 01-18-2015, 09:07 PM
Kyttias's Avatar
Kyttias Kyttias is offline
Premium Member
 
Join Date: Jan 2014
Posts: 857
Gender: Unknown/Other
Credits: 86,973
Kyttias is on a distinguished road
Wink Database Query: COUNT(*)

How can I perform:
SELECT COUNT(*) FROM vote_voters
with $mysidia->db->select()??


edit (answer):
PHP Code:
$stat_totalclicks $mysidia->db->select("vote_voters")->rowCount(); 


Then perhaps, as above, but a count of all rows in vote_voters between two variable dates, such as:
SELECT COUNT(*) FROM vote_voters WHERE date >= '{$date1}' and date < '{$date2}'
(Mental notes to self: format needs to be 2015-01-15, that is, (Y-m-d).)


edit (answer):
PHP Code:
$today date("Y-m-d");
$weekago date("Y-m-d"strtotime("-7 days"));
$monthago date("Y-m-d"strtotime("-1 month"));
$stat_weeklyclicks $mysidia->db->select("vote_voters", array(), "date >= '{$weekago}' and date <= '{$today}'")->rowCount();
$stat_monthlyclicks $mysidia->db->select("vote_voters", array(), "date >= '{$monthago}' and date <= '{$today}'")->rowCount(); 



Next, both of the last two above (all time and between certain dates), but also taking into account:
WHERE username = '{$user}'
(Mental notes to self, $user = $mysidia->input->get("user") on profile pages.)


edit (answer):
PHP Code:
$thisuser $mysidia->input->get("user");
$stat_weeklyclicks $mysidia->db->select("vote_voters", array(), "date >= '{$weekago}' and date <= '{$today}' and username = '{$thisuser}'")->rowCount();
    
$stat_monthlyclicks $mysidia->db->select("vote_voters", array(), "date >= '{$monthago}' and date <= '{$today}' and username = '{$thisuser}'")->rowCount(); 


Would it be possible to pull a list of the names of the top 10 users (this week/month)?? (Though this is problematic as uid is not currently being stored in vote_voters, so I can't use it as a secondary order checker, and alphabetic just won't cut it, as older members should have precedence in score charts).
edit (answered by HoF over aim, huge thanks!)
PHP Code:
/* Top 10 Users With Most Interactions This WEEK */
$top10Users_weekly $mysidia->db->query("SELECT username, COUNT(username) AS interactions FROM adopts_vote_voters WHERE date >= '{$weekago}' and date <= '{$today}' GROUP BY username ORDER BY COUNT(*) DESC LIMIT 10")->fetchAll();
        for(
$i 0$icount($top10Users_weekly); $i++){ 
            
$order_w $i 1
if (
$top10Users_weekly[$i]['username'] != NULL){
            
$document->add(new Comment("No.{$order_w}: <a href='../../profile/view/{$top10Users_weekly[$i]['username']}'>{$top10Users_weekly[$i]['username']} ({$top10Users_weekly[$i]['interactions']})</a>")); 
}
        }
/* Top 10 Users With Most Interactions This MONTH */
$top10Users_monthly $mysidia->db->query("SELECT username, COUNT(username) AS interactions FROM adopts_vote_voters WHERE date >= '{$monthago}' and date <= '{$today}' GROUP BY username ORDER BY COUNT(*) DESC LIMIT 10")->fetchAll();
        for(
$i 0$icount($top10Users_monthly); $i++){ 
            
$order_m $i 1
if (
$top10Users_monthly[$i]['username'] != NULL){
            
$document->add(new Comment("No.{$order_m}: <a href='../../profile/view/{$top10Users_monthly[$i]['username']}'>{$top10Users_monthly[$i]['username']} ({$top10Users_monthly[$i]['interactions']})</a>")); 
}
        } 


Can I also get the most common pet type in owned_adoptables?
edit (answered by HoF over aim): see below



In the end I will want to set nine variables:
  • community interactions of all time = $mysidia->db->select("vote_voters")->rowCount();
  • community interactions last month = $mysidia->db->select("vote_voters", array(), "date >= '{$monthago}' and date <= '{$today}'")->rowCount();
  • community interactions last week = $mysidia->db->select("vote_voters", array(), "date >= '{$weekago}' and date <= '{$today}'")->rowCount();
  • user interactions of all time = $mysidia->db->select("vote_voters", array(), "username = '{$thisuser}'")->rowCount();
  • user interactions last month = $mysidia->db->select("vote_voters", array(), "date >= '{$monthago}' and date <= '{$today}' and username = '{$thisuser}'")->rowCount();
  • user interactions last week = $mysidia->db->select("vote_voters", array(), "date >= '{$weekago}' and date <= '{$today}' and username = '{$thisuser}'")->rowCount();
  • top 10 most active users last week = see question above
  • top 10 most active users last month = see question above
  • most owned pet = $mysidia->db->query("SELECT type AS favpet FROM adopts_owned_adoptables GROUP BY type ORDER BY COUNT(*) DESC LIMIT 1")->fetchColumn();

Later I may extend this data to 'factions', to create a competitive atmosphere, not just against other players, but other faction groups. The most active factions getting rewarded regularly, of course.
__________________
Please do not contact me directly outside of Mysidia.
I also cannot troubleshoot code more than two years old - I legit don't remember it.

Last edited by Kyttias; 01-19-2015 at 03:24 AM.
Reply With Quote
  #2  
Old 01-18-2015, 11:26 PM
Hall of Famer's Avatar
Hall of Famer Hall of Famer is offline
Administrator, Lead Coder
 
Join Date: Dec 2008
Location: South Brunswick
Posts: 4,448
Gender: Male
Credits: 327,501
Hall of Famer is on a distinguished road
Default

Quote:
Originally Posted by Kyttias View Post

top 10 most active users last week = ???

top 10 most active users last month = ???
most owned pet = ???
Well in these cases you will need to make use of some complex MYSQL syntax. One hint I can give to you is that Mysidia's Database class inherits from PDO, so whatever syntax works with PDO will work here too. You can just execute a query using SQL syntax like this, for complex features not provided in the current database class yet. For instance, to fetch the user with most money, you can just run this query below:

PHP Code:
$mysidia->db->query("SELECT  MAX(money) FROM {$prefix}users"); 
In your case, this below query will do:
PHP Code:
$mysidia->db->query("SELECT type, COUNT(type) AS favpet FROM {$prefix}owned_adoptables GROUP BY type LIMIT 1"); 
Note you will need to manually add the $prefix to the database table name, which is cumbersome and error-prone. It is one of the primary reasons that Mysidia uses query builders nowadays(so you are free of the concern of prefixing database tables, the script does it for you behind the scene). But of course, the query builder has limited functionality so far, so to execute complex SQL queries you have to fall back to the plain old SQL approach.
__________________


Mysidia Adoptables, a free and ever-improving script for aspiring adoptables/pets site.
Reply With Quote
  #3  
Old 01-19-2015, 03:26 AM
Kyttias's Avatar
Kyttias Kyttias is offline
Premium Member
 
Join Date: Jan 2014
Posts: 857
Gender: Unknown/Other
Credits: 86,973
Kyttias is on a distinguished road
Default

Thanks for all the help over aim, HoF! I really appreciate it and I learned a lot of new things.

For anyone interested in more things to modify their stats page with, this will list the 10 newest users:

PHP Code:
/* Newest Users */
        
$document->add(new Comment("<h4>Newest Users</h4>"FALSE));
        
$newest_ten_users $mysidia->db->select("users", array("username"), "1 ORDER BY username DESC LIMIT 10");
        while(
$username $newest_ten_users->fetchColumn()){            
            
$n_order++;     
            
$document->add(new Comment("<b>{$n_order}.</b> <a href='../../profile/view/{$username}'>{$username}</a>"));
        } 
__________________
Please do not contact me directly outside of Mysidia.
I also cannot troubleshoot code more than two years old - I legit don't remember it.

Last edited by Kyttias; 01-19-2015 at 04:32 AM.
Reply With Quote
  #4  
Old 01-19-2015, 03:30 AM
MikiHeart's Avatar
MikiHeart MikiHeart is offline
Premium Member
 
Join Date: Apr 2009
Posts: 187
Gender: Female
Credits: 20,330
MikiHeart
Default

The stats view page?
Reply With Quote
  #5  
Old 01-19-2015, 03:34 AM
Kyttias's Avatar
Kyttias Kyttias is offline
Premium Member
 
Join Date: Jan 2014
Posts: 857
Gender: Unknown/Other
Credits: 86,973
Kyttias is on a distinguished road
Default

Yes, in statsview.php. ^^
__________________
Please do not contact me directly outside of Mysidia.
I also cannot troubleshoot code more than two years old - I legit don't remember it.
Reply With Quote
  #6  
Old 01-19-2015, 03:57 AM
MikiHeart's Avatar
MikiHeart MikiHeart is offline
Premium Member
 
Join Date: Apr 2009
Posts: 187
Gender: Female
Credits: 20,330
MikiHeart
Default

Awesome. Thank you ^^
Reply With Quote
Reply

Tags
database, database queries, mysql, stats

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
CSS Query .-. ChibiMaestro Templates and Themes 2 12-08-2012 05:51 AM
Delete query deletes from all usesrs SilverDragonTears Questions and Supports 8 05-14-2012 07:49 PM
Having a problem running a query !Alive Questions and Supports 8 03-11-2012 02:39 PM
Reward Message Character Count - answered, thank you! we are halloween Questions and Supports 3 09-26-2010 07:43 AM
Join query for optimization? Arianna Questions and Supports 2 04-09-2010 12:32 PM


All times are GMT -5. The time now is 02:22 PM.

Currently Active Users: 461 (0 members and 461 guests)
Threads: 4,080, Posts: 32,024, Members: 2,016
Welcome to our newest members, jolob.
BETA





What's New?

What's Hot?

What's Popular?


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
vBCommerce I v2.0.0 Gold ©2010, PixelFX Studios
vBCredits I v2.0.0 Gold ©2010, PixelFX Studios
Emoticons by darkmoon3636