table data question

[The Lone Wolf]

Hi,

is it possible to put somekind of parameter on a table that when i put data
into the table it automaticly erase the row when a certain time has past??

i know i can use cookies on the client side but i like to create a simelar
effect on the server side.

is this possible???

thanks

[JKD]

Re: table data question

Sound like you would want to execute a perl script via cron.


"The Lone Wolf" <jcorp.webmaste r@pandora.be> wrote in message
news:8Xz0b.7598 7$F92.8420@afro dite.telenet-ops.be...[color=blue]
> Hi,
>
> is it possible to put somekind of parameter on a table that when i put[/color]
data[color=blue]
> into the table it automaticly erase the row when a certain time has past??
>
> i know i can use cookies on the client side but i like to create a simelar
> effect on the server side.
>
> is this possible???
>
> thanks
>
>[/color]

[Nikolai Chuvakhin]

Re: table data question

"The Lone Wolf" <jcorp.webmaste r@pandora.be> wrote
in message news:<8Xz0b.759 87$F92.8420@afr odite.telenet-ops.be>...[color=blue]
>
> is it possible to put somekind of parameter on a table that
> when i put data into the table it automaticly erase the row
> when a certain time has past??[/color]

You would have to define a trigger for that. Check you database's
documentation to see if it supports triggers. In case you are using
MySQL, it doesn't support triggers.

Cheers,
NC

[Jochen Buennagel]

Re: table data question

The Lone Wolf wrote:
[color=blue]
> is it possible to put somekind of parameter on a table that when i put data
> into the table it automaticly erase the row when a certain time has past??[/color]

a cron job or a trigger would be preferable, but if you don't have
access to cron on your server and using MySQL that doesn't support
triggers, the common practice is this:

Put a column in your table that holds the date/time when it should
expire, then do a "garbage collection" at the start of each page. If you
have enough hits, you might want to give it only a 5% or 10% probability
of running with "if(11>rand(1,1 00))". Adjust the probability so that it
gets run often enough, but doesn't impact your server's performance to
much. When it does run have it delete all records whos expiration has
passed.

Jochen

--
/**
* @author Jochen Buennagel <zang at buennagel dot com>
* @see http://www.sourceforge.net/projects/zang
*/

[Wes Bailey]

Re: table data question

You bet it is but with a little work. I will assume you are on LAMP
server (Linux, Apache, MySQL, PHP) and provide a general framework for
hot to accomplish this task as I do it now for a customized
replication environment for updating a server that communicates with a
wireless client. You can code the rest for your task.

In the database table add a column with a timestamp field in it. In
mysql the first column of this type will automatically be populated
with the current timestamp by default (a nice feature).

Note: I am not responsible for syntax errors.

CREATE TABLE hotspot
( id MEDIUMINT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY
, active_flag CHAR(1) NOT NULL DEFAULT 'Y'
, name VARCHAR(75) NOT NULL
, update_date TIMESTAMP
, comments TEXT
, INDEX name_idx (name)
) TYPE=InnoDB;

The insert statement to create the record would look like:

insert into hotspot (name, comments) values ('mytest', 'Nothing worth
mentioning');

Next, write a php script that then connects to the database and all
that. At this point say you want to find all of the records that are
older than a day you would issue the following sql statement

$sql = 'SELECT id FROM hotspot WHERE update_date >
DATE_SUB(CURREN T_DATE(), INTERVAL 1 DAY)';

$result = mysql_query($sq l, $conn);

while ($row = mysql_fetch_obj ect($result)) {

$deleteRecordSe t[] = $row->id;

}

foreach ($deleteRecordS et as $row) {

$sql = "delete from hotspot where id = $row->id";

....

}


You can then setup a cron job to run this php script using a command
line tool like curl (which you should install if you system doesn't
have it)

0 4 * * * curl http://localhost:81/deleteHotspots.php >>
/log/deleteexport.lo g

Hope that helps!

wes bailey

[color=blue]
> "The Lone Wolf" <jcorp.webmaste r@pandora.be> wrote in message
> news:<8Xz0b.759 87$F92.8420@afr odite.telenet-ops.be>...
> Hi,
>
> is it possible to put somekind of parameter on a table that when i put data
> into the table it automaticly erase the row when a certain time has past??
>
> i know i can use cookies on the client side but i like to create a simelar
> effect on the server side.
>
> is this possible???
>
> thanks[/color]