PHP Download Twitter Followers and Save in MySQL Database

Regular readers will know I created a TwitterBot and documented it here. The biggest problems with the system which retweets according to search criteria are two-fold:

  1. It can be hijacked by the malicious / nefarious for sending off-message tweets to the entire follower base
  2. The retweet facility is quite promiscuous and anyone mentioned in a tweet will also see the retweet in their interactions, and they will get a mention email to the email account they used to register on Twitter. This can conceivably be considered spam.

In fact, there's no two ways about it, Twitter do consider this a breach of their terms and conditions and it is ranked as egregious tweeting. As such, it is more than likely that any account indulging in such behaviour will be suspended. My twitterbot has now been suspended twice, so it's fair to say I've been flirting with a permanent ban.

What's the solution? Well, it's purely guesswork, but my feeling by adopting more cautious strategies with the retweeting, I can avoid further suspensions. To achieve this, I'm going for the following:

  1. I will only retweet any tweets originated from my followers list which I will store on my server
  2. I will only renew the aforementioned followers list every few days - so it acts as a buffer to prevent it being hijacked and immediately used
  3. I will remove any non-followers as recipients from the retweets

Ok - so the first activity is to save a local (and fairly static) list of my followers on my server. This will be the list I will use when determining whether to to retweet or not.

Firstly we need a database schema to do this. I have created two new tables - one for storing followers and one for storing a log of events
database schema

-- MySQL dump 10.13  Distrib 5.5.28, for Linux (x86_64)
--
-- Host: localhost    Database: xxxxxxxxxx
-- ------------------------------------------------------
-- Server version 5.5.28-log


--
-- Table structure for table `log`
--

DROP TABLE IF EXISTS `log`;

CREATE TABLE `log` (
  `lid` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(256) NOT NULL,
  `created_dt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`lid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


--
-- Table structure for table `followers`
--

DROP TABLE IF EXISTS `followers`;

CREATE TABLE `followers` (
  `twitterid` bigint(20) NOT NULL,
  `name` varchar(16) NOT NULL,
  `update_dt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `twitterid` (`twitterid`),
  UNIQUE KEY `twitterid_2` (`twitterid`,`name`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Dump completed on 2013-04-16 22:24:34

The PHP followers class works on the principle of downloading all the followers from Twitter and then updating the database for each of the Twitter followers with a timestamp. If the timestamp is before the update commenced, then clearly that follower has unfollowed and is therefore deleted.
followers.php

<?php
// Copyright @badzillacouk <a href="http://www.badzilla.co.uk
//" title="http://www.badzilla.co.uk
//">http://www.badzilla.co.uk
//</a> Licence GPL. This program may be distributed as per the terms of GPL and all credits
// must be retained
//
// If you find this script useful, please consider a donation to help me fund my web presence
// and encourage me to develop more products to be placed under the terms of GPL
// To donate, go to <a href="http://www.badzilla.co.uk" title="http://www.badzilla.co.uk">http://www.badzilla.co.uk</a> and click on the donation button
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

require_once 'config.php';
require_once
'log.php';




class
Followers {
   
    public
$db_dsn;
    public
$db_user;
    public
$db_password;
    public
$db_pdo;


    public function
__construct($db_dsn, $db_user, $db_password) {

       
$this->db_dsn = $db_dsn;
       
$this->db_user = $db_user;
       
$this->db_password = $db_password;

        try {
           
$this->db_pdo = new PDO($this->db_dsn, $this->db_user, $this->db_password);
        } catch (
PDOException $e) {
            echo
'Connection failed: ' . $e->getMessage();
            exit(
1);
        }

    }



   
/**
    * getFollowers - main loop to download list of followers and save in database
    * @param $user_account - name of the account
    * @return no return value
    */
   
public function getFollowers($user_account) {
       
       
$start = time();

       
$log = new log($this->db_pdo);

       
$cursor = -1;
       
$followers = array();

       
// Main loop for download
       
do {
           
$json = file_get_contents('http://api.twitter.com/1/statuses/followers/' . $user_account . '.json?cursor=' . $cursor);
           
$accounts = json_decode($json);
            foreach (
$accounts->users as $account)
               
$followers[$account->id_str] = $account->screen_name;

           
$cursor = $accounts->next_cursor;

        } while (
$cursor > 0);

       
$log->logTwitterEvent('getFollowers downloaded ' . count($followers) . ' followers');

       
// Now save in the database
       
foreach($followers as $key => $value) {

           
// Do we update or do we insert?
           
$res = $this->db_pdo->prepare('SELECT COUNT(*) FROM followers WHERE twitterid = :id');
           
$res->execute(array('id' => $key));
           
$rows = $res->fetch(PDO::FETCH_NUM);

            if (!
$rows[0])
               
$this->db_pdo->prepare('INSERT INTO followers SET twitterid = :id, name = :name, update_dt = NOW()')
                        ->
execute(array(':id' => $key, ':name' => $value));
            else
               
$this->db_pdo->prepare('UPDATE followers SET name = :name, update_dt = NOW() WHERE twitterid = :id')
                        ->
execute(array(':id' => $key, ':name' => $value));
        }

       
// Delete all those that haven't been updated - they are no longer following
       
$this->db_pdo->prepare('DELETE FROM followers WHERE update_dt < FROM_UNIXTIME(:time)')
                ->
execute(array(':time' => $start)); 

       
$log->logTwitterEvent('getFollowers completed db write');     
       
    }

}




$obj = new Followers($db_dsn, $db_user, $db_password);
$obj->getFollowers($account_user);
?>

log.php

<?php
// Copyright @badzillacouk <a href="http://www.badzilla.co.uk
//" title="http://www.badzilla.co.uk
//">http://www.badzilla.co.uk
//</a> Licence GPL. This program may be distributed as per the terms of GPL and all credits
// must be retained
//
// If you find this script useful, please consider a donation to help me fund my web presence
// and encourage me to develop more products to be placed under the terms of GPL
// To donate, go to <a href="http://www.badzilla.co.uk" title="http://www.badzilla.co.uk">http://www.badzilla.co.uk</a> and click on the donation button
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.




class log {
   
    public
$db_pdo;

    public function
__construct($db_pdo) {
       
       
$this->db_pdo = $db_pdo;
    }


   
/**
    * logTwitterEvent - log a Twitter database event
    * @param $description - text to add to the db
    * @return no return value
    */
   
public function logTwitterEvent($description) {
       
       
$this->db_pdo->prepare('INSERT INTO log SET description = :desc, created_dt = NOW()')
                      ->
execute(array(':desc' => $description));     
    }
}
?>

Anyone looking for the config.php file should cast their eyes to my original PHP-Retweeting-Twitter-Bot-Using-Streaming-API-Search-Results post.



It's not working because API v1 is no longer functional

Followers.php script is not working because API v1 is no longer functional.

Warning: Invalid argument supplied for foreach()

at line "foreach ($accounts->users as $account)"

I put some code to show $accounts content

echo '';
print_r($accounts);
echo '';

And the result is:

stdClass Object
(
[errors] => Array
(
[0] => stdClass Object
(
[message] => Bad Authentication data
[code] => 215
)

)

)

apparently authentication is necessary to get followers using API v1.1

If anybody knows how to fix that, please post the solution

Regards,
Luis

badzilla's picture

that's true...

The underlying PHP libraries contributed by others that have been used in my examples need a little work to make the solution work. I have added the authentication and it works fine for me.

This tutorial is intended to be a starting point for PHP developers - you will need to add your own authentication.