monika0021 Posted April 28, 2006 Share Posted April 28, 2006 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 More sharing options...
justsomeguy Posted April 28, 2006 Share Posted April 28, 2006 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 More sharing options...
monika0021 Posted April 29, 2006 Author Share Posted April 29, 2006 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"; } }}?> <{POST_SNAPBACK}> Link to comment Share on other sites More sharing options...
monika0021 Posted April 29, 2006 Author Share Posted April 29, 2006 <{POST_SNAPBACK}> Thanks a lot, but still no result. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now