date in search options 1

[safra]

Hi,

I did a search on this form about this problem but couldn't find a relevant thread.

I have a database that contains a date field (day-month-year).

I would like to add an element to the search options. 'two' date input fields (each of them has 3 comboboxes day-month-year).

if both are filled out search all records between the two input dates. if only the first input field has been filled out search all records till this date. If only the last input field has been filled out search all records from this date till now.

I can only think of an ugly way and create an if-statements (check year>check month>check day>true) for each input field. And then match the two.

Is this the way to go or does anybody know a more direct and elegant way to do this?

regards,

Ron

 

[tsdragon]

How about:

$start_date = "01-01-1900" unless $start_date;
$end_date = "31-12-2099" unless $end_date;

That will set the start date to a date way before anything likely to be in your database, UNLESS it has a value.
Likewise it will set the end date to a date way after anything likely to be in your database, UNLESS it has a value.

Then you can always search for &quot;dbdate > $start_date && dbdate < $end_date&quot;.
Tracy Dryden
tracy@bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[safra]

Yes, I was thinking to do that the way you suggested but I was trying to find an efficient way of doing the actual search.

I am far from an expert!

looking to your code, I don't think this will work:

$start_date = &quot;01-01-1900&quot; unless $start_date;
$record_date = &quot;02-02-2000&quot; #example record date
$end_date = &quot;31-12-2099&quot; unless $end_date;
if ($start_date < $record_data and $record_data < $end_date ){

add record to result list code

}



I was thinking to deal with this the following way:
the date has to be split in 3 strings and then if-statements:

$start_month = 1;
$start_day = 1;
$start_year = 1900;
$record_month = 1;
$record_day = 1;
$record_year = 2001;
$current_month = 1;
$current_day = 1;
$current_year = 2001;

if ($start_month < $record_month and $start_day < $record_day and $start_year < $record_year){
if ($record_month < $current_month and $record_day < $current_day and $record_year < $current_year ){

add record to result list code

}
}

But is this the way to go or is there another way to do this?

Ron

 

[tsdragon]

Actually, I was thinking you want to do a database query using the dates, and SQL would handle the date comparisons correctly. If you want to compare date strings, just put them in YYYYMMDD form and they will compare properly. Tracy Dryden
tracy@bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[safra]

I have been looking for a simple perl-mysql tutorial with all basic actions so I could learn how to work with this but couldn't find one.

I use a simple flattext file.

But that is a handy tip (yyyymmdd). That makes things a lot simpler.

Thanks!

Ron

 

[tsdragon]

Try the MySQL web site:

http://www.mysql.com

and see if they have a tutorial there. I love using MySQL. It makes a lot of my job MUCH simpler. For example, a lot of the logic in selecting, sorting and grouping records is done by the SQL query, rather than in perl code. And that's not even mentioning how much easier it is to add, update and delete records! It's well worth learning.

Tracy Dryden
tracy@bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[safra]

A while ago I had a look on

http://www.mysql.com.

But they don't offer a ready to use simple 'setup' and 'maintain' perl-mysql application. This would be so helpfull just to learn how to setup things and deal with the basic principles: add fields, add resords, delete records, view records,etc.

Right now I have no idea where to start.

Ron

 

[goBoating]

humbly submitted for your consideration....

MySQL is a relational database management system. While it is possible to build trivial applications with it, I contend that the added system complexity, hardware and software overhead make the use of an RDBMS appropriate only when certain conditions exist.
Things like:
Will you need multiple concurrent connections to this data?
Will the data set be to complex to use flat files easily?
Is the data set to large to be served efficiently from flat files?
Might the data set quickly grow to the point that flat files are to slow/unweildy?
Will you need to make truely complex queries?

If you want to do MySQL so you can learn how, certainly go for it. But, if the only thing you want to do is compare dates, an RDBMS is a little overkill.

Try one of the date modules from CPAN.

Another approach might be..... convert your dates into a format that is easily dealt with.... You can use the Time::Local module to convert dates into seconds since the Perl epoch, 01-01-1970. Then, date comparisons are trivial.

#!/usr/local/bin/perl
use Time::Local;

$start_date = "01-01-1985";
@sdate = split(/-/,$start_date);

$seconds = timelocal(0,0,0,$sdate[0],$sdate[1] - 1,$sdate[2]);
print "seconds => $seconds\n";

$date = localtime($seconds);
print "Date is $date\n";

Note that this has the serious limitation of not being useful for dates prior to the epoch.

'just my $.02


keep the rudder amid ship and beware the odd typo

 

[safra]

Thanks goBoating for your comments. A SQL database is certainly not needed for the project I started this thread for and the suggestions made by you and tsdragon will be perfect to solve the issue in a good way.

MySQL was brought up and I do want to start learning this.

I have another database project (flat file) which started the beginning this year.

At the moment the file has over 1000 records and file size about 350kb. Actually I have almost zero experience with databases and I am not sure when I should expect problems running it. Accidentally I tested the database last week. I multiplied the current contents 4 times (filesize almost 1.5mb). Tested it and it seemed to work fine.

When should I expect problems or when is it going to run significantly slower? Should I consider MySql given the current file size after 4 months?

Ron

 

[tsdragon]

If you want a great tool for working with MySQL databases try DBTools. You can download it through mysql.com under contributed software. It will allow you to create and maintain databases, and has a great import wizard for converting other formats (access, excel, etc.) to mysql format. It's also free. Tracy Dryden
tracy@bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.