Expiration date delete

[dino2dy]

Hello guys once again. Hope some of you are still looking through code at the forums, instead of looking at women at the beaches. Fingers crossed.

 

Let's cut to the chase. I have a database table with a field expiration date which is filled using a javascript calendar and which fills a form on a form.php page, which then sends it to a fillform.php page where the values from the form are inserted into the database using the INSERT INTO ... VALUES... '$_POST[Exp_Date]'. Now This is all well and good and it works inserting it into the database but what I want to do is make a sort of code that will automatically delete the whole row, or make it invisible somehow, if the expiration date is > than today's date. I know that this code echo date("Y-m-d") writes the today's date. So my thinking is to do something like

 

while ($exp_date=$_POST['Exp_date'])
if 
$exp_date>date
$expired=$exp_date
$query=Delete from... Where ['Exp_date'] $expired

 

The thing is though it's supposed to run automatically through the database and check if something expired so it could delete it. I'm not sure really how to do it. So if u guys have any suggestions, I'd really appreciate em.

 

Thanks for your continued help, I'd be nowhere without you.

[rhodesa]

well...you have 2 options....assuming the 'expiration' field is DATETIME datatype...

 

1) You can hide it when you select rows (this is how i like to do it):

SELECT * FROM tableName WHERE Expdate < NOW()

This will only return rows who's expiration date is still in the future

 

2) Delete records. Easy enough:

DELETE FROM tableName WHERE Expdate < NOW()

This will remove all old records. The problem with this is when to run it. You could just check it every time the page loads...but that is resource intensive. You could run it periodically with a cronjob (linux) or scheduled task (windows)...but things may expire between jobs...so you still need to do the method above.

[defeated]

Hey Rhodesa,

What's the difference between that and

SELECT * FROM tableName WHERE TO_DAYS(NOW()) - TO_DAYS(Expdate) < 0

 

apart from mine being more complicated that is.

[rhodesa]

besides the more complicated reason, i don't see anything wrong with it

 

edit: but if you are going to use that, skip the subtraction:

SELECT * FROM tableName WHERE TO_DAYS(Expdate) > TO_DAYS(NOW())

[defeated]

Could it have something to do with the field being date rather than datetime?  About 2 years ago somebody gave it to me as the solution.  It took me a while to get my head around it being new to php and adverse to any sort of maths (even just subtraction).

 

Wish I had just asked you!

[dino2dy]

Ok here is my attempt to make this work, but while it doesn't give me any error, it won't delete the Ponude where the OpcijaPonude is smaller than the current date. It doesn't give me any error, and the if statements seem to work, because if i play around with echo instead of the sql statement it echoes the date as a date.

 

<?php

$query="SELECT OpcijaPonude FROM tenderlotponude";
		   // Connect to MySQL
              if ( !( $database = mysql_connect( "localhost", "root", "pokemon" ) ) )                      
                die( "Could not connect to database </body></html>" );
           // open Products database
              if ( !mysql_select_db( "dino", $database ) )
                die( "Could not open register database </body></html>" );
			   if ( !( $result = mysql_query( $query, $database ) ) ) 
         {
            print( "Could not execute query! <br />" );
            die( mysql_error() . "</body></html>" );
         } // end if

  while ($row = mysql_fetch_array( $result ))
   {


$opcijaponude=strtotime ($row['OpcijaPonude']);
$currentdate =strtotime("now");
if ($currentdate > $opcijaponude){

$expiry= date ("Y-m-d", $opcijaponude);

	$sql="DELETE FROM tenderlotponude WHERE OpcijaPonude = $expiry";
			mysql_query($sql) or die ("Pojavila se slijedeæa greška pri upisu podataka, molim prijavite je!<br /><strong>".mysql_error()."</strong>");




} 
		 }
		 	         mysql_close( $database );

?>

 

I hope you guys can tell me what I'm doing wrong. Also mine is date time as well and don't know if that's a problem. And it's a small database so how would I do it if I wanted it to go through every time the page loads? I did the

 include timestamp.php; 

in a page that lists all of the rows in the table tenderlotponude, so every time somebody enters that page it should delete the things that are expired. Resources really aren't an issue at this point so is that the way to do it?

 

 

Thanks a lot for all your continued help

[rhodesa]

first thing i notice is you need single quotes around the date:

$sql="DELETE FROM tenderlotponude WHERE OpcijaPonude = '$expiry'";

[defeated]

Not sure but try..

 

 $sql="DELETE FROM tenderlotponude WHERE OpcijaPonude = '$expiry' ";

The single quotes should do the trick.

[dino2dy]

Actually Rick James helped me (I wonder if he's the guy Dave Chappele mocks).

 

This hides expired items from the user:

SELECT ... FROM tbl WHERE exp_date > NOW();

 

This removes all the expired items in one statement -- no loop:

DELETE FROM tbl WHERE exp_date < NOW();

 

I tried it out it works, the only thing is it doesn't show files which expire today, but very simple solution. (when i think i wrote a page of php... sad)

[rhodesa]

Actually Rick James helped me (I wonder if he's the guy Dave Chappele mocks).

 

This hides expired items from the user:

SELECT ... FROM tbl WHERE exp_date > NOW();

 

This removes all the expired items in one statement -- no loop:

DELETE FROM tbl WHERE exp_date < NOW();

 

I tried it out it works, the only thing is it doesn't show files which expire today, but very simple solution. (when i think i wrote a page of php... sad)

 

weird...that looks just like my first post....

[dino2dy]

Damn i must have missed it. Could have saved myself a lot of bother, but the stupidity is strong with me.

 

If I could bother you on a different problem, I have 2 tables, Movies and Actors. And in one table i have a column called Movie Rating (not really but the names are in my language and it's easier to get this way) and in the other table i have AverageActorRating. Now i have a INSERT INTO query for the MovieRating column. It's a simple drop down list consisting of numbers 1-5. Now this works and it inserts it into the database as an int. There is an ActorID column in both Movies and Actors table. Now what i wanna do is insert into AverageActorRating an average of all the Ratings he's gotten so far for the movies in Movies he's been in (where the ActorID is the same). Now I have some code as to how i was thinking of doing it, but I haven't tried it out yet (been doing the expiry date thing), but since I have the expert here I thought why not ask you if my code is any good, and if there is some easier way (like with the expiry date) to do it.

Here's the code so far

<?php
$query=SELECT KomitentID, AVG (tenderi.RatingTendera)
FROM komitenti,tenderi
WHERE komitenti.KomitentID=tenderi.KomitentID

while ($prosjek=mysql_fetch_array($query)){
$sql=INSERT INTO komitenti.ProsjecniRatingKomitenta
VALUES $prosjek;
?>

 

I copied this from notepad cos I don't have dreamweaver on this computer so there are probably some syntax mistakes for which i apologise profusely.