$sql = "SELECT count(distinct(id)) as count, ";
$sql .= sqlInterval();
$sql .= " FROM " . $esc['mysql']['dataset'] . "_tweets t ";
$sql .= sqlSubset();
$sql .= " AND retweet_id != '' AND retweet_id != '0'";
$sql .= " GROUP BY datepart ORDER BY datepart ASC";
//print $sql."<br>";
$sqlresults = mysql_query($sql);
if ($sqlresults && mysql_num_rows($sqlresults) > 0) {
while ($data = mysql_fetch_assoc($sqlresults)) {
$numretweets[$data['datepart']] = $data["count"];
}
}
// number of replies
$sql = "SELECT count(distinct(id)) as count, ";
$sql .= sqlInterval();
$sql .= " FROM " . $esc['mysql']['dataset'] . "_tweets t ";
$sql .= sqlSubset();
$sql .= " AND in_reply_to_status_id != ''";
$sql .= " GROUP BY datepart ORDER BY datepart ASC";
//print $sql."<br>";
$sqlresults = mysql_query($sql);
if ($sqlresults && mysql_num_rows($sqlresults) > 0) {
while ($data = mysql_fetch_assoc($sqlresults)) {
$numReplies[$data['datepart']] = $data["count"];
}
}
$csv->writeheader(array("Date", "Number of tweets", "Number of tweets with links", "Number of tweets with hashtags", "Number of tweets with mentions", "Number of tweets with media uploads", "Number of retweets", "Number of replies"));
foreach ($numtweets as $date => $tweetcount) {
$linkcount = $hashtagcount = $mentioncount = $retweetcount = $replycount = 0;
if (isset($numlinktweets[$date])) {
function frequencyTable($table, $toget)
{
global $esc, $intervalDates;
$results = array();
$sql = "SELECT COUNT({$table}.{$toget}) AS count, {$table}.{$toget} AS toget, ";
$sql .= sqlInterval();
$sql .= "FROM " . $esc['mysql']['dataset'] . "_{$table} {$table}, " . $esc['mysql']['dataset'] . "_tweets t ";
$where = "t.id = {$table}.tweet_id AND ";
$sql .= sqlSubset($where);
$sql .= " GROUP BY toget, datepart ORDER BY datepart ASC, count DESC";
$rec = mysql_unbuffered_query($sql);
$date = false;
while ($res = mysql_fetch_assoc($rec)) {
if ($res['count'] > $esc['shell']['minf']) {
if (!empty($intervalDates)) {
$date = groupByInterval($res['datepart']);
} else {
$date = $res['datepart'];
}
if ($table == 'urls') {
$res['toget'] = validate($res['toget'], 'url');
}
$results[$date][$res['toget']] = $res['count'];
}
}
mysql_free_result($rec);
return $results;
}
</script>
</head>
<body>
<h1>TCAT :: Word frequency</h1>
<?php
validate_all_variables();
$tempfile = tmpfile();
fputs($tempfile, chr(239) . chr(187) . chr(191));
mysql_query("set names utf8");
$sql = "SELECT text, " . sqlInterval() . " FROM " . $esc['mysql']['dataset'] . "_tweets t ";
$sql .= sqlSubset();
//$sql .= " GROUP BY datepart ORDER BY datepart ASC";
$sql .= " ORDER BY datepart ASC";
$sqlresults = mysql_unbuffered_query($sql);
if ($sqlresults) {
while ($data = mysql_fetch_assoc($sqlresults)) {
$text = $data["text"];
$datepart = str_replace(' ', '_', $data["datepart"]);
preg_match_all('/(https?:\\/\\/[^\\s]+)|([@#\\p{L}][\\p{L}]+)/u', $text, $matches, PREG_PATTERN_ORDER);
foreach ($matches[0] as $word) {
if (preg_match('/(https?:\\/\\/)/u', $word)) {
continue;
}
if ($lowercase !== 0) {
$word = mb_strtolower($word);
</script>
</head>
<body>
<h1>TCAT :: Media frequency</h1>
<?php
validate_all_variables();
$media_url_count = array();
$tempfile = tmpfile();
fputs($tempfile, chr(239) . chr(187) . chr(191));
mysql_query("set names utf8");
$sql = "SELECT m.media_url_https as url, " . sqlInterval() . " FROM " . $esc['mysql']['dataset'] . "_tweets t, " . $esc['mysql']['dataset'] . "_media m ";
$sql .= sqlSubset();
$sql .= " AND m.tweet_id = t.id ";
$sql .= " ORDER BY datepart ASC";
$sqlresults = mysql_unbuffered_query($sql);
$debug = '';
if ($sqlresults) {
while ($data = mysql_fetch_assoc($sqlresults)) {
$datepart = $data["datepart"];
$url = $data["url"];
if (!array_key_exists($datepart, $media_url_count)) {
$media_url_count[$datepart] = array();
}
if (!array_key_exists($url, $media_url_count[$datepart])) {
$media_url_count[$datepart][$url] = 1;
} else {
echo '</body></html>';
die;
}
// make filename and open file for write
if ($bin_type == "geotrack") {
$module = "rateLimitDataGeo";
} else {
$module = "ratelimitData";
}
$module .= "-" . $esc['date']['interval'];
$filename = get_filename_for_export($module);
$csv = new CSV($filename, $outputformat);
// write header
$header = "querybin,datetime,tweets ratelimited (estimate)";
$csv->writeheader(explode(',', $header));
$sqlInterval = sqlInterval();
$sqlSubset = sqlSubset();
$sqlGroup = " GROUP BY datepart ASC";
// Use native MySQL to create a temporary table with all dateparts. They should be identical to the dateparts we will use in the GROUP BY statement.
// Prepare the string mysql needs in date_add()
$mysqlNativeInterval = "day";
// default $interval = daily
switch ($esc['date']['interval']) {
case "hourly":
$mysqlNativeInterval = "hour";
break;
case "daily":
$mysqlNativeInterval = "day";
break;
case "weekly":
$mysqlNativeInterval = "week";