Post image for Comment Contest Code and FAQ

Comment Contest Code and FAQ

by Jeremy Schoemaker on April 23, 2012 · 19 comments

Since we launched the comment contest we have been somewhat bombarded with questions. Everything from how it works to if we can share the “plugin” we used for it.

First off its not a plugin its some code I hacked together. I think by answering some of these it will also explain why the code below does what it does. Its really all in the sql querry.

What post do I have to comment on to win daily?

The winner is randomly selected from comments made on that day at 23:55.

If I spam all past posts with comments does that help?

No it has to be from a comment on the days post.

If you don’t post anything today can I win by commenting on previous ones?

No its only on that day.

So again when a post is made you come in and comment you are entered to win. Hopefully that is straight forward enough.

Now the biggest question was what plugin we used to make it happen. Again its not a plugin. To make the random selection happen many things had to come into play and lots of cross table selects. I am sure this code is far from perfect as I, yes me, did it. With my lack of programing ability this took me a long time to figure out.

It was difficult for me because of all the factors in play:

  • It had to select a random commentator.
  • It had to select the commentator from a post made that day.
  • it had to select from ONLY posts that were published (I made lots of drafts).
  • It had to select from only posts and NOT pages.
  • It had to select from the proper wordpress post type (we have custom post types for stuff like marketplace and what not).
  • It had to select uniq from that day.
  • IT had to have a user with an email (so we can contact them).
  • It had to have a user that had an approved comment.
  • It had to not have me as a eligible winner.

I also want to automatically:

  • Email the winner.
  • Email the person on my staff who will ship these out.
  • BCC email myself so I know its working nightly.
  • Put a unique code in the subject line so we can properly track it and authenticate it.

So here is the code that makes all that happen:

< ?
$today=date("Y-m-d");
 
$db_host = "localhost";
$db_user = "dbusername";
$db_passwd = "password";
$db_db = "database";
 
mysql_connect("$db_host","$db_user","$db_passwd");
mysql_select_db("$db_db");
 
$result=mysql_query("select comment_author,comment_author_email,comment_author_url,comment_content, comment_date from wp2_comments where comment_author_email NOT IN (SELECT email FROM commentcontest) AND comment_author !='shoemoney' AND comment_approved=1 AND comment_post_id =(select id from wp2_posts where post_status ='publish' AND post_type = 'post' AND  post_date like '$today%' order by post_date limit 1) order by rand() limit 1");
 
while($row = mysql_fetch_object($result))
                {
 
                $author = $row->comment_author;
                   $email = $row->comment_author_email;
                   $url = $row->comment_author_url;
                   $content = $row->comment_content;
                   $comment_date = $row->comment_date;
                }
 
if (!$email=='') {
 
$content=addslashes($content);
$code =md5($email);
 
$subject = "$today ShoeMoney Comment Contest Winner $code";
 
        $headers = "Return-Path: annasemail@shoemoney.com\r\n";
        $headers .= "From: Anna \r\n";
        $headers .= 'Bcc: Jeremy ' . "\r\n";
 
$message = "Dear $author,
 
Congratulations!  You are the randomly selected winner for your comments written on a shoemoney.com blog post on $today. You have won a ShoeMoney t-Shirt and a chance at this months grand prize! \nTo Claim your t-shirt Please reply to this email with your shirt size, style of shirt (male or female Small to XXXL),  and your mailing address.\n We will ship them to you free of charge anywhere in the world!  Please respond to this email within 48 hours with your address if you wish to claim your free shirt!\nCongratulations and good luck at winning this months grand prize.  For more information check http://www.shoemoney.com/commentcontest/
 
Please note - We ship out shirts on the first and 15th of each month. \n
 
Also please keep the subject line with the code intact so we can verify its authenticity!i\n
 
Thanks for your contribution to the ShoeMoney Blog!  We appreciate your support and input!\n
 
Jeremy Schoemaker AKA ShoeMoney";
 
        mail($email,$subject,$message,$headers);
 
mysql_query("INSERT INTO commentcontest(doc,author,email,url,content) VALUES('$today','$author','$email','$url','$content')")  or die(mysql_error());
 
print "$author $email $url $content $comment_date";
} else {
 
        die("no data\n\n");
}
?>

Then for the actual page that shows the winners I select from that from the winners to show on the comment contest results page:

< ?php
$db_host = "localhost";
$db_user = "dbusername";
$db_passwd = "password";
$db_db = "db";
 
mysql_connect("$db_host","$db_user","$db_passwd");
mysql_select_db("$db_db");
$contestdate=date('F Y');
?>

< ?php $result=mysql_query(“select * from commentcontest where dtime > ’2008-12-01 00:00:00′ order by dtime DESC”); while($row = mysql_fetch_object($result)) { $doc=$row->doc; $author = $row->author; $url = $row->url; $content = $row->content; $comment_date = $row->comment_date; print “”; } echo ”

Date Winner Name Comment
$doc $author $content
";
?&gt;

. So again its not a plugin and with the code above if you want to do it, it should save you a lot of time whether you do it yourself or hire some monkey off of Odesk.

BTW if your going to hate on my code at least offer suggestions on how to improve it =P If someone wants to turn this into a plugin go ahead.

full disclosure

About the author...

– who has written 2855 posts on ShoeMoney.com.

Jeremy "ShoeMoney" Schoemaker is the founder & CEO of the ShoeMoney Blog, Elite Retreat Internet Conference, & the PAR Program. In 2013 Jeremy released his #1 Amazon Best selling Autobiography titled "Nothing's Changed But My Change" - The ShoeMoney Story. Jeremy currently lives in Lincoln Nebraska with his wife and 2 daughters.


Michelle recommends you check out these amazing posts:

  1. facebooksheep How I hacked your Facebook account
  2. iStock_000006091595XSmall How To Get People To Promote Your Stuff
  3. guide-tipsforsuccess 9 Random Tips On Being A Successful Online Marketer

{ 19 comments }

1 Geiger

I believe the correct SQL should be…
select * from commentcontest where dtime > ’2008-12-01 00:00:00′ and author = ‘Geiger’ order by dtime DESC

2 Jeremy

I am sure their are many correct or better statements… the one I pasted above just works ;)

3 Dr Jeremy Schoemaker

OH nm I see what you did there. LOL sorry I had to get up at 3:30 this morning to drive 1.5 hours to get a flight to SLC then to SFO. 3 hour layover

4 Ramon

You also could use random.org , count the comments and the outcome # will be the winner :)

5 Dr Jeremy Schoemaker

I am sure you could… if you wanted to do it manually every time

6 Kevin Thomas

I wish I could code lol. But it’s a pretty cool system. I might be able to use something like this for my own blog

7 Chris

Nice little setup. Definitely takes the crap work out of setting up a contest like this.

8 Dr Jeremy Schoemaker

Its fun and works well

9 Squirrel @ cute animal videos

I look at code and my head starts to spin.

10 Georgie

If you knew the email of a winner couldn’t you just generate the MD5 and email with your delivery details before the other guy?!

P.S.
Send me a free t-shirt.

11 George

Shhhhhh

12 d3so

Did you write this code yourself or did you have it outsourced?

13 Jeremy

I did it

14 Ron

Pretty cool and I am surprised it isn’t just a plugin (yet lol). You could even go one step further and make all prizes digital (gift certs like amazon, for example) and have those codes already setup in the auto email to send out to the winners. Or maybe a dropshipper with your shirts lol. But then you are getting silly and taking jobs from Americans ;).

Thanks for sharing the code as well!

15 Jeremy

Yup lots of cool options

16 John Caskoby

Cool. It’s really handy for someone who wants to get some daily comments, because Google Likes it.

17 sukosaki @ sukosaki

Pretty cool to begin with and when cool features like this end up on blogs and are totally from scratch, the code is never given to the general public. Good work and thanks for giving me another reason I don’t need to code myself :)

18 fas

I think you can wrap it up as a plugin and lets other develop.

19 Thomas Vanhoutte

No a bad idea to automate the process (image if you had to manually select a winner, every day!).

Also, great you share the code. It looks quite optimized to be honest. I write SQL almost every day so believe me :-)

Previous post:

Next post: