[SOLVED] How to delete the oldest record

[liam1412]

I have a table that users can only have 5 records in. What query do I use to find out which is the oldest and delete if they try to insert more. Baring in mind my table had no date column and has no reason to so I want to just do it based on the primary key which is car_id.

[chronister]

If your table uses an auto-increment on your key, then you simply need to do this..
[code]

$query="SELECT * FROM yourtablename WHERE user_id=$user_id ORDER BY car_id ASC"; [/code]

This would return all records sorted by car_id lowest to greatest where user_id is equal to user_id.. assuming that car_id uses the auto-increment, then the lowest number is the oldest and the highest number is the newest.

Simply count the results, then if it is greater than 4 (yes 4 because 5 is greater than 4), then delete the lowest number in the results.

Hope this helps

Post back if you need more clarification.

[trq]

In order to delete the oldest record reliably you need a date field. An autoincrement field is NOT to be relied upon for such tasks, that is NOT at all what it is designed for.

[liam1412]

[quote]In order to delete the oldest record reliably you need a date field. An autoincrement field is NOT to be relied upon for such tasks, that is NOT at all what it is designed for.[/quote]

SO supposing a did add a date field which im assuming (using now() or date() ?? ?? Which one ???) then I would obviously order by date Ascending. So then if i just use mysql_fetch_array this record taht it fetches back should be the oldest. i can then collect the car id from that and execute the delete.  Maybe I have misunderstood or im just ssuming coz now I think about it I haven't seen taht mentiond.  Man Im lost  ;D  ??? ??? ???

[redarrow]

select example
[code]
$query="SELECT * FROM yourtablename WHERE `user_id`='$user_id' AND `date_added`='$date_added' ASC";
[/code]

add to the database table an int for the date and use the command now()

[chronister]

Thorpe is right, an auto id field is not the best way.. I would use it depending on what the circumstances were, but for a more accurate way use a date field.

Make it a timestamp field and set it to use current timestamp you don't even have to worry about the coding for the timestamp.. it will add the current date automatically..

nate

[liam1412]

Sp im right in saying that when you don't use a while loop then mysql only fetch's back the oldest record  in the record set. i have only ever collected the one row need or echoed out all the rows by using a while loop.  Basically if I don't use a while loop does it bring abck the whole lot in one massive array!!!

[redarrow]

example

[code]

$date=$time();

query="insert into whatever (date) value('$date')";


to get the time out you do this

select statement while loop then this


echo "date('y-m-d',$date)";

[/code]

[liam1412]

But the then time is just a string. I thought string was just text regardless of wether it has numbers in it so how do you then compare which is the oldest.  I think it might be time for bed and hit this with a clear head in the morning. lol  ;D

[liam1412]

Im okay with adding a time column what I need to know is how to select the oldest record from the database.  Do I need to fetch them all back ro is there a way of just selecting it form the database. if I do need to fetch them all back what is the code for the picking the oldest.

[redarrow]

not sure but it nearly there .
[code]

<?php

$time_now=time();

$query="select * from what_ever where id='$id' and $time_now < time limit 1";

?>

[/code]

[liam1412]

Querying my database isn't the issue.  What i don't understand is where I get the value to compare it with.  Lets just pretend im not adding a time field and using the primary key.

my code

$last5 = "SELECT * FROM last_5 WHERE userid=$userid ORDER BY car_id ASC";
$query = mysql_query($last_5);

Will return upto 5 records. if it does return 5 I want to delete the first one to make way for the new one. 

Where do I go from here. 

[redarrow]

sorry i dont no as i use a date as always, but deleting a record via last id is bad code pratice if you insist look at the example given earlier ok.

[Cagecrawler]

For that example, put LIMIT 1 on the end of the query so it only returns the oldest.  In fact, you could use this query:

"DELETE FROM last_5 WHERE userid=$userid ORDER BY car_id ASC LIMIT 1"

I'd still add in a date column, like was earlier mentioned.  Then you can use this:

"DELETE FROM last_5 WHERE userid=$userid ORDER BY date ASC LIMIT 1"

[liam1412]

Thanks so much mate.  Thats all I needed to Know.  Now I know collect the date time from that result and execute my delete. phew. Got there in the end. thanks everyone for all you help.