Auto Delete Database

[matvespa]

Hi. I have the following code to delete a row when the date has passed.

 

$today = date("dmY");
$vLongDate = date("dmY", strtotime($thisDate));

if ($today > $vLongDate) {
mysql_query("DELETE FROM events WHERE id < '$id'");
}

 

$today is current date, while $vLongDate is the date that is on my database. Am i doing the if statement correctly??

 

 

[AyKay47]

yes

[skwap]

post your full code here... & also explain what trouble you are facing ?

[AyKay47]

you could also do this condition inside of the query.. are you facing an issue or was this just a general question..?

[matvespa]

<?php
    $query = "SELECT * FROM events ORDER BY date asc";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
	$id = $row["id"];
	$title = $row["title"];
	$thisDate = $row["date"];
	$price = $row["price"];
	$ShortDate = date("d/m/Y", strtotime($thisDate));
	$LongDate = date("j F Y", strtotime($thisDate));
	$today = date("dmY");
	$vLongDate = date("dmY", strtotime($thisDate));
		<?php
    $query = "SELECT * FROM events ORDER BY date asc";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
	$id = $row["id"];
	$title = $row["title"];
	$thisDate = $row["date"];
	$price = $row["price"];
	$ShortDate = date("d/m/Y", strtotime($thisDate));
	$LongDate = date("j F Y", strtotime($thisDate));
	$today = date("dmY");
	$vLongDate = date("dmY", strtotime($thisDate));
	if ($today > $vLongDate) {
	mysql_query("DELETE FROM events WHERE id < '$id'");
	}
?>
?>

 

I want to delete row(s) if the date data stored in my database has passed the current date. What happen here is it seem not to be deleting my database row. I have 2 rows which the date have passed the current date.

[matvespa]

Sorry. What happen when i execute the code is only the last row in the database is being displayed while the earlier rows are being deleted. Even when the earlier rows date have NOT passed.

[DavidAM]

When comparing the STRING representation of dates, you have to build the string starting with the largest component: i.e. Year, Month, Day, Hour, Minute, Second.

 

	$today = date("dmY");
	$vLongDate = date("dmY", strtotime($thisDate));
	if ($today > $vLongDate) {

 

Using Day, Month, Year; you end up with: 02092011 (Sep 2, 2011) which (as a string) is LESS than 03011900 (Jan 3, 1900). Using Year, Month, Day; it would be 20110902 and 19000103, which would compare safely as a string.

 

Also, your delete logic is deleting all records with an ID less than the current ID (AND NOT EQUAL TO IT), when you have confirmed the date is LESS. You really should ONLY be deleting the ONE ROW that you have tested.

 

However, don't waste resources and time doing this in PHP. Make sure your "date" column is a DATE or DATETIME datatype, then remove the loop and just use a SINGLE query:

 

DELETE FROM events WHERE `date` < CURDATE()

 

Note: Date is a reserved word in SQL so we have to include it in backticks. I highly recommend you rename that column to avoid confusion and problems.

[Pikachu2000]

'date' is one of those words that sounds like it should be, but is actually not a reserved word in MySQL.

[DavidAM]

Wow, you learn something new every day! I'd go check, but I always have trouble finding the "reserved words" list in the mySql documentation. How can it not be reserved when it is the name of a datatype? Makes no sense to me. In any case, I would avoid using it as a column name (or table name or database name).

[matvespa]

	//$today = date("Ymd");
	//$vLongDate = date("Ymd", strtotime($thisDate));
	mysql_query("DELETE FROM events WHERE 'date' <  CURDATE()");

 

This is my code. However, it doesnt delete anything. Is this how to do it? Without the if statement at all? Or am i missing anything?

[DavidAM]

mysql_query("DELETE FROM events WHERE 'date' <  CURDATE()");

 

If "date" were a reserved word (as I had thought) you need back-ticks, NOT quotes

 

mysql_query("DELETE FROM events WHERE `date` <  CURDATE()");

 

Since "date" is NOT a reserved word, you can do it without

 

mysql_query("DELETE FROM events WHERE date <  CURDATE()");

[matvespa]

THANK YOU! SOLVED!