advanced code snippet search

Posted By

eddequincey on 07/21/11


Tagged

mysql php api twitter


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

freaktheclown


Save tweets using PHP from the twitter search API to a MySQL database

 / Published in: PHP
 

Related SQL query to create the storage table

Expand | Embed | Plain Text 
  1. <?php
  2.  
  3. $q = $_GET["q"]; // query string
  4. $request = "http://search.twitter.com/search.json?q=".urlencode($q);
  5. $response = file_get_contents($request);
  6. $jsonobj = json_decode($response);
  7.  
  8. if($jsonobj != null){
  9.  
  10. 	$con = mysql_connect('host', 'username', 'password');
  11.  
  12. 	if (!$con){
  13. 		die('Could not connect: ' . mysql_error());
  14. 	}
  15.  
  16. 	foreach($jsonobj->results as $item){
  17.  
  18. 		$id = $item->id;
  19. 		$created_at = $item->created_at;
  20. 		$created_at = strtotime($created_at);
  21. 		$mysqldate = date('Y-m-d H:i:s',$created_at);
  22. 		$from_user = mysql_real_escape_string($item->from_user);
  23. 		$from_user_id = $item->from_user_id;
  24. 		$text = mysql_real_escape_string($item->text);
  25. 		$source = mysql_real_escape_string($item->source);
  26. 		$geo = $item->geo;
  27. 		$iso_language_code = $item->iso_language_code;
  28. 		$profile_image_url = mysql_real_escape_string($item->profile_image_url);
  29. 		$to_user_id = $item->to_user_id;
  30. 		if($to_user_id==""){ $to_user_id = 0; }
  31. 		$query = mysql_real_escape_string($query);
  32.  
  33. 		mysql_select_db("database", $con);
  34.                 // SQL query to create table available at http://snipplr.com/view/56995/sql-query-to-create-a-table-in-mysql-to-store-tweets/
  35. 		$query = "INSERT into tweets VALUES ($id,'$mysqldate','$from_user',$from_user_id,'$text','$source','$geo','$iso_language_code','$profile_image_url',$to_user_id,'$q')";
  36. 		$result = mysql_query($query);
  37.  
  38. 	}
  39.  
  40. 	mysql_close($con);
  41. }
  42.  
  43. ?>

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: skavhellen on December 4, 2011

Can't seem to get this one to work with the database. I get now error and no data in the database.

I've configured it with my own database information but nothing happens when I open this file in a browser window. Am I missing something?

Hope for some help here :)

Posted By: JustinN on March 12, 2012

@skavhellen - which version of MySQL have you got installed? You need 5.2 or above as this script makes use of the json_decode function.

Posted By: kso on August 7, 2012

Dear,

 plz advice why i'm getting this error ?

Warning: Invalid argument supplied for foreach() in /home/kso/publichtml/gettweet.php on line 27

please find my code and let me know where is the mistake.

apprecite your feedback....

$username='hangover_2011'; // set user name $format='json'; // set format

$q = $GET["q"]; // query string $request = "http://api.twitter.com/1/statuses/usertimeline/{$username}.{$format}".urlencode($q); $response = filegetcontents($request); $jsonobj = json_decode($response);

if($jsonobj != null){

$con = mysql_connect($host, $userdname, $password);

if (!$con){
    die('Could not connect: ' . mysql_error());
}

foreach ($jsonobj->results as $status) {

 $text = mysql_real_escape_string($item->text);
 $created_at = $status->created_at;
 $created_at = strtotime($created_at);
 $mysqldate = date('Y-m-d H:i:s',$created_at);    
 $profile_image_url = mysql_real_escape_string($item->profile_image_url);

 $screen_name = $status->user->screen_name;

 $query = mysql_real_escape_string($query);

    mysql_select_db($database, $con);
            // SQL query to create table available at http://snipplr.com/view/56995/sql-query-to-create-a-table-in-mysql-to-store-tweets/

    $query = "INSERT into tweets (`tweet_id`,`text`,`screen_name`,`created_at`,`q`) VALUES ('','$text','$screen_name',' $created_at','$q')";

    $result = mysql_query($query);

}

mysql_close($con); }

You need to login to post a comment.