/** * Counts the total number users (of a given cohort) who who have hit an event. * * @param event (which event are we looking for) * @param cohort sql query returning the our target users * @param instances the minimum number of events for the user to be counted (for example * you may want to pull how many users have uploaded a photo at least 5 times. * * @example $userCohort = "SELECT uid FROM log WHERE date BETWEEN '2011-05-01' AND '2011-06-11' AND event = 'first time fut use: user record created'"; * countUsersWithEvent('first time fut use: user record created', $userCohort) * * IMPORTANT: NEVER EXPOSE 'cohort' TO END-USER AS IT EXECUTES SQL DIRECTLY WITHOUT ESCAPING */ function countUsersWithEvent($event, $minInstances = '', $cohort) { $event = mysql_real_escape_string($event); $minInstances = intval($minInstances); if($minInstances) { $having = "HAVING event_instances >= $minInstances"; } else { $having = ''; } $sql = "SELECT log.uid AS users, count(log.uid) AS event_instances FROM log INNER JOIN ($cohort) AS target_users ON target_users.uid = log.uid WHERE event = '$event' GROUP BY log.uid $having"; //group by uid to ensure that only unique users returned $res = SonarStatManager::dbQuery($sql); return mysql_num_rows($res); }
/** * gets date / value array for count('log event') in that date span. * * @param string event * @param int limit * @return array ('date'=>'value'); * */ public function getStatValues($interval = 'daily', $limit = '20') { $from = ($interval == 'daily') ? 'sonar_stat_val_day' : 'sonar_stat_val_hr'; /* get stats */ $sql = "SELECT date, count FROM $from WHERE event = '" . mysql_real_escape_string($this->event) . "' ORDER BY date DESC LIMIT $limit;"; SonarStatManager::dbConnect(); $res = SonarStatManager::dbQuery($sql); $data = array(); while (list($date, $value) = mysql_fetch_array($res)) { $data[$date] = $value; } return $data; }