...

View Full Version : Resolved mysql delete record problem



afdah
09-20-2011, 09:02 AM
what is wrong with the below code?
for example, i have 10 rows in files2
5 rows fulfil this condition ($filetime < $oldest)
no matter what, 1 row get deleted instead of 5. to delete the 5 affected rows, i need to run the below code 5 times.
i believe it's something to do with my while loop statement
please assist. thank you.



$q = $this->sql->query("SELECT * FROM `files2`");
$t = $this->sql->count_rows($q);
if ($t >= 1) {
while ($r = $this->sql->fetch_row($q)) {
$filetime = $r['time'];
$oldest = time() - $this->ttl*60;
if ($filetime < $oldest) {
$this->sql->query("DELETE FROM `files2` WHERE `time`='$filetime'");
}
}
}

Wanna
09-20-2011, 09:12 AM
You won't need a while loop for this.

With a delete query you delete all the rows matching your pattern.
So like i see it: Every row needs to be deleted the first time you run the query.

afdah
09-20-2011, 10:06 AM
You won't need a while loop for this.

With a delete query you delete all the rows matching your pattern.
So like i see it: Every row needs to be deleted the first time you run the query.

i know what you mean. the revised code below fulfil my requirement.
thank you. :-)



$q = $this->sql->query("SELECT * FROM `files2`");
$t = $this->sql->count_rows($q);
if ($t >= 1) {
$oldest = time() - $this->ttl*60;
$this->sql->query("DELETE FROM `files2` WHERE `time`<'$oldest'");
}



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum