Forum Moderators: open
2 - remove the record when it "expires"? (each record has an expiration date)
Further explaining
#1 - I'm hoping to have an admin panel for adding records. The information comes from multiple sources, so duplication is probable. I would like an "already in database" message, or similar.
#2 - has a field for expiration date and I would like it to either self delete or?
You do a query like:
SELECT fieldname FROM tablename WHERE fieldname = 'data';
Count the number of results. If you have more than one, you have a duplicate. Code example:
$query = "SELECT fieldname FROM tablename WHERE fieldname = '".addslashes($data)."'";
$result = mysql_query($query);
$num = mysql_num_rows($result);
if ($num){
// display a message saying a duplicate has been found
}
To remove old records:
$target = '2006-03-07';
$query = "DELETE FROM tablename WHERE recorddate < '".$target."'";
$result = mysql_query($query);
delete from table_name where experation_date < now()
$query = "SELECT fieldname FROM tablename WHERE fieldname = '".addslashes($data)."'";
$result = mysql_query($query);
$num = mysql_num_rows($result);
if ($num){
// display a message saying a duplicate has been found
}
Could this be set up so it runs as a 'second' query when trying to add a record with the "Admin"? IOW, I want to add "stuff,code12345", and if "code12345" is already in the db I get a "already in db" message? Or is that overly complex?
" For #2 you can make it self deleting but you will need to set up a timed task that will run every day? hour? and what it will do is run an SQL that looks like this.
delete from table_name where experation_date < now() "
Basically, I would want to do that daily.
I should probably also say that this is a tiny db, a few hundred records, each with maybe 10 fields
Can that code be added to every page that would query the db so that every time a page loads the db is checked for expired records?
Or should it be set up as a daily CRON job? And if it's a cron job, can I have that code on a page (expire.php) and run that?
Could this be set up so it runs as a 'second' query when trying to add a record with the "Admin"? IOW, I want to add "stuff,code12345", and if "code12345" is already in the db I get a "already in db" message? Or is that overly complex?
A simple set of if/else blocks will work fine here.
pseudo code:
do the query mentioned above
if ($num){
echo 'already in database';
} else {
perform insert query
}
Can that code be added to every page that would query the db so that every time a page loads the db is checked for expired records?
Sure.
Or should it be set up as a daily CRON job? And if it's a cron job, can I have that code on a page (expire.php) and run that?
It's your call. A cron job would probably be more efficient and reliable. Sure you could put the code on an expire.php page. Just make sure you disallow expire.php in your robots.txt, or, even better, keep it outside the document tree on your server so it can't be accessed from the web.
And for the record I would go with the cron tab and a special page to hit. Run it late when the server traffic is low. I know you said it is a small set of data, but it is good practice to run stuff like that at low times so that if data set ever grows to a really large size you aren't taking resources during high traffic times.
