Per one of the FAQ's, I made the appropriate changes to sub_query in db.cgi, but Im at a loss on how to actually search for what I need.

I need to have a drop down box that says "Search all ads with a requested date of" = Jan, Feb, March, etc.. The actual date requested field is a full date like 1/1/2002, but I only need it to pull the month part of the date.

But I have no idea how to make it search. I tried making a select name="Dates1" with option values of 1,2,3, etc. for the month part of the date, but it didnt work. I have a another drop down search of the date posted field by how old an ad is (1 week, 2 weeks, etc.), but that is configured with the get_date sub, and I dont think that will work with just searching on part of the dates1 field.

Obviously I cannot make a regular link with a statement of dates1=1 (for January) because it needs to be a drop down box instead of a link.

Can someone help me out?

You could modify your search drop down that is for weeks, to have it include more options to search back through the months, but than you still wouldn't be able to label them with specific months.

Unoffical DBMan FAQ

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

Ok, I just wrote a post, but deleted it when I figured 2 things out. I still need help with the drop down box though.

I changed my date fields to date type instead of alpha and that helped with the date translator problem. I also realized that I probably have to do a date range search instead of searching on just part of the date.

So now my only question is, how do I code a range search into a drop down box? It seems like I would need something like <option value="dates1-lt">, but where does it pick up the actual range from? This is where Im confused. How do I code a less than 7/31/2002 but greater than 7/1/2002 into a drop down box?

You'd want to code a select list with <select name="date-lt">, or "date-gt", depending on what you want. Then add

<option value="7/31/2002">before 7/31/2002</option>

and so on.

But you can only have it one way: Either search dates before the specified value, or after.

Combined date range searches are not simple to accomplish. Well, perhaps simple, but you'd have to do some fiddling with sub query.

I suppose you looked at the FAQ and searched the forum?
kellner

Thanks Kellner, I understand what you are saying and that is a probably option, but in my searches through the FAQs, I found a few topics that speak about searching on just part of the date, but it doesnt go into exactly how to setup the search, only how to modify the sub_query. Is this something that maybe I can accomplish if I go without the drop down box and chose another way of searching? Searching on just the month part of the date field is really what I need and if that means making other modifications, I will. BTW, I had changed my dates1 field to a date type instead of alpha as I had it, so it does come out now as 31-Jul-2002 like it should. But as a bottom line, is there anyway possible to only search for the Jul part of the date, no matter how the search would be setup?

Why not do a regular expression search?

If your format is "31-Jul-2001", or "24-Apr-1999", you could code a select list on the search form like this:

<select name="datebymonth">
<option value="Jan">January</option>
<option value="Feb">February</option>
</select>

Then, in the beginning of sub query add this:

if ($in{datebymonth}) { $in{dates1} = "^\d+-$in{datebymonth}-\d+$""; $in{re} = on; } }

Mind you, that would get you *all* dates with the month matching, so you'd get, say, "31-Jul-2001" and "24-Jul-1956". You'd probably want to have a year condition together with this.

Would this be what you're looking for?
kellner

Thanks again Kellner, thats exactly what I was looking for. I will try the datebymonth mod tomorrow. 2 quick questions though. Do I have to have a defined field in my .cfg file named datebymonth? And as far as the year goes, I guess I could have a 2nd drop down box for the year, so would I do the exact same thing in the sub_query for the year as you wrote for the month? Thanks for all your help! Im finally getting somewhere!

Yes, you can have a select list with the year. You'd then simply have to add the year part to the regexp: "^\d+-$in{datebymonth}-$in{year}$".

I don't think you'll need to define any additional fields in your cfg.
kellner

Ok, I must be doing something wrong. I got the drop down box part viewing fine, but when I add the lines

if ($in{datebymonth}) {
$in{dates1} = "^\d+-$in{datebymonth}-\d+$""; $in{re} = on; }
}

to sub_query, it comes back with internal server errors. I tried to put that line in different places in the sub_query, but no matter where it is, I get errors. Then I tried to add this, which I assumed is the correct code for the year part:

if ($in{datebyyear}) { $in{dates1} = "^\d+-$in{datebyyear}-$in{year}$"; $in{re} = on; } }

but that didnt work either, still got internal server errors. Do you know what could be wrong?

Im slowly learning some Perl, but I dont understand this coding for sub_query so Im having problems troubleshooting it.

Sorry to bother you for more help Kellner, but I was wondering if you had read my previous post about the fact I was receiving Internal Server errors with your code in my sub_query? I have been playing around with adding/removing brackets, quotations, etc. but nothing seems to help. When I remove the code, the script works, but not the search, so I assumed its something with this line of code.

Another quick question that I had instead of adding a new post that you might be able to answer quickly for me is this:

Im using the force user to add a record mod I found in the FAQ's. When I use
it works, but I just receive a text line in my html_home that says
Location: http://www.wdu.net/cgi-local/dbman/db.cgi?db=default&uid=steff01.102467573197428&add_form=1
I am trying to make the script go directly to the add_form without a link (automatically), but I also cant use the html_add_form line instead of the Location line since they will be going to an add form in a different db. Would you know how to fix this? I assume its something simple, but I just dont know what it is.
Thanks for all your help. I only have a few more things to work out, then I can hopefully get my site up and running.

