php mysql automatic deletion

[darkestsello]

Hello group

This is my first post to this group and I'm hoping you will be able to help me. I'm trying to work out how to create an automatic deletion php script based on the date.

Ideally the script will be adaptable as I'd like to be able to use it on a kind of countdown as well as by the date (hope that makes sense)

I've searched for hours for a suitable tutorial but have been unsuccessful.

tia
~kristina

(a php/mysql newbie)

[Anon]

Okay... we DON'T want to delete the whole database. Instead, we just want to delete the ENTRIES. To delete entries from a database, we use this script

<?php
$user = "myusername";
$pass = "mypassword";
$db = "mydatabase";
$link = mysql_connect( "localhost", $user, $pass );
if ( ! $link )
die( "error" );
mysql_select_db( $db, $link )
mysql_query( "delete from mydatabase" );
mysql_close( $link );
?>

Then, since you want to do this at intervals, we use, "cron."

Everything you need to know should be here:
http://www.aota.net/Script_Installation_Tips/cronhelp.php4

Let me know how that works!

[pnoeric]

or are you trying to delete files from the disk, not database entries?

first get the code working that deletes what you want.

THEN hook it up to 'cron,' the system program that runs things at specific times.

to set up a 'countdown,' you should make a note about when something is to be deleleted, then when 'cron' runs the script, check to see if it's time for the delete to happen yet. (i.e. 'cron' may run the script every 2 minutes, but the script will only delete when it says 'ok, now it's time.')

hope that helps

best
Eric

[darkestsello]

Thanks for your reply, I see now that I was very vague about my request, apologies for that.

What I have is a database that has x number of records, the records have an expiry date and should automatically delete when that expiry date becomes true?

I have in my mind something like this:

if date == todaysDate
= drop record from tablename
else = do nothing

But I'm not sure of how to code this, I'm using PHP & MySQL.

Should I use cron to run this? As I was also wondering how it would know to do this?

Hope that is a bit clearer.

I'm going away for a week tomorrow and I'm concerned that I'll lose the thread @ phpbuilder would it be okay for you to email me (hope thats not being too cheeky)

tia
~kristina

[e-mail]

[wshost]

Try something like this:

$datetodelete = "2002-02-15"; // or whatever

mysql_query("DELETE * FROM yourtable WHERE datetable=$datetodelete");

you can also throw in a < with the = in case your cron job, that is running this script, once a day or once an hour, etc. forgets and skips a day. This is very simplistic, let me know if you have any other details.

[darkestsello]

Thanks for your reply.

When the record is entered the user enters a an expiry date for the record.

$datetodelete needs to update itself to todays date automatically - how does this happen? what is the syntax for it

and then when

$expirydate == $todaysdate

mysql_query("DELETE * FROM yourtable WHERE record=$expirydate");

also as I want to make sure the user enters the date in the correct format I've used drop downs so the user can only select a day month and year not actually type it in manually.

how does

$deleteday, $deletemonth, $deleteyear - turn into $expirydate? and be a proper date? instead of just a string of digits....?

(getting very confused here!)

I apologise for my toal newbieNess! ;o)

tia
~kristina

[pnoeric]

kristina, you're on the right track. check out:

php.net/date

php.net/strtotime

and be sure to read the notes at the bottom of each

they should get you going in the right direction.

-eric

[darkestsello]

Eric

Thanks for the reply.

As you can see its been a while, mainly because I have been trying to figure this out...!

The part I need help with is converting the string to a date.

the date is in this format:

      <tr>
        <td>Delete record on:</td>
        <td>
          <select name="deleteday" size="1">
            <option> </option>
            <option>01</option>
            <option>02</option>
            <option>03</option>
            <option>04</option>
            <option>05</option>
            <option>06</option>
            <option>07</option>
            <option>08</option>
            <option>09</option>
            <option>10</option>
            <option>11</option>
            <option>12</option>
            <option>13</option>
            <option>14</option>
            <option>15</option>
            <option>16</option>
            <option>17</option>
            <option>18</option>
            <option>19</option>
            <option>20</option>
            <option>21</option>
            <option>22</option>
            <option>23</option>
            <option>24</option>
            <option>25</option>
            <option>26</option>
            <option>27</option>
            <option>28</option>
            <option>29</option>
            <option>30</option>
            <option>31</option>
          </select>&#47;
          <select name="deletemonth" size="1">
            <option> </option>
            <option>01</option>
            <option>02</option>
            <option>03</option>
            <option>04</option>
            <option>05</option>
            <option>06</option>
            <option>07</option>
            <option>08</option>
            <option>09</option>
            <option>10</option>
            <option>11</option>
            <option>12</option>
          </select>&#47;
          <select name="deleteyear" size="1">
            <option> </option>
            <option>2002</option>
            <option>2003</option>
            <option>2004</option>
            <option>2005</option>
          </select>
        </td>
      </tr>

How is
deleteday
deletemonth
deleteyear
converted to a proper date

is it as simples as
$deleteday = d
$deletemonth = m
$deleteyear = Y

which are all = $expirydate

as then I think that all I need to do is add a query that tests to see if $expirydate = to today if so delete

tia
~kristina