SEO Warrior Keyword DashBoard Update

Social networking automation can be helpful. What if you wanted to schedule your Tweets? If you are implementing a strategy for Twitter, instead of worrying about the steady flow of your tweets, you can create a simple application to send pre-recorded (scheduled) tweets. Imagine a simple application as described in the use case diagram below:
 

Creating the Database

We can use PHP and MySQL to create a simple Twitter scheduler application to send your tweets. To create the application we will use the single database table (queue) to store your tweets. Here is how the ‘queue’ table will look like when created in MySQL:

+———+——–+——+—–+———+—————-+
| Field   | Type   | Null | Key | Default | Extra          |
+———+——–+——+—–+———+—————-+
| id      | int(6) | NO   | PRI | NULL    | auto_increment |
| message | text   | NO   |     | NULL    |                |
| status  | int(1) | NO   |     | 0       |                |
+———+——–+——+—–+———+—————-+

The queue table will contain only the three fields ‘id’, ‘message’, and the ‘status’ field. The ‘id’ (integer) field auto-increments by default. The ‘message’ (text) field contains your tweets. The ‘status’ (integer) field indicates whether or not the tweet was sent. The status value of 0 indicates tweet ‘not sent’ condition. The status value of 1 indicates ‘tweet sent’ condition. The following fragment is the SQL fragment required to create the ‘queue’ table:

CREATE TABLE `mytest`.`queue` (
`id` INT( 6 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`message` TEXT NOT NULL ,
`status` INT( 1 ) NOT NULL DEFAULT ‘0’
) ENGINE = MYISAM ;

With the database out of the way, we can create a simple HTML/PHP interface.

Building the Interface

All we need for our interface is a simple HTML form that submits (future) tweets to our MySQL database. It would also be nice to have a visible list of (future) tweets already stored in the database. Last and not the least, there should be a way to delete unwanted tweets. The following Figure shows the index.php when rendered in a web browser.

As you can see in the preceding figure, the index page is relatively simple. There is a database query that is always called (to show stored tweets) just below the new (tweet entry) form. 

When you click on the ‘Add Future Tweet’ button, the form submits the new tweet to database returning you back to the same screen as your newly added tweet appears on the top of the (‘My Future Tweets’) list.

Sending the Tweets

We can send Twitter updates using the Twitter API. We can use PHP and curl to send our tweet using the http://twitter.com/statuses/update.xml link. The following code fragment (sendTweet.php) is used to send a single tweet.

<?php

include(“config.php”);
mysql_connect(localhost,$username,$password);
@@mysql_select_db($database) or die( “Unable to select my database”);

### get the tweet
$result = mysql_query(“select id, message from queue where status=0 order by id asc LIMIT 1”);
$row = mysql_fetch_array($result);

### send the tweet
$curl_handle = curl_init();
curl_setopt($curl_handle, CURLOPT_URL, “$tURL”);
curl_setopt($curl_handle, CURLOPT_CONNECTTIMEOUT, 2);
curl_setopt($curl_handle, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($curl_handle, CURLOPT_POST, 1);
$message = $row[‘message’];
curl_setopt($curl_handle, CURLOPT_POSTFIELDS, “status=$message”);
curl_setopt($curl_handle, CURLOPT_USERPWD, “$tusrid:$tpasswd”);
$response = curl_exec($curl_handle);
curl_close($curl_handle);

// get the status message
if (empty($response)) {
   echo ‘tweet not delivered’;
} else {
   echo ‘tweet delivered’;
   ###update db status
   $mid = $row[‘id’];
   mysql_query(“UPDATE queue SET status = 1 WHERE id = $mid”);
}
mysql_close();
?>

We use a POST request coupled with the user credentials necessary to handle the basic authentication used at http://twitter.com/statuses/update.xml. Note that this link is stored in an external configuration file and is referenced by $tURL variable.
Scheduling Your Tweets

The web part of the application does not submit the tweet(s). We leave that to a scheduled job. Now we come to the part that actually initiates the tweet uploads. On a Linux flavored environment you can use crontab to create your schedule.

Let’s suppose that you want to send 5 tweets per day at 7am, 9am, 11am, 1pm and 3pm. Executing crontab –e to edit your crontab list, enter the following:

# Tweet 5 times a day at 7am, 9am, 11am, 1pm and 3pm
* 8,10,12,14,16 * * * php sendTweet.php

Note that you can also send tweets manually by using the same command (php sendTweet.php). in your shell window.

Extending Your Application

The application we have built can be extended to do many more things. You can add custom authentication, the workflow approval process, RSS integration, etc.

Click here to Download the entire Twitter Scheduler Code.

Enjoy!

Tags: schedule tweets, SMO, social media strategy, tweets, twitter