vigiw Posted September 23, 2006 Share Posted September 23, 2006 Hello, I have a working PHP form that sends data to my MySQL database. It is used for a weather alert system. One of the fields in the form & database is an expiration field. It shows the user when the alert will expire. Is there a way to auto-delete just the data sent with that form when it is supposed to expire? I have many weather alerts and I would like them to be emptied from the database when it is needed. Please help, this is a much needed feature. All help appreciated :) Thank you so much! Link to comment https://forums.phpfreaks.com/topic/21788-php-form-auto-empty-script/ Share on other sites More sharing options...
Barand Posted September 23, 2006 Share Posted September 23, 2006 Periodically run a query such asDELETE FROM alerts WHERE expiretime < NOW() Link to comment https://forums.phpfreaks.com/topic/21788-php-form-auto-empty-script/#findComment-97322 Share on other sites More sharing options...
vigiw Posted September 23, 2006 Author Share Posted September 23, 2006 Will this delete my tables? Link to comment https://forums.phpfreaks.com/topic/21788-php-form-auto-empty-script/#findComment-97326 Share on other sites More sharing options...
Barand Posted September 23, 2006 Share Posted September 23, 2006 It will delete records which have expired from your table. Link to comment https://forums.phpfreaks.com/topic/21788-php-form-auto-empty-script/#findComment-97330 Share on other sites More sharing options...
vigiw Posted September 23, 2006 Author Share Posted September 23, 2006 Ok.. I have many tables in this database that contain this data that need to be emptied. Do I have to name each table in there or the database name, or what?Thanks. ;D Link to comment https://forums.phpfreaks.com/topic/21788-php-form-auto-empty-script/#findComment-97333 Share on other sites More sharing options...
Barand Posted September 23, 2006 Share Posted September 23, 2006 Sounds like a case for re-thinking db design. You shouldn't be creating and dropping tables from an application. I'm surprised your DBA allows it.Can you explain your whole scenario, from creating the alerts (and why in different tables) to deleting them? Link to comment https://forums.phpfreaks.com/topic/21788-php-form-auto-empty-script/#findComment-97338 Share on other sites More sharing options...
vigiw Posted September 23, 2006 Author Share Posted September 23, 2006 No, I don't want to delete tables. I was just verifying that it only empties the data that I want it to, and not deleting.I have a weather forecast system, it officially launched to the public last night. I have different forecast areas and different alert types and so they are in their own tables of one database. It is already working, the hard part is over. I just want the old data to be removed.I have another question:What format does the expiretime have to be in?Date, Time, maybe, I don't know the exact format to make the database understand.Thanks. Link to comment https://forums.phpfreaks.com/topic/21788-php-form-auto-empty-script/#findComment-97340 Share on other sites More sharing options...
Barand Posted September 23, 2006 Share Posted September 23, 2006 Make it a DATETIME field. Write to it in the format YYYY-MM-DD HH:ii:ss.I'd set it up so that when you add an alert to one of your tables, also call the query to delete any expired ones from that table. Link to comment https://forums.phpfreaks.com/topic/21788-php-form-auto-empty-script/#findComment-97341 Share on other sites More sharing options...
vigiw Posted September 23, 2006 Author Share Posted September 23, 2006 Ok Thanks..Is there a way to make this script automatic? Maybe cron jobs?I honestly do not know what a cron job is and have never used one so I would not know what to do.It seems very tedious running this every so often since there will be quite the amount of alerts to empty when needed. Link to comment https://forums.phpfreaks.com/topic/21788-php-form-auto-empty-script/#findComment-97347 Share on other sites More sharing options...
Barand Posted September 23, 2006 Share Posted September 23, 2006 [quote]Is there a way to make this script automatic?[/quote][quote author=Barand link=topic=109197.msg440039#msg440039 date=1159038196]I'd set it up so that when you add an alert to one of your tables, also call the query to delete any expired ones from that table.[/quote] Link to comment https://forums.phpfreaks.com/topic/21788-php-form-auto-empty-script/#findComment-97350 Share on other sites More sharing options...
vigiw Posted September 23, 2006 Author Share Posted September 23, 2006 Ok thank you and sorry for the late reply here, but how do I make the following script:DELETE FROM alerts WHERE expiretime < NOW()use multiple tables such as:alertsalerts2alerts3Thanks! Link to comment https://forums.phpfreaks.com/topic/21788-php-form-auto-empty-script/#findComment-97360 Share on other sites More sharing options...
Barand Posted September 23, 2006 Share Posted September 23, 2006 You said you have working form that writes the alerts to your database.So if you processing script is inserting a record into, say, alerts2 table, then alsomysql_query ("DELETE FROM alerts2 WHERE expiretime < NOW()"); Link to comment https://forums.phpfreaks.com/topic/21788-php-form-auto-empty-script/#findComment-97363 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.