pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Date validation?
8+ messages / 5 participants
[nested] [flat]

* Date validation?
@ 2001-06-30 21:08 Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Josh Berkus @ 2001-06-30 21:08 UTC (permalink / raw)
  To: pgsql-sql

Folks, 

Is there any PgSQL or PL/pgSQL function which validates dates?  I've
tried ISFINITE and CAST ... AS DATE but these suffer fatal errors if the
date isn't valid (e.g. '2001-06-31').  I need a test I can trap inside a
PL/pgSQL function.

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Date Validation?
@ 2001-07-13 15:24 Josh Berkus <josh@agliodbs.com>
  2001-07-13 16:39 ` Re: Date Validation? Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-07-13 17:04 ` Re: Date Validation? Richard Huxton <dev@archonet.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Josh Berkus @ 2001-07-13 15:24 UTC (permalink / raw)
  To: pgsql-sql

Folks,

Pardon me for asking this again, but I received *no* responses last
week.

Question:  Is there any function or method which will allow me, in SQL
or PL/pgSQL, to validate dates without raising an error for invalid
dates?

Expansion:  I have a number of PL/pgSQL functions which must accept
dates as parameters from the PHP4 interface.  There is, however, the
possibility that an invalid date will be passed, such as "2001-02-31"
due to the nature of HTML forms date selector.
	What I would like to do is accept the date as a VARCHAR parameter, and
then validate it and convert it to a date inside the function if it
passes validation.
	However, all validation methods I've tried, such as ISFINTTE and
TO_DATE produce SQL errors if the date is not valid; this causes the
function to error out and the user gets an unfriendly error message
which I have not control over.  This is unacceptable.  Obviously, one
answer is error-handling inside functions, but we won't get that until
7.2 as far as I know.
	I could write a complicated string-parsing function to test the
validity of each part of the date, but that would a) lock the interface
into passing the dates in only one format, and b) be really complicated
for leap years and the millenium.
	I am hoping that someone has already dealt with this issue in a way
that date validation can be trappable.

Thanks for your advice.

-Josh Berkus



______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Date Validation?
  2001-07-13 15:24 Date Validation? Josh Berkus <josh@agliodbs.com>
@ 2001-07-13 16:39 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Stephan Szabo @ 2001-07-13 16:39 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

On Fri, 13 Jul 2001, Josh Berkus wrote:

> Folks,
> 
> Pardon me for asking this again, but I received *no* responses last
> week.
> 
> Question:  Is there any function or method which will allow me, in SQL
> or PL/pgSQL, to validate dates without raising an error for invalid
> dates?

Not as far as I know off hand, although I guess you could duplicate
the code for to_date to another function except changing the error
handling (a bit of a mess, but would probably function in a pinch).





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Date Validation?
  2001-07-13 15:24 Date Validation? Josh Berkus <josh@agliodbs.com>
@ 2001-07-13 17:04 ` Richard Huxton <dev@archonet.com>
  2001-07-13 17:44   ` Re: Date Validation? Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Richard Huxton @ 2001-07-13 17:04 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; pgsql-sql

From: "Josh Berkus" <josh@agliodbs.com>

> Folks,
>
> Pardon me for asking this again, but I received *no* responses last
> week.
>
> Question:  Is there any function or method which will allow me, in SQL
> or PL/pgSQL, to validate dates without raising an error for invalid
> dates?
>
> Expansion:  I have a number of PL/pgSQL functions which must accept
> dates as parameters from the PHP4 interface.  There is, however, the
> possibility that an invalid date will be passed, such as "2001-02-31"
> due to the nature of HTML forms date selector.

Out of curiosity Josh, why aren't you validating in PHP? - only takes a
couple of lines there.

- Richard Huxton




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Date Validation?
  2001-07-13 15:24 Date Validation? Josh Berkus <josh@agliodbs.com>
  2001-07-13 17:04 ` Re: Date Validation? Richard Huxton <dev@archonet.com>
@ 2001-07-13 17:44   ` Josh Berkus <josh@agliodbs.com>
  2001-07-14 15:29     ` Re: Date Validation? dev@archonet.com
  0 siblings, 1 reply; 8+ messages in thread

From: Josh Berkus @ 2001-07-13 17:44 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; Josh Berkus <josh@agliodbs.com>; pgsql-sql

Richard,

> Out of curiosity Josh, why aren't you validating in PHP? - only takes
> a
> couple of lines there.

We are.  It's just that all other validation takes place inside the
PL/pgSQL functions in order to keep all the business logic in the
database.  Having one form of validation in the interface (PHP) and the
rest in the function can cause the user to go through multiple
validation errors, which is especially frustrating if the second
validation error is fatal.  Example:

USER: '2001-06-31'

PHP Validation: '2001-06-31 is not a valid date.'

USER: '2001-06-30'

PL/pgSQL FUNCTION: 'Your session has timed out.  Please log back in.'

USER: '%^#$&*&#!!!'

See what I mean?  Not a showstopper, but it mars our otherwise
user-friendly validation formula.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Date Validation?
  2001-07-13 15:24 Date Validation? Josh Berkus <josh@agliodbs.com>
  2001-07-13 17:04 ` Re: Date Validation? Richard Huxton <dev@archonet.com>
  2001-07-13 17:44   ` Re: Date Validation? Josh Berkus <josh@agliodbs.com>
@ 2001-07-14 15:29     ` dev@archonet.com
  2001-07-14 18:40       ` Re: Date Validation? Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 8+ messages in thread

From: dev@archonet.com @ 2001-07-14 15:29 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-sql

> Richard,
> 
>> Out of curiosity Josh, why aren't you validating in PHP? - only takes
>> a
>> couple of lines there.
> 
> We are.  It's just that all other validation takes place inside the
> PL/pgSQL functions in order to keep all the business logic in the
> database.  Having one form of validation in the interface (PHP) and the
> rest in the function can cause the user to go through multiple
> validation errors, which is especially frustrating if the second
> validation error is fatal.  Example:
[snip]

Hmm - fair enough - PITA really.

Poked around and had a think and can't come up with anything better than
Stephan Szabo's suggestion of hacking PG's built-in function. You'll want
to_timestamp() which is called from to_date and it's in
src/backend/utils/adt/formatting.c - AFAICT you'll just need to comment out
the elog(...) calls and return a null value (or whatever).

Looks like there are a lot of dependencies - my C is too rusty to figure
that out just by skimming. I'm guessing the validation is fiddly though -
PHP's date handling code is no shorter.

Failing that, what about writing is_valid_date() in pl-tcl/perl? Don't know
anything about tcl, but should be easy enough in perl (cut & paste from a
suitable module)

Or (and this is horrible) check the date in PHP and if it's not valid
replace it with NULL. You can check for the null in the trigger fn, but
unfortunately you no longer have the original value for your error message
(no, I don't like it either).

For the interested on the list:
The central problem seems to be that the error logging function elog() never
returns from an ERROR message and kills the whole transaction.
This makes sense since any code can call elog(ERROR,...) and not have to
worry about recovering from the error.
If PostgreSQL had been written using Java, there'd probably be try...catch
everywhere and it wouldn't be an issue (of course there might well be
performance problems as well as someone having to invent java 10 years
before Sun did ;-)

Presumably, once we have nested transactions all this will be magically
solved by wrapping the possibly dodgy statements with an inner transaction.

Aside: I may have found the world's first "Y2K BC" bug - if we make enough
noise over this it could turn the IT industry round again.

richardh=> select '01-01-01 BC'::date, '0001-01-01 BC'::date;
   ?column?    |   ?column?
---------------+---------------
 2001-01-01 BC | 0001-01-01 BC
(1 row)

- Richard Huxton




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Date Validation?
  2001-07-13 15:24 Date Validation? Josh Berkus <josh@agliodbs.com>
  2001-07-13 17:04 ` Re: Date Validation? Richard Huxton <dev@archonet.com>
  2001-07-13 17:44   ` Re: Date Validation? Josh Berkus <josh@agliodbs.com>
  2001-07-14 15:29     ` Re: Date Validation? dev@archonet.com
