Only 25 records for each ID?!?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jmansa
Forum Commoner
Posts: 81
Joined: Wed Aug 23, 2006 4:00 am

Only 25 records for each ID?!?

Post by jmansa »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


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:
[syntax="asp"] 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?


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Yes. Logically it'll be exactly the same. The SQL will be exactly the same too. The only difference will be in the code syntax.
jmansa
Forum Commoner
Posts: 81
Joined: Wed Aug 23, 2006 4:00 am

Post by jmansa »

Now I tryid this...:

Code: Select all

$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 inserts the record as it should, but doesnt delete the oldest record depending on ID!? Can somebody see the error?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Your subquery won't work, I would imagine. mysql_error() may tell you more.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

jmansa wrote:Now I tryid this...:

Code: Select all

$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)"); 
} 

 Can somebody see the error?[/quote]
Your IN() function call needs to have a valid SELECT statement in it.
Post Reply