Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Query help?

Quote Reply
Query help?
I am having trouble writing a query that will do the following (any help would be appreciated):

I have a membership database with a field called "date_exp" and a field called "Member" which contains a "Yes" or "No". What I am trying to do is print out a message to let the member know whenever they login if their membership will expire within the next 30 days.

I know I can write an "if" statement to take care of determining if they are a member or not. But I am having a problem coming up with a query for the "date_exp" that will work for comparing the date_exp against current date at an INTERVAL 1 MONTH and against the members membership expiration date = "date_exp" that is currently logged in.

I hope I explained this well enough?

Any help getting me started in the right direction would be great !
Quote Reply
Re: [donm] Query help? In reply to
Hi,
You can write a simple function like below and put it into the global :
Code:
sub {
my $tags = shift;
if ($tags->{Member} eq 'No') {
my $today = GT::Date::date_get();
my $days_used = GT::Date::date_diff($today,$tag->{date_registered}) ;
return ($days_used < 30)?'Your account will expire in '.(30-$days_used).' days':'Your account has expired';
}
return;
}
I am not sure what value you put in your "date_exp" field but you can change "date_registered" field a little bit.

Also, you can do the same thing with SQL but I think using GT::Date is the best way in this case. Wink

Cheers,
jean@gossamer-threads.com

Last edited by:

jean: Nov 26, 2001, 4:39 PM
Quote Reply
Re: [jean] Query help? In reply to
Thanks for your help - not sure what you mean by "put it in the global".

What I have in the "date_exp" field is the date of expiration in the form of "2001-11-26".

--------------------------
donm

Quote Reply
Re: [donm] Query help? In reply to
ops I mean the Template Globals in Admin panel >> Templates where you can set a global tag (i.e. something => a value) or a simple function (return a value) then you can simply get a new tag <%something%> to display that value in your templates.
Quote:
What I have in the "date_exp" field is the date of expiration in the form of "2001-11-26".
So you can replace the code above with :
my $date_exp_num = GT::Date::date_diff($tag->{date_exp},$today) ; # which returns number of days difference between date_exp and today.
and customize things like :
if ($date_exp_num < 0) {
....expired....
}
elsif ($date_exp_num < 30) {
...will expire within the next $date_exp_num days
}

Cheers,
jean@gossamer-threads.com


Last edited by:

jean: Nov 26, 2001, 5:56 PM
Quote Reply
Re: [jean] Query help? In reply to
Oh - I must have a version different than what you have? I do not have an admin panel or templates?

Is there a way to modify what I have to accomplish what I am trying to do?

Code:
$dbh = DBI->connect (@db_connect) or &cgierr ("Connection Error. Reason: " . $DBI::errstr . "\n.");
$query = qq~
SELECT
date_exp,
TO_DAYS(date_exp) - TO_DAYS(CURRENT_DATE) AS days
FROM $db_table
WHERE date_exp between CURDATE() and DATE_ADD(CURDATE(), INTERVAL 1 MONTH)
ORDER BY date_exp
~;
$sth = $dbh->prepare ($query) or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
while (@tmp = $sth->fetchrow_array) {

Here is where I get lost - I don't know how to get it to compare the date_exp of the member who is logged in and the list that I just retrieved in this query


}


$sth->finish;
$dbh->disconnect or &cgierr ("Can't Disconnect. Reason: $DBI::errstr\n. Query: $query");


--------------------------
donm

Last edited by:

donm: Nov 26, 2001, 6:13 PM
Quote Reply
Re: [donm] Query help? In reply to
Anyone? Help?

-----------------
donm

Quote Reply
Re: [jean] Query help? In reply to
Hi Jean,

I'm looking for a similar # of days print out...except that the formula for the # of days is calculated based on the 'casestatus' field.

-if 'casestatus' == 'Open'

days_open = 'creationdate' - todays_date

-if 'casestatus' == 'Closed

days_open = 'creationdate' - 'closeddate'



