| Kyttias |
01-18-2015 09:07 PM |
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; $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. :happycbig:
|
