Delete Everything After 48 hours has elapsed ?

[phplover]

Hi,

When a user registers on my site there status is 0 (not active) ... meaning they not activated there account via email. When they register i also store the DATETIME they registered, i store it in MySQL using MySQL DATETIME and using NOW() in my INSERT query on registration.

Format stored as: 0000-00-00 00:00:00 .

I want to be able to delete everything from users table where status = 0 (not active) and where a user has not activated there account within 48 hours.

My MySQL query so far is:

SELECT *
FROM users
WHERE
user_status = 0
AND user_registered // not sure what i do here.

Could someone tell me how i go about checking if 48 hours have elapsed and account is still not activated ? the first part of query i think i got rite, its the AND bit etc im not sure about.

Basically i will create a script to run on a cron job every 24 hours ( i know how to do the cron job in cpanel) at low peak times, but to ensure i don't cause havoc with the server resources (on shared hosting) i want the script to delete xx records and then pause for a while to make sure it does not effect system resources and lock up MySQL.

any help much appreciated, if anyone has a sample script that would be great. One thing that came to mind was as i have access to my php.ini file i set the time in php.ini to UK time where i am, but i know the server i am on is in US so i guess that MySQL will use the server time in the US, not sure how i go about that. Another note is i use PHP PDO but not OOP as not learnt OOP yet.

Thank you,
phplover

[Biiim]

DELETE 
FROM users
WHERE
user_status = 0
AND date_signed_up < DATE_SUB(NOW(), INTERVAL 2 DAY);

or

UPDATE users
SET `deleted` = 1
WHERE
user_status = 0
AND date_signed_up < DATE_SUB(NOW(), INTERVAL 2 DAY);

[MarPlo]

Hi,

Try with this query:

DELETE FROM users WHERE user_status = 0 AND UNIX_TIMESTAMP(user_registered)<(UNIX_TIMESTAMP(NOW())-172800)

Or this:

DELETE FROM users WHERE user_status = 0
AND UNIX_TIMESTAMP(user_registered)<(NOW()-172800)

[phplover]

Wow, thank you both,

Will try one of these, can i ask how would i got about in php to get it to delete lets say 10 rows then to pause for lets say xx seconds before deleting more rows ? i know i could use sleep/usleep but i don't understand how i say delete 10 records and pause for xx seconds then loop through again until no more records that match the query, i know i will need a while loop i think but putting this into practice is confusing me for some reason. I do understand the queries posted thou.

Even thou i will never need to delete so many rows in one go it's nice to know i have such a design in place incase i did need to.

Once agian thanks for the help, much apprecaited +1 for both of you, well deserved!

[Biiim]

thanks:p

just add LIMIT 10 on the end of the query

DELETE 
FROM users
WHERE
user_status = 0
AND date_signed_up < DATE_SUB(NOW(), INTERVAL 2 DAY)
LIMIT 10;

you can use sleep(); in php but i find php isn't the greatest thing at waiting - it might be better to consider javascript - even something simple.

deletePage.htm

<script type='text/javascript'>
var theTimer;
function deleteTen(){
    window.open("deleteTen.php");
    theTimer = setTimeout("deleteTen()",10000);
}
function endTimer(){
    theTimer = null;
}
function showMessage(msg){
    document.getElementById('msgDiv').innerHTML = msg;
}
</script>
<div id='msgDiv'>msgs here</div>

deleteTen.php

<?php
//your php delete script

//once none left to delete echo out
if($none_deleted){
    ?>
    <script type='text/javascript'>
    window.opener.endTimer();
    window.close();
    </script>
    <?php
 }else{
    ?>
    <script type='text/javascript'>
    window.opener.showMessage('10 deleted');
    window.close();
    </script>
    <?php
 }
 ?>

I'm a little rusty at that javascript so hope its right

Edit just checked and updated it, it may only work on chrome/safari the javascript might change for the other browsers

[phplover]

Hi,

As script will be run via cron job Javascript wouldn't work would it ?

Once again thank you so much!

[Biiim]

, i know i will need a while loop i think but putting this into practice is confusing me for some reason. I do understand the queries posted thou.

Ah you wanted help on the php too

deleteTen.php

<?php
$db_link = mysql_connect("1.2.3.4","user","pass");
mysql_select_db("my_db");
$query = "UPDATE users
SET `deleted` = 1
WHERE
user_status = 0
AND date_signed_up < DATE_SUB(NOW(), INTERVAL 2 DAY)";
$result = mysql_query($query,$db_link) or die(mysql_error($db_link));
$effected_rows = mysql_affected_rows($db_link);

if($effected_rows < 10){
    ?>
    <script type='text/javascript'>
    window.opener.endTimer();
    window.opener.showMessage('<?php echo $effected_rows;?> deleted.  All Deleted');
    window.close();
    </script>
    <?php
 }else{
    ?>
    <script type='text/javascript'>
    window.opener.showMessage('<?php echo $effected_rows;?> deleted');
    window.close();
    </script>
    <?php
 }
 ?>

[Biiim]

Hi,

As script will be run via cron job Javascript wouldn't work would it ?

Once again thank you so much!

Ah can't you just schedule cron job to run every 30 seconds or so?

[phplover]

Hi,

Even thou i doubt i will ever have any heavy usage on resources i would prefer running every 24 hours, depends really how site progresses in the future.

Thanks
phplover

[phplover]

Anyone have an example of the php code to do what i want above ?

@Biiim, javascript is something i rather not use, but thanks as you have been a great help :)

Thanks
phplover

Will running one delete query via cronjob once a day lock up that many resources? Just asking

[phplover]

My tables have a many to many relationship, but to be honest i think your rite Ardav, Thanks, will mark solved.

A special thanks to Both Biiim and MarPlo