mysql query to delete records that match multiple fields

I have a database that looks like this:

CREATE TABLE cargo (
    cargodate int unsigned NOT NULL,
    cargoname CHAR(128) NOT NULL,
    lattitude double NOT NULL,
    longitude double NOT NULL,
    user CHAR(64) NOT NULL开发者_如何学Python
) type=MyISAM;

I want to make sure there are no more than 5 entries for this cargo at the same location with the same user. A user can have multiple entries as long as they are in different locations (lattitude, longitude).

How do I make my sql INSERT statement take care of this?

Right now I execute:

   INSERT INTO cargo VALUES (UNIX_TIMESTAMP(), '{$cargoname}', '{$lat}', '{$lng}', '{$user}');

I can do a DELETE FROM, but I want to only delete entries if there are more than 5. In that case I want to delete the oldest entries

Thanks Deshawnt

You could use triggers http://dev.mysql.com/doc/refman/5.0/en/triggers.html Just after insert you may delete all not needed entries.

Using a trigger as mentioned by Ruslan Polutsygan would be the most natural solution.

If you don't want to mess around with them, then the alternative would be to run:

SELECT * FROM cargo ORDER BY cargodate DESC LIMIT 4,18446744073709551615

before you run an insert, and then delete the rows that are returned by that query, and then do the insert.

As a side-note, the number 18446744073709551615 was taken out of the example from the MySQL documentation:

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter