delete last row from specific user

[canabatz]

i got for example results like that

user1
user1
user2
user1
user1
user3
user1

i want to limit user1 to have maximum 4 resilts ,if user1 have 5 results it will be deleted ,this is example of code that im trying to build:

<?
$query = mysql_query("SELECT * FROM `my_table` where username='$username' ") or die(mysql_error());
$last = '';
$count = 0;
while($line = mysql_fetch_assoc($query)) {
  if($last == $line['username']) $count++; 
  else {
    $last = $line['username'];
    $count = 1;
  }
  if($count > 4) {
$sql_delete="DELETE FROM my_table WHERE username = '$username'";
mysql_query($sql_delete)or die(mysql_error());
  }
} 
?>

what i need is to delete the oldest record from the specific user ,but it is not doing that!! ,please help!

thanx in advanced.

[Ciary]

i can guess what does happen: all records from user1 are deleted when he has more then 4 records.

the problem is very simple. if you use a delete query it will delete every record that matches with the given patern.

WHERE username = '$username'"

what you need to do is this. in your while-loop you save the ID or the primary key of your record in a variable.

then you use the same query but this time you use

WHERE PK_ID= '$lastID'"

[Dormilich]

there's probably a way to do that using SQL too (using subqueries and such), but you should ask the SQL guys, because that's a more advanced matter.

[canabatz]

can i do it like that?

<?
$query = mysql_query("SELECT * FROM `my_table` where username='$username' order by id asc") or die(mysql_error());
$last = '';
$count = 0;
while($line = mysql_fetch_assoc($query)) {
  if($last == $line['username']) $count++; 
  else {
    $last = $line['username'];
    $count = 1;
  }
  if($count > 4) {
$sql_delete="DELETE FROM my_table WHERE username = '$username' order by id asc limit 1";
mysql_query($sql_delete)or die(mysql_error());
  }
} 
?>

i got the last id from user1 ,and after i delete the oldest id from user1 with limit 1

is it going to work like that? i didnt try it!

thanx

[Ciary]

i'm not sure. never used delete with limit. i think you just need to try if it does what you need. if it does, pls report back at the forum. i think this might also be helpful to many others :)

[canabatz]

working perfect!! :)

working perfect!! :)

[canabatz]

thanx all for your help!

[Ciary]

np, i'm glad to help :)