</script> </head> <body> <h1>TCAT :: Export URLs</h1> <?php validate_all_variables(); $filename = get_filename_for_export('urlsExport'); $csv = new CSV($filename, $outputformat); $csv->writeheader(array('tweet_id', 'url', 'url_expanded', 'url_followed')); $sql = "SELECT t.id as id, u.url as url, u.url_expanded as url_expanded, u.url_followed as url_followed FROM " . $esc['mysql']['dataset'] . "_tweets t, " . $esc['mysql']['dataset'] . "_urls u "; $sql .= sqlSubset(); $sql .= " AND u.tweet_id = t.id ORDER BY id"; $sqlresults = mysql_unbuffered_query($sql); $out = ""; if ($sqlresults) { while ($data = mysql_fetch_assoc($sqlresults)) { $csv->newrow(); $csv->addfield($data['id'], 'integer'); $csv->addfield($data['url'], 'string'); if (isset($data['url_followed']) && strlen($data['url_followed']) > 1) { $csv->addfield($data['url'], 'string'); } else { $csv->addfield('', 'string'); }
} // 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])) { $linkcount = $numlinktweets[$date]; } if (isset($numTweetsWithHashtag[$date])) { $hashtagcount = $numTweetsWithHashtag[$date]; } if (isset($numTweetsWithMentions[$date])) { $mentioncount = $numTweetsWithMentions[$date]; } if (isset($numTweetsWithMedia[$date])) { $mediacount = $numTweetsWithMedia[$date]; } if (isset($numretweets[$date])) {
<?php validate_all_variables(); /* @todo, use same export possibilities as mod.export_tweets.php */ $header = "id,time,created_at,from_user_name,from_user_lang,text,source,location,lat,lng,from_user_tweetcount,from_user_followercount,from_user_friendcount,from_user_realname,to_user_name,in_reply_to_status_id,quoted_status_id,from_user_listed,from_user_utcoffset,from_user_timezone,from_user_description,from_user_url,from_user_verified,filter_leveli,cld_name,cld_code,cld_reliable,cld_bytes,cld_percent"; if (isset($_GET['includeUrls']) && $_GET['includeUrls'] == 1) { $header .= ",urls,urls_expanded,urls_followed,domains"; } $header .= "\n"; $langset = $esc['mysql']['dataset'] . '_lang'; $sql = "SELECT * FROM " . $esc['mysql']['dataset'] . "_tweets t inner join {$langset} l on t.id = l.tweet_id "; $sql .= sqlSubset(); $sqlresults = mysql_query($sql); $filename = get_filename_for_export("fullExportLang"); $csv = new CSV($filename, $outputformat); $csv->writeheader(explode(',', $header)); if ($sqlresults) { while ($data = mysql_fetch_assoc($sqlresults)) { $csv->newrow(); if (preg_match("/_urls/", $sql)) { $id = $data['tweet_id']; } else { $id = $data['id']; } $csv->addfield($id); $csv->addfield(strtotime($data["created_at"])); $fields = array('created_at', 'from_user_name', 'from_user_lang', 'text', 'source', 'location', 'geo_lat', 'geo_lng', 'from_user_tweetcount', 'from_user_followercount', 'from_user_friendcount', 'from_user_realname', 'to_user_name', 'in_reply_to_status_id', 'quoted_status_id', 'from_user_listed', 'from_user_utcoffset', 'from_user_timezone', 'from_user_description', 'from_user_url', 'from_user_verified', 'filter_level'); foreach ($fields as $f) { $csv->addfield(isset($data[$f]) ? $data[$f] : ''); } $csv->addfield($data['name']);
$array = array(); while ($res = mysql_fetch_assoc($sqlresults)) { $array[$res['datepart']]['followercount'][$res['from_user_id']] = $res['from_user_followercount']; $array[$res['datepart']]['friendcount'][$res['from_user_id']] = $res['from_user_friendcount']; $array[$res['datepart']]['tweetcount'][$res['from_user_id']] = $res['from_user_tweetcount']; } mysql_free_result($sqlresults); if (!empty($array)) { foreach ($array as $date => $ar) { $stats[$date]['followercount'] = stats_summary($ar['followercount']); $stats[$date]['friendcount'] = stats_summary($ar['friendcount']); $stats[$date]['tweetcount'] = stats_summary($ar['tweetcount']); } } // @todo: aantal retweets $csv->writeheader(array("date", "what", "min", "max", "avg", "Q1", "median", "Q3", "25%TrimmedMean")); foreach ($stats as $date => $datestats) { foreach ($datestats as $what => $stat) { $csv->newrow(); $csv->addfield($date); $csv->addfield($what); $csv->addfield($stat['min']); $csv->addfield($stat['max']); $csv->addfield($stat['avg']); $csv->addfield($stat['Q1']); $csv->addfield($stat['median']); $csv->addfield($stat['Q3']); $csv->addfield($stat['truncatedMean']); $csv->writerow(); } }
function generate($what, $filename) { global $tsv, $network, $esc, $titles, $database, $interval, $outputformat; require_once __DIR__ . '/CSV.class.php'; // initialize variables $tweets = $times = $from_user_names = $results = $urls = $urls_expanded = $hosts = $hashtags = array(); $csv = new CSV($filename, $outputformat); $collation = current_collation(); // determine interval $sql = "SELECT MIN(t.created_at) AS min, MAX(t.created_at) AS max FROM " . $esc['mysql']['dataset'] . "_tweets t "; $sql .= sqlSubset(); //print $sql . "<bR>"; $rec = mysql_query($sql); $res = mysql_fetch_assoc($rec); // get frequencies if ($what == "hashtag") { $results = frequencyTable("hashtags", "text"); } elseif ($what == "urls") { $results = frequencyTable("urls", "url_followed"); } elseif ($what == "hosts") { $results = frequencyTable("urls", "domain"); } elseif ($what == "mention") { $results = frequencyTable("mentions", "to_user"); // get other things } else { // @todo, this could also use database grouping $sql = "SELECT id,text COLLATE {$collation} as text,created_at,from_user_name COLLATE {$collation} as from_user_name FROM " . $esc['mysql']['dataset'] . "_tweets t "; $sql .= sqlSubset(); // get slice and its min and max time $rec = mysql_query($sql); if ($rec && mysql_num_rows($rec) > 0) { while ($res = mysql_fetch_assoc($rec)) { $tweets[] = $res['text']; $ids[] = $res['id']; $times[] = $res['created_at']; $from_user_names[] = strtolower($res['from_user_name']); } } // extract desired things ($what) and group per interval foreach ($tweets as $key => $tweet) { $time = $times[$key]; switch ($interval) { case "hourly": $group = strftime("%Y-%m-%d %Hh", strtotime($time)); break; case "weekly": $group = strftime("%Y %u", strtotime($time)); break; case "monthly": $group = strftime("%Y-%m", strtotime($time)); break; case "yearly": $group = strftime("%Y-%m", strtotime($time)); break; case "overall": $group = "overall"; break; case "custom": $group = groupByInterval(strftime("%Y-%m-%d", strtotime($time))); break; default: $group = strftime("%Y-%m-%d", strtotime($time)); // default daily } switch ($what) { //case "hashtag": // foreach ($hashtags as $hashtag) // $results[$group][] = $hashtag; // break; //case "mention": // @todo, mentions might be taken from own table // $stuff = get_replies($tweet); // foreach ($stuff as $thing) // $results[$group][] = $thing; // break; case "user": $results[$group][] = $from_user_names[$key]; break; case "user-mention": $stuff = get_replies($tweet); foreach ($stuff as $thing) { $results[$group]['mentions'][] = $thing; } $results[$group]['users'][] = $from_user_names[$key]; //var_dump($results); break; case "retweet": $results[$group][] = $tweet; // TODO, write stemming function break; //case "urls": // if (isset($urls_expanded[$ids[$key]])) // $results[$group][] = $urls_expanded[$ids[$key]]; // break; //case "hosts": // if (isset($urls_expanded[$ids[$key]])) // $results[$group][] = $hosts[$ids[$key]]; // break; //case "urls": // if (isset($urls_expanded[$ids[$key]])) // $results[$group][] = $urls_expanded[$ids[$key]]; // break; //case "hosts": // if (isset($urls_expanded[$ids[$key]])) // $results[$group][] = $hosts[$ids[$key]]; // break; default: break; } } // count frequency of occurence of thing, per interval if ($what != "user-mention") { foreach ($results as $group => $things) { $counted_things = array_count_values($things); arsort($counted_things); $results[$group] = $counted_things; } } } // network output for users if ($what == "user-mention") { foreach ($results as $group => $things) { $tmp_mentions = array_count_values($things['mentions']); $tmp_users = array_count_values($things['users']); $counted_things = array(); // add all from_user_names foreach ($tmp_users as $user => $count) { if (isset($tmp_mentions["@" . $user])) { $counted_things[$user] = $tmp_mentions["@" . $user] . "," . $count; } else { $counted_things[$user] = "0," . $count; } } // add all users which were replied but not in the set foreach ($tmp_mentions as $user => $count) { $user = str_replace("@", "", $user); if (!isset($counted_things[$user])) { $counted_things[$user] = $count . ",0"; } } ksort($counted_things); $results[$group] = $counted_things; } if (isset($titles[$what])) { if (!empty($esc['shell']['query'])) { $q = " with search " . $esc['shell']['query']; } else { $q = ""; } $csv->writeheader(array($titles[$what] . $q . " from " . $esc['date']["startdate"] . " to " . $esc['date']["enddate"])); } $csv->writeheader(array("date", "user", "mentions", "tweets")); foreach ($results as $group => $things) { foreach ($things as $thing => $count) { $csv->newrow(); $csv->addfield($group); $csv->addfield($thing); $exp = explode(",", $count); // unpack what we packed $csv->addfield($exp[0]); $csv->addfield($exp[1]); $csv->writerow(); } } // write tsv output } elseif (in_array($what, $tsv) !== false) { ksort($results); // construct file if (isset($titles[$what])) { if (!empty($esc['shell']['query'])) { $q = " with search " . $esc['shell']['query']; } else { $q = ""; } $csv->writeheader(array($titles[$what] . " for " . $esc['shell']['datasetname'] . $q . " from " . $esc['date']["startdate"] . " to " . $esc['date']["enddate"])); } if ($what == "urls") { $csv->writeheader(array("date", "frequency", "tweetedurl")); } elseif ($what == "hosts") { $csv->writeheader(array("date", "frequency", "domain", "name")); } else { $csv->writeheader(array("date", "frequency", $what)); } foreach ($results as $group => $things) { arsort($things); foreach ($things as $thing => $count) { if (empty($thing)) { continue; } if ($count < $esc['shell']['minf']) { continue; } $csv->newrow(); $csv->addfield($group); $csv->addfield($count); $csv->addfield($thing); $csv->writerow(); } } } else { die('no valid output format found'); } $csv->close(); }
$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 { $media_url_count[$datepart][$url]++; } } mysql_free_result($sqlresults); } // write csv results $filename = get_filename_for_export("mediaFrequency"); $csv = new CSV($filename, $outputformat); $csv->writeheader(array('interval', 'media url', 'frequency')); foreach ($media_url_count as $datepart => $url_count) { arsort($url_count); foreach ($url_count as $url => $count) { $csv->newrow(); $csv->addfield($datepart); $csv->addfield($url); $csv->addfield($count); $csv->writerow(); } } $csv->close(); echo '<fieldset class="if_parameters">'; echo '<legend>Your File</legend>'; echo '<p><a href="' . filename_to_url($filename) . '">' . $filename . '</a></p>'; echo '</fieldset>';
if (array_key_exists($word, $negativeSentiments) === false) { $negativeSentiments[$word] = 0; } $negativeSentiments[$word]++; } else { if (array_key_exists($word, $positiveSentiments) === false) { $positiveSentiments[$word] = 0; } $positiveSentiments[$word]++; } $wordValues[$word] = $sentimentValue; } } } mysql_free_result($rec); $csv->writeheader(array('word', 'count', 'sentistrength')); arsort($positiveSentiments); foreach ($positiveSentiments as $word => $val) { $csv->newrow(); $csv->addfield($word); $csv->addfield($val); $csv->addfield($wordValues[$word]); $csv->writerow(); } arsort($negativeSentiments); foreach ($negativeSentiments as $word => $val) { $csv->newrow(); $csv->addfield($word); $csv->addfield($val); $csv->addfield($wordValues[$word]); $csv->writerow();
<h1>TCAT :: Host hashtag co-occurence</h1> <?php validate_all_variables(); $filename = get_filename_for_export("hostHashtag"); $csv = new CSV($filename, $outputformat); $collation = current_collation(); $sql = "SELECT COUNT(LOWER(h.text COLLATE {$collation})) AS frequency, LOWER(h.text COLLATE {$collation}) AS hashtag, u.domain COLLATE {$collation} AS domain FROM "; $sql .= $esc['mysql']['dataset'] . "_tweets t, " . $esc['mysql']['dataset'] . "_hashtags h, " . $esc['mysql']['dataset'] . "_urls u "; $where = "t.id = h.tweet_id AND h.tweet_id = u.tweet_id AND u.url_followed !='' AND "; $sql .= sqlSubset($where); $sql .= " GROUP BY u.domain COLLATE {$collation}, LOWER(h.text COLLATE {$collation}) ORDER BY frequency DESC"; //print $sql." - <br>"; $sqlresults = mysql_unbuffered_query($sql); $csv->writeheader(array("frequency", "hashtag", "domain")); while ($res = mysql_fetch_assoc($sqlresults)) { $csv->newrow(); $csv->addfield($res['frequency']); $csv->addfield($res['hashtag']); $csv->addfield($res['domain']); $csv->writerow(); $urlHashtags[$res['domain']][$res['hashtag']] = $res['frequency']; } mysql_free_result($sqlresults); $csv->close(); echo '<fieldset class="if_parameters">'; echo '<legend>Your spreadsheet (CSV) file</legend>'; echo '<p><a href="' . str_replace("#", urlencode("#"), str_replace("\"", "%22", $filename)) . '">' . $filename . '</a></p>'; echo '</fieldset>'; $gexf = new Gexf();
<body> <h1>TCAT :: URL user co-occurence</h1> <?php validate_all_variables(); $collation = current_collation(); $filename = get_filename_for_export("urlUser"); $csv = new CSV($filename, $outputformat); $sql = "SELECT COUNT(LOWER(t.from_user_name COLLATE {$collation})) AS frequency, LOWER(t.from_user_name COLLATE {$collation}) AS username, u.url_followed AS url, u.domain AS domain, u.error_code AS status_code FROM "; $sql .= $esc['mysql']['dataset'] . "_tweets t, " . $esc['mysql']['dataset'] . "_urls u "; $where = "t.id = u.tweet_id AND u.url_followed !='' AND "; $sql .= sqlSubset($where); $sql .= " GROUP BY u.url_followed, LOWER(t.from_user_name) ORDER BY frequency DESC"; $sqlresults = mysql_query($sql); $csv->writeheader(array("frequency", "user", "url", "domain", "status_code")); while ($res = mysql_fetch_assoc($sqlresults)) { $csv->newrow(); $csv->addfield($res['frequency']); $csv->addfield($res['username']); $csv->addfield($res['url']); $csv->addfield($res['domain']); $csv->addfield($res['status_code']); $csv->writerow(); $urlUsernames[$res['url']][$res['username']] = $res['frequency']; $urlDomain[$res['url']] = $res['domain']; $urlStatusCode[$res['url']] = $res['status_code']; } $csv->close(); echo '<fieldset class="if_parameters">'; echo '<legend>Your spreadsheet (CSV) file</legend>';
</script> </head> <body> <h1>TCAT :: Export Tweets sentiment</h1> <?php validate_all_variables(); /* @todo, use same export possibilities as mod.export_tweets.php */ $filename = get_filename_for_export("fullExport-sentiment"); $csv = new CSV($filename, $outputformat); $header = "id"; $header .= ",sentistrength,negative,positive"; $csv->writeheader($header); $sql = "SELECT s.positive, s.negative, s.explanation, t.from_user_name as user, t.id as tid FROM " . $esc['mysql']['dataset'] . "_sentiment s, " . $esc['mysql']['dataset'] . "_tweets t "; $sql .= sqlSubset("t.id = s.tweet_id AND "); $rec = mysql_unbuffered_query($sql); while ($res = mysql_fetch_assoc($rec)) { $sentiment[$res['tid']]['pos'] = $res['positive']; $sentiment[$res['tid']]['neg'] = $res['negative']; $sentiment[$res['tid']]['desc'] = $res['explanation']; } mysql_free_result($rec); $sql = "SELECT * FROM " . $esc['mysql']['dataset'] . "_tweets t "; $sql .= sqlSubset(); $sqlresults = mysql_unbuffered_query($sql); if ($sqlresults) { while ($data = mysql_fetch_assoc($sqlresults)) { $csv->newrow();
</script> </head> <body> <h1>TCAT :: Export mentions</h1> <?php validate_all_variables(); $filename = get_filename_for_export('mentionExport'); $csv = new CSV($filename, $outputformat); $csv->writeheader(array('tweet_id', 'user_from_id', 'user_from_name', 'user_to_id', 'user_to_name', 'mention_type')); $sql = "SELECT t.id as id, t.text as text, m.from_user_id as user_from_id, m.from_user_name as user_from_name, m.to_user_id as user_to_id, m.to_user as user_to_name FROM " . $esc['mysql']['dataset'] . "_tweets t, " . $esc['mysql']['dataset'] . "_mentions m "; $sql .= sqlSubset(); $sql .= " AND m.tweet_id = t.id ORDER BY id"; $sqlresults = mysql_unbuffered_query($sql); $out = ""; if ($sqlresults) { while ($data = mysql_fetch_assoc($sqlresults)) { $csv->newrow(); $csv->addfield($data['id'], 'integer'); $csv->addfield($data['user_from_id'], 'integer'); $csv->addfield($data['user_from_name'], 'string'); $csv->addfield($data['user_to_id'], 'integer'); $csv->addfield($data['user_to_name'], 'string'); $csv->addfield(detect_mention_type($data['text'], $data['user_to_name']), 'string'); $csv->writerow();
// tweets with hashtags, per user $sql = "SELECT h.from_user_name COLLATE {$collation} as from_user_name, count(distinct(h.tweet_id)) as count, "; $sql .= sqlInterval(); $sql .= " FROM " . $esc['mysql']['dataset'] . "_hashtags h, " . $esc['mysql']['dataset'] . "_tweets t "; $sql .= sqlSubset(); $sql .= " AND h.tweet_id = t.id "; $sql .= "GROUP BY datepart, h.from_user_name"; //print $sql . "<br>"; flush(); $rec = mysql_unbuffered_query($sql); $tweetsWithhashtags = array(); while ($res = mysql_fetch_assoc($rec)) { $tweetsWithhashtags[$res['datepart']][$res['from_user_name']] = $res['count']; } mysql_free_result($rec); $csv->writeheader(explode(',', "date,from_user_id,from_user_name,from_user_lang,from_user_tweetcount (all time user queries),from_user_followercount,from_user_friendcount,from_user_listed,from_user_utcoffset,from_user_verified,tweets in data set,retweets by user, mentioning,mentioned,total nr of hashtags,nr of tweets with hashtags")); foreach ($array as $date => $user_array) { foreach ($user_array as $user => $a) { $csv->newrow(); $csv->addfield($date); $csv->addfield($a["from_user_id"]); $csv->addfield($a["from_user_name"]); $csv->addfield($a["from_user_lang"]); $csv->addfield($a["from_user_tweetcount"]); $csv->addfield($a["from_user_followercount"]); $csv->addfield($a["from_user_friendcount"]); $csv->addfield($a["from_user_listed"]); $csv->addfield($a["from_user_utcoffset"]); $csv->addfield($a["from_user_verified"]); $csv->addfield($a["tweetcount"]); if (isset($retweets[$date][$user])) {
$sql .= sqlSubset() . " AND "; $sql .= "h.tweet_id = m.tweet_id AND h.tweet_id = t.id"; $rec = mysql_unbuffered_query($sql); while ($res = mysql_fetch_assoc($rec)) { if (!isset($hashtagMentions[$res['h1']][$res['u']])) { $hashtagMentions[$res['h1']][$res['u']] = 0; } $hashtagMentions[$res['h1']][$res['u']]++; } mysql_free_result($rec); foreach ($hashtagMentions as $hashtag => $mentions) { $hashtagDistinctMentions[$hashtag] = count($mentions); $hashtagMentions[$hashtag] = array_sum($mentions); } // user-hashtag stats: hashtag, nr. of mentions, nr. of users participating (option: nr. of mentions) $csv->writeheader(array("hashtag", "nr of tweets with hashtag", "distinct users for hashtag", "distinct mentions with hashtag", "total mentions with hashtag", "nr of tweets in selection", "nr of users in selection")); foreach ($hashtagCount as $hashtag => $count) { $csv->newrow(); $csv->addfield($hashtag); $csv->addfield($count); $csv->addfield($hashtagDistinctUsers[$hashtag]); $csv->addfield(isset($hashtagDistinctMentions[$hashtag]) ? $hashtagDistinctMentions[$hashtag] : 0); $csv->addfield(isset($hashtagMentions[$hashtag]) ? $hashtagMentions[$hashtag] : 0); $csv->addfield($nrOfTweets); $csv->addfield($nrOfUsers); $csv->writerow(); } $csv->close(); echo '<fieldset class="if_parameters">'; echo '<legend>Your CSV File</legend>'; echo '<p><a href="' . filename_to_url($filename) . '">' . $filename . '</a></p>';
function variabilityOfAssociationProfiles($filename, $series, $keywordToTrack, $ap) { global $outputformat; if (empty($series) || empty($keywordToTrack)) { die('not enough data'); } $filename = get_filename_for_export("variability", "_variabilityOfAssociationProfiles"); $csv = new CSV($filename, $outputformat); // group per slice // per keyword // get associated words (depth 1) per slice // get frequency, degree, ap variation (calculated on cooc frequency), words in, words out, ap keywords $degree = array(); foreach ($series as $time => $cw) { $cw = $cw->getCowords(); foreach ($cw as $word => $cowords) { foreach ($cowords as $coword => $frequency) { // save how many time slices the word appears $words[$word][$time] = 1; $words[$coword][$time] = 1; // keep track of degree per word per time slice if (array_key_exists($word, $degree) === false) { $degree[$word] = array(); } if (array_key_exists($coword, $degree) === false) { $degree[$coword] = array(); } if (array_key_exists($time, $degree[$word]) === false) { $degree[$word][$time] = 0; } if (array_key_exists($time, $degree[$coword]) === false) { $degree[$coword][$time] = 0; } $degree[$word][$time]++; $degree[$coword][$time]++; } } } // count nr of time slices the words appears in foreach ($words as $word => $times) { $documentsPerWords[$word] = count($times); } // calculate similarity and changes foreach ($ap as $word => $times) { $times_keys = array_keys($times); for ($i = 1; $i < count($times_keys); $i++) { $im1 = $i - 1; $v1 = $times[$times_keys[$im1]]; $v2 = $times[$times_keys[$i]]; $cos_sim[$word][$times_keys[$i]] = cosineSimilarity($v1, $v2); $change_out[$word][$times_keys[$i]] = change($v1, $v2); $change_in[$word][$times_keys[$i]] = change($v2, $v1); $stable[$word][$times_keys[$i]] = array_intersect(array_keys($v1), array_keys($v2)); } } // @todo, frequency $csv->writeheader(array("key", "time", "degree", "similarity", "associational profile", "change in", "change out", "stable")); foreach ($ap as $word => $times) { foreach ($times as $time => $profile) { if (isset($change_in[$word][$time])) { $inc = ""; foreach ($change_in[$word][$time] as $w => $c) { $inc .= "{$w} ({$c}), "; } $inc = substr($inc, 0, -2); } else { $inc = ""; } if (isset($change_out[$word][$time])) { $outc = ""; foreach ($change_out[$word][$time] as $w => $c) { $outc .= "{$w} ({$c}), "; } $outc = substr($outc, 0, -2); } else { $outc = ""; } if (isset($stable[$word][$time])) { $stablec = array(); foreach ($stable[$word][$time] as $w) { $stablec[] = $w; } $stablec = implode(", ", $stablec); } else { $stablec = ""; } $prof = ""; foreach ($profile as $w => $c) { $prof .= "{$w} ({$c}), "; } $prof = substr($prof, 0, -2); if (isset($degree[$word][$time])) { $deg = $degree[$word][$time]; } else { $deg = ""; } if (isset($cos_sim[$word][$time])) { $cs = $cos_sim[$word][$time]; } else { $cs = ""; } $csv->newrow(); $csv->addfield($word); $csv->addfield($time); $csv->addfield($deg); $csv->addfield($cs); $csv->addfield($prof); $csv->addfield($inc); $csv->addfield($outc); $csv->addfield($stablec); $csv->writerow(); } } $csv->close(); echo '<fieldset class="if_parameters">'; echo '<legend>Your co-hashtag variability File</legend>'; echo '<p><a href="' . filename_to_url($filename) . '">' . $filename . '</a></p>'; echo '</fieldset>'; }
$sql .= "GROUP BY datepart,source ORDER BY count DESC"; //print $sql . "<br>"; exit; $sqlresults = mysql_unbuffered_query($sql); $array = array(); while ($res = mysql_fetch_assoc($sqlresults)) { $res['source'] = preg_replace("/<.+>/U", "", $res['source']); $res['source'] = preg_replace("/,/", "_", $res['source']); $res['source'] = preg_replace("/[ \\s\t]+/", " ", $res['source']); $res['source'] = trim($res['source']); if (!isset($array[$res['datepart']][$res['source']])) { $array[$res['datepart']][$res['source']] = 0; } $array[$res['datepart']][$res['source']] += $res['count']; } mysql_free_result($sqlresults); $csv->writeheader(array("date", "source", "count")); foreach ($array as $date => $sources) { arsort($sources); foreach ($sources as $source => $freq) { $csv->newrow(); $csv->addfield($date); $csv->addfield($source); $csv->addfield($freq); $csv->writerow(); } } $csv->close(); echo '<fieldset class="if_parameters">'; echo '<legend>Source stats</legend>'; echo '<p><a href="' . str_replace("#", urlencode("#"), str_replace("\"", "%22", $filename)) . '">' . $filename . '</a></p>'; echo '</fieldset>';
</script> </head> <body> <h1>TCAT :: Export hashtags</h1> <?php validate_all_variables(); $filename = get_filename_for_export('hashtagExport'); $csv = new CSV($filename, $outputformat); $csv->writeheader(array('tweet_id', 'hashtag')); $sql = "SELECT t.id as id, h.text as hashtag FROM " . $esc['mysql']['dataset'] . "_tweets t, " . $esc['mysql']['dataset'] . "_hashtags h "; $sql .= sqlSubset(); $sql .= " AND h.tweet_id = t.id ORDER BY id"; $sqlresults = mysql_unbuffered_query($sql); $out = ""; if ($sqlresults) { while ($data = mysql_fetch_assoc($sqlresults)) { $csv->newrow(); $csv->addfield($data['id'], 'integer'); $csv->addfield($data['hashtag'], 'string'); $csv->writerow(); } mysql_free_result($sqlresults); } $csv->close();