Jump to content

deleting old records


monika0021
 Share

Recommended Posts

HelloI am trying to delete records from mysql database. Records should be removed after the chosen date. Unfortunately, it doesn't work. Instead of removing only outdated records, all records added today are deleted with warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 25 (- the line with the while loop)Here's the code:

<?mysql_connect ("host", "user", "password") or       die ("Cannot connect with MySQL");      mysql_select_db ("db") or       die ("Cannot connect with database");     $query = "SELECT * FROM table WHERE year IS NOT NULL”; $result = mysql_query($query) or die (mysql_error());	      while ($rekord = mysql_fetch_assoc ($result)) {   	 $year = $rekord['year']; // varchar(4) field in db  $month = $rekord['month']; //char(2) field in db  $day = $rekord['day']; //char(2) field in db    	if ($year && $month && $day) {	$day=$day+1; // day of deleting records	$timestamp = strtotime($year."-".$month."-".$day);	$kasuj=date("Y-m-d", $timestamp);        if (date("Y-m-d")>=$kasuj) {$query = "DELETE FROM table WHERE CURDATE() >='".$kasuj."' AND year IS NOT NULL";    	$wynik = mysql_query($query) or die (mysql_error());	print "Old recods removed";                 }    }                                                 }?>      

Link to comment
Share on other sites

Try this code. I loaded it into a code editor to format it, and it looks like you had a non-quote closing a string. This line:$query = "SELECT * FROM table WHERE year IS NOT NULL";was closed by some other character (not a "). It looked like a ", but it was something else (some sort of evil twin anti-quote no doubt).

<?phpmysql_connect ("host", "user", "password") or  die ("Cannot connect with MySQL");mysql_select_db ("db") or  die ("Cannot connect with database");$query = "SELECT * FROM table WHERE year IS NOT NULL";$result = mysql_query($query) or die (mysql_error());while ($rekord = mysql_fetch_assoc ($result)) {  $year = $rekord['year']; // varchar(4) field in db  $month = $rekord['month']; //char(2) field in db  $day = $rekord['day']; //char(2) field in db  if ($year && $month && $day)   {    $day=$day+1; // day of deleting records    $timestamp = strtotime($year."-".$month."-".$day);    $kasuj=date("Y-m-d", $timestamp);    if (date("Y-m-d")>=$kasuj)     {      $query = "DELETE FROM table WHERE CURDATE() >='".$kasuj."' AND year IS NOT NULL";      mysql_query($query) or die (mysql_error());      print "Old recods removed";    }  }}?>

Link to comment
Share on other sites

Try this code.  I loaded it into a code editor to format it, and it looks like you had a non-quote closing a string.  This line:$query = "SELECT * FROM table WHERE year IS NOT NULL";was closed by some other character (not a ").  It looked like a ", but it was something else (some sort of evil twin anti-quote no doubt).
<?phpmysql_connect ("host", "user", "password") or  die ("Cannot connect with MySQL");mysql_select_db ("db") or  die ("Cannot connect with database");$query = "SELECT * FROM table WHERE year IS NOT NULL";$result = mysql_query($query) or die (mysql_error());while ($rekord = mysql_fetch_assoc ($result)) {  $year = $rekord['year']; // varchar(4) field in db  $month = $rekord['month']; //char(2) field in db  $day = $rekord['day']; //char(2) field in db  if ($year && $month && $day)   {    $day=$day+1; // day of deleting records    $timestamp = strtotime($year."-".$month."-".$day);    $kasuj=date("Y-m-d", $timestamp);    if (date("Y-m-d")>=$kasuj)     {      $query = "DELETE FROM table WHERE CURDATE() >='".$kasuj."' AND year IS NOT NULL";      mysql_query($query) or die (mysql_error());      print "Old recods removed";    }  }}?>

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...