PHP / MySQL Question

RJM62

RJM62

Touchdown! Greaser!
Gone West
Joined
Jun 15, 2007
Messages
13,157
Location
Upstate New York
Display Name
Geek on the Hill
Is there any easy way to limit the size of a MySQL table to a given number of rows, so that once that limit is reached, when a new row is inserted, the oldest is automatically deleted?

Thanks,

Rich
 
I don't know of any 'built in' function that would do that. I assume you would have to do a few queries to get it done. Select number of records; if number of records > desired table size, then delete oldest record; insert new record.

But then again, I don't know a lot of the 'built in' parts of MySQL that might do this on the fly.
 
Yep, I'd just put the limit in the stored procedure. Something like :

DELETE FROM table t WHERE
t.ID not in (SELECT ID from table t2 ORDER BY t2.date DESC LIMIT 1000)
 
Last edited:
If you're using v 5.x or higher you can write a trigger that checks the number of records in the table and when it reaches the limit, deletes the oldest (earliest) then adds the new one.

This depends on how you've constructed the table, what the timestamps are, keys (lowest numerical key is oldest record, etc). Might just be easiest to add a timestamp field but may not be the most efficient.
 
Triggers that take a bit of time are generally considered a bad idea as they can cause locking problems.

I haven't seen the implementation of the mySql triggers, however last I heard they were inferior to the triggers in postgres and real DBMS's, so I would imagine these warnings are equally valid.
 
Keane said:
Yep, I'd just put the limit in the stored procedure. Something like :

DELETE FROM table t WHERE
t.ID not in (SELECT ID from table t2 ORDER BY t2.date DESC LIMIT 1000)
Click to expand...

That's pretty much what I've been playing with, Chris and Erich. But I was concerned that two users making entries at the same time might cause problems if two steps were required (one to delete the oldest entry, and one to insert the new one). I probably could get around that by setting the limit higher than the actual number of posts I want to render.

Basically, I'm building a site that will allow literally anyone to post anything they want about a given topic, without logging in. Like an online soapbox in the town square where people can rant freely. The posts will remain until they are replaced by new posts (in other words, the most recent post will replace the oldest post, once the limit is reached). I figure I'll probably use an initial limit of five posts, until the site catches on.

Selecting only the five most recent posts to display is easy. But ideally, I also would like the older posts to be deleted from the database itself when the new ones are posted, not so much to limit the database size (which will be quite small, anyway); but because I want the existence of the posts anywhere to be ephemeral, so if someone asks what was posted on such-and-such a date, the honest answer will be, "I have no idea."

In effect, I'm trying to simulate a real soapbox in a town square, where things that are said will soon fade into the ether, and will be heard only by those who happened to be there to hear them.

In addition, my lawyer has advised me that whatever liability I might have for making such a soapbox available will be "substantially mitigated by the ephemeral nature of the venue." But just in case someone does show up with a subpoena and wants to know what was posted on such-and-such a date, it would be in the best interests of freedom of expression if the database contained no more then the most current posts.

He likened it to a person who owns a piece of land, such as a courtyard, that is commonly used by anonymous individuals to rant and rave. The owner of the courtyard shouldn't be required to tape-record everything that's said there.

I'll probably use something along the lines of what you posted, but just set the limit to a safe margin above the number of posts I actually want to present on the site, in case two posters happen to click "submit" at the very same moment in time.

As for triggers, I'm running 5.0.91, but I've found the triggers to be a little buggy. I used them to try to auto-prune some databases in the past, and sometimes they wound up locking things up so tight that I had to restart the SQL server (which kind of stinks on a server hosting multiple domains). So I'm a little hesitant to go there again...

Rich
 
I can't quote you the code to use off the top of my head, but couldn't you just limit the database size and instead of a delete/add sequence, merely update the oldest record with the new stuff?
1=Taxes stink.
2=I like taxes.
3=You're a nut.
4=I like nuts.
5=You stink.
1=So do you.
 
RJM62 said:
... But I was concerned that two users making entries at the same time might cause problems if two steps were required (one to delete the oldest entry, and one to insert the new one)....
Click to expand...
It's been a while since I've played with mysql, but any time you need to make updates to multiple records, those updates must be made sequentially without any other intervening updates taking place between them, and the database would be in an inconsistent state if, say, the server crashed after one of the updates had been committed but before the second one had, then you have an "atomicity" requirement. These are handled by doing the updates in the context of a transaction:


-harry
 
AuntPeggy said:
I can't quote you the code to use off the top of my head, but couldn't you just limit the database size and instead of a delete/add sequence, merely update the oldest record with the new stuff?
1=Taxes stink.
2=I like taxes.
3=You're a nut.
4=I like nuts.
5=You stink.
1=So do you.
Click to expand...

Thanks. When I tried that, it deleted the newest entry. Most probably an error on my part, as I haven't had the uninterrupted time to sit down and do this without being distracted.

-Rich
 
mantakos said:
It's been a while since I've played with mysql, but any time you need to make updates to multiple records, those updates must be made sequentially without any other intervening updates taking place between them, and the database would be in an inconsistent state if, say, the server crashed after one of the updates had been committed but before the second one had, then you have an "atomicity" requirement. These are handled by doing the updates in the context of a transaction:
-harry
Click to expand...

