restricting table size

[Hal Halloway]

Say I want to make sure that a table can not have more than 100 records
- how could I do this from MYSQl or PHP.

Thanks

[Bill Karwin]

Hal Halloway wrote:

Say I want to make sure that a table can not have more than 100 records
- how could I do this from MYSQl or PHP.

Have you looked at the MAX_ROWS table option?

e.g.,

CREATE TABLE (
...column definitions...
) MAX_ROWS = 100;

See http://dev.mysql.com/doc/mysql/en/create-table.html

Regards,
Bill K.

[Leif Neland]

"Bill Karwin" <bi**@karwin.co m> skrev i en meddelelse
news:ct******** @enews2.newsguy .com...

Hal Halloway wrote:

Say I want to make sure that a table can not have more than 100 records -
how could I do this from MYSQl or PHP.

Have you looked at the MAX_ROWS table option?

e.g.,

CREATE TABLE (
...column definitions...
) MAX_ROWS = 100;

See http://dev.mysql.com/doc/mysql/en/create-table.html

It appears from the docs that MAX_ROWS is only used when creating the table
to hint for the database engine the most efficient way to store the data.

It doesn't look like it is being enforced when inserting rows.

But try, and report back.

Leif

[Good Man]

"Leif Neland" <le***@neland.d k> wrote in
news:yp******** *******@news.ge t2net.dk:

It appears from the docs that MAX_ROWS is only used when creating the
table to hint for the database engine the most efficient way to store
the data.

It doesn't look like it is being enforced when inserting rows.

But try, and report back.

if that's the case, you can just do a COUNT(*) before inserting into your
table... ie: don't allow inserts when the Count >= 100

[Thomas Bartkus]

"Hal Halloway" <Ha******@nospa m.net> wrote in message
news:rRXKd.2043 $qP.161@trnddc0 4...

Say I want to make sure that a table can not have more than 100 records
- how could I do this from MYSQl or PHP.

You haven't mentioned what rule you want to apply that decides which records
you want to delete when the table grows beyond 100 records. And I haven't
tried MAX_ROWS myself and I am not clear how that works. If it serves, that
might be best. Otherwise, here is what I would do.

I'm going to assume you want to toss the oldest records and you have an
autonumbered key field. In this case, you will want to toss the records with
the smallestKeyId values to cut back down to 100 records.

# Skip past the most recent 100 records and fetch the next
# highest key value.
SELECT {AutoNumbered_K eyID} As HighestKeyToKil l
FROM {SomeTable}
ORDER BY {AutoNumbered_K eyID}DESC # biggest (newest) keys to the top!
LIMIT 1, 101 # 101 will be
the first record to delete!

# Now delete all records that are at or smaller than your HighestKeyToKil l
value
DELETE FROM SomeTable
WHERE KeyID<=$Highest KeyToKill

If you are MySQL version 4.1 or higher you can do that in one query with the
SELECT in a subquery.

Everytime you do this, your table gets trimmed back down to 100 records by
tossing out older records. You can hang it on a cron to do it every once in
a while. Commonly, I do something similar when I need to toss records that
are older than 90 (or whatever!) days and I have a datetime field to work
with. I'm sure you can see how to adapt.

If there is something critical about never exceeding 100 records, you will
have to do your trim immediately before adding records. I.E before you add 5
recs, you will need to delete 5 first.

Thomas Bartkus