I noticed your reply allright, but have been too busy to check up on things. Where did you add the code? I thought it could be something to do with quotes - have you tried "$in{dates1} = q~^\d~ . qq~$somevariable~ . q~\d+$~;" ? (All variables need to be inside qq~ ... ~; but I guess the escaped character of the regexp should only be inside q~ ... ~;).

As for your second problem: so you want to call the add form for a different database?

If both use the same password file & user authentication, you could do it like this:

undef(@db_cols);
do("otherdatabasesetupname.cfg");
&html_page_top; # or however else you call the script header
&html_add_form;
&html_print_bottom;
kellner

Thanks for your reply. Didnt mean to rush you, just wanted to make sure my post doesnt get lost in the shuffle until I can figure this out.

But neither of your suggestions worked:

The first, I added this right after local sortby in sub_query:

does that look right? As I said, all these funny characters confuse me, so Im not too sure what Im doing half the time in db.cgi

As far as the forcing users to add a record, now I have this:

and that doesnt work either. What happens is after I create a login and profile in my adusers db, they will login to the adusers db, but all links in the html_home of adusers points to my ads db since they will be creating all records in the ads db. Well after the login, it does go directly to the add_form in my ads db, but 2 problems from there. The first is that it isnt carrying over the userid into the ads db add_form so I have no way of knowing who created the record and when I fill in the form and click submit, I get errors that I havent filled out all the fields in my adusers add_form (which is the profile form for my adusers db) and shows me the adusers form as blank to fill out. Seems to be some crossed lines there, eh?

As for the first question, try this code instead:

if ($in{datebymonth}) {
$in{dates1} = q~^\d+~ . qq~$in{datebymonth}~ . q~-\d+$~; $in{re} = "on";
}
kellner

Now for the second problem - the two databases must share all authentication routines; they also must store the session IDs in the same /auth directory. If this is the case, the login problem should not arise.

Second, do you mean you get the add form for the ads database allright, only when it is submitted you get errors which actually are related to the adusers setup? Check whether the form has a hidden input field "db", and whether this has $db_setup as its value - it should.
kellner

Hmmm, still not working. With my date search, I dont get the Internal Server errors anymore, but nothing shows up when I search either. At first I had just put in your code and tried to search, but came back with nothing, so then I added the year part ( I think) to the code and still nothing. So this is what I have now:

Does that look right? Im still getting no results even though there are records there.

For the second problem, both my db's use the same db.cgi file and the same .pass and auth.pl file. The only difference I dont use the html_login_forms for my logins....they are both coded into a static html page. I have 2 seperate logins on one page. One for people who want to signup or add their listings and one for people who just want to view listings.
But what happens is when I login to the adusers db and it goes directly to the add_form in the ads db like it should...when I fill it out and click submit, it gives me errors from the adusers db telling me that the fields from my adusers add_form are not filled out and shows a blank add_form from adusers waiting to be filled out. And yes, my ads db add_form does have the db_setup hidden field. But the main thing I am trying to accomplish here is that I dont want strangers just making up fake usernames and profiles just so they can view the ads. I want to make sure that if they do signup for an account, they are actually going to use it to post an ad and not just spy.

Sorry if this sounds confusing, im trying to make it as easy as possible to understand...but I do appreciate your time and effort trying to help me figure this out.

Quick observation on the first issue: $in{year} should also be inside qq~ ... ~, and not q~ ...~. Let me know if that changes anything.
kellner

I was just coming back to edit that when I saw your post :) I actually have this now:





Another edit.....while trying to do a search, I noticed something in the address line of my browser that doesnt make a lot of sense....this is what it says:

http://www.wdu.net/cgi-local/dbman/db.cgi?db=ads&uid=wahm2002.10247964392328&datebymonth=Jun&datebyyear=2003&view_records=View&Post+Date-gt=20-Jun-2002

Its trying to search my Post Date field (which is wrong, its supposed to search by Dates1 field) but also no where does the code give a day of the 20th of june. Do you know where that might have come from? That might be the answer right there. It seems to be looking for dates greater than June 20th in my Post Date field...but what im trying to do is look for all dates in June from my Dates1 field.
Also, no matter which month or year I select from my drop down box, it always shows 20-June-2002.

We may be getting somewhere, though not quickly LOL I had posted in the last message about a weird search I was getting...fixed that...it seemed I had 2 forms intertwined, but now, I get this error.

Invalid date format: '-2003'

I just changed the dots in the sub_query line to dashes since thats how it shows in the date field and yet it gives me this weird error now. But at least its not telling me I have no records!


BTW, this is my address line now:

http://www.wdu.net/cgi-local/dbman/db.cgi?db=ads&uid=wahm2002.102479776562322&datebymonth=Jun&datebyyear=2003&view_records=View

and my sub query line of:

