Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Getting value of field in current record

Quote Reply
Getting value of field in current record
I have three databases which are somewhat relational. In one, people can record activity or exercise performed during the day and, in another, I have it pulling the total amount of time spent in exercise for that date into a food journal. Only problem is, I have it pulling for "today"...if a person enters an activity journal and a food journal on 1/15 then updates or modifies the food journal on 1/16, the activity time no longer defaults in because that journal entry's date no longer equals "today".

This is the subroutine in db.cgi:
In Reply To:
sub get_activity {
#-------------------------------------------------------------
#
my ($act, $today, @data);

my $username_q = $DBH->quote($db_userid);
my $today = $DBH->quote(&get_date());

$query = qq!
SELECT * FROM Activity
WHERE UserID = $username_q AND
Date = $today
!;
my $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");


if ($sth->rows) {
while (@data = $sth->fetchrow_array) {
$act = $data[28];
}
}

$sth->finish;
return $act;
}
Then, in the html.pl file, in sub html_record_form, I have the following:

In Reply To:
my (%rec) = @_;
($db_auto_generate and print &build_html_record_form(%rec) and return);
$rec{eActivity} = &get_activity;
I have tried various and sundry forms of $in{'Date'} (which is the field I'm trying to grab a value from) with no success.

What it seems like I need this to do is define the value of the Date field of the current record (if I'm modifying) and search the activity journal for a match OR, if there's no value, search the activity journal for a match on "today" (because the new record hasn't yet been added, therefore the field has no value yet).

Am I missing something? Is there an easier way to do this? And how do I obtain the value of a field in a current record?

I'd appreciate any help on this...I'm getting some complaints from folks that their data is disappearing. Thanks!

Melanie
http://www.somemoorecats.com/
http://www.okhima.org/
Quote Reply
Re: Getting value of field in current record In reply to
Well, as usually happens, the very act of posting a question seems to trigger something and I manage to figure out the solution. Anyway, I moved the "guts" of the get_activity sub from db.cgi and placed it in the html.pl file where I previously had just $rec{eActivity} = &get_activity; . (Of course, I changed $today to pull the value of the date field of the record being worked on rather than the current date.) And it worked!

My question now is.....why does this work when placed in the html.pl file, in sub html_record_form, but it doesn't work when placed in the db.cgi file and called from the html_record_form sub?


Melanie
http://www.somemoorecats.com/
http://www.okhima.org/