I made a few modifications to your sub and this is what I came up with:

Code:


sub {
my $tags = shift;
if ($tags->{casestatus} eq 'Open') {
my $today = GT::Date::date_get();
my $days_used = GT::Date::date_diff($today,$tag->{creationdate}) ;
return $value->{$days_used};

}
if ($tags->{casestatus} eq 'Closed') {
my $days_used = GT::Date::date_diff($tag->{closeddate},$tag->{creationdate});
return $value->{$days_used};

}
return;
}


Only thing is that this code gives me errors:

Stack Trace
======================================
Dbsql (6357): Dbsql::Home::__ANON__ called at /auto/cweb0/www-home/virtual/wwwin-gsbu/cgi/seg/dbman_SQL/admin/GT/Template.pm line 759 with arguments
(HASH(0x5e6214)).
Dbsql (6357): GT::Template::_get_var called at /auto/cweb0/www-home/virtual/wwwin-gsbu/cgi/seg/dbman_SQL/admin/templates/default/compiled/search_results.html.compiled line 343 with arguments
(GT::Template=HASH(0x597368), get_days_open, 0, 1).
Dbsql (6357): GT::Template::__ANON__ called at /auto/cweb0/www-home/virtual/wwwin-gsbu/cgi/seg/dbman_SQL/admin/GT/Template.pm line 539 with arguments
(GT::Template=HASH(0x597368)).
Dbsql (6357): GT::Template::_parse called at /auto/cweb0/www-home/virtual/wwwin-gsbu/cgi/seg/dbman_SQL/admin/GT/Template.pm line 89 with arguments
(GT::Template=HASH(0x597368), search_results.html, HASH(0x5ccb2c)).
Dbsql (6357): GT::Template::parse called at /auto/cweb0/www-home/virtual/wwwin-gsbu/cgi/seg/dbman_SQL/admin/Dbsql/Home.pm line 116 with arguments
(GT::Template, search_results.html, HASH(0x5e5498), HASH(0x5ccb2c)).
Dbsql (6357): Dbsql::Home::print called at /auto/cweb0/www-home/virtual/wwwin-gsbu/cgi/seg/dbman_SQL/admin/Dbsql/Home.pm line 76 with arguments
(Dbsql::Home=HASH(0xb4fec), search_results.html, HASH(0x5e5498)).
Dbsql (6357): Dbsql::Home::process called at /cweb0/www-home/virtual/wwwin-gsbu/cgi/seg/dbman_SQL/db.cgi line 29 with arguments
(Dbsql::Home=HASH(0xb4fec), in, GT::CGI=HASH(0x54e45c), sql, GT::SQL=HASH(0x56e1ac), cfg, Dbsql::Config=HASH(0x231ca4)).
Dbsql (6357): main::main called at /cweb0/www-home/virtual/wwwin-gsbu/cgi/seg/dbman_SQL/db.cgi line 22 with no arguments.



What changes do I need to make to the sub? Also, how do debug subs based on the error messages..I don't really understand where I would start debugging or where the error is. Thanks
Reena
Quote Reply
Re: [jean] Query help? In reply to
Okay I've tried so many variations of this, but I can't seem to get it to work.

<%GT::Date::date_diff(GT::Date::date_get(),$creationdate)%>



Whenever I used GT::Date::date_get() to get the current date the date_diff comes out to be some absurd number. But if I type in the date '2002-09-10' everything works fine. I've been working on this for days, but I can't seem to figure out what I'm doing wrong...help Frown


Reena
Quote Reply
Re: [Reena0330] Query help? In reply to
Hi Reena,

You may want to try the code below:

Code:
<%set Today = GT::Date::date_get()%>
<%GT::Date::date_diff($Today,$creationdate)%>
Cheers,
Jean
Gossamer Threads Inc.
Quote Reply
Re: [jean] Query help? In reply to
Ahh...so simple Blush..thank you
Reena