How to delete rows between two dates

[amirkhansemail]

Hello,

I want to delete rows with the values posted by two date range drop downs in the following form. The rest of the form functionality is working perfectly but I am unable to write the correct delete from table SQL command to be able to delete the rows between the date range. Your help is highly appreciated if you guide me how to do that.

The code is 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;
[b]	
	@mysql_query ("delete 	from dow_news where date_format(pub_dt, '%b, %d %Y %W') like between '%$start_date%' and '%$end_date%'");
[/b]
	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>

If I need to change the way drop downs are populated, kindly let me know.

Thanks and best regards,
Amir Khan

 

[r937]

on behalf of those of us who don't do php, thanks for highlighting the sql

your sql treats dates as strings, which is not the best way to do it, and uses LIKE BETWEEN, which isn't valid

change this --

delete
from dow_news
where date_format(pub_dt, '%b, %d %Y %W')
like between '%$start_date%' and '%$end_date%'

to this --

delete
from dow_news
where pub_dt between '$start_date' and '$end_date'

this assumes that $start_date and $end_date are date values

note that if pub_dt contains datetime values, instead of date values, then you should use this instead --

delete
from dow_news
where pub_dt >= '$start_date'
and pub_dt < '$end_date'

where $end_date is the day after the last pubs you want included




r937.com | rudy.ca