Jump to content


Photo

PHP Form Auto-Empty Script


  • Please log in to reply
11 replies to this topic

#1 vigiw

vigiw
  • Members
  • PipPipPip
  • Advanced Member
  • 100 posts

Posted 23 September 2006 - 05:04 PM

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!

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 23 September 2006 - 06:20 PM

Periodically run a query such as

DELETE FROM alerts WHERE expiretime < NOW()
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 vigiw

vigiw
  • Members
  • PipPipPip
  • Advanced Member
  • 100 posts

Posted 23 September 2006 - 06:29 PM

Will this delete my tables?

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 23 September 2006 - 06:44 PM

It will delete records which have expired from your table.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 vigiw

vigiw
  • Members
  • PipPipPip
  • Advanced Member
  • 100 posts

Posted 23 September 2006 - 06:47 PM

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

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 23 September 2006 - 06:54 PM

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?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 vigiw

vigiw
  • Members
  • PipPipPip
  • Advanced Member
  • 100 posts

Posted 23 September 2006 - 06:57 PM

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.

#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 23 September 2006 - 07:03 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#9 vigiw

vigiw
  • Members
  • PipPipPip
  • Advanced Member
  • 100 posts

Posted 23 September 2006 - 07:19 PM

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.

#10 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 23 September 2006 - 07:39 PM

Is there a way to make this script automatic?


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.


If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#11 vigiw

vigiw
  • Members
  • PipPipPip
  • Advanced Member
  • 100 posts

Posted 23 September 2006 - 08:16 PM

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:

alerts
alerts2
alerts3

Thanks!

#12 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 23 September 2006 - 08:22 PM

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 also

mysql_query ("DELETE FROM alerts2 WHERE expiretime < NOW()");
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users