Thanks, Harry.

I have a few errands to run, and I also have to get to the credit union before they close; so I'll read that more thoroughly when I get back.

-Rich
 
RJM62 said:
Thanks. When I tried that, it deleted the newest entry. Most probably an error on my part, as I haven't had the uninterrupted time to sit down and do this without being distracted.

-Rich
Click to expand...
Using pseudocode:

limit = 5
nextNumber = 0
Loop forever
comment = GetComment()
StoreComment( nextNumber, comment )
nextNumber = ( nextNumber + 1) mod limit
End Loop
 
There are lots of things you could do Richard from triggers to stored procedures to other trickery.

The simplest thing to do would be to do this:
Code: 
CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
add fives posts:
Code: 
INSERT INTO post (post) VALUES ('Hello!');
INSERT INTO post (post) VALUES ('Hello!');
INSERT INTO post (post) VALUES ('Hello!');
INSERT INTO post (post) VALUES ('Hello!');
INSERT INTO post (post) VALUES ('Hello!');
When you add a new post do this (you could wrap it into a transaction):
Code: 
DELETE FROM post ORDER BY id LIMIT 1;
INSERT INTO post(post) VALUES ('New post!!!');

The thing Aunt Peggy suggested would work but then you need to roll through and update the ID on every row which is a lot more action happening on the DB and more code for you to worry about.
 
Last edited:
You could also just run a batch update at the end of the day (or each hour, etc.) to truncate all of the tables and leave the hard limit (i.e. 5) in the display code. Then you needn't worry about the performance cost of triggers.
 
jesse said:
There are lots of things you could do Richard from triggers to stored procedures to other trickery.

The simplest thing to do would be to do this:
Code: 
CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
add fives posts:
Code: 
INSERT INTO post (post) VALUES ('Hello!');
INSERT INTO post (post) VALUES ('Hello!');
INSERT INTO post (post) VALUES ('Hello!');
INSERT INTO post (post) VALUES ('Hello!');
INSERT INTO post (post) VALUES ('Hello!');
When you add a new post do this (you could wrap it into a transaction):
Code: 
DELETE FROM post ORDER BY id LIMIT 1;
INSERT INTO post(post) VALUES ('New post!!!');
The thing Aunt Peggy suggested would work but then you need to roll through and update the ID on every row which is a lot more action happening on the DB and more code for you to worry about.
Click to expand...

Hey, that works great! Thanks.

Here's a test page you can play with. I still have to add the spam filters and such, but it'll give you an idea of what I want to do.

http://www.bigoldsoapbox.com/test_out.php

Thanks again,

-Rich

EDIT: Try this one instead. The spam filters have been added, so the test page (which lacks the code the spam filters need) no longer works.

http://www.bigoldsoapbox.com/politics.php

-Rich
 
Last edited:
Thanks, too, to everyone who offered advice. I was running around all over the place today, but I did try various methods. Jesse's seems to do exactly what I want it to.

-Rich
 
Well, one problem solved, but I've discovered another bug.

My anti-spam script prevents submissions from BlackBerries (and possibly other mobile devices) because it requires that the IP address of the computer submitting the form match that of the computer that loaded the form. Apparently, on a BlackBerry, the IP address changes every time you load a page. So I disabled that check for now.

I might just use a cookie or a session instead, or maybe I'll do nothing. There are quite a few other checks done, so even without the IP check, I think it should still be reasonably spam-resistant.

Oh, the site's done, by the way (except for the ads). Let me know what you thing. And post something, if you don't mind. Gotta move the Lipsum outta there.

http://www.bigoldsoapbox.com/

I'm actually pretty curious to see whether this site catches on. I think it's a pretty
unique concept.

-Rich
 
That iframe concept needs to go.

You have a ton of screel real estate on each page but the actual content that matters is forced into a tiny box that requires you scroll.
 
jesse said:
That iframe concept needs to go.

You have a ton of screel real estate on each page but the actual content that matters is forced into a tiny box that requires you scroll.
Click to expand...

What iframe? There are no iframes. It's all CSS. You mean the pseudo-frames?

I can easily change the size of those in the stylesheet. Do you think they should be bigger, or eliminated altogether?

If I eliminate them altogether (well, the top one, anyway, not the form), the visitor will still have to scroll to read the comments. Do you think that's preferable?

Thanks,

Rich
 
I just made the top one a little bigger, and the input form a little wider. I'm trying to keep both the output box and the top of the input box visible in the viewport of a window sized at 1024x768. I want to make it obvious that people can reply if they're not familiar with the site.

But I'm open to suggestions.

-Rich
 
I made a few other "bugfix"-type changes. For example, on slow connections, the user had to stare at a white screen waiting for the page to refresh after submission, while the spam checker was doing its thing and the database being updated, because the posting script wasn't wrapped in the same page style.

So I wrapped the posting script (which includes the spam checker) in a page using the same elements as the rest of the pages (those elements being in cache already), so now they at least get to look at a page with the spam-check message rather than a white screen.

-Rich
 
You must log in or register to reply here.
Back
Top