CODEWITHSUNDEEP
phpmysql
Rice_Crisp
Rice_Crisp

Reputation: 1261

Deleting older database entries

I'm trying to keep the 10 most recent entries in my database and delete the older ones. I tried DELETE FROM people ORDER BY id DESC LIMIT $excess, but it just deleted the top 10 entries.

$query = "SELECT * FROM people";
$result = mysqli_query($conn, $query);
$count = mysqli_num_rows($result);
if ($count > 10) {
    $excess = $count - 10;
    $query = "DELETE FROM people WHERE id IN(SELECT id FROM people ORDER BY id DESC LIMIT '$excess')";
    mysqli_query($conn, $query);
}

Upvotes: 0

Views: 101

Answers (4)

Sammitch
Sammitch

Reputation: 32272

Your logic is all over the place, [you should ORDER BY ASC, not DESC] and your query will take ages if there are [for example] 10,000 entries because you'll have an IN clause with 9,990 entries to compare all 10,000 to.

Select the 10 most recent, and delete where NOT in.

DELETE FROM people
  WHERE id NOT IN(
    SELECT id 
    FROM people
    ORDER BY id DESC
    LIMIT 10
  )

Upvotes: 0

Vivek Sadh
Vivek Sadh

Reputation: 4268

You can use this:-

DELETE FROM `people`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `people`
    ORDER BY id DESC
    LIMIT 10
  ) 
);

Also your query is logically incorrect and you are fetching the records in descending order. i.e. Latest to older and you are deleting the most recent records. Use ASC instead.

Upvotes: 2

Joel Hinz
Joel Hinz

Reputation: 25414

Something like this? Gets the ten latest ids in the subquery, then deletes all of the other ids.

DELETE FROM people WHERE id NOT IN (SELECT id FROM PEOPLE ORDER BY id DESC LIMIT 10)

Upvotes: 2

user2520968
user2520968

Reputation: 368

Maybe find the ID of the 10th element and then delete all rows which are older?

Upvotes: -1

Related Questions