Delete Record After 24 Hours How To

[videobroker]

Hello,

I have only one table with the following fields:

table name : visitcount

ip
time
lastvisit
visitcount

I want to delete the users record every 24 hours. What I do is block ip for further posting until 24 hours later.

I have the following but it doesn't work and I am sure you guys can figure out why:

<?php				 
include ('db.php');	   //db connection
$getDate = mktime (0,0,0,date("m") ,date("d")-1,date("Y")); 
$limitDate = date("Y-m-d", $getDate);
$query = "DELETE FROM 'visitcount' WHERE 'lastvisit' = ' . $limitDate .' LIMIT 1";
$result = mysql_query($query) or die("Query Failed!");
?>

Query fails every time. What am I doing wrong? Thanks.

[wizardry]

how do you have date stored in the database? i would change limit to equal that format i.e. 01/30/2009 you might need to use to chr if you included a full date format to conver the full time stamp into an editable one.

as far as a dbms job goes that is not included in the latest version of mysql yet!

what type of system are you on? windows or linux?

their are two methods to do this in either; however linux is easier by using a cron job, and running a .sh script to access the database to perform your sql and you can log it by useing the tee command and noteee at the end of your script.

windows is a .bat which is a batch file, you can do the same in windows but you will need to do a lot of reading if your not use to batch processing.

[wizardry]

here is some reading sources for converting of the date stamp in your table.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

http://www.ispirer.com/doc/sqlways38/Output/SQLWays-1-067.html

[Atli]

Hi.

There are two problems in your query.

First, column names should not be quoted using single-quote marks (').
If you need to quote them, you should use back-ticks (`).

And Second, you query is being assembled incorrectly:
[code=php]$query = "DELETE FROM 'visitcount' WHERE 'lastvisit' = ' . $limitDate .' LIMIT 1";[/code]
Specifically, the part where you add the $limitDate, you are writing it as if you were adding it to the end of a PHP string, while in fact, you are embedding it into a string.

Consider the following:
[code=php]
$var = "Text";

// Output should be:
// Text: 'Text'
$out1 = "Text: '$var'"; // == Text: 'Text'
$out2 = "Text: '". $var ."'"; // == Text: 'Text'
$out3 = "Text: '. $var .'"; // == Text: '. Text .'[/code]
Your query is created like $out3 in that example.

So, try changing your code to:
[code=php]$query = "DELETE FROM visitcount WHERE lastvisit = '$limitDate' LIMIT 1";[/code]

[Atli]

Also, there may be a better way to accomplish this, other than deleting the rows every 24 hours.

If you only have your website check if the IP already exists in the table, you will have to clear the table every 24 hours.

But if you also have it check the time when the IP was logged, clearing the table is not necessary.

For example, if you have this table:
[code=mysql]
CREATE TABLE `postLog` (
`IP` VarChar(15) Not Null Primary Key,
`LastPost` Timestamp Not Null Default CURRENT_TIMESTA MP,
`TotalPosts` Int Not Null Default 1
);[/code]

To limit the IP to one post per 24 hours, you could do:
[code=php]
<?php
// Get the IP
$ip = mysql_real_esca pe_string($_SER VER['REMOTE_ADDR']);

// Check if the IP has posted in the last 24 HOURS
// Get the amount of time since it did if it has.
$sql = "SELECT TIMEDIFF(NOW(), `LastPost`) AS 'TimeSinceLast'
FROM `postLog`
WHERE `IP` = '{$ip}'
AND `LastPost` > DATE_SUB(NOW(), INTERVAL 1 DAY)";
$result = mysql_query($sq l) or die(mysql_error ());

if(mysql_num_ro ws($result) > 0) {
// Show a "Sorry" message.
$row = mysql_fetch_ass oc($result);
$timeSinceLast = date("G\h i\m s\s", strtotime($row['TimeSinceLast']));
echo "Sorry, there has only been {$timeSinceLast } since your last post. You need to wait 24 hours.";
}
else {
// Post the message
// ... your code here

// Check if the IP has already been logged
$sql = "SELECT TRUE
FROM `postLog`
WHERE `IP` = '{$ip}'
LIMIT 1";
$result = mysql_query($sq l) or die(mysql_error ());

if(mysql_num_ro ws($result) > 0) {
// Update the existing row
$sql = "UPDATE `postLog` SET
`LastPost` = NOW(),
`TotalPosts` = `TotalPosts` + 1
WHERE `IP` = '{$ip}'
LIMIT 1";
mysql_query($sq l) or die(mysql_error ());
}
else {
// Create a new row
$sql = "INSERT INTO `postLog`(`IP`, `LastPost`)
VALUES ('{$ip}', NOW())";
mysql_query($sq l) or die(mysql_error ());
}

// Show "Success" message.
echo "Your post has been added.";
}
?>[/code]
See what I mean?

[Niek]

Apologize for my English but I speak no English normally and write not at all.

This work only if you put the line below changed, because otherwise it will leave that post just by




echo "Sorry There has only been {$timeSinceLast } minutes since your last post. You need to wait 1 hours."; header('Refresh : 2; url=index.php') ; exit;

[mHealth]

No need to calculate it outside database

where lastvisit = CURDATE() + 1 should work