Search code examples
phpmysqldatetimetimereferrals

Checking time was within past 24 hours in PHP

I have developed a referral system where logged in members can send referrals to there family/friends to recommend them to sign up.

Everything works fine but yesterday decided I would limit the maximum referrals someone could send within a 24 hour period. I have limited this to 3 referrals maximum per day.

I posted the bit of code that I seem to be having problems with below. The problem I am having is that no matter what it seems I get the error message saying I have reach the maximum referrals for today. I'm not sure what I am doing wrong in my code.

// referral query
$referral_limit = mysql_query("SELECT 'created_on' FROM 'user_referrals'
WHERE `referrer_uid` = $referrer_uid ") or die(mysql_error());

if(mysql_num_rows($referral_limit) > 0){
    while($row = mysql_fetch_assoc($referral_limit)){

            $db_time = $row['created_on'];

            if((time() - $db_time) > 86400){
                // is within 24 hours and has reached maximum daily referral allowance
                $error[] = "You have reached the maximum referrals for today.";
            }
    }
}

I did try and echo out $db_time and when I do all I get returned is the field name which is created_on and not the actual value which in this case should display the timestamp. The created_on field in database contains the timestamp a referral was made and I check this to ensure the referring user has not made a referral within the past 24 hours.

You will also notice I have not added the extra bit that restricts it to 3 per day but I did not want to add that bit until I can fix this problem first.

The database table looks like this:

CREATE TABLE IF NOT EXISTS `user_referrals` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`referrer_uid` int(11) NOT NULL,
`recipient_username` varchar(15) NOT NULL,
`referrer_email` varchar(254) DEFAULT NULL,
`referred_id` char(32) NOT NULL,
`referred_email` varchar(254) NOT NULL,
`status` char(9) NOT NULL,
`created_on` int(11) NOT NULL,
`updated_on` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `referred_id` (`referred_id`),
KEY `referrer_uid` (`referrer_uid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=72 ;

Edit

Here is my code after some assistance. It still says a referral was made in past 24 hours even though there isn't.

I think I am doing the error checking wrong. 

$referral_limit = mysql_query("
        SELECT COUNT(*)
        FROM `user_referrals`
        WHERE `referrer_uid` = $referrer_uid
        AND `created_on` > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))") or die(mysql_error());

if($referral_limit > 0) {
    $error[] = "You have reached the maximum referrals for today.";
}
Solution

  • Do NOT EVER filter the full MySQL result set in PHP when you can also filter it in MySQL. In your code example, you fetch maybe thousands of rows from MySQL only to run them through your filtering loop. This is the hardest performance killer ever. Rather use a better SQL statement:

    SELECT COUNT(*) FROM 'referrals' WHERE `referrer_uid`=? AND created_on > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))

    This statement just returns the count (!) of referals of the given referer in the last 24 hours.

    Besides, you should never insert variables directly into MySQL like WHERE name=$name, this opens your application to a myriad of SQL injection attacks. If you do not know what sql injection is, you should learn it right now.