Many complementary tools, sites, and services are available for most of the social media sites. It is wise to explore what can help you achieve your social media strategy. You can also automate some tasks. For example, 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 prerecorded scheduled tweets. Imagine a simple application as described in the use case diagram in the below diagram.

Creating the Database:

You can use PHP and MySQL to create a simple Twitter scheduler application to send your tweets. To create such an application, we will use a single database table (queue) to store some tweets. Here is how the queue table will look when created in MySQL:

The queue table will contain only three fields: id, message, and status. The id (integer) field auto-increments by default. The message (text) field contains your tweets. The status (integer) field indicates whether the tweet was sent. A status value of 0 indicates that a tweet was not sent. A status value of 1 indicates that a tweet was sent. Here 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. Finally, there should be a way to delete unwanted tweets. The below diagram shows how our index.php file will look when rendered in a web browser.

As you can see in the above figure, the index page is relatively simple. A database query 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 the database, bringing you back to the same screen as the screen in which your newly added tweet appears on the top of the My Future Tweets list.

Sending Tweets:

We can send Twitter updates using the Twitter API. We can use PHP and curl to send our tweet by visiting http://twitter.com/statuses/update.xml. 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 used 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 the $tURL variable.

Scheduling 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. In a Linux-flavored environment you can use crontab to create your schedule.

Let’s suppose we want to send five tweets per day at 7:00 a.m., 9:00 a.m., 11:00 a.m., 1:00 p.m., and 3:00 p.m. Execute crontab -e to edit the crontab list, and enter the following:

# Tweet 5 times a day at 7am, 9am, 11am, 1pm and 3pm

* 8,10,12,14,16 * * * php sendTweet.php

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

Extending the Application:

We can extend the application we have built to do many more things. We can add custom authentication and RSS integration, among other things.

Tags: Search Engine Marketing, Search Engine Optimization, SEM, SEO, SEO Tips, SMM, SMO, Social Media, Social Media Marketing, Social Media Optimization, Twitter, Twitter Scheduler, Web 2.0

This entry was posted on Tuesday, March 30th, 2010 at 11:40 pm and is filed under Daily SEO Tips, SEM, Social Media, Web 2.0. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.