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! Quote Link to comment 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() Quote Link to comment Share on other sites More sharing options...
vigiw Posted September 23, 2006 Author Share Posted September 23, 2006 Will this delete my tables? Quote Link to comment 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. Quote Link to comment 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 Quote Link to comment 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? Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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] Quote Link to comment 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! Quote Link to comment 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()"); Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.