public function gc($max) { $q = "DELETE FROM #prefix#sessions WHERE updated < DATE_SUB(NOW(), INTERVAL :max SECOND)"; $vars = array(':max' => $max); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); return true; }
public function insertCompanyDetails($branch_data) { $ret = $this->makeInsertQueryArray($branch_data); $q = "INSERT INTO #prefix#user SET " . $ret['q']; $q .= ", added_date=NOW();"; if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $ret['vars']); return $this->getUpdateCount($ps); }
public function updateMapNotification($id) { $q = "UPDATE #prefix#map_notify SET read_at=NOW(), seen=1 "; $q .= "WHERE id=:id;"; $vars = array(':id' => $id); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); $result = $this->getUpdateCount($ps); return $result > 0; }
public function getRecentClickStats(Instance $instance, $limit = 10) { $q = "SELECT p.post_text, l.expanded_url, ls.short_url, ls.click_count "; $q .= "FROM #prefix#links_short ls INNER JOIN #prefix#links l "; $q .= "ON l.id = ls.link_id INNER JOIN #prefix#posts p ON p.id = l.post_key "; $q .= "WHERE p.author_username=:author_username AND p.network=:network "; $q .= "AND ls.click_count > 0 AND p.in_retweet_of_post_id IS NULL "; $q .= "GROUP BY short_url ORDER BY p.pub_date DESC LIMIT :limit"; $vars = array(':author_username' => $instance->network_username, ':network' => $instance->network, ':limit' => (int) $limit); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); return $this->getDataRowsAsArrays($ps); }
public function getLinkByUrl($url) { $q = "SELECT l.* "; $q .= "FROM #prefix#links AS l "; $q .= "WHERE l.url=:url "; $vars = array(':url' => $url); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); return $this->getDataRowAsObject($ps, "Link"); }
public function setTimezone($email, $timezone) { $q = "UPDATE #prefix#owners\n SET timezone=:timezone\n WHERE email=:email"; if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $stmt = $this->execute($q, array(':timezone' => $timezone, ':email' => $email)); return $this->getUpdateCount($stmt); }
public function updateUsername($id, $network_username) { $q = "UPDATE " . $this->getTableName() . " SET network_username = :network_username WHERE id = :id LIMIT 1"; $vars = array(':id' => $id, ':network_username' => $network_username); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); return $this->getUpdateCount($ps); }
public function getOAuthTokens($id) { $q = "SELECT\n oauth_access_token, oauth_access_token_secret, auth_error\n FROM\n #prefix#owner_instances\n WHERE\n instance_id = :instance_id ORDER BY id ASC LIMIT 1"; if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $stmt = $this->execute($q, array(':instance_id' => $id)); $tokens = $this->getDataRowAsArray($stmt); return $tokens; }
/** * Get all the favorited posts of a user. * @TODO Use $order_by parameter to customize sort order. * @param int $author_user_id * @param str $network * @param int $count * @param str $order_by * @param int $page * @param bool $iterator Whether or not to return an iterator * @returns array Post objects */ private function getAllFavoritedPostsForUserID($author_user_id, $network, $count, $order_by = "pub_date", $page = 1, $iterator = false) { // $direction = $direction=="DESC" ? "DESC": "ASC"; $start_on_record = ($page - 1) * $count; // order-by information currently hardwired; this will probably change // if ( !in_array($order_by, $this->REQUIRED_FIELDS) && !in_array($orderaa_by, $this->OPTIONAL_FIELDS )) { // $order_by="pub_date"; // } $q = "SELECT p.*, pub_date - interval #gmt_offset# hour AS adj_pub_date, "; //TODO: Store favlike_count_cache during Twitter crawl so we don't do this dynamic GROUP BY fakeout $q .= "count(*) AS favlike_count_cache "; $q .= "FROM (#prefix#posts p INNER JOIN #prefix#favorites f on f.post_id = p.post_id) "; $q .= "WHERE p.author_user_id = :author_user_id AND p.network = :network "; $q .= "GROUP BY p.post_text ORDER BY YEARWEEK(p.pub_date) DESC, favlike_count_cache DESC, p.pub_date DESC "; $q .= "LIMIT :start_on_record, :limit"; $vars = array(':author_user_id' => (string) $author_user_id, ':network' => $network, ':limit' => $count, ':start_on_record' => (int) $start_on_record); $ps = $this->execute($q, $vars); if ($iterator) { return new PostIterator($ps); } $all_post_rows = $this->getDataRowsAsArrays($ps); $posts = array(); if ($all_post_rows) { $post_keys_array = array(); foreach ($all_post_rows as $row) { $post_keys_array[] = $row['id']; } // Get links $q = "SELECT * FROM #prefix#links WHERE post_key in (" . implode(',', $post_keys_array) . ")"; if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q); $all_link_rows = $this->getDataRowsAsArrays($ps); // Combine posts and links $posts = array(); foreach ($all_post_rows as $post_row) { $post = new Post($post_row); foreach ($all_link_rows as $link_row) { if ($link_row['post_key'] == $post->id) { $post->addLink(new Link($link_row)); } } $posts[] = $post; } } return $posts; }
public function searchFollowers(array $keywords, $network, $user_id, $page_number = 1, $page_count = 20) { //parse advanced operators $name_keywords = array(); $description_keywords = array(); foreach ($keywords as $keyword) { if (substr($keyword, 0, strlen('name:')) == 'name:') { $name_keywords[] = substr($keyword, strlen('name:'), strlen($keyword)); } else { $description_keywords[] = $keyword; } } $vars = array(':user_id' => (string) $user_id, ':network' => $network); $q = "SELECT u.*, " . $this->getAverageTweetCount() . " "; $q .= "FROM #prefix#users u "; $q .= "INNER JOIN #prefix#follows f ON f.follower_id = u.user_id AND f.network = u.network "; $q .= "WHERE f.user_id=:user_id AND u.network=:network AND ("; if (count($name_keywords) > 0 && count($description_keywords) > 0) { $q .= "("; $counter = 0; foreach ($description_keywords as $keyword) { $q .= " u.description LIKE :keyword_d" . $counter . " "; if ($keyword != end($description_keywords)) { $q .= "AND"; } $counter++; } $q .= ") AND ( "; $counter = 0; foreach ($name_keywords as $keyword) { $q .= " u.full_name LIKE :keyword_n" . $counter . " "; if ($keyword != end($name_keywords)) { $q .= "AND"; } $counter++; } $q .= ")) "; $counter = 0; foreach ($description_keywords as $keyword) { $vars[':keyword_d' . $counter] = '%' . $keyword . '%'; $counter++; } $counter = 0; foreach ($name_keywords as $keyword) { $vars[':keyword_n' . $counter] = '%' . $keyword . '%'; $counter++; } } elseif (count($name_keywords) > 0) { $counter = 0; foreach ($name_keywords as $keyword) { $q .= " u.full_name LIKE :keyword_n" . $counter . " "; if ($keyword != end($name_keywords)) { $q .= "AND"; } $counter++; } $q .= ") "; $counter = 0; foreach ($name_keywords as $keyword) { $vars[':keyword_n' . $counter] = '%' . $keyword . '%'; $counter++; } } elseif (count($description_keywords) > 0) { $counter = 0; foreach ($description_keywords as $keyword) { $q .= " u.description LIKE :keyword_d" . $counter . " "; if ($keyword != end($description_keywords)) { $q .= "AND"; } $counter++; } $q .= ") "; $counter = 0; foreach ($description_keywords as $keyword) { $vars[':keyword_d' . $counter] = '%' . $keyword . '%'; $counter++; } } $q .= "ORDER BY first_seen DESC "; if ($page_count > 0) { $q .= "LIMIT :start_on_record, :limit;"; } else { $q .= ';'; } if ($page_count > 0) { $vars[':limit'] = (int) $page_count; $vars[':start_on_record'] = (int) $start_on_record; } if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); return $this->getDataRowsAsObjects($ps, 'User'); }
public function getHistory($network_user_id, $network, $units, $limit = 10, $before_date = null) { if ($before_date == date('Y-m-d')) { $before_date = null; } if ($units != "DAY" && $units != 'WEEK' && $units != 'MONTH') { $units = 'DAY'; } if ($units == 'DAY') { $group_by = 'fc.date'; $date_format = "DATE_FORMAT(date, '%m/%d/%Y')"; } else { if ($units == 'WEEK') { $group_by = 'YEAR(fc.date), WEEK(fc.date)'; $date_format = "DATE_FORMAT(date, '%m/%e')"; } else { if ($units == 'MONTH') { $group_by = 'YEAR(fc.date), MONTH(fc.date)'; $date_format = "DATE_FORMAT(date,'%m/01/%Y')"; } } } $vars = array(':network_user_id' => (string) $network_user_id, ':network' => $network, ':limit' => (int) $limit); $q = "SELECT network_user_id, network, count, date, full_date FROM "; $q .= "(SELECT network_user_id, network, count, " . $date_format . " as date, date as full_date "; $q .= "FROM #prefix#follower_count AS fc "; $q .= "WHERE fc.network_user_id = :network_user_id AND fc.network=:network "; if ($before_date != null) { $q .= "AND date <= :before_date "; $vars[':before_date'] = $before_date; } $q .= "GROUP BY " . $group_by . " ORDER BY full_date DESC LIMIT :limit ) as history_counts "; $q .= "ORDER BY history_counts.full_date ASC"; if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); $history_rows = $this->getDataRowsAsArrays($ps); $resultset = array(); switch ($network) { case 'facebook': $follower_description = 'Friends'; break; case 'facebook page': $follower_description = 'Fans'; break; case 'twitter': default: $follower_description = 'Followers'; break; } foreach ($history_rows as $row) { $timestamp = strtotime($row['full_date']); $resultset[] = array('c' => array(array('v' => sprintf('new Date(%d,%d,%d)', date('Y', $timestamp), date('n', $timestamp) - 1, date('j', $timestamp)), 'f' => $row['date']), array('v' => intval($row['count'])))); } $metadata = array(array('type' => 'date', 'label' => 'Date'), array('type' => 'number', 'label' => $follower_description)); $vis_data = json_encode(array('rows' => $resultset, 'cols' => $metadata)); // Google Chart docs say that a string of the form "Date(Y,m,d)" should // work, but chrome throws an error if we don't use an actual Date // object. $vis_data = preg_replace('/"(new Date[^"]+)"/', '$1', $vis_data); if (sizeof($history_rows) > 1) { //break down rows into a simpler date=>count assoc array $simplified_history = array(); foreach ($history_rows as $history_row) { $simplified_history[$history_row["date"]] = $history_row["count"]; } $trend = false; if (sizeof($history_rows) == $limit) { //we have a complete data set //calculate the trend $first_follower_count = reset($simplified_history); $last_follower_count = end($simplified_history); $trend = ($last_follower_count - $first_follower_count) / sizeof($simplified_history); $trend = intval(round($trend)); //complete data set $history = $simplified_history; } else { //there are dates with missing data //set up an array of all the dates to show in the chart $dates_to_display = array(); $format = 'n/j'; $date = date($format); $i = $limit; while ($i > 0) { if ($units == "DAY") { $format = 'm/d/Y'; $date_ago = date($format, strtotime('-' . $i . ' ' . $units . $date)); } else { if ($units == "WEEK") { if ($i == $limit) { $last_saturday = Utils::getLastSaturday(); } $date_ago = date($format, strtotime('-' . $i . ' ' . $units . $last_saturday)); } else { $first_day_of_this_month = date('n/1'); $format = 'm/d/Y'; $date_ago = date($format, strtotime('-' . $i . ' ' . $units . $first_day_of_this_month)); } } $dates_to_display[$date_ago] = "no data"; $i--; } //merge the data we do have with the dates we want $history = array_merge($dates_to_display, $simplified_history); //cut down oversized array if (sizeof($history) > $limit) { $history = array_slice($history, sizeof($history) - $limit); } if ($units == "DAY") { ksort($history); } } $history = $simplified_history; $milestone = Utils::predictNextMilestoneDate(intval($history_rows[sizeof($history_rows) - 1]['count']), $trend); //If $before_date set, add difference between then and now to how long it will take if (isset($before_date)) { if ($units == 'DAY') { $current_day_of_year = date('z'); $before_date_day_of_year = date('z', strtotime($before_date)); if (date('Y') == date('Y', strtotime($before_date))) { $difference = $current_day_of_year - $before_date_day_of_year; if ($milestone['will_take'] > $difference) { $milestone['will_take'] = $milestone['will_take'] + $difference; } } } elseif ($units == 'WEEK') { $current_week_of_year = date('W'); $before_date_week_of_year = date('W', strtotime($before_date)); if (date('Y') == date('Y', strtotime($before_date))) { $difference = $current_week_of_year - $before_date_week_of_year; if ($milestone['will_take'] > $difference) { $milestone['will_take'] = $milestone['will_take'] + $difference; } } } elseif ($units == 'MONTH') { $current_month_of_year = date('n'); $before_date_month_of_year = date('n', strtotime($before_date)); if (date('Y') == date('Y', strtotime($before_date))) { $difference = $current_month_of_year - $before_date_month_of_year; if ($milestone['will_take'] > $difference) { $milestone['will_take'] = $milestone['will_take'] + $difference; } } } } if (isset($milestone)) { $milestone['units_of_time'] = $units; } //only set milestone if it's within 10 to avoid "954 weeks until you reach 1000 followers" messaging if ($milestone['will_take'] > 50) { $milestone = null; } } else { $history = false; $trend = false; $milestone = false; } return array('history' => $history, 'trend' => $trend, 'milestone' => $milestone, 'vis_data' => $vis_data); }
public function resetID() { /** * Modified this ALTER to TRUNCATE due to not being able to reset auto_increment to 1. As per: * http://dev.mysql.com/doc/refman/5.6/en/alter-table.html * "You cannot reset the counter to a value less than or equal to to the value that is currently in use. For both * InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT * column, the value is reset to the current maximum AUTO_INCREMENT column value plus one." */ //$q = "ALTER TABLE #prefix#stream_data auto_increment = 1"; $q = "TRUNCATE TABLE #prefix#stream_data"; if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q); }
public function deleteUsersByHashtagId($hashtag_id) { $q = "DELETE u.* "; $q .= "FROM #prefix#users u INNER JOIN #prefix#posts t ON u.user_id=t.author_user_id "; $q .= "INNER JOIN #prefix#hashtags_posts hp ON t.post_id = hp.post_id "; $q .= "WHERE hp.hashtag_id= :hashtag_id;"; $vars = array(':hashtag_id' => $hashtag_id); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); return $this->getDeleteCount($ps); }
public function updateTitle($id, $title) { $q = "UPDATE #prefix#links SET title=:title WHERE id=:id;"; $vars = array(':title' => $title, ':id' => $id); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); return $this->getUpdateCount($ps); }
public function getAllOwnerInstanceInsightsSince($owner_id, $since) { $q = "SELECT i.*, i.id as insight_key, su.*, u.avatar FROM #prefix#insights i "; $q .= "INNER JOIN #prefix#instances su ON i.instance_id = su.id "; $q .= "INNER JOIN #prefix#owner_instances oi ON su.id = oi.instance_id "; $q .= "LEFT JOIN #prefix#users u ON (su.network_user_id = u.user_id AND su.network = u.network) "; $q .= "WHERE su.is_active = 1 AND oi.owner_id = :owner_id AND time_generated > :since "; $q .= "AND i.filename != 'dashboard' ORDER BY date DESC, emphasis DESC, i.id;"; $vars = array(":owner_id" => (int) $owner_id, ':since' => $since); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); $rows = $this->getDataRowsAsArrays($ps); $insights = array(); foreach ($rows as $row) { $insight = new Insight($row); $insight->instance = new Instance($row); $insight->instance->avatar = $row['avatar']; $insights[] = $insight; } return $insights; }
public function updateGroupMembershipCount($network_user_id, $network) { $q = "INSERT INTO #prefix#count_history "; $q .= "(network_user_id, network, type, date, count) "; $q .= "SELECT :network_user_id, :network, 'group_memberships', NOW(), COUNT(group_id) "; $q .= "FROM #prefix#group_members WHERE member_user_id = :network_user_id "; $q .= "AND network = :network AND is_active = 1"; $vars = array(':network_user_id' => (string) $network_user_id, ':network' => $network); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); return $this->getInsertCount($ps); }
public function addPhoto($vals) { if (self::hasAllRequiredFields($vals)) { // Insert values into the post table $post_key = parent::addPost($vals); // If the post insertion went fine insert the values that go into the photos table if ($post_key) { //SQL variables to bind $vars = array(); //SQL query $q = "INSERT IGNORE INTO #prefix#photos SET "; //Set up required fields foreach ($this->REQUIRED_PHOTO_FIELDS as $field) { $q .= $field . "=:" . $field . ", "; $vars[':' . $field] = $vals[$field]; } //Set up any optional fields foreach ($this->OPTIONAL_PHOTO_FIELDS as $field) { if (isset($vals[$field]) && $vals[$field] != '') { $q .= $field . "=:" . $field . ", "; $vars[':' . $field] = $vals[$field]; } } // Append the internal post ID $q .= 'post_key=:post_key;'; $vars[':post_key'] = $post_key; // Insert the photo in the database if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); $res = $this->getInsertId($ps); return $res; } } else { $status_message = "Could not insert photo ID " . $vals["post_id"] . ", missing values"; $this->logger->logError($status_message, __METHOD__ . ',' . __LINE__); //doesn't have all req'd values return false; } }
public function getPostPlace($post_id, $network = 'twitter') { $q = "SELECT id, AsText(longlat) AS longlat, post_id, place_id, network FROM #prefix#places_posts " . "WHERE post_id = :post_id AND network = :network"; if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, array(':post_id' => (string) $post_id, ':network' => $network)); $row = $this->getDataRowAsArray($ps); if ($row) { return $row; } else { return null; } }
public function getNewMembershipsByDate($network, $member_user_id, $from_date = null) { $vars = array(':member_user_id' => strval($member_user_id), ':network' => $network); if (!isset($from_date)) { $from_date = 'CURRENT_DATE()'; } else { $vars[':from_date'] = $from_date; $from_date = ':from_date'; } $q = "SELECT g.* FROM #prefix#group_members gm INNER JOIN #prefix#groups g on g.group_id = gm.group_id "; $q .= "WHERE gm.member_user_id=:member_user_id AND g.network=:network AND gm.is_active=1 "; $q .= "AND (YEAR(gm.first_seen)=YEAR({$from_date})) "; $q .= "AND (DAYOFMONTH(gm.first_seen)=DAYOFMONTH({$from_date})) AND (MONTH(gm.first_seen)=MONTH({$from_date})) "; $q .= "ORDER BY gm.first_seen DESC;"; if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); $groups = $this->getDataRowsAsObjects($ps, "Group"); return $groups; }
public function findStalestMemberships($user_id, $network) { $q = "SELECT g.id, g.group_id, g.group_name, g.network, DATEDIFF(NOW(), m.last_seen) AS days_old "; $q .= "FROM #prefix#groups AS g "; $q .= "INNER JOIN #prefix#group_members AS m ON m.group_id = g.group_id AND g.network = m.network "; $q .= "WHERE m.member_user_id = :user_id and m.network = :network AND m.is_active=1 "; $q .= "AND m.last_seen < DATE_SUB(NOW(), INTERVAL 1 DAY) "; $q .= "ORDER BY days_old DESC LIMIT 1"; $vars = array(':user_id' => (string) $user_id, ':network' => $network); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); return $this->getDataRowAsObject($ps, "Group"); }
public function resetID() { $q = "ALTER TABLE #prefix#stream_data auto_increment = 1"; if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q); }
public function deleteProcess($process_id) { $q = "DELETE FROM #prefix#stream_procs "; $q .= "WHERE process_id = :process_id"; $vars = array(':process_id' => $process_id); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); $res = $this->getUpdateCount($ps); if (!$res) { throw new StreamingException("Could not delete pid {$process_id}"); } }
public function insert($group_id, $group_name, $network) { $q = "INSERT INTO #prefix#groups "; $q .= "(group_id, group_name, first_seen, last_seen, network) "; $q .= "VALUES ( :group_id, :group_name, NOW(), NOW(), :network );"; $vars = array(':group_id' => (string) $group_id, ':group_name' => $group_name, ':network' => $network); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); return $this->getInsertId($ps); }
public function getVersionBeforeDay($user_key, $before_day, $field) { $vars = array(':user_key' => $user_key, ':before_day' => $before_day, ':field_name' => $field); $q = "SELECT user_key, field_name, field_value, crawl_time FROM #prefix#user_versions as uv "; $q .= "WHERE uv.user_key = :user_key AND date(crawl_time) < :before_day "; $q .= " AND field_name = :field_name "; $q .= " ORDER BY crawl_time DESC LIMIT 1"; if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); $rows = $this->getDataRowsAsArrays($ps); if (count($rows)) { return $rows[0]; } else { return null; } }
public function isValidPluginId($plugin_id) { $q = 'SELECT id FROM #prefix#plugins where id = :id'; $data = array(':id' => $plugin_id); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $stmt = $this->execute($q, $data); return $this->getDataIsReturned($stmt); }
public function deleteCountry($country_id) { $q = "DELETE from #prefix#country WHERE id = :country_id LIMIT 1"; $vars = array(':country_id' => $country_id); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); return $this->getUpdateCount($ps); }
private function getRepliedToPostIDs($username, $network, $page, $page_size) { $page = $page - 1; $start_on = $page * $page_size; $q = "SELECT in_reply_to_post_id as post_id FROM #prefix#posts WHERE "; $q .= "author_username = :author_username AND network=:network AND in_reply_to_post_id IS NOT null "; $q .= "LIMIT " . $start_on . ", " . $page_size; $vars = array(':author_username' => $username, ':network' => $network); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $stmt = $this->execute($q, $vars); return $this->getDataRowsAsArrays($stmt); }
public function getPostCountForYear($author_username, $network, $year) { $vars = array(':author_username' => $author_username, ':network' => $network, ':year' => $year); $q = "SELECT YEAR(pub_date) as YEAR, COUNT(*) AS post_count FROM #prefix#posts p "; $q .= "WHERE author_username = :author_username AND p.network = :network "; $q .= "AND YEAR(pub_date) = :year"; if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); $all_rows = $this->getDataRowsAsArrays($ps); return $all_rows[0]; }
public function getHighestClickCountByLinkID($link_id) { $q = "SELECT ls.click_count "; $q .= "FROM #prefix#links_short ls "; $q .= "WHERE ls.link_id = :link_id "; $q .= "ORDER BY ls.click_count DESC LIMIT 1;"; $vars = array(':link_id' => $link_id); if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); $result = $this->getDataRowsAsArrays($ps); if (isset($result[0]["click_count"])) { return $result[0]["click_count"]; } else { return 0; } }
public function getLinksByUserSinceDaysAgo($user_id, $network, $limit = 0, $days_ago = 0) { $vars = array(':user_id' => $user_id, ':network' => $network); $q = "SELECT l.*, p.in_retweet_of_post_id FROM #prefix#links AS l "; $q .= "INNER JOIN #prefix#posts AS p ON p.id = l.post_key "; $q .= "WHERE p.author_user_id=:user_id AND p.network=:network "; $q .= "AND p.in_reply_to_user_id IS NULL "; if ($days_ago != 0) { $q .= "AND p.pub_date>=DATE_SUB(CURDATE(), INTERVAL :days_ago DAY) "; $q .= "ORDER BY p.pub_date DESC "; $vars[':days_ago'] = $days_ago; } if ($limit != 0) { $q .= "ORDER BY p.pub_date DESC "; $q .= "LIMIT :limit "; $vars[':limit'] = $limit; } if ($this->profiler_enabled) { Profiler::setDAOMethod(__METHOD__); } $ps = $this->execute($q, $vars); $all_rows = $this->getDataRowsAsArrays($ps); return $all_rows; }