How can I find out the id of the first record in a database table, assuming record 1 is deleted?

[Chappie]

Hello.

Just a brief query regarding mysql and php.

I'm currently planning, looking into a simple method of searching a database table, and creating list 10 most clicked links. (ie creating a top 10).
I've done the search, created the temporary array, then inserted the array in a separate database table.

All I'd like to know how do I find out the primary key/id of the first record in the table, then I can delete it once the next record has been inserted?
This would leave only 2 or 3 records in the table at any one time.

$last_id = $conn->lastInsertId(); would fetch the last record in the table.
Would $first_id = $conn->firstInsertId(); show the first record, assuming record 1 has been deleted.

Thank You.

 

[VBService]

AFAIK,
In SQL databases there is no such thing as firstInsertId(). Only lastInsertId() exists, because the database does not explicitly track the "first row.". Rows in a table have no inherent order, you can only define an order explicitly, for example using ORDER BY id ASC or ORDER BY created_at ASC.

If you want to get the first record’s primary key (the smallest id value currently in the table), you can query it directly, for example:

// This will give you the primary key of the "earliest" row (based on id)
$sql = "SELECT id FROM your_table ORDER BY id ASC LIMIT 1";
$stmt = $conn->query($sql);
$first_id = $stmt->fetchColumn();

// Then you can delete it like
$sql = "DELETE FROM your_table WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->execute([':id' => $first_id]);

An important notes, Auto-increment gaps, If you delete a row, it's id is gone forever, the next insert keeps incrementing. That’s totally fine, but keep in mind that the first row won’t necessarily be id = 1.

 

[Chappie]

Hello VBService.

Thank you for your reply/post. I've now found a solution, after reading your post .
I have read the database, and placed it into an array. I've also gone a little further and used date diff, so that if the database record is less than 5 days old, then add to the array, then read back the first 3 or 4 entries in the array.

This works for me.

Thank you again.