@ 2001-07-14 18:40       ` Josh Berkus <josh@agliodbs.com>
  2001-07-15 03:43         ` Re: Date Validation? Christopher Sawtell <csawtell@xtra.co.nz>
  0 siblings, 1 reply; 8+ messages in thread

From: Josh Berkus @ 2001-07-14 18:40 UTC (permalink / raw)
  To: dev@archonet.com; josh@agliodbs.com; +Cc: pgsql-sql

Richard,

> Poked around and had a think and can't come up with anything better
> than
> Stephan Szabo's suggestion of hacking PG's built-in function. You'll
> want
> to_timestamp() which is called from to_date and it's in
> src/backend/utils/adt/formatting.c - AFAICT you'll just need to
> comment out
> the elog(...) calls and return a null value (or whatever).

Not too likely from me ... I don't do C at all!  I'm one of those
horrible people who came to SQL & PHP from VB and MS Access rather than
from a C.S. degree.  Heck, my college degree is in sculpture.

> Failing that, what about writing is_valid_date() in pl-tcl/perl?
> Don't know
> anything about tcl, but should be easy enough in perl (cut & paste
> from a
> suitable module)

Sounds good.  Anybody on the list I can beg (or pay $50) to write it and
post it to the list?  I don't know from perl or tcl either (I do SQL,
PHP, Java and VB).

