{"id":5675,"date":"2013-04-09T08:13:34","date_gmt":"2013-04-09T08:13:34","guid":{"rendered":"http:\/\/www.hbyconsultancy.com\/?p=4992"},"modified":"2013-04-09T08:13:34","modified_gmt":"2013-04-09T08:13:34","slug":"mysql-rand-for-social-media","status":"publish","type":"post","link":"https:\/\/hbyconsultancy.com\/2013\/04\/mysql-rand-for-social-media.html","title":{"rendered":"MySQL RAND for social media"},"content":{"rendered":"
I don\u2019t usually recommend auto-posting in social media however some applications require this behavior, and there should be an ethical way of doing this. I was experimenting a security project and wanted to share a tips for mysql usage with auto-tweeting.<\/p>\n
The idea is to have twitter account which is connected to a database of pre-configured tweets, and tweeting randomly every hour or so. A pretty simple table of tweets :<\/p>\n
A Cron job that select random tweet and share it to the world :<\/p>\n Now in addition to the \u201cid, tweet\u201d, you noticed that I have added a status field which will be incremented every time the message is tweeted. And to make the behavior not annoying to followers, I had to tweak this to keep tweets during the whole day, but in the same time not repeating myself !<\/p>\n So a quick check in the database I noticed these status values : 0,2,4,2,2,0,0,1,0,1\u2026<\/p>\n Which means that some tweets have been shared 4 times and more, while others have not been tweeted at all. Let\u2019s check this on database :<\/p>\n Refresh the results few times :<\/p>\n So you can notice that selecting randomly a tweet that have not being broadcasted before is not very common in this quick test. To tweak this you should order by status equal zero first, then randomize. Which will give something like :<\/p>\n Refresh the results few times again :<\/p>\n Now you notice that all status values are zeros, the non zeros values will be in the end, so in case I will add a limit the select will always give me a status zero !<\/p>\n Hope this help, enjoy !<\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":" I don\u2019t usually recommend auto-posting in social media however some applications require this behavior, and there should be an ethical way of doing this. I was experimenting a security project and wanted to share a tips for mysql usage with auto-tweeting. The idea is to have twitter account which is connected to a database of […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,15],"tags":[167,225,260],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/hbyconsultancy.com\/wp-json\/wp\/v2\/posts\/5675"}],"collection":[{"href":"https:\/\/hbyconsultancy.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hbyconsultancy.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hbyconsultancy.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/hbyconsultancy.com\/wp-json\/wp\/v2\/comments?post=5675"}],"version-history":[{"count":0,"href":"https:\/\/hbyconsultancy.com\/wp-json\/wp\/v2\/posts\/5675\/revisions"}],"wp:attachment":[{"href":"https:\/\/hbyconsultancy.com\/wp-json\/wp\/v2\/media?parent=5675"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hbyconsultancy.com\/wp-json\/wp\/v2\/categories?post=5675"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hbyconsultancy.com\/wp-json\/wp\/v2\/tags?post=5675"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}--
\n-- Table structure for table `tweets`
\n--
\nCREATE TABLE IF NOT EXISTS `tweets` (
\n`id` bigint(21) NOT NULL AUTO_INCREMENT,
\n`tweet` varchar(150) NOT NULL,
\n`status` tinyint(1) NOT NULL DEFAULT '0',
\nPRIMARY KEY (`id`),
\nKEY `id` (`id`)
\n) ENGINE=MyISAM;<\/code><\/p>\nSelect tweet from tweets order by RAND() Limit 0,1;<\/code><\/p>\n
SELECT STATUS FROM `tweets` ORDER BY RAND( ) <\/code><\/p>\n
\n2,3,0,0,1,0...
\n2,0,0,0,0,0...
\n0,0,1,3,0,2...
\n1,1,0,0,1,1...
\n<\/code><\/p>\nSELECT id,STATUS FROM `tweets` ORDER BY status ASC,RAND()<\/code><\/p>\n
\n(23,0),(57,0),(40,0),(31,0)...
\n(28,0),(56,0),(22,0),(44,0),...
\n(100,0),(2,0),(33,0),(43,0),...
\n<\/code><\/p>\n