Alright, this one site I've started working on has been using dbman for years. It has a few date range searches, and now NONE of them work. All I did was add a new user. They are using the month/day/year date format, and it is being added to the database like that, no problems. Just the searching is problematic. Here is the relevant code:

In db.cgi:


They have JPDeni's excellent "What's New" MOD applied from: http://www.jpdeni.com/dbman/Mods/whatsnew.txt (as well as her bug fixes for the database). The "last 7 days" link looks like this:


In sub query of db.cgi the listnew code is:


19 is the number of my date field in default.cfg, which looks like:


This link returns hundreds of random records from the past year. Not just the ones within the last 7 days.

They also have the code to select records newer than a date selected from a drop-down menu. Here's the code for that in html.pl:


Here's where the weirdness starts. If I select to search 3 days, it will show me hundreds of random records, none of which are from the last 3 days. If I select 2 weeks from the drop-down menu, the program will tell me that it's an invalid date format

I also have an advanced search form where you can write in dates. In html.pl this is like:


This also returns hundreds of random results that don't match the day.

What the heck?!? Please help me!

If you code has been working for a long time then I would think it may be that the database itself may be corrupted.

How big is your db file? Have you opened it in something like Excel to check to be sure all the fields are in the proper order?

The db file is the first thing i would check. Let us know.

Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/

Thank you for the excellent suggestion. The database was extremely large, so I made a copy with just the records from 2002. That still was 7908 records. I imported that into Filemaker Pro, and everything looked kosher.

Still, I tried creating a brand new, empty database and adding a record to it. That worked fine, but searching it still had the same problem so it can't be the database.

Any other suggestions?

Also, since their database is so huge, I was thinking of suggesting they buy the SQL version to replace their flat file DMan. Would all the MODs work in the SQL version?

What a shame, I was hoping for your sake it would be something simple to fix such as the database.

I'm sorry, but I have no other suggestions, I still don't know how the coding could be causing problems after such a long time of use? That is interesting that it occurred with just one record in the database.

I'm not sure about the conversion to the SQL version, as I have never used it. I would think some function would not work the same but you may want to check in that support forum for any ideas and suggestions.

You might also want to consider breaking up the database into 2 databases by year. I did that with one of mine and just used 1997-2000 and then 2001 - to present. Some of the functions didn't work properly as the database got very large as the server would time-out during long processes.

I hope someone else will come along with some suggestions for locating what the problem could be with the database.

I would upload all the files other than the db file again just in case the server messed the files up somehow ???

Sorry, I could not be of more help

Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/

Found a clue as to what might be wrong...

I went back to an older version of the script from a few months back. I know for sure that it was working just fine. However, it had the SAME problems when I tried doing the search.

I noticed that if I do a 2 week search, it shows me the inquiries up to November 30th. I'm thinking the "show today" and "last 7 days" links don't work because the records were created in December.

I don't have a clue as to what to fix for this, but it seems the records created from 12/1/2002 on are what is causing the problem.

Any ideas based on this?

Thanks!

More clues!

Well I actually found some bad records. They were from back in July though. There have been thousands added since, and the problem only just showed up.

Anyway, I took out the bad records, and now I don't get the "invalid date format" anymore. Yay!

But..

The database is somehow confusing the order of my dates. I have my dates as: $mon/$day/$year

12/11/2002

(for a record added today)

but the search thinks it's $day/$mon/$year somehow. This has gotta be fixable. But whwereabouts?

Thanks!

It just HAS to somehow be my sub get_date and sub date_to_unix 'cause what I did was grab a fresh copy of DBman, installed it, and used my current database. It displayed everything fine. Then I applied 2 MODs. One to make the date like 12/03/2002 and the What's New MOD and I'm getting the same problems with the new script! What's New returns hundreds of matches, thinking the middle value is the month instead of the first.

Please take a look at my original posting and see what (if anything) should be changed with those subroutines.

Thanks so much!

I did notice in sub get_date you have the months defined as Jan, Feb, etc. but then you stated your output should be 12/19/2002

my (@months) = qw!Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec!;

perhaps should be:

my (@months) = qw!01 02 03 04 05 06 07 08 09 10 11 12!;

I do use all but the What's New mod in one of my databases, so I'm not sure how that mod changes things. Here is how I have that database setup (Note in my sub get_date I have several lines commented out:

sub get_date {
# --------------------------------------------------------
my ($time1) = $_[0];
($time1) or ($time1 = time());

my ($sec, $min, $hour, $day, $mon, $year, $dweek, $dyear, $daylight) = localtime($time1);
my (@months) = qw!01 02 03 04 05 06 07 08 09 10 11 12!;
**** my (@months) = qw!Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec!;
($day < 10) and ($day = "0$day");
# ++$mon;
# ($mon < 10) and ($mon = "0$mon");
$year = $year + 1900;
return "$months[$mon]/$day/$year";
}

sub date_to_unix {
# --------------------------------------------------------
my ($date) = $_[0];
my ($time);
my ($mon, $day, $year) = split(/\//, $_[0]);
unless ($mon and $day and $year) { return undef; }

use Time::Local;
eval {
$day = int($day); $year = int($year) - 1900; $mon = int($mon) - 1;
$time = timelocal(0,0,0,$day, $mon, $year);
};

if ($@) { return undef; } # Could return 0 if you want.
return ($time);
}

On my custom search form I also use a different variation of choosing the time span:

print qq|<TR><TD colspan=2><$font><B>Show by Article Date:</B></font> &nbsp; &nbsp; |;
my (@months) = qw!1 2 3 4 5 6 7 8 9 10 11 12!;
my (@number_of_days) = qw!2 3 4 8 15 32 64 192 366 732!;
# my (@number_of_days) = qw!2 3 4 8 15 32 64 192 366 732 1098 1464 1830 2196!;
$daytext[2] = "last day";
$daytext[3] = "last two days";
$daytext[4] = "last three days";
$daytext[8] = "last week";
$daytext[15] = "last two weeks";
$daytext[32] = "last month";
$daytext[64] = "last two months";
$daytext[192] = "last six months";
$daytext[366] = "last year";
$daytext[732] = "last two years";
# $daytext[1098] = "last three year";
# $daytext[1464] = "last four years";
# $daytext[1830] = "last five years";
# $daytext[2196] = "last six years";
# Note that each of the numbers is one day more than indicated by the text.

and then I also use:

print qq|<SELECT NAME="ArticleDate-gt"><OPTION VALUE ="">---|;
foreach $days (@number_of_days) {
$time1 = time() - ($days * 86400);
my ($sec, $min, $hour, $day, $mon, $year, $dweek, $dyear, $daylight) = localtime($time1);
($day < 10) and ($day = "0$day");
$year = $year + 1900;
print qq|<OPTION VALUE="$months[$mon]/$day/$year">$daytext[$days]|;
}
print "</SELECT>";
}

Perhaps comparing yours with mine may help you find a solution.

Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/

I switched the month definition in my sub get_date and then tried your select menu in my search and now it returns:

There were problems with the search. Reason: Invalid date format: ''

With that quote at the end. Usually there's a date there in quotes. Hrmmmm

Alrighty... mostly good news. I have the date range search working with my drop-down menus using LoisC's time span code.. thanks LoisC! AND I have the date range search working in regular text fields. *phew*

Here is my final sub get_date and sub date_to_unix:


The only thing that doesn't work still is the What's New MOD. I still can't convince it that my date format isn't mm/dd/yyyy instead of dd/mm/yyyy but I'll find another solution for that I'm sure.

Thanks for your posts again LoisC!

Nah, scratch all that. I didn't fix squat. Sure the searches work now.. why? 'Cause now the dates are posting dd/mm/yyyy instead of mm/dd/yyyy and the search likes it that way.

I wish my job was flipping burgers at McDonalds.

Last post

I did get the fix working now. In case anyone else is using mm/dd/yyyy my sub get_date had to be like this:


Almost like I posted above, I tried uncommenting different lines and that worked. Now it posts to the database in the right format and I can search with the range search LoisC posted. No What's New, but no biggie.

My sub date_to_unix was fine the way I posted it a couple posts back.

Glad you got it working again !!!

For the What's New mod, are you also using the sub get_date_and_time as instructed for your auth.pl file?

Perhaps the format of it has to also be changed to match sub_get date?

Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/