Please give PHP code for date range deletion

[aak1772]

I need PHP code for date range deletion for date and date_time columns in MYSQL DB.

I have a php form which has two select option drop downs. These drop are populated by a date_time column in MySQL and posted for deletion using $_POST as follows:

<h4>Delete News </h4> 

<?php

include ('conn_db.php');

if ($_POST['submit'])
{
	$start_date = $_POST['start_date'];
	$end_date   = $_POST['end_date'];
	
	echo $start_date;
	echo $end_date;
	
	@mysql_query ("delete 	from dow_news where date_format(pub_dt, '%b, %d %Y %W') like between '%$start_date%' and '%$end_date%'");

	echo "<script> alert ('News Deleted.')<//script>";
	echo '<meta http-equiv="refresh" content="0;url=delete_news.php" />';
}

?>


<form name="form1" action="delete_news.php" method="POST">
<?php

$query = "select distinct date_format(pub_dt, '%b, %d %Y %W') pub_dt1 from dow_news";


$result = @mysql_query ($query);
echo "News Deletion Start Date ";
echo "<select name= \"start_date\">";
while ($row_start = mysql_fetch_array ($result, MYSQL_ASSOC))
	if ( $row_start['pub_dt1'] == $start_date)
		echo "<option selected value=\"{$row_start['pub_dt1']}\">{$row_start['pub_dt1']}</option>\n";
	else
		echo "<option value=\"{$row_start['pub_dt1']}\">{$row_start['pub_dt1']}</option>\n";
echo "</select>";

echo "<br />";
echo "<br />";

$result = @mysql_query ($query);
echo "News Deletion End Date &nbsp;";
echo "<select name= \"end_date\">";
while ($row_end = mysql_fetch_array ($result, MYSQL_ASSOC))
	if ( $row_end['pub_dt1'] == $end_date)
		echo "<option selected value=\"{$row_end['pub_dt1']}\">{$row_end['pub_dt1']}</option>\n";
	else
		echo "<option value=\"{$row_end['pub_dt1']}\">{$row_end['pub_dt1']}</option>\n";
echo "</select>";

?>

<br />
<br />

<input type="submit" name="submit" value="Delete">

</form>

I want to know what should I do in the delete SQL query to able to delete the rows requested by the form? Thank you.

 

[techris]

Hi.

This is not tested, but I think it can get you in the right direction.

<?PHP 


// This all depends on the format of your dates.  This was written for date format MM-DD-YYYY 

// first take apart start_date and separate into values

	$from_d = substr($start_date, 3, 2);
	$from_m = substr($start_date, 3, 2);
	$from_y = substr($start_date, 3, 2);
	
// separate out ending date to get an end point

	$to_d = substr($to_date, 3, 2);
	$to_m = substr($to_date, 3, 2) + '1';
	$to_y = substr($to_date, 3, 2);

// create a date range between the two dates

$hour=date('H');
$min=date('i');
$sec=date('s');

$to_far = date("m-d-Y", mktime($hour, $min, $sec, $to_m, $to_d, $to_y));

$g='1';

$thedates[0] = $start_date;

while ($thedates[$g] != $to_far) {
	$from_d = $from_d + '1';
	$thedates[$g] = date("m-d-Y", mktime($hour, $min, $sec, $from_m, $from_d, $from_y));

	$g++;
}

// now search your sql table for the delete dates

include ('conn_db.php');

for ($i='0'; $i<count($thedates); $i++) {

	$sql="DELETE FROM $table WHERE date='$thedates[$i]'";
	$result = mysql_query($sql);

	if ($result) {
		echo "successful delete of date $thedates[$i]";
	
	} else {
		echo "error deleting $thedates[$i] from $table";
	
	}

} // end for loop


?>

Enjoy!

Techris

 

[jpadie]

does it need to be so complex? and do you really need a loop to delete? assuming that the dates comes in from the user as YYYY-MM-DD (the MySql standard) why not just do:

delete from table where datefield between start_date and end_date

if the s-d and e-d need to be manipulated from another format then use the date function

$start_date = "1 January 2006";
$sqlStart_date = date ("Y-m-d", strtotime($start_date));

if the dates are stored as unix epoch values then just use the strtotime() function without the date.