/
TwitterDB.php
145 lines (130 loc) · 4.33 KB
/
TwitterDB.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
<?php
class TwitterDB {
function getDBConnection() {
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "tweets";
$tweets = array();
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
mysqli_set_charset($conn, 'utf8mb4');
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
return $conn;
}
function closeDBConnection($conn) {
$conn->close();
}
function getRetweetCountAndText() {
$sql = "SELECT text,retweetCount FROM `tweet` AS t INNER JOIN tweetpopularity AS tp ON t.tweetId = tp.tweetId WHERE NOT tp.retweetCount = 'NULL' AND tp.retweetCount > 1000 AND tp.retweetCount <5000 LIMIT 10 ";
$twitterDB = new TwitterDB();
$conn = $twitterDB->getDBConnection();
$result = $conn->query($sql);
$tweets = array();
if ($result->num_rows > 0) {
// output data of each row
$i = 0;
while($row = $result->fetch_assoc()) {
$tweets[$i]["text"] = htmlentities($row["text"]);
$tweets[$i]["retweetCount"] = $row["retweetCount"];
settype($tweets[$i]["retweetCount"], "integer");
$i++;
}
}
$twitterDB->closeDBConnection($conn);
return $tweets;
}
function getStatusSource() {
$sql = "SELECT statusSource, COUNT(statusSource) AS statusCount FROM `tweetsource` GROUP BY statusSource DESC";
$twitterDB = new TwitterDB();
$conn = $twitterDB->getDBConnection();
$result = $conn->query($sql);
$tweets = array();
if ($result->num_rows > 0) {
// output data of each row
$i = 0;
while($row = $result->fetch_assoc()) {
$regex = '#<\s*?a\b[^>]*>(.*?)</a\b[^>]*>#s';
$code = preg_match($regex, $row["statusSource"], $matches);
$tweets[$i]["statusSource"] = $matches[1];
$tweets[$i]["statusCount"] = $row["statusCount"];
settype($tweets[$i]["statusCount"], "integer");
$i++;
}
}
$twitterDB->closeDBConnection($conn);
return $tweets;
}
function getTweetsOfDifferentDays() {
$sql = "SELECT SUBSTRING(created,1,11) AS created, count(created) AS createdCount FROM tweet GROUP BY SUBSTRING(created,1,11)";
$twitterDB = new TwitterDB();
$conn = $twitterDB->getDBConnection();
$result = $conn->query($sql);
$tweets = array();
if ($result->num_rows > 0) {
// output data of each row
$i = 0;
while($row = $result->fetch_assoc()) {
$created = explode(" ", $row["created"]);
$tweets[$i]["created"] = "Date : " . $created[0] . "\t Time : " . $created[1] . " AM/PM";
$tweets[$i]["createdCount"] = $row["createdCount"];
settype($tweets[$i]["createdCount"], "integer");
$i++;
}
}
$twitterDB->closeDBConnection($conn);
return $tweets;
}
function getMaxRetweetCount() {
$sql = "SELECT MAX(tweetpopularity.retweetCount) AS MaxRetweetCount, tweet.text from tweet
INNER JOIN tweetpopularity ON tweet.tweetId = tweetpopularity.tweetId GROUP BY tweet.text
ORDER BY MaxRetweetCount DESC LIMIT 10 OFFSET 10";
$twitterDB = new TwitterDB();
$conn = $twitterDB->getDBConnection();
$result = $conn->query($sql);
$tweets = array();
if ($result->num_rows > 0) {
// output data of each row
$i = 0;
while($row = $result->fetch_assoc()) {
$tweets[$i]["text"] = $row["text"];
$tweets[$i]["MaxRetweetCount"] = $row["MaxRetweetCount"];
settype($tweets[$i]["MaxRetweetCount"], "integer");
$i++;
}
}
$twitterDB->closeDBConnection($conn);
return $tweets;
}
function getFavoriteCount() {
$sql = "SELECT tweet.text, tweetpopularity.favoriteCount FROM `tweetpopularity`
INNER JOIN tweet ON tweetpopularity.tweetId = tweet.tweetId
where favoriteCount != 0 group by favoriteCount limit 10 offset 10";
$twitterDB = new TwitterDB();
$conn = $twitterDB->getDBConnection();
$result = $conn->query($sql);
$tweets = array();
if ($result->num_rows > 0) {
// output data of each row
$i = 0;
while($row = $result->fetch_assoc()) {
$tweets[$i]["text"] = $row["text"];
$tweets[$i]["favoriteCount"] = $row["favoriteCount"];
settype($tweets[$i]["favoriteCount"], "integer");
$i++;
}
}
$twitterDB->closeDBConnection($conn);
return $tweets;
}
function printArray($arr) {
echo "<pre>";
print_r($arr);
echo"</pre>";
die;
}
}
?>