Query Twitter Search API for specific keywords in specific geolocation and insert into MySQL table

twitter_indicators.php

<?php

/*This script hits the Twitter Search API for a specific set of keywords in a specific geolocation, takes and stores those **tweets in a MySQL table
**Note that this page is not public facing, outputs raw data for debugging

**If you want to store the data, and get up and running create MySQL table with following structure

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment | 
| user  | varchar(30)  | YES  |     | NULL    |                | 
| text  | varchar(140) | YES  |     | NULL    |                | 
| twid  | varchar(30)  | YES  |     | NULL    |                | 
| geo   | varchar(30)  | YES  |     | NULL    |                | 
| loc   | varchar(30)  | YES  |     | NULL    |                | 
| date  | varchar(50)  | YES  |     | NULL    |                | 
+-------+--------------+------+-----+---------+----------------+
*/


// This first bit creates a connection with the existing MySQL database table and checks for duplicates
mysql_connect("path", "user", "pass") or die(mysql_error());
mysql_select_db("db") or die(mysql_error());

$tweet_archive = mysql_query("SELECT twid FROM twindicators");

while($check = mysql_fetch_assoc($tweet_archive)){
    $column[] = $check['twid'];
}

print_r ($column);

echo '<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />';

/*URL request string. To add more keywords enter keywords divided by %20OR%20
**additional parameters can be added following the ampersand &
*/

$info_url = "http://search.twitter.com/search.json?q=keyword&geocode=your_lat,your_long,2mi&rpp=100";

$raw_data = file_get_contents(  $info_url);

$data = json_decode(  $raw_data);

$count = count( $data->results);

//prints out data in PHP array for debugging and development

print '<pre>';

print_r ( $data);

print '</pre>';

//iterate through array and insert non-duplicate entries into MySQL table

for ($i = 0; $i < $count; $i++) {

if (in_array($data->results[$i]->id_str, $column)) {

 echo "no new data";
}
 
else {

$text = urlencode( $data->results[$i]->text);

mysql_query('INSERT INTO twindicators (user, text, twid, geo, loc, date) VALUES("' . $data->results[$i]->from_user_name . '","' . $text . '","' . $data->results[$i]->id_str . '","' . $data->results[$i]->geo->coordinates[0] . '","' . $data->results[$i]->location . '","' . $data->results[$i]->created_at . '" ) ') or die(mysql_error()); 


}

}

?>