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 `referre开发者_开发百科r_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.";
}

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.

You may also use MySQL to calculate that:

$referral_limit = mysql_query("SELECT COUNT(*)
                               FROM referrals
                               WHERE referrer_uid = $referrer_uid
                               AND created_on >= NOW() - INTERVAL 1 DAY
                              ") or die(mysql_error());

provided that created_on is a datetime or a timestamp. Why is it declared INT ?

Do not put single quotes around the fieldname you are selecting, since this will be interpreted as a string to return. E.g. you need

SELECT created_on ...

(unless you've actually got backticks in your code and the included code in your example is incorrect).

Your query should be

$referral_limit = mysql_query("SELECT `created_on` FROM `referrals`
 WHERE `referrer_uid` = $referrer_uid ") or die(mysql_error());

That is backticks for created_on and not quotes. If you use quotes, the field becomes a string, similar to SELECT 1 FROM table which returns 1

created_on int(11) NOT NULL, updated_on int(11) DEFAULT NULL,

Surely these should be datetime fields?

change your query

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

to

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