Gossamer Forum
Home : Products : DBMan : Customization :

range search syntax

Quote Reply
range search syntax
I am using DBman as the back end for a room reservation program, for a small resort.
I wish to do two separate range searches, one to show vacant rooms within a specified date range,
and one to show occupied rooms within a specified date range.
First I create a database with a 69 day span, (severn days prior to localtime and two months in advance)
When a room is booked a new value for 'field 1' is printed using the modify_form sub.
So: 'field 1' with value 'unique0' = vacant
and 'field 1' with 'unique1' = occupied
Simple, I can now search and display the vacant rooms or the occupied rooms by searching either
for unique0 or unique1.
But I don't want to see all the days up until the end of the records that a room is vacant or occupied,
I wish only to see the results between two specified dates.
This is what I have tried passing, but I can only get dbman to reconize the first part of the date range.
/cgi-bin/db.pl?db=default&uid=&OC=unique1&ID=&Room+%23=&Date=%3E1135051260+%3C1135569660&Type=---&Rate=&Persons=&Customer=&Checkin=&Checkout=&Comments=&keyword=&view_records=View+Records
Can someone please help me with the syntax?
rgs Peter.Unsure
Quote Reply
Re: [peterhall] range search syntax In reply to
What are your fields? I don't understand how you are setting up this database.

BTW, I moved this to the Customization forum because this is more of a Customization issue than an Installation issue.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.
Quote Reply
Re: [JPDeni] range search syntax In reply to
The fields that matter are Status and Date, I am trying to search in this manner:
status must equal unique0 and dates must be between two submitted query dates.

However I gave it some thought and I have duplicated the date field, (different field names "Checkin" "Checkout" but the with the same data)
So now I search for status, and then perform the range search in the two date fields.
It works but a bit messy.
The result gives the vacant rooms that fall into the period between the two submitted dates. My front end script takes care of the time details.

I now have to work on how to see if a room is booked for part of the time between the two given dates.

Any sugestions?

rgs Peter
Quote Reply
Re: [peterhall] range search syntax In reply to
I still don't understand what you're doing. I can't tell you how to see if a room is booked for part of the time unless I can understand how it's decided that the room is booked at all. Is there a record for each room? A record for each room for each day? A record for each day, with fields for each room? Something completely different? There are a lot of ways you could do this, and the answer to your question would depend on which one you use.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.
Quote Reply
Re: [JPDeni] range search syntax In reply to
Sorry, I missed a bit out, when I write the database using my own script, it writes a record for each room for each day, for the length of the desired record. I show two months in advance.
The time stamp for a day is set for 12:00:00 (midday)
My query to the database then goes like this:
Is the room vacant? test unique field, (if yes) Is it vacant between these days? .........
test date1 field with >time stamp of 11:59:00, test date2 field with <time stamp of 12:01:00
What I attempted to do, was to test only one date field with two different arguments, that was where I came unstuck. (but is this possible?)

I have tried the following:
#give value to query
$config{'time'} = ">$config{'checkin'}++<$config{'checkout'}";

#send query within submit routine
<input type=hidden name="Checkin" value="$config{'time'}">

But the database does not reconise the second part of the query '<$config{'checkout'}'
---------------------------------------
With regard to seeing which rooms are vacant/occupied for part of a period between two given days, DBman does this in its logic (daah I was not thinking too good)
------------------------------------------
Next issue, In the DBman demo, one field is a URL, and the record displayed has an active hyperlink to whatever. I have tried to use this so that a record can have a link to more information, such as guest name etc. I cannot get the record that is displayed to show a active hyperlink? could this be my .db format, I used \t for a delimiter, or is this issue something more complex?

rgs Peter.
Quote Reply
Re: [peterhall] range search syntax In reply to
I'm not sure what the $config hash is for. It doesn't mean anything in DBMan, but maybe you've merged DBMan with something else. In general, if you want to search for something that is "less than" or "greater than" use fieldname-lt for "less than" and fieldname-gt for "greater than." So, if you have a field that's called "height" and you want to find all people who are between 58 inches tall and 63 inches tall, inclusive, you would have two fields:

name="height-lt" value=64
name="height-gt" value=57

I"m not sure how that would relate to your application, but maybe this'll help. :-)

Regarding the URL hyperlink, you have to tell DBMan to do that. If you're using the autogenerate feature in DBMan, you might want to consider using the mod I wrote, called "Autogenerate Enhancements." It's at http://www.jpdeni.com/...ods/autogenerate.txt . If you apply that mod, the script will automatically make clickable links when either a URL or an email address is being displayed. The URL only works if it starts with http://.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.
Quote Reply
Re: [JPDeni] range search syntax In reply to
Thanks Carol I will give your mod a try.

Yes you are correct, I use $config in my front end script, it is a method to asign global variables at any position in a script by using the following:

use vars qw(%config);

it's a useful shortcut when you need to assign a global inside of { if argument}

rgs Peter.Cool