if ($in{datebymonth}) {
$in{Dates1} = q~^\d+~ - qq~$in{datebymonth}~ - qq~$in{datebyyear}~; $in{re} = "on";
}

No, please don't change the dots into dashes - the dots are perl's concatenation signs, they have nothing to do with your problem, please put them back in. The dashes must be put elsewhere, like this:

if ($in{datebymonth}) {
$in{Dates1} = q~^\d+~ . "-" . qq~$in{datebymonth}~ . "-" . qq~$in{datebyyear}~ . q~$~; $in{re} = "on";

push (@search_fields, $db_def{'Dates1'}[0]); # why this? See below!
}

Something else must be the problem. I guess it's that sub query doesn't take into account this particular type of regexp search on date fields and therefore validates dates fields regardless of the "\d+" in the beginning.

A quick fix for this would be to surround the date validation routine in sub query with an "unless" condition for regexp searches, like this:

unless ($in{re} eq "on") { # added for partial date search

foreach $column (@db_cols) { # this is the loop you should see in your db.cgi
if ($in{$column} =~ /^\>(.+)$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: '$1'");
push (@search_gt_fields, $i); $in{"$column-gt"} = $1; $i++; next; }
if ($in{$column} =~ /^\<(.+)$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: '$1'");
push (@search_lt_fields, $i); $in{"$column-lt"} = $1; $i++; next; }
if ($in{$column} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{$column}) or return "Invalid date format: '$in{$column}'");
push(@search_fields, $i); $i++; next; }
if ($in{"$column-gt"} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{"$column-gt"}) or return "Invalid date format: '$in{$column}'");
push(@search_gt_fields, $i); }
if ($in{"$column-lt"} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{"$column-lt"}) or return "Invalid date format: '$in{$column}'");
push(@search_lt_fields, $i); }
$i++;
}

} # end unless

If you do this, you need to add the extra line to the first condition (the one with "push ..."), to make sure the date field is added to the fields which are searched.
kellner

Ok, now I have a new error LOL I at first just added the changes you made with putting the dots back in and quoting the dashes and got the Invalid date fomat error, so then I went back and tried to add in the push statement with the rest of the longer code and get this error:

Invalid date format: '^\d+-Jun-2003$'

For some reason, I guess it seems to be trying to tell me that it doesnt like the way I am searching? I dont understand though because the dates are viewed in my db as 31-Jun-2003. So doesnt look like we've gotten very far.

Ive copied my sub_query to a text file and attached it just in case you want to look at it.

All of this is completely over my head now, so thanks for taking the time to help :)

It's not a new error. It's still the old one, because you haven't made all changes I suggested. Note the line in bold print.

else { # Otherwise this is a regular search, and we only want records
# that match everything the user specified for.

unless ($in{re} eq "on") { #ADD THIS LINE!

$i = 0;
foreach $column (@db_cols) { # this is the loop you should see in your db.cgi
if ($in{$column} =~ /^\>(.+)$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: '$1'");
push (@search_gt_fields, $i); $in{"$column-gt"} = $1; $i++; next; }
if ($in{$column} =~ /^\<(.+)$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: '$1'");
push (@search_lt_fields, $i); $in{"$column-lt"} = $1; $i++; next; }
if ($in{$column} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{$column}) or return "Invalid date format: '$in{$column}'");
push(@search_fields, $i); $i++; next; }
if ($in{"$column-gt"} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{"$column-gt"}) or return "Invalid date format: '$in{$column}'");
push(@search_gt_fields, $i); }
if ($in{"$column-lt"} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{"$column-lt"}) or return "Invalid date format: '$in{$column}'");
push(@search_lt_fields, $i); }
$i++;
}
} # end unless
}


I hope this works now. At least you shouldn't get the date format error anymore :)
kellner

OMG, THANK YOU, THANK YOU, IT WORKED!!!

All this programming is enough to send somone to the funny farm LOL

I dont mean to keep you, but did you think of anything else regarding my other problem of forcing users to add a record, but the add_forms getting crossed? As I said, both db's use the same db.cgi, auth.pl and .pass file. The only thing I can think to tell you about any login is that both logins for each db are created on a static html page...and this is the form info for adusers:

<form action="http://www.wdu.net/cgi-local/dbman/db.cgi" method="post" name="form1">
<input type=hidden name="db" value="adusers">
<input type=hidden name="uid" value="">
Username&nbsp;
<input type="TEXT" name="userid" size="10">&nbsp;&nbsp;

Password&nbsp;
&nbsp;<input type="PASSWORD" name="pw" size="10">
&nbsp;<input type="SUBMIT" name="login" value="Go!">

and the same thing for ads except the db line says "ads". Then of course I have the:

$in{'Userid'}=$db_userid;
my ($status, @hits) = &query("view");
if ($status ne "ok") {
undef(@db_cols);
do("ads.cfg");
&html_add_form;
return;
}

in my adusers db html_home sub. So I dont understand where its getting crossed at.

But thank you again SO MUCH for your help! I would have never of figured that out without a Perl expert.