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
