Data plays an important role in an online business. People are always looking for data in order to build their business strategies. Twitter is a hub for data about what’s happening around the world, public sentiments about your business, identifying trends, discovering insights, etc. Your job is to fetch tweets particular to your interest or business and organize them. The better way of organizing such data is to store them offline. That’s the purpose of this tutorial. I am going to show you how to fetch and store tweets in Excel using PHP. Once you store it physically, you can utilize this information the way you wish.
When I say tweets, it can be anything related to your business. It may contain interesting keywords, mentions about your brands or hashtags. Twitter allows us to build a search query that will avoid the noise of unnecessary tweets. By this, you get only relevant information and use it to build your strategy or whatever you call it.
Fetching tweets requires having the access token of a Twitter account. One can generate the access token of Twitter by building either OAuth flow or application-only-authentication. I already explained about OAuth in the previous tutorial – How to Integrate Twitter OAuth 2.0 in PHP.
Here, as we need read-only access to publicly available tweets, I’m going to use the second approach of application-only-authentication. It gives the ability to issue authenticated requests on behalf of the application itself without authenticating the specific user.
Get Consumer Key and Consumer Secret
In order to fetch the access token, you need to get your consumer key and consumer secret by following the below steps.
- Sign up for a Twitter developer account.
- Create a Twitter App within a developer portal.
- The Callback URL can be left blank as it is not required.
- Copy the consumer key and consumer secret.
Build a Search Query
Twitter has a well-documented resource for building a search query. We have to pass the query via GET request to the Twitter endpoint which in response gives the historical tweets matching the search query. Here, I am giving you a few examples of a search query.
WordPress: It gives you tweets that contain a string WordPress.@WordPress: Get tweets where it mentions @WordPress.#WordPress: Get tweets where it contains #WordPress.@WordPress #WordPress: Only fetch tweets that have both mentions and hashtags.@WordPress OR #WordPress: Fetch tweets that have either mentions or hashtags.WordPress lang:en: It will return tweets written in the English language containing a keyword.WordPress -is:retweet: Exclude the retweets and return only original tweets having a keyword.
These are some of the queries one can use to fetch relevant tweets. I would recommend reading the documentation to get a better understanding of how to build a search query.
Installing PhpSpreadsheet
The goal of the topic is to save tweets into an Excel file. For this, let’s install a popular PhpSpreadsheet library to your project. Head over to the terminal and run the command below.
composer require phpoffice/phpspreadsheet
We’ll write a code to add tweets into Excel in the next part of the tutorial.
Here, I am taking Excel as an option. In your case, you can choose any suitable service. You might save it to a database, Google Sheets, OneDrive Sheets, etc.
I’ll use the Guzzle library to send HTTP requests to the Twitter API and handle the response. Install the library using the command:
composer require guzzlehttp/guzzle
Download Tweets into Excel
You are ready with API keys and the PhpSpreadsheet library. It means we can now interact with the Twitter API endpoints. Now to fetch relevant tweets I am taking an example of mentions and hashtags. For instance, I want to fetch original tweets containing @WordPress and #WordPress. I also want to filter tweets using the language parameter which is English. So my search string would be
@WordPress #WordPress lang:en -is:retweet
The PHP code below will call the Twitter API’s search endpoint, fetch tweets matching a query, write them to Excel, and send Excel to the browser.
Note: At a time, you can ask for tweets ranging from 10-100 using the max_results parameter. For getting the next set of records you have to use the next_token parameter received in a response.
<?php
require_once "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
define("CONSUMER_KEY", "");
define("CONSUMER_SECRET", "");
function get_app_only_access_token() {
$client = new GuzzleHttp\Client([
'base_uri' => 'https://api.twitter.com',
]);
$response = $client->request('POST', '/oauth2/token', [
"headers" => [
"Authorization" => "Basic ". base64_encode(CONSUMER_KEY.":".CONSUMER_SECRET),
"Content-Type" => "application/x-www-form-urlencoded;charset=UTF-8"
],
"form_params" => [
"grant_type" => "client_credentials"
],
]);
$res = json_decode($response->getBody());
return $res->access_token;
}
$search_string = "@WordPress #WordPress lang:en -is:retweet"; // pass search string here
save_tweets($search_string);
function save_tweets($search_string) {
$access_token = get_app_only_access_token();
$search_string = "$search_string";
try {
$client = new GuzzleHttp\Client([
'base_uri' => 'https://api.twitter.com',
]);
$response = $client->request("GET", "/2/tweets/search/recent", [
"headers" => [
"Authorization" => "Bearer ". $access_token
],
'query' => [
'query' => $search_string, // search keyword
'tweet.fields' => 'created_at',
'expansions' => 'author_id',
'max_results' => 10, // can be between 10 - 100
]
]);
$res = json_decode($response->getBody());
$arr_tweets = [];
if ( $res && !empty($res->data) ) {
$arr_users = $res->includes->users;
foreach ( $res->data as $r ) {
$key = array_search($r->author_id, array_column($arr_users, 'id'));
array_push($arr_tweets, [
'date' => date('Y-m-d H:i', strtotime($r->created_at)),
'screen_name' => $arr_users[$key]->username,
'full_name' => $arr_users[$key]->name,
'author_link' => 'https://twitter.com/'.$arr_users[$key]->username,
'tweet_text' => $r->text,
'tweet_id' => $r->id,
'tweet_link' => 'https://twitter.com/'.$arr_users[$key]->username.'/status/'.$r->id,
]);
}
// Write Tweets to Excel
$spreadsheet = new Spreadsheet();
$Excel_writer = new Xlsx($spreadsheet);
$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet();
$activeSheet->setCellValue('A1', 'Date');
$activeSheet->setCellValue('B1', 'Screen Name');
$activeSheet->setCellValue('C1', 'Full Name');
$activeSheet->setCellValue('D1', 'Author Link');
$activeSheet->setCellValue('E1', 'Tweet Text');
$activeSheet->setCellValue('F1', 'Tweet ID');
$activeSheet->setCellValue('G1', 'Tweet Link');
if(!empty($arr_tweets)) {
$i = 2;
foreach($arr_tweets as $row) {
$activeSheet->setCellValue('A'.$i , $row['date']);
$activeSheet->setCellValue('B'.$i , $row['screen_name']);
$activeSheet->setCellValue('C'.$i , $row['full_name']);
$activeSheet->setCellValue('D'.$i , $row['author_link']);
$activeSheet->setCellValue('E'.$i , $row['tweet_text']);
$activeSheet->setCellValueExplicit('F'.$i , $row['tweet_id'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$activeSheet->setCellValue('G'.$i , $row['tweet_link']);
$i++;
}
}
$filename = 'tweets.xlsx';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='. $filename);
header('Cache-Control: max-age=0');
$Excel_writer->save('php://output');
}
} catch(Exception $e) {
echo $e->getMessage();
}
}From the above code, along with tweets I also fetched extra information. In the final excel, you will get values of Tweets info(text, id, link, date) and Author’s info(screen name, full name, profile link). You might fetch much more information like followers, retweets, and verified users by calling the appropriate endpoint.
Below is the screenshot of the Excel generated using the above code.
I hope you may have learned how to write code to download tweets into Excel using PHP. I would like to hear your thoughts and suggestions in the comment section below.
If you liked this article, then please subscribe to our YouTube Channel for video tutorials.