468,483 Members | 2,468 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,483 developers. It's quick & easy.

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:

Expand|Select|Wrap|Line Numbers
  1. <?php                 
  2. include ('db.php');       //db connection
  3. $getDate = mktime (0,0,0,date("m") ,date("d")-1,date("Y")); 
  4. $limitDate = date("Y-m-d", $getDate);
  5. $query = "DELETE FROM 'visitcount' WHERE 'lastvisit' = ' . $limitDate .' LIMIT 1";
  6. $result = mysql_query($query) or die("Query Failed!");
  7. ?>
Query fails every time. What am I doing wrong? Thanks.
Feb 23 '09 #1
6 11616
wizardry
201 100+
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.
Feb 24 '09 #2
wizardry
201 100+
here is some reading sources for converting of the date stamp in your table.

http://dev.mysql.com/doc/refman/5.1/...functions.html
http://www.ispirer.com/doc/sqlways38...ays-1-067.html
Feb 24 '09 #3
Atli
5,058 Expert 4TB
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:
Expand|Select|Wrap|Line Numbers
  1. $query = "DELETE FROM 'visitcount' WHERE 'lastvisit' = ' . $limitDate .' LIMIT 1";
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:
Expand|Select|Wrap|Line Numbers
  1. $var = "Text";
  2.  
  3. // Output should be:
  4. // Text: 'Text'
  5. $out1 = "Text: '$var'";       // == Text: 'Text'
  6. $out2 = "Text: '". $var ."'"; // == Text: 'Text'
  7. $out3 = "Text: '. $var .'";   // == Text: '. Text .'
Your query is created like $out3 in that example.

So, try changing your code to:
Expand|Select|Wrap|Line Numbers
  1. $query = "DELETE FROM visitcount WHERE lastvisit = '$limitDate' LIMIT 1";
Feb 26 '09 #4
Atli
5,058 Expert 4TB
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:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE `postLog` (
  2.   `IP` VarChar(15) Not Null Primary Key,
  3.   `LastPost` Timestamp Not Null Default CURRENT_TIMESTAMP,
  4.   `TotalPosts` Int Not Null Default 1
  5. );
To limit the IP to one post per 24 hours, you could do:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. // Get the IP
  3. $ip = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);
  4.  
  5. // Check if the IP has posted in the last 24 HOURS
  6. // Get the amount of time since it did if it has.
  7. $sql = "SELECT TIMEDIFF(NOW(), `LastPost`) AS 'TimeSinceLast'
  8.         FROM   `postLog`
  9.         WHERE  `IP` = '{$ip}'
  10.         AND    `LastPost` > DATE_SUB(NOW(), INTERVAL 1 DAY)";
  11. $result = mysql_query($sql) or die(mysql_error());
  12.  
  13. if(mysql_num_rows($result) > 0) {
  14.     // Show a "Sorry" message.
  15.     $row = mysql_fetch_assoc($result);
  16.     $timeSinceLast = date("G\h i\m s\s", strtotime($row['TimeSinceLast']));
  17.     echo "Sorry, there has only been {$timeSinceLast} since your last post. You need to wait 24 hours.";
  18. }
  19. else {
  20.     // Post the message
  21.     // ... your code here
  22.  
  23.     // Check if the IP has already been logged
  24.     $sql = "SELECT TRUE 
  25.             FROM   `postLog` 
  26.             WHERE  `IP` = '{$ip}'
  27.             LIMIT 1";
  28.     $result = mysql_query($sql) or die(mysql_error());
  29.  
  30.     if(mysql_num_rows($result) > 0) {
  31.         // Update the existing row
  32.         $sql = "UPDATE `postLog` SET
  33.                   `LastPost` = NOW(),
  34.                   `TotalPosts` = `TotalPosts` + 1
  35.                 WHERE `IP` = '{$ip}'
  36.                 LIMIT 1";
  37.         mysql_query($sql) or die(mysql_error());
  38.     }
  39.     else {
  40.         // Create a new row
  41.         $sql = "INSERT INTO `postLog`(`IP`, `LastPost`)
  42.                 VALUES ('{$ip}', NOW())";
  43.         mysql_query($sql) or die(mysql_error());
  44.     }
  45.  
  46.     // Show "Success" message.
  47.     echo "Your post has been added.";
  48. }
  49. ?>
See what I mean?
Feb 26 '09 #5
Niek
1
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;
May 26 '14 #6
mHealth
13
No need to calculate it outside database
Expand|Select|Wrap|Line Numbers
  1. where lastvisit = CURDATE() + 1 should work
  2.  
Jun 8 '14 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

16 posts views Thread by Philip Boonzaaier | last post: by
8 posts views Thread by Steve | last post: by
4 posts views Thread by Susan Bricker | last post: by
4 posts views Thread by Swinky | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by kmladenovski | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.