pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Sawtell <csawtell@xtra.co.nz>
To: Josh Berkus <josh@agliodbs.com>
To: dev@archonet.com
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Date Validation?
Date: Sun, 15 Jul 2001 15:43:44 +1200
Message-ID: <20010715034251.RZSF3874492.mta6-rme.xtra.co.nz@there> (raw)
In-Reply-To: <web-85772@davinci.ethosmedia.com>
References: <web-85772@davinci.ethosmedia.com>

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>"; */ -->

view thread (8+ messages)

Message-ID: <20010715034251.RZSF3874492.mta6-rme.xtra.co.nz@there>
Permalink:  ../20010715034251.RZSF3874492.mta6-rme.xtra.co.nz@there/
Also on:    postgresql.org/message-id/20010715034251.RZSF3874492.mta6-rme.xtra.co.nz@there

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: csawtell@xtra.co.nz, josh@agliodbs.com, dev@archonet.com
  Subject: Re: Date Validation?
  In-Reply-To: <20010715034251.RZSF3874492.mta6-rme.xtra.co.nz@there>
  References: <web-85772@davinci.ethosmedia.com> <20010715034251.RZSF3874492.mta6-rme.xtra.co.nz@there>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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