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($messa ge){
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]

Re: Limiting number of rows in a table

Shane Niebergall wrote:
[color=blue]
> 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:[/color]

This would be easy to do with PHP but I'm not sure if you want to use
PHP...IMHO
[color=blue]
>
> logEntry($messa ge){
> 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
>
>[/color]

[Shane Niebergall]

Re: Limiting number of rows in a table

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

Thanks,
Shane

"Hal Halloway" <Halloway@nospa m.net> wrote in message
news:REUJd.253$ u45.160@trnddc0 8...[color=blue]
> Shane Niebergall wrote:
>[color=green]
> > Hey all -
> >
> > I'm trying to implement a basic log in a table. Instead of writing to a[/color][/color]
text[color=blue][color=green]
> > file on the server, I want to insert entries into a 'log' table.[/color][/color]
However, I[color=blue][color=green]
> > want to limit this table to only have the last 500 rows or so. How do I[/color][/color]
do[color=blue][color=green]
> > this?
> >
> > Here's my pseudocode, but it seems as if there would be a better way:[/color]
>
> This would be easy to do with PHP but I'm not sure if you want to use
> PHP...IMHO
>[color=green]
> >
> > logEntry($messa ge){
> > 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
> >
> >[/color][/color]

[Bill Karwin]

Re: Limiting number of rows in a table

Shane Niebergall wrote:[color=blue]
> logEntry($messa ge){
> insert message into table
> get number of rows in table
> if (numrows > 500){
> delete from table where ((numrows - id) > 500)
> }
> }[/color]

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($messa ge){
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]

Re: Limiting number of rows in a table

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" <bill@karwin.co m> wrote in message
news:ctekeh02g1 h@enews1.newsgu y.com...[color=blue]
> Shane Niebergall wrote:[color=green]
> > logEntry($messa ge){
> > insert message into table
> > get number of rows in table
> > if (numrows > 500){
> > delete from table where ((numrows - id) > 500)
> > }
> > }[/color]
>
> 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($messa ge){
> 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.[/color]

[Bill Karwin]

Re: Limiting number of rows in a table

Shane Niebergall wrote:[color=blue]
> Thanks for the tips. Do you mind expanding on this:
> "delete first row of table"
>
> How would that delete query look?[/color]

This could do it:

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

Regards,
Bill K.

[Shane Niebergall]

Re: Limiting number of rows in a table

Perfect - exactly what I was looking for. Thanks Bill!!


"Bill Karwin" <bill@karwin.co m> wrote in message
news:ctlut411cb 2@enews3.newsgu y.com...[color=blue]
> Shane Niebergall wrote:[color=green]
> > Thanks for the tips. Do you mind expanding on this:
> > "delete first row of table"
> >
> > How would that delete query look?[/color]
>
> This could do it:
>
> delete from myTable where primary_key_id =
> (select min(primary_key _id) from myTable)
>
> Regards,
> Bill K.[/color]