[RESOLVED] Deleting older entries.. (mysql)

goaman

Hi guys..
I need help in a query..

I've a comment form.. I've allowed only 10 comments to be viewd, so when I submit new comments, I want to know how to delete older ID comments except the latest 10 comments. Means if there are 13 comments.. 13...3 shd be there and very 1st and 2nd(older) comments must be deleted.

I cannot think of any easy way to do this... pls, lend me a hand.. thanks 🙂

bpat1434

DELETE 
FROM `table`
WHERE comment_id=(SELECT MIN(comment_id) FROM `table` WHERE post_id=$post_id)

THat's probably the simples. The subquery [ the part between parenthesis ] returns the lowest ID based upon the post ID. So your table should look something like:
post_id: refers to post unique key (foreign key to posts table)
comment_id: refers to comment unique key (primary key for comments table)

goaman

tnx for the reply, could you please explain this query? as I understood, this query will delete the lowest ID entry(1 row only?), but it doesn't make sure to leave latest 10 comments.

[edit]

$post_id = mysql_insert_id();

//I've tested the code, it doesn't delete the older entries..  

$query = @mysql_query("DELETE FROM `comments` WHERE id=(SELECT MIN(id) FROM `comments` WHERE id=$post_id)");

bpat1434

umm... why would you delete a row that isn't there? if $post_id is the next mySQL auto-increment value, nothing will be deleted. You'd need to use the same value that you inserted with....

As for leaving the 10 comments, if you're adding 1 to the current comment, and deleting 1, would the number change? I guess you could try this:

IF( (SELECT COUNT(*) FROM `table` WHERE post_id='$post_id')>10, (DELETE FROM `table` WHERE id=(SELECT MIN(id) FROM `table` WHERE post_id='$post_id')), null)

Not sure if that will work, but it gets you going... if it doesn't work, just take the if() out, and do it via PHP:

if(mysql_num_rows(mysql_query("SELECT COUNT(*) FROM `table` WHERE post_id='$post_id'"))>10)
{
  mysql_query("DELETE FROM `table` WHERE id=(SELECT MIN(id) FROM `table` WHERE post_id='$post_id'");
}

goaman

hey, thanks for the reply, this code doesn't do anything.. this shd delete one lowest ID row, but it doesn't do.. darn..

if(mysql_num_rows(mysql_query("SELECT COUNT(*) FROM `table'"))>10)
{
  mysql_query("DELETE FROM `table` WHERE id=(SELECT MIN(id) FROM `table`)");
}

goaman

I've solved the problem... maybe it's not the best way to do, but it works.. 🙂
thanx for your time guys..

SOLUTION:

$result = mysql_query('SELECT MIN(id) as min, MAX(id) AS max FROM comments') or exit(mysql_error());
$row = mysql_fetch_assoc($result);
$delfoo = $row['max'] - 10;
$min = $row['min'];

//Loop DELETE
for($i=$min; $i<$delfoo; $i++)
{
  mysql_query("DELETE FROM `comments` WHERE id='$i'");
}