ExpiryDate On 28-Feb-2003 Jeff Snoxell wrote: > Hi, > > If I run a mysql query then work through the results of that query one at a > time, deleting the record sometimes eg: > > while (my $href = $sth->fetchrow_hashref()) > { > if ($href->{'ExpiryDate'} eq '2003-02-22 00:00:00') { > # Do something > # then... > $db->do("DELETE FROM MyTable WHERE ID=$href->{'ID'}"); > } > else { > # Do something else > } > } > > Does the DELETE cause a change in the results of the surrounding $sth > parsing loop? > > I know it would be best to do a: > > DELETE FROM MyTable WHERE ExpiryDate = whatever > > But I need to use the data to do some other stuff, only from those records > that are up for deletion. > > Any ideas? >
Fetch all the IDs of interest into an array and loop on that. In my libsql.pl I wrote a sub: sub GetArray { my $qry= shift; my @na=(); my $res=SQLQuery($qry); if ($res) { while (my(@row) = $res->fetchrow()) { push( @na, $row[0]) ; } } return(@na); } $tbl='mytable'; $qry="SELECT id FROM $tbl WHERE ExpiryDate='2003-02-22'"; @ids=GetArray($qry); while ( my(@id)= splice (@ids,0,64)) { $deleteqry="DELETE FROM $tbl WHERE id IN (" .join(',',@id) .")"; SQLQuery($deleteqry); } $qry="SELECT id FROM $tbl WHERE ExpiryDate < DATE_ADD(CURRENT_DATE, INTERVAL 1 WEEK)"; @ids=GetArray($qry); while ( @id= splice (@ids,0,64)) { $weekqry="UPDATE $tbl SET foo='bar' WHERE id IN (" .join(',',@id) .")"; SQLQuery($weekqry); } Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php