Database Query: COUNT(*)

[Kyttias]

How can I perform:

SELECT COUNT(*) FROM vote_voters​

with $mysidia->db->select()??

edit (answer):

$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):

$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):

$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!)

/* 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.

 

[Hall of Famer]

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:

$mysidia->db->query("SELECT  MAX(money) FROM {$prefix}users");

In your case, this below query will do:

$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.

 

[Kyttias]

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:

/* 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>"));
		}

 

[MikiHeart]

The stats view page?

 

[Kyttias]

Yes, in statsview.php. ^^

 

[MikiHeart]

Awesome. Thank you ^^