SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Maximum 10 records in db!?

    I have asked this before, but havent got it to work yet so here goes again!
    I'm trying to create a script wich only allows 10 records in a dbtable for a specific ID. I have tryid with this, but somehow it doesnt delete like it should and I cant see my error!?
    PHP Code:
    $query=mysql_query("SELECT * FROM list WHERE PlayerID=1"); 
    if(
    mysql_num_rows($query) > 10

      
    mysql_query("DELETE * FROM list WHERE Date IN(SELECT Date FROM list ORDER BY Date LIMIT 1)"); 

    Please help somebody!

  2. #2
    SitePoint Addict tina88's Avatar
    Join Date
    Jan 2007
    Location
    UK
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hiya, not sure I am understanding you correctly but from what you are saying you are adding a record to a specific ID and then checking if there are more than 10 records? If there are then you are deleting the extra? It seems like it would be much simpler to query the db and count the number of rows for the ID before adding to determine if it will allow? Is this what you mean? Tina

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats exactly what I mean, but I want to delete the oldest record and allow the new one... but I'm not shure how to about that!

  4. #4
    Always learning kigoobe's Avatar
    Join Date
    May 2004
    Location
    Paris
    Posts
    1,563
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First, you may use something like this -
    PHP Code:
    $query=mysql_query("COUNT (PlayerID) FROM list WHERE PlayerID=1"); //this will make your query faster, if you are using this just to count the number of returned rows. 
    Then, to delete the last in case you have more than 10 (once you have inserted the new entries), I may add another field in the table (say, counter) that will have numbers of the rows, vis-à-vis playerIDs, from 1 ++

    In that case, I can simply delete that using a query
    PHP Code:
    $query=mysql_query("DELETE FROM list WHERE PlayerID=1 AND counter>10"); 
    Having said that, there must be something simpler (incase you don't want to add the field counter and change a few codes here and there), you can try posting this in mysql forum, Rudy or longneck might give you a very simple solution.

  5. #5
    SitePoint Addict tina88's Avatar
    Join Date
    Jan 2007
    Location
    UK
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could use something like min() to select the earliest record?
    Not sure if this syntax is correct or if this is the best way to do it but you could try something like this
    PHP Code:
    $query mysql_query("SELECT PlayerID FROM list WHERE PlayerID = 1"); 
    if(
    mysql_num_rows($query) > 10) { 
          
    $query2 mysql_query("SELECT min(Date) FROM list WHERE PlayerID = 1");
          
    $row mysql_fetch_array($query2);
          
    $Date $row['Date'];
          
    mysql_query("DELETE * FROM list WHERE Date = '$Date' AND PlayerID = 1"); 



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •