php/mysql question

[____]

I built a shoutbox type thing on my flash page...It works fine...but i want it to never have any more messages than 20...so i can check how many rows in the database..but how do i erase the 21st line.
I'm fairly new to sql...but fairly versed at php...thanks for reading
Eric

[prt1]

Why not use 'limit 20' at the end of your SQL string to only get 20 records back.
ie SELECT * FROM COMPANY ORDER LIMIT 20;

You can then delete the extra records at your leisure.

[freddycodes]

Yeah you will need to tag each one with a timestamp, I assume you have a field for time posted. So you can get the latest 20


select * from tablename order by timestampfield LIMIT 20

[____]

Yeah you will need to tag each one with a timestamp, I assume you have a field for time posted. So you can get the latest 20


select * from tablename order by timestampfield LIMIT 20


Ya thats the sql query i have right now....But how do i erase the rest....

[freddycodes]

Why do you care about erasing them. Its a database.

[Das123]

In php you can set up a query that gives you $myRowCount of the table.
If $myRowCount > 20 then $numberToDelete = $myRowCount - 20
and run a query...
DELETE FROM myTable ORDER BY timestamp LIMIT $numberToDelete

That will delete all the older rows other than the last 20.

[____]

Why do you care about erasing them. Its a database.
Because I'm going to deleted the extraneous shouts anyway, I'd much rather have the process automated

[____]

In php you can set up a query that gives you $myRowCount of the table.
If $myRowCount > 20 then $numberToDelete = $myRowCount - 20
and run a query...
DELETE FROM myTable ORDER BY timestamp LIMIT $numberToDelete

That will delete all the older rows other than the last 20.

Ok, thank you very much

[freddycodes]

Well do whatever you want, but if you are only storing a set number, why not just use a text file, why use the overhead of a database for 20 records?

[____]

Well do whatever you want, but if you are only storing a set number, why not just use a text file, why use the overhead of a database for 20 records?

haha well this is a pretty bad answer...it was supposed to be a more verbose shoutbox system..then the client changed their mind and only wants 20....And since i already have it built i figured..why not make it work

[freddycodes]

Okay so why not leave them in there, and down the road when they say we want to look at history, you can show them historical entries, its not going to hurt anything, mysql can handle thousands of records, and you are only grabbing 20 at a time.

[hswaseer]

yes dear freddycodes is right .... it will be useful when u have to see the old records... You can write a function which displays only 20 records and the rest can be saved in the database.

HS