Database Query: COUNT(*)

[Kyttias]

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; $i< count($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; $i< count($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.