mysql delete

[blueman378]

hi guys well im trying to write a code which orders all results in a table by the "time" column and if there is more than 20 it will delete the oldest one i have this so far

$q = "SELECT * FROM latest
          ORDER BY `time` ASC
         ";   
    $result = $database->query($q) or die("Error: " . mysql_error());
    /* Error occurred, return given name by default */
    $num_rows = mysql_numrows($result);
    if( $num_rows == 20 ){
      mysql_query("DELETE FROM latest");
    }

 

but im kinda stuck as how to tell it to delete the result with the oldest time

 

cheers matt

[burnside]

try :

$q = "SELECT * FROM latest ORDER BY `time` ASC";   
    $result = $database->query($q) or die("Error: " . mysql_error());
    /* Error occurred, return given name by default */
    $num_rows = mysql_num_rows($result);
    if( $num_rows == 20 ){
      mysql_query("DELETE FROM latest");
    }

[blueman378]

well thats not what i mean i mean i need to extend

$q = "SELECT * FROM latest ORDER BY `time` ASC";   
    $result = $database->query($q) or die("Error: " . mysql_error());
    /* Error occurred, return given name by default */
    $num_rows = mysql_num_rows($result);
    if( $num_rows == 20 ){
      mysql_query("DELETE FROM latest WHERE !!!HERE!!!");
    }

 

to make it delete the oldest results

 

cheers

[psychowolvesbane]

Have you tried ordering the records into descending order in your sql selection?

[burnside]

DELTE * FROM `table` WHERE id >= 20

 

thats if your only allowing 20 records will delete all ov them above  20

 

 

[blueman378]

well that could work buit the data is inserted into the table with 2 columns one is simply name the other is time which is something like 020220080156 sort thing which is 2nd of the 2nd 2008 at 1:56 am so i order using that not ids,

 

[AndyB]

well that could work buit the data is inserted into the table with 2 columns one is simply name the other is time which is something like 020220080156 sort thing which is 2nd of the 2nd 2008 at 1:56 am ...

 

Inventing your own method of storing datetime is your mistake. Use a real datetime stamp and your problems will evaporate.