> Or (and this is horrible) check the date in PHP and if it's not valid
> replace it with NULL. You can check for the null in the trigger fn,
> but
> unfortunately you no longer have the original value for your error
> message
> (no, I don't like it either).

Hmmm ... but given the lack of alternatives, may be better than the
2-stage system ...

> Presumably, once we have nested transactions all this will be
> magically
> solved by wrapping the possibly dodgy statements with an inner
> transaction.

Yeah.  I'm also assuming that when Jan and co. get full cursor support
and stored procedures worked out, error trapping will also become
available.  Then this whole issue goes away with a simple ON EXCEPTION.

> Aside: I may have found the world's first "Y2K BC" bug - if we make
> enough
> noise over this it could turn the IT industry round again.
> 
> richardh=> select '01-01-01 BC'::date, '0001-01-01 BC'::date;
>    ?column?    |   ?column?
> ---------------+---------------
>  2001-01-01 BC | 0001-01-01 BC
> (1 row)

Quick! Call the presses!  Stop the computers!  

I could also make a number of Christian theological jokes, but I don't
want to offend anyone on the list ....

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Date Validation?
  2001-07-13 15:24 Date Validation? Josh Berkus <josh@agliodbs.com>
  2001-07-13 17:04 ` Re: Date Validation? Richard Huxton <dev@archonet.com>
  2001-07-13 17:44   ` Re: Date Validation? Josh Berkus <josh@agliodbs.com>
  2001-07-14 15:29     ` Re: Date Validation? dev@archonet.com
  2001-07-14 18:40       ` Re: Date Validation? Josh Berkus <josh@agliodbs.com>
@ 2001-07-15 03:43         ` Christopher Sawtell <csawtell@xtra.co.nz>
  0 siblings, 0 replies; 8+ messages in thread

From: Christopher Sawtell @ 2001-07-15 03:43 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; dev@archonet.com; josh@agliodbs.com; +Cc: pgsql-sql

On Sun, 15 Jul 2001 06:40, Josh Berkus wrote:
> Richard,
>
> > Poked around and had a think and can't come up with anything better
> > than
> > Stephan Szabo's suggestion of hacking PG's built-in function. You'll
> > want
> > to_timestamp() which is called from to_date and it's in
> > src/backend/utils/adt/formatting.c - AFAICT you'll just need to
> > comment out
> > the elog(...) calls and return a null value (or whatever).
>
> Not too likely from me ... I don't do C at all!

'Tis a shame. I could teach you C if you want. Get the lessons from the URL 
in the .signature box below.

> I'm one of those
> horrible people who came to SQL & PHP from VB and MS Access rather than
> from a C.S. degree.

Not 'horrible', nobody who has 'seen the light' in any way can be described 
as "horrible". That description is reserved for those folks who revel in 
their intimacy with darkness. ( How you define "darkness" is over to you )

> Heck, my college degree is in sculpture.

No wonder the aesthetic is so important to you.

> > Failing that, what about writing is_valid_date() in pl-tcl/perl?
> > Don't know
> > anything about tcl, but should be easy enough in perl (cut & paste
> > from a
> > suitable module)
>
> Sounds good.  Anybody on the list I can beg (or pay $50) to write it and
> post it to the list?  I don't know from perl or tcl either (I do SQL,
> PHP, Java and VB).

Well here is how I deal with the problem.

First you have to turn off the error reporting flag in the php.ini 
configuration file. That stops most of the geek-speak rubbish getting out to 
the client.

Now, please find attached the .php files I am using in a project.
I feel that the level of geek-speak in the solution is accepable,
but if it isn't to you or your clients, then you could always replace the 
error message goming out of PostgreSQL to something more understandable to 
the "Common Man / Lay Person" by editing the PostgreSQL source and 
re-compiling it. ( not difficult ) Note that I have the database here 
configured to the European representation of dates and the wording of the 
error messages in the php file reflects this.

Also note that I have forgotton to test for dates being in the past, and that 
it would be better if the error message was delivered in a pop-up html page 
or a JavaScript alert.

Off the top of my head another untested idea for a solution to the problem is 
to do the data verification in JavaScript on the client machine.
There is a JavaScript date.parse() function.

Qualify for the $50 ?

-- 
Sincerely etc.,

 NAME       Christopher Sawtell
 CELL PHONE 021 257 4451
 ICQ UIN    45863470
 EMAIL      csawtell @ xtra . co . nz
 CNOTES     ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz

 -> Please refrain from using HTML or WORD attachments in e-mails to me <-

Attachments:

record_absence.php (text/html, 3.0K) download <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <title>Record Legitimate Absence</title> <STYLE type="text/css"> h2 { text-align: center } h3 { text-align: center } table { text-align: center } a { hover: color="#ff8080" } </STYLE> <!-- <script language="php"> $school_conn=pg_Connect("host=berty dbname=chris port=5432"); $school_name_result=pg_Exec( $school_conn, "select name from school limit 1;" ); $school_name=pg_Result( $school_name_result, 0, 'name' ); </script> --> </head> <body> <h2><?php echo $school_name; ?></h2> <h3>Record Legitimate Absence</h3> <form action="./post_absence.php" method="get"> <table border="0" cellpadding="2" align="center"> <tr> <th align="right"><label for="pn">Pupil Number:</label></th> <td align="left"><input tabindex="1" type="text" name="number" id="pn" maxlength="4" size="4"><br></td> </tr> <tr> <th align="right"><label for="fn">First Name:</label></th> <td align="left"><input tabindex="2" type="text" name="first_name" id="fn" maxlength="15" size="15"><br></td> </tr> <tr> <th align="right"><label for="ln">Last Name:</label></th> <td align="left"><input tabindex="3" type="text" name="last_name" id="ln" maxlength="15" size="15"><br></td> </tr> <tr> <th align="right"><label for="dob">Date of Birth:</label></th> <td align="left"><input tabindex="4" type="text" name="date_of_birth" id="dob" maxlength="17" size="18"><br></td> </tr> <tr> <th align="right"><label for="ld">Leave Date:</label></th> <td align="left"><input tabindex="5" type="text" name="leave_date" id="ld" value="Today" maxlength="17" size="18"><br></td> </tr> <tr> <th align="right"><label for="rd">Return Date:</label></th> <td align="left"><input tabindex="5" type="text" name="return_date" id="rd" value="Tomorrow" maxlength="17" size="18"><br></td> </tr> <tr> <th align="right"><label for="dur" accesskey="D">Duration:</label></th> <td align="left"> <table> <tr> <td> <input tabindex="6" type="text" name="duration" id="dur" maxlength="2" size="2"> </td> <td> <SELECT multiple size="3" name="dur_uom" tabindex="7"> <option value="days">Days</option> <option value="weeks">Weeks</option> <option value="months">Months</option> </select> </td> </tr> </table> </td> </tr> <tr> <td colspan="2"> <table align="center" border="0" cellpadding="0"> <tr> <th align="right" valign="top"><h3><input name="submit" tabindex="8" type="submit" value="Send Data">&nbsp;</h3></th> <th align="left" valign="top"><h3>&nbsp;<input name="reset" tabindex="9" type="reset"></h3> </tr> </table> </td> </tr> </table> </form> </body> </html>
post_absence.php (text/html, 14.4K) download <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"> <html> <head> <title>Record Legitimate Absence</title> <STYLE type="text/css"> title { text-align: center } h2 { text-align: center } </STYLE> <script language="php"> $debug = 0; $a = split ('&', $QUERY_STRING); $n = count ($a); $ta = array ( "'" => "\\'" ); // Translation array to put backslashes $flag = 'f'; // on the left of apostrophes. $id_flag = 'f'; $first_name_flag = 'f'; if ($debug) print "Query String:-<br>$QUERY_STRING\n"; $sql = "select to_char(CURRENT_TIMESTAMP, 'D') as dow, number, " . "first_name || ' ' || last_name as name, " . "rtrim(to_char(date_of_birth, 'DD Month')) || ' ' || to_char(date_of_birth, 'YYYY') as date_of_birth " . 'from pupil where '; $b = split('=', $a[0]); $c = urldecode($b[1]); if ($debug) { echo "$b[0] -> $c<p>"; } if ( $b[0] == 'number' ) { if ( ereg ( '([[:<:]][.0-9.]*[[:>:]])', $c )) // regular expression match to numbers { $sql = $sql . "number=$c;"; $flag = 't'; $id_flag = 't'; // We have got a number, pupil uniquely identified if ($debug) print "Pupil Number: $c<p>"; } } if ( $id_flag == 'f' ) // Didn't get a number, try to get id via names. { for ( $i=0; $i < $n; $i++ ) { $b = split('=', $a[$i]); $c = urldecode($b[0]); $d = strtr(urldecode($b[1]), $ta ); // Translate O'Tool to O\'Tool for PostgreSQL if ( $debug ) echo "$c - $b[1] - $d<p>\n"; // Debug diagnostics. if ( $c == 'first_name' and $d ) { if ( $flag == 't') $sql = $sql . 'and '; $sql = $sql . "first_name ~* '.*$d.*' "; $first_name_flag = 't'; $id_flag = 't'; } if ( $c == 'last_name' and $d ) { if ( $first_name_flag == 't') { $sql = $sql . "and last_name ~* '.*$d.*' "; } else { $sql = $sql . "last_name ~* '.*$d.*' "; $id_flag = 't'; } } } for ( $i=0; $i < $n; $i++ ) { $b = split('=', $a[$i]); $c = urldecode($b[0]); $b = split('=', $a[$i]); $c = urldecode($b[0]); $d = strtr(urldecode($b[1]), $ta ); // Translate O'Tool to O\'Tool for PostgreSQL if ( $debug ) echo "$c - $b[1] - $d<p>\n"; // Debug diagnostics. if ( $c == 'date_of_birth' and $d ) { if ( $flag == 't') { $sql = $sql . 'and '; } $sql = $sql . "date_of_birth=date('$d') "; $id_flag = 't'; } if ( $c == 'leave_date' and $d ) { $leave_date="$d"; $flag = 't'; } if ( $c == 'return_date' and $d ) { $return_date="$d"; $flag = 't'; } if ( $c == 'duration' and $d ) { $duration=$d; } if ( $c == 'dur_uom' and $d ) { $dur_uom=$d; } } // end of for loop. $sql=$sql . 'order by last_name;'; } // end of if if ( $debug ) echo "$sql<p>\n"; if ( $id_flag == 'f' ) // No data entered. "Send Data" button clicked by mistake. { echo <<<END_1 </head> <body> <h2 align="center"><font color="red">No Data Entered</font>!</h2><p> You have not entered sufficient data to identify any pupil. You can use a part of either of the pupil's names for identity, e.g. the characters 'beth' will identify the full name 'Elizabeth' as well as 'Beth'. Note that the Pupil Number must be a number. <h3><center><a href="record_absence.php">Another Try</a></h2></center> </body> </html> END_1; exit; } // Got some data. $post_absence_conn=pg_Connect("host=berty dbname=chris port=5432"); $post_absence_result=pg_Exec( $post_absence_conn, $sql ); $number_rows=pg_Numrows( $post_absence_result ); if ( $number_rows == 0 ) // ERROR: No Pupil selected. { echo <<<END_2 </head> <body> <h2 align="center"><font color="red">No Absence Posted</font>!</h2> <h3 align="center">You must select a pupil in order to post the fact<br> that (s)he is going to be absent. </h3><p> <h3 align="center">Check that the names or number are correct.</h3><p> <h1><center><a href="record_absence.php">Another Try</a></center></h1> </body> </html> END_2; exit; } if ( $number_rows > 20 ) // ERROR: Too many Pupils selected. { echo <<<END_2 </head> <body> <h2 align="center"><font color="red">No Absence Posted</font>!</h1><p> <h3 align="center">You have selected too many pupils.</h3><p> <h3 align="center">Please input more of the name(s),<br>and possibly the pupil's date of birth.</h3><p> <h2><center><a href="record_absence.php">Another Try</a></center></h2> </body> </html> END_2; exit; } if ( $number_rows == 1 ) // Hurray! we've got a single pupil!!. { $number=pg_Result( $post_absence_result, 0, 'number' ); // From the pupil table. $name=pg_Result( $post_absence_result, 0, 'name' ); $dow=pg_Result( $post_absence_result, 0, 'dow' ); // Get the current day of the week. // Now let's try to extract sensible dates from the human-readable stuff. $leave_date_in_quotes="'" . $leave_date . "'"; $return_date_in_quotes="'" . $return_date . "'"; if (($leave_date == '' and $duration == '' and $dur_uom == '') or ( $duration != '' and $dur_uom == '' )) { echo <<<END_4 </head> <body> <h2 align="center"><font color="red">No Absence Posted</font>!</h2><p> <h3 align="center"><font color="red"> You must set a date for return to school<br> or the duration of the absence!</font> </h3> <p> <h3><center><a href="record_absence.php">Another Try</a></h3></center> </body> </html> END_4; exit; } if ( $duration != '' and $dur_uom != '' ) { $return_date_in_quotes = "$leave_date_in_quotes + '@ $duration $dur_uom'::reltime"; } $leave_date_sql="select date($leave_date_in_quotes) as leave_date;"; $leave_day_result=pg_Exec( $post_absence_conn, $leave_date_sql ); if ( pg_Errormessage( $post_absence_conn )) // Is it a date? { $error_message=pg_Errormessage( $post_absence_conn ); // No, have another go. echo <<<END_3 </head> <body> <h3 align="center"><font color="red">$error_message</font></h3> <p> You have entered something that the computer can't understand for the date on which leave starts. You should enter either one of the words 'Yesterday", 'Today', 'Tomorrow', or the actual date you require. This date may be written in most of the ways in which English speaking people write dates. If you input the date using thee numbers, then the second, or middle, number is the month. You may enter the number for the year using either the last two digits or the full four digits. <!-- Alternatively you can use the name of the day of the week. In this case today's day name, and those prior to today will refer to dates in the next week. <p> --> <h2><center><a href="record_absence.php">Another Try</a></h2></center> </body> </html> END_3; exit; } // If we have got to here we have $leave_date=pg_Result( $leave_day_result, 0, 'leave_date' ); // an acceptable leave date. $leave_date_in_quotes="'" . $leave_date . "'"; $return_date_sql="select date($return_date_in_quotes) as return_date;"; if ( $debug ) echo "Return date sql:- $return_date_sql<p>"; $return_day_result=pg_Exec( $post_absence_conn, $return_date_sql ); if ( pg_Errormessage( $post_absence_conn )) { $error_message=pg_Errormessage( $post_absence_conn ); echo <<<END_3a </head> <body> <h3 align="center"><font color="red">$error_message</font></h3> <p> You have entered something that the computer can't understand as the date on which the pupil returns from leave. You may enter either the word 'Tomorrow', or the actual date you require. This date may be written in most of the ways in which English speaking people write dates. If you input the date using thee numbers, then the second, or middle, number is the month. You may enter the number for the year using either the last two digits or the full four digits. <!-- Alternatively you can use the name of the day of the week. In this case today's day name, and those prior to today will refer to dates in the next week. <p> --> <h2><center><a href="record_absence.php">Another Try</a></h2></center> </body> </html> END_3a; exit; } $return_date=pg_Result( $return_day_result, 0, 'return_date' ); $return_date_in_quotes="'" . $return_date . "'"; $test_dates_query="select (date($leave_date_in_quotes) < date($return_date_in_quotes)) " . "and (date($leave_date_in_quotes + '@ 1 day'::reltime) >= date(current_timestamp)) " . "and (date($return_date_in_quotes) <= date(max(date_time))) as date_flag from absence;"; if ( $debug ) echo $test_dates_query; $test_dates_result=pg_Exec( $post_absence_conn, $test_dates_query ); $test_dates_flag=pg_Result( $test_dates_result, 0, 'date_flag' ); if ( $test_dates_flag == 'f' ) { echo <<<END_3c </head> <body> <table border="0"> <tr> <td align="center"> <h2 align="center"><font color="red">No Absence Posted</font>!</h2> <h3><font color="red">You have entered the dates incorrectly</font></h3><p> </td> </tr> <tr> <td> Check these situations:- <ul> <li>It is impossible for a pupil to return from leave before (s)he goes away!</li> <li>You may not enter a going on leave date previous to yesterday.</li> <li>You may not enter a return from leave date after the end of term.</li> </ul> </td> </tr> <tr> <td align="center"> <a href=./record_absence.php>Another Try</a> </td> </tr> </table> </body> </html> END_3c; exit; } $sql2="begin; insert into absence_buffer ( pupil_number, from_date, return_date ) " . "values ( $number, $leave_date_in_quotes, $return_date_in_quotes ); end;"; if ( $debug ) echo "sql2:- $sql2<p>Leave Date:- $leave_date<p> Return Date:- $return_date<p>\n"; // DEBUG statement. $post_absence_result=pg_Exec( $post_absence_conn, $sql2 ); if (( $duration != '' ) && ( $dur_uom != '' )) { $line = "$name away from $leave_date for $duration $dur_uom"; $query = "duration=" . htmlentities (urlencode ($duration)) . "&dur_uom=" . htmlentities (urlencode ($dur_uom)); } $nice_dates_sql="select nice_date($leave_date_in_quotes) as nice_leave_date, " . // Use Postgress to "nice_date($return_date_in_quotes) as nice_return_date"; // make nice dates. if ( $debug ) echo "Nice Dates sql: $nice_dates_sql<p>"; $dates_result=pg_Exec( $post_absence_conn, "$nice_dates_sql" ); $nice_leave_date=pg_Result($dates_result, 0, 'nice_leave_date' ); $nice_return_date=pg_Result($dates_result, 0, 'nice_return_date' ); $line = "<h3 align=\"center\"><u>$name</u><h3 align=\"center\">" . "Away from<p><h3 align=\"center\">$nice_leave_date<br>until<br>$nice_return_date</h3>"; $query = "?leave_date=duration=" . htmlentities(urlencode($leave_date)) . "&return_date=duration=" . htmlentities(urlencode($return_date)); echo <<<END_3 </head> <body> <h2 align="center">Absence Posted</h3><p> $line <center><h3><a href="record_absence.php$query">Next One</a></h3></center> </body> </html> END_3; exit; } echo <<<END_4 </head> <body> <table border="0" cellpadding="5" align="center"> <tr> <td colspan="3"> <table border="0" align="center"> <tr> <td> <font color="red">You have selected more than one pupil.</font><br> Select the one to record by clicking the name.<br> If none are correct then click the <a href=./record_absence.php>Another Try</a> link. </td> </tr> </table> </td> </tr> <tr><th>Number</th><th>Name</th><th>&nbsp;&nbsp;&nbsp;Date of Birth&nbsp;&nbsp;&nbsp;</th</tr>\n END_4; for ( $i=0; $i < $number_rows; $i++ ) { $name=pg_Result( $post_absence_result, $i, 'name' ); $number=pg_Result( $post_absence_result, $i, 'number' ); $date_of_birth=pg_Result( $post_absence_result, $i, 'date_of_birth' ); $query="number=$number&leave_date=" . htmlentities(urlencode($leave_date)) . "&return_date=" . htmlentities(urlencode($return_date)); echo " <tr><td align=\"right\">$number</td><td align=\"left\">" . "<a href=\"./post_absence.php?$query\">$name</td>" . "<td align=\"left\">$date_of_birth</td></tr>\n" ; } echo "<tr><td colspan=\"3\" align=\"center\"><h3><a href=\"./record_absence.php\">Another Try</a></h3></td></tr>"; </script> </table> </body> </html> <!-- /* Leave out this name of day stuff for the moment. if ( $ok == 'false' and $leave_date != '' and $return_date != '' ) { // Second Preference - Explicit dates. // If we have got here it is a date $leave_day_result=pg_Exec( $post_absence_conn, "select number from day_of_week where name = $leave_date;" ); if ( $ $leave_day_result=pg_Exec( $post_absence_conn, // Test to see they are day names. "select number from day_of_week where name = $leave_date;" ); if ( pg_Numrows ( $leave_day_result ) == 1 ) { // Day of week for start of leave. $return_day_result==pg_Exec( $post_absence_conn, "select number from day_of_week where name = $return_date;" ); $ok = 'true'; } ================= { $leave_date = "'$leave_date::timestamp'"; $return_date = "'$leave_date' + '@ $duration $dur_uom'::reltime'"; $ok = 'true'; } if (( $return_date == '' ) && (( $duration == '' ) || ( $dur_uom == '' ))) if ( $debug ) echo "Dates sql: $dates_sql<p>"; */ -->
^ permalink raw reply [nested|flat] 8+ messages in thread


end of thread, other threads:[~2001-07-15 03:43 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-30 21:08 Date validation? Josh Berkus <josh@agliodbs.com>
2001-07-13 15:24 Date Validation? Josh Berkus <josh@agliodbs.com>
2001-07-13 16:39 ` Re: Date Validation? Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-07-13 17:04 ` Re: Date Validation? Richard Huxton <dev@archonet.com>
2001-07-13 17:44   ` Re: Date Validation? Josh Berkus <josh@agliodbs.com>
2001-07-14 15:29     ` Re: Date Validation? dev@archonet.com
2001-07-14 18:40       ` Re: Date Validation? Josh Berkus <josh@agliodbs.com>
2001-07-15 03:43         ` Re: Date Validation? Christopher Sawtell <csawtell@xtra.co.nz>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox