Printable Version of Topic

Posted by: CodeKing Sep 12 2006, 05:08 PM

In the following code doesn't work. It's suppose to delete users who haven't confirmed their account within 24 hours.

<?
$dbh=mysql_connect ("localhost", "mostmojo_mojo", "*********") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("mostmojo_members");
print($signtime = date("YmdHis")-240000);
$query = "DELETE FROM `users` WHERE `confirm`='0' AND `signtime`<'".$signtime."'";
if (!mysql_query($query))
{
print(mysql_error());
}
mysql_close($dbh);
?>

$signtime outputs as 2.00609119313E+13. Is that the reason it doesn't work? If you need any info about the database, just ask.

Posted by: Brian Chandler Sep 13 2006, 07:28 AM

In what format do you write the signup time to the DB? Using date("YmdHis")? This seems extravagant - and gives you *enormous* pseudo-numbers to subtract, which may lead to problems with floating point overflow and goodness knows what. Why not just throw away the minutes and seconds, and record to the nearest hour? Notice that the value above is 2006-09-11 @ 9313, except that 9313 is not an obvious time.

Why are there _three_ different sorts of quotes in the MySQL query?

Posted by: Brian Chandler Sep 13 2006, 07:59 AM

"Goodness knows what" includes the fact that the arithmetic doesn't work, because subtract causes the wrong sort of carry.

Suppose now is (in some time zone)
200609130615

Presumably you'd like people to be chopped if their signup time was before
200609120615

But the following sum gives a different answer *NEED MONOSPACE FONT*
20060913061500
_______-240000
20060912821500

So the chop will come at midnight on the day you sign up.

You can do date arithmetic using the time() function (what utterly _stupid_ names) to create a unix datestamp for the right value, then convert to the format above using date(). Look in the manual for helpful examples. Note that you will then have to untangle the mess created by MySQL using server local time vs Unix timestamps. I suggest that even if you _say_ "24 hours", it's simpler to delete them at the end of the day after signup.

HTH Smilies in batch mode: