Suppose you have quite a number of decent posts in your blog and you are wondering that if there is a way to post to twitter/facebook (or other social networks) automatically and regularly.
This is not difficult. Although this post will guide you to post to twitter but the principle is similar for other social networks. This post introduces the PHP function to post to twitter.
We don’t want to post the same content/articles again and again within a short period of time. Therefore, we need to create a SQL table that stores the history of the posts re-published.
-- -- Table structure for table `twitter` -- CREATE TABLE IF NOT EXISTS `twitter` ( `id` bigint(32) unsigned NOT NULL, `url` varchar(255) NOT NULL, `posttime` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
The table has three fields, one is the primary key (auto increment), the second field is the URL location that has been re-published and the third one is the date and time.
All fields should be properly indexed.
ALTER TABLE `twitter` ADD PRIMARY KEY (`id`), ADD KEY `url` (`url`,`posttime`), ADD KEY `posttime` (`posttime`);
Now, if you have a wordpress, you need to query the valid posts and randomly pick one.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | // database constants require('db.php'); // twitter function require('twitter.php'); mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die(mysql_error()); mysql_select_db(DB_NAME); $today = date("Y-m-d h:i:s"); // get total number of valid posts $query = " select count(1) from `wp_posts` where (`post_type` = 'page' or `post_type` = 'post') and (`post_status` = 'publish') "; $result = mysql_query($query) or die(mysql_error()); $total = mysql_result($result, 0, 0); $cnt = 0; if ($total > 0) { while (1) { // pick a random npost $idx = mt_rand(0, $total - 1); $query = " select `post_name`, `post_title` from `wp_posts` where (`post_type` = 'page' or `post_type` = 'post') and (`post_status` = 'publish') limit $idx, 1 "; $result = mysql_query($query) or die(mysql_error()); $row = mysql_fetch_array($result); // post URL $url = "http://www.codingforspeed.com/".$row['post_name']."/"; $title = $row['post_title']; $query2 = "select date(`posttime`) from `twitter` where `url` = '$url' order by `posttime` desc limit 1"; $result2 = mysql_query($query2) or die(mysql_error()); if (mysql_num_rows($result2) > 0) { $last = mysql_result($result2, 0, 0); // last reposted date/time $diff = abs((strtotime($today) - strtotime($last)) / 24 / 3600); if ($diff <= 90) { $cnt ++; if ($cnt > 16) { // max retry time to avoid endless loop break; } continue; // try next random post } } // record the post in the table $query = " insert into `twitter` set `url` = '$url', `posttime` = '$today' "; mysql_query($query) or die(mysql_error()); $msg = "#Repost ".$title; echo "$msg $url"; postTwitter($msg, $url); break; } } |
// database constants
require('db.php');
// twitter function
require('twitter.php');
mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die(mysql_error());
mysql_select_db(DB_NAME);
$today = date("Y-m-d h:i:s");
// get total number of valid posts
$query = "
select
count(1)
from
`wp_posts`
where
(`post_type` = 'page' or `post_type` = 'post') and
(`post_status` = 'publish')
";
$result = mysql_query($query) or die(mysql_error());
$total = mysql_result($result, 0, 0);
$cnt = 0;
if ($total > 0) {
while (1) {
// pick a random npost
$idx = mt_rand(0, $total - 1);
$query = "
select
`post_name`, `post_title`
from
`wp_posts`
where
(`post_type` = 'page' or `post_type` = 'post') and
(`post_status` = 'publish')
limit $idx, 1
";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
// post URL
$url = "http://www.codingforspeed.com/".$row['post_name']."/";
$title = $row['post_title'];
$query2 = "select date(`posttime`) from `twitter` where `url` = '$url' order by `posttime` desc limit 1";
$result2 = mysql_query($query2) or die(mysql_error());
if (mysql_num_rows($result2) > 0) {
$last = mysql_result($result2, 0, 0);
// last reposted date/time
$diff = abs((strtotime($today) - strtotime($last)) / 24 / 3600);
if ($diff <= 90) {
$cnt ++;
if ($cnt > 16) { // max retry time to avoid endless loop
break;
}
continue; // try next random post
}
}
// record the post in the table
$query = "
insert into `twitter`
set
`url` = '$url',
`posttime` = '$today'
";
mysql_query($query) or die(mysql_error());
$msg = "#Repost ".$title;
echo "$msg $url";
postTwitter($msg, $url);
break;
}
}The idea here is to get a random post and check if it has been re-posted in the last 90 days (modify if necessary), if not, post it and record it in the database. Otherwise, try next random posts until a few number of times have been tried. If there are no such ‘valid’ posts found (e.g. no new articles for a long time) then it will detect this and abandon the loop.
Also check this post for getting random row using SQL.
The next thing is to properly test it and put it in the crontab e.g. once per day.
1 | 0 8 * * * php /home/justyy.lai/auto.php > /dev/null 2>&1 |
0 8 * * * php /home/justyy.lai/auto.php > /dev/null 2>&1
The message posting to twitter is customizable, and I added a hash tag “#Repost” to distinguish from other normal posts. I also link my twitter account to a facebook page, so that every time the twits are posted to Twitter, the Facebook page status is also synchronised, which is a lot easier for me.
–EOF (The Ultimate Computing & Technology Blog) —
loading...
Last Post: Tutorial 8 – C Programming in 6502 – Sprites
Next Post: How to Create a Page of Archives (Summary) for All WordPress Posts/Pages using PHP?
