Simple Twitter Auto Poster

This is the first in what will end up being a several part tutorial on creating a Twitter posting program.  These tutorials will show how to create the bot using the PHP library that interacts with Twitter’s API, called TwitterOAuth.  It’s by far the most used PHP based Twitter API library.  You can use this example anywhere, but the queries you will see are specific to the WordPress database schema.  In the examples I’m demonstrating how to auto Tweet your WordPress posts.

Alright, lets get started.  First login to your server using SSH and navigate to the directory you want this project to reside.  Then run this command:

composer require abraham/twitteroauth

If you are not using composer, learn it now.  It will save you a ton of time over the long run.

Once that command finishes running open up your project either on FTP, or an IDE, I prefer PHPStorm.

Inside the folder where you ran the composer command you will see a folder called “vendor” and a few other files.  You don’t need to modify any of these.  Go ahead and create a new file, called whatever you like, mine for example is post.php.  Once you have that file open it up and paste in the following code:

<?php
//Show any errors for debugging.
error_reporting(E_ALL);
ini_set('display_errors', 1);

//Load TwitterOAuth Library
require "vendor/autoload.php";
use Abraham\TwitterOAuth\TwitterOAuth;

//Enter in your app keys and secrets
$consumerKey = 'your consumer key';
$consumerSecret = 'your consumer secret';
$accessToken = 'your access token';
$accessTokenSecret = 'your access token secret';

//Connect to Twitter API
$connection = new TwitterOAuth($consumerKey,$consumerSecret,$accessToken,$accessTokenSecret);
$content = $connection->get("account/verify_credentials");

//Connect to WordPress Database
$db = new mysqli('hostname','database user name','database user password','database name');

//Query the needed data
$sql = "select p.ID as id, post_title as postTitle, guid as url
        from wp_posts p
        where p.post_type = 'post'  
        and p.post_status = 'publish'
        and p.ID not in (select postID from twitterPublished)
        limit 1";
$result = $db->query($sql);
while($row = $result->fetch_assoc()) {
    $postID = $row['id'];
    $postTitle = $row['postTitle'];

    //Replace any special characters with the correct web formatted characters
    $postTitle = str_replace(
       array("\xe2\x80\x98", "\xe2\x80\x99", "\xe2\x80\x9c", "\xe2\x80\x9d", "\xe2\x80\x93", "\xe2\x80\x94", "\xe2\x80\xa6"),
       array("'", "'", '"', '"', '-', '--', '...'),
      $postTitle);
    $postTitle = str_replace(
      array(chr(145), chr(146), chr(147), chr(148), chr(150), chr(151), chr(133)),
      array("'", "'", '"', '"', '-', '--', '...'),
      $postTitle);
    //Add the post title and the URL to share together
    $statusText = $postTitle.' '.$row['url'];
    
    //Send Tweet to Twitter
    $statues = $connection->post("statuses/update", ["status" => $statusText]);

    //Insert postID so we don't try to Tweet the same thing again
    $sqlInsert = "insert into twitterPublished(postID)
                  values('$postID')";
    $db->query($sqlInsert);

    //Output Response
    var_dump($statues);

}

There are few variables in there that you will need to update with your own.

Lines 11-14 you will need to update with your consumerKey, consumerSecret, accessToken, and accessTokenSecret.  You can get these by setting up your application on Twitters website.  You can go here to join: https://dev.twitter.com/.  If you already have an account you can go here to login: https://apps.twitter.com/.

Once logged in, select “Create New App”.

You need to fill out the name, description, website, and agree to the developer agreement.  You do not need to enter a Callback URL for this application.

Once you click to create the application under those fields you will see your new app.  Click the tab for Keys and Access Tokens.

From here you can get all of your keys and secrets as needed.  If you can’t find your Access Token and Access Token Secret just click the button to generate them.

Now, once you’ve updated all of the keys in your post.php file from above, next you need to figure out your database name, database user, password, and host name.  We’ll update these on line 21 of the file.

Now you’re almost all finished up, but on line 51 you will notice a comment about what that insert does.  It keeps a record of what you have already Tweeted so you don’t have to worry about duplication in the future.  It uses a table I called twitterPublished.  So you will need to create that table in your database.  Log in to phpMyAdmin and run this query on the SQL tab in your selected database to create that table:

-- phpMyAdmin SQL Dump
-- version 4.6.6
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Aug 06, 2017 at 03:37 PM
-- Server version: 10.1.25-MariaDB
-- PHP Version: 5.6.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `thenet_wordpress`
--

-- --------------------------------------------------------

--
-- Table structure for table `twitterPublished`
--

CREATE TABLE `twitterPublished` (
  `id` int(11) NOT NULL,
  `postID` int(11) NOT NULL,
  `twitterID` int(11) NOT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `twitterPublished`
--
ALTER TABLE `twitterPublished`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `twitterPublished`
--
ALTER TABLE `twitterPublished`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Now you’re all set to test this out.  Open up a broswer window and type in the url that will lead you to your new post.php file.  You will either get an error printed on the screen or receive a response of code showing that the post successfully posted.  Go to your Twitter page and take a look, you should see your newly create Tweet.  If you receive an error, feel free to comment and I can help you resolve it, but if you followed the tutorial and have the correct credentials it should work just fine.

Now one other note, I already had about 1200 posts in my WordPress database, I didn’t want to start Tweeting old stuff.  If you’re in the same boat, here is a query to add all of the existing postID’s to the twitterPublished table so that they will not be Tweeted.  Of course always make backups of your database before running commands like this, better yet do it in a development environment 🙂

insert into twitter_published(postID)
select ID
from wp_posts
where post_type = 'post'
and post_status = 'publish'

Lastly, you will see on line 29 we are limiting the query to only return one result.  I scheduled this to run every 5 minutes, so if there are new posts that have not been Tweeted it will send them out every 5 minutes until there are no new posts.  I limit it to 1 per every 5 minutes as I don’t want to send for example 10 new Tweets all at the same time.  You can tweak the limit here if you want to do more and also modify your cron schedule to however many minutes/hours to get the desired results.

That’s all for now.  Next week I’ll continue working on this more by building out a GUI interface for setting all of this up for those of you that don’t know how to or don’t want to do this via code.

Questions or comments? Ask them here in our forum discussion.