public static function getTitles($seedKeyword, $groupTitles)
{
if (empty($seedKeyword)) {
return;
}
$dbr = DbUtils::getDbr();
$query = "SELECT\n\t\t\tgroup_concat(DISTINCT t.short_title SEPARATOR 0x0B) as short_titles,\n\t\t\tgroup_concat(DISTINCT IF (t.wh_aid <> 0, concat('http://www.wikihow.com/',t.wh_title), '') SEPARATOR 0x0B) as wh_article,\n\t\t\tsum(k.avg_month_searches) as sum_of_ams,\n\t\t\tcount(t.short_title) as num_sites\n\t\tFROM\n\t\t\tleonard.leo_keywords k INNER join leonard.leo_titles t ON k.keyword = t.keyword\n\t\tWHERE\n\t\t\tk.status = 'A'\n\t\t\tAND t.status = 'A'\n\t\t\tAND k.seed = " . $dbr->addQuotes($seedKeyword) . " \n" . "GROUP BY ";
// Also had following columns in the select query however elizabeth needs only few as seen in the actual query
// group_concat(DISTINCT t.site order by t.position_in_results SEPARATOR 0x0B) as site_list,
// group_concat(DISTINCT concat(t.keyword,':',k.avg_month_searches) ORDER BY k.avg_month_searches DESC) as kwd_n_avg_m_searches,
// group_concat(t.original_title order by t.position_in_results SEPARATOR 0x0B) as original_titles,
// group_concat(DISTINCT t.url SEPARATOR 0x0B) as urls,
// min(k.ip_rank) as min_kw_ip_rank,
// max(k.ip_rank) as max_kw_ip_rank
if ($groupTitles === true) {
$query .= " dup_grp_id ";
} else {
$query .= " short_title ";
}
$query .= " ORDER BY\n\t\t\tsum(k.avg_month_searches) DESC,\n\t\t\tt.keyword,\n\t\t\tcount(t.short_title) DESC";
$dbr = DbUtils::getDbr();
return DbUtils::exDbR($query);
}
