Hi,
I am throughly stumped, I have a mysql database, it holds two fields, Username and Date, I have a login.cgi script that logs them in and does two things, it writes to the database named login their username and date, and takes them to a specified page.

Then I have a logoff script that removes their username and date from the login mysql database.

My problem is that im not sure how to write a sub that would allow me to read from the database and just display the username and date in simply html page, also Im lost when it comes to having the login script check everytime that a user logs in to see if there are any entries that are older than 30 minutes, and then delete them, I understand the delete, but not how to check and see if they are older then 30 minutes, the date is getting written to the database as Sun Jun 4 21:11:48 2000

Any help with this is greatly appreciated.

Okay, I've got most of the problem figured out and functioning, my last problem with this is now, to write a subroutine that would check the date and if it is older than 30 minutes, delete the line from the mysql database.

Can anyone of you perl gurus write a sub for me that would do this?

Thanks
Harrison

-- Gordon


s/(\d{2})/chr($1)/ge + print if $_ = '8284703280698276687967';

Ive been staring at your response for pert near an hour now, and this is what i have translated it to for use in my script. Of course it doesn't work for me cuz I screwed something up or left something undone.

sub clean{
my $dbh = DBI->connect("dbi:mysql:$mysqldatabase","$mysqlusername","$mysqlpassword") || die "Can't connect: $!\n";
my $query = qq~select *, now() - date~;
my $sth = $dbh->prepare($query);
$sth->execute || die "Can't execute: $!\n";
while (my ($id, $t) = $sth->fetchrow_array)
{
if ($t > 3000)
{
$query = qq~delete from lastlogin where date = $id~;
my $sth2 = $dbh->prepare($query);
$sth2->execute or warn "Could not delete field $id : $!\n";
}}$sth->finish;
$dbh->disconnect;
}

So, what have i left out or screwed up here?

Thanks for your assistance.
Harrison

In the line:
my $query = qq~select *, now() - date~;

there is no need to grab every field (just more work for the database). Just grab the id field for that table. If you don't have an id field you might want to think about adding one and making it the primary key (you can auto_increment it to save yourself a lot of time). Otherwise just use the username or whatever field is unique, tho it will be slower than using an id.

Secondly, in the line:
my $query = qq~select *, now() - date~;

You aren't telling MySQL what table to select from.
Try something like
select id, now() - date from lastlogin

And in the line:
$query = qq~delete from lastlogin where date = $id~;

You wouldn't want this to be based on the date field as there could be multiple instances of the same date (no matter how improbable). As i said before, you would want to use a unique field, preferably an id.

Check out
http://www.devshed.com/Server_Side/MySQL
for some good tutorials on MySQL.

-- Gordon


s/(\d{2})/chr($1)/ge + print if $_ = '8284703280698276687967';

Hi Gordon,
First off, thanks for the help, heres what i've got now.

-------------------------------------------------------
sub newclean{
my $query = qq~select $Username, now() - date from lastlogin~;
my $sth = $dbh->prepare($query);
$sth->execute || die "Can't execute: $!\n";
while (my ($id, $t) = $sth->fetchrow_array)
{
if ($t > 3000)
{
$query = qq~delete from lastlogin where $Username = $id~;
my $sth2 = $dbh->prepare($query);
$sth2->execute or warn "Could not delete field $id : $!\n";
}
}
$sth->finish;

}
---------------------------------------------------------

The error the script brings back now is:
DBD::mysql::st execute failed: You have an error in your SQL syntax near ' now() - date from lastlogin' at line 1 at clean.cgi line 23.


Kinda cryptic to me, considering I don't know what now() actually means at this point.
The table name is lastlogin,
the fields inside of it are
date
Username <Primary Key
city
location
state

What we want to do, is to delete any row in the database that is more than 30 minutes old, so deleting multiple lines is a definate plus for us.


Thanks, Harrison

"I've got if's pretty good, but that's about it"

get rid of the '$' sign in front of Username, now()

-- Gordon


s/(\d{2})/chr($1)/ge + print if $_ = '8284703280698276687967';

Okay, weve now got:

--------------------------
#############################
# CLEAN WHOS ONLINE AT LOGIN
#############################
sub newclean{
my $query = qq~select Username, now() - date from lastlogin~;
my $sth = $dbh->prepare($query);
$sth->execute || die "Can't execute: $!\n";
while (my ($id, $t) = $sth->fetchrow_array)
{
if ($t > 3000)
{
$query = qq~delete from lastlogin where Username = "$id"~; #id where 2nd username is
my $sth2 = $dbh->prepare($query);
$sth2->execute or warn "Could not delete field $id : $!\n";
}
}
$sth->finish;
$dbh->disconnect;
}
--------------------------

This no longer causes any server errors, but the problem is that it deletes everything from the database whether or not it's 30 minutes old.

Do we need to store the date in the table as something other than: Wed Jun 7 14:43:36 2000 to get the sub to understand how to compare the time?

Thanks again
Harrison


"I've got if's pretty good, but that's about it"

Hi again,
im still confused as to why this sub would remove all items in the database instead of just the lines that are older than 30 minutes.

Any clues?

Harrison

"I've got if's pretty good, but that's about it"

Here is the final solution that finally works.