Delete all rows except first 50

Hello mates, I want to delete all mysql rows in a table except last 50 rows. Please help me, thanks...

$query="SELECT * FROM looks"; 

    $result=mysql_query($query);
    echo mysql_error();

        while($row = mysql_fetch_array($result, MYSQL_BOTH))

        {
                $query1="SELECT * FROM looks where id = '".$row[1]."'"; 

                $result1=mysql_query($query1);
                $count = mysql_num_rows($result1) - 50;

                mysql_query("DELETE FROM looks WHERE id='".$row[1]."' LIMIT $count",$link);
                echo "deleted $row[1] ";

}
        echo mysql_error();

PHP:

 

I'm not sure i got it right but wouldn't it be more simple to do it like :

$count=mysql_query("SELECT COUNT(*) FROM looks");
$delete=$count-50;
mysql_query("DELETE FROM looks WHERE id<'$delete'");

Code (markup):

 

Try this but I have not tested it.

$query="SELECT * FROM looks ORDER BY id ASC";

    $result=mysql_query($query);
    echo mysql_error();

    $all_looks = array();

    for($i=0; $i<mysql_num_rows($result); $i++){
        $row = mysql_fetch_array($result);

        $all_looks[$i] = $row["id"];
    }

    for($i=0; $i<count($all_looks)-50; $i++){
         mysql_query("DELETE FROM looks WHERE id='".$all_looks[$i]."';");
    }

    echo mysql_error();

Code (markup):

 

try this

$query="SELECT * FROM looks";

    $result=mysql_query($query);
    echo mysql_error();

        while($row = mysql_fetch_array($result, MYSQL_BOTH))

        {
                [COLOR="DarkGreen"]//get the 50th id[/COLOR]
                $query1="SELECT id FROM looks where id = '".$row[1]."' order by id asc limit 49,1"; 

                $result1=mysql_query($query1);
             
                [COLOR="DarkGreen"]//delete anything that have id more than the 50th one[/COLOR]
                mysql_query("DELETE FROM looks WHERE id >". $result1.",$link); 
                echo "deleted $row[1] ";

}
        echo mysql_error();

Code (markup):

 

Nice, but how to do it except last 50 rows ?

 

I have changed my post, try it again.

 

x = select count(*) from table;
y = select id from table order by id limit x - 50, 1;
delete from table where id < y;

 

But "looks" "id"-s can be for example 1,24,58,158,159,178,254...

 

That worked perfectly, thanks a lot

 

Hi,

As I like to say, SQL is not procedural language

DELETE FROM looks USING looks NATURAL LEFT JOIN (SELECT id FROM looks a ORDER BY id DESC LIMIT 50) A WHERE A.id IS NULL;

Code (markup):

Regards,
Nick