Only 25 records for each ID!?!?

jmansa

Is it possible to only collect 25 records for each ID? When I insert the 26'th record for that ID I want to get rid of the oldest record for that ID? I did it once in ASP:queasy: and it looked like this:

 strSQL = "SELECT * FROM list WHERE ID=1"
 rs.open strSQL, conn, 1 , 1

 If rs.recordcount > 24 Then
 strSQL = "DELETE * FROM list WHERE Date IN(SELECT TOP 1 Date FROM list ORDER BY Date)"
 conn.Execute (strSQL)
 End If

 strSQL = "INSERT INTO list ( ID, Point, Name ) VALUES (" & _
 "'" & sqllize(Request("player1ID")) & "', '" & sqllize(Request("player1Point")) & "', '" & _
	sqllize(Request("Player1Name")) & "')"
 conn.Execute (strSQL)
 rs.Close

Is this possible in PHP, and if yes, how?

Weedpacket

It is possible in PHP, and it would be done in pretty much the same way (though there are more advanced techniques possible, both in PHP and in the database).
Have a crack at it; you should find it worthwhile.

jmansa

Now I tryid this but only got half the result I wanted:

$query=mysql_query("SELECT * FROM list WHERE PlayerID=1"); 
if(mysql_num_rows($query) > 24) 
{ 
  mysql_query("DELETE * FROM list WHERE Date IN(LIMIT 1 Date FROM list ORDER BY Date)"); 
} 

$query="INSERT INTO list SET Point='$appoints', Name='Arnold Perterson', PlayerID='1'"; 
mysql_query($query);

It inserted the record like it should but didn't delete the oldest record? Can somebody see the error!?

Weedpacket

You're missing a "SELECT Date" in the delete command's subquery.