Limiting number of rows in a table

[Shane Niebergall]

Hey all -

I'm trying to implement a basic log in a table. Instead of writing to a text
file on the server, I want to insert entries into a 'log' table. However, I
want to limit this table to only have the last 500 rows or so. How do I do
this?

Here's my pseudocode, but it seems as if there would be a better way:

logEntry($message){
insert message into table
get number of rows in table
if (numrows > 500){
delete from table where ((numrows - id) > 500)
}
}

Is that right??

Thanks,
Shane

[Hal Halloway]

Shane Niebergall wrote:

Hey all -

I'm trying to implement a basic log in a table. Instead of writing to a text
file on the server, I want to insert entries into a 'log' table. However, I
want to limit this table to only have the last 500 rows or so. How do I do
this?

Here's my pseudocode, but it seems as if there would be a better way:
This would be easy to do with PHP but I'm not sure if you want to use
PHP...IMHO

logEntry($message){
insert message into table
get number of rows in table
if (numrows > 500){
delete from table where ((numrows - id) > 500)
}
}

Is that right??

Thanks,
Shane

[Shane Niebergall]

I will be using PHP - how will that make it easier?

Thanks,
Shane

"Hal Halloway" <Ha******@nospam.net> wrote in message
news:REUJd.253$u45.160@trnddc08...

Shane Niebergall wrote:

Hey all -

I'm trying to implement a basic log in a table. Instead of writing to a text file on the server, I want to insert entries into a 'log' table. However, I want to limit this table to only have the last 500 rows or so. How do I do this?

Here's my pseudocode, but it seems as if there would be a better way:

This would be easy to do with PHP but I'm not sure if you want to use
PHP...IMHO

logEntry($message){
insert message into table
get number of rows in table
if (numrows > 500){
delete from table where ((numrows - id) > 500)
}
}

Is that right??

Thanks,
Shane

[Bill Karwin]

Shane Niebergall wrote:

logEntry($message){
insert message into table
get number of rows in table
if (numrows > 500){
delete from table where ((numrows - id) > 500)
}
}

I'd do the delete before I do the insert, if you want the number of rows
never to exceed 500. Also, you only need to delete one row if your
$message always results in inserting one row.

logEntry($message){
get number of rows in table
if numrows = 500 {
delete first row of table
}
insert $message into table
}

Be careful about multiple clients doing this operation simultaneously;
you might want to lock the table. See the docs on LOCK TABLES.

Also you can specify the MAX_ROWS in your CREATE TABLE statement if you
want MySQL to enforce it! See the docs of the CREATE TABLE statement.

Regards,
Bill K.

[Shane Niebergall]

Hi Bill -

Thanks for the tips. Do you mind expanding on this:
"delete first row of table"

How would that delete query look?

Thanks so much!
Shane

"Bill Karwin" <bi**@karwin.com> wrote in message
news:ct*********@enews1.newsguy.com...

Shane Niebergall wrote:

logEntry($message){
insert message into table
get number of rows in table
if (numrows > 500){
delete from table where ((numrows - id) > 500)
}
}

I'd do the delete before I do the insert, if you want the number of rows
never to exceed 500. Also, you only need to delete one row if your
$message always results in inserting one row.

logEntry($message){
get number of rows in table
if numrows = 500 {
delete first row of table
}
insert $message into table
}

Be careful about multiple clients doing this operation simultaneously;
you might want to lock the table. See the docs on LOCK TABLES.

Also you can specify the MAX_ROWS in your CREATE TABLE statement if you
want MySQL to enforce it! See the docs of the CREATE TABLE statement.

Regards,
Bill K.

[Bill Karwin]

Shane Niebergall wrote:

Thanks for the tips. Do you mind expanding on this:
"delete first row of table"

How would that delete query look?

This could do it:

delete from myTable where primary_key_id =
(select min(primary_key_id) from myTable)

Regards,
Bill K.

[Shane Niebergall]

Perfect - exactly what I was looking for. Thanks Bill!!
"Bill Karwin" <bi**@karwin.com> wrote in message
news:ct*********@enews3.newsguy.com...

Shane Niebergall wrote:

Thanks for the tips. Do you mind expanding on this:
"delete first row of table"

How would that delete query look?

This could do it:

delete from myTable where primary_key_id =
(select min(primary_key_id) from myTable)

Regards,
Bill K.