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


/ Published in: PHP

[Related SQL query to create the storage table](http://snipplr.com/view/56995/sql-query-to-create-a-table-in-mysql-to-store-tweets/ "Related SQL query to create the storage table")


Copy this code and paste it in your HTML
  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

You need to login to post a comment.