Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using upper() / decode() together

Using upper() / decode() together

From
"Ross Gohlke"
Date:
I'm using postgres as the backend of a custom PHP Web application. Text
is base64_encode-d in php, then stored in varchar fields in postgres.

I'm trying to do this:
SELECT * from table where upper(decode(field,'base64')) like
upper('%sometext%');

I keep getting this error:
ERROR:  function upper(bytea) does not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.


Is there any way to use upper in this manner?

--
Ross Gohlke
ross at grinz dot com

Re: Using upper() / decode() together

From
"Sean Davis"
Date:
----- Original Message -----
From: "Ross Gohlke" <ross@grinz.com>
To: <pgsql-novice@postgresql.org>
Sent: Thursday, February 24, 2005 8:03 PM
Subject: [NOVICE] Using upper() / decode() together


> I'm using postgres as the backend of a custom PHP Web application. Text
> is base64_encode-d in php, then stored in varchar fields in postgres.
>
> I'm trying to do this:
> SELECT * from table where upper(decode(field,'base64')) like
> upper('%sometext%');
>
> I keep getting this error:
> ERROR:  function upper(bytea) does not exist
> HINT:  No function matches the given name and argument types. You may
> need to add explicit type casts.

Does doing something like (untested):

SELECT * from table where upper(decode(field,'base64')::varchar) like
upper('%sometext%');

work for you?

Sean

Re: Using upper() / decode() together

From
"Ross Gohlke"
Date:
I get this error:

ERROR:  cannot cast type bytea to character varying

Any ideas?

> Does doing something like (untested):
>
> SELECT * from table where upper(decode(field,'base64')::varchar) like
upper('%sometext%');
>
> work for you?
>
> Sean
>

Ross Gohlke
ross at grinz dot com

Please help

From
Kumar S
Date:
Hello all,


I have a table and it has is like this:
exp_id
 exp_name
 exp_type
 exp_desc
 exp_pmid
 exp_paper
 exp_author
 genechip_id
 exp_rawdata_link
 con_id
Indexes:
    "experiment_pkey" primary key, btree (exp_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (genechip_id) REFERENCES
genechip_array(genechip_id)
    "$2" FOREIGN KEY (con_id) REFERENCES
contacts(con_id)


Based on the data I have information that should fill
in this column U133A.

What I have in the genechip_array table is

 genechip_id |        fc_genechip_array  |gc_spec
--+----------------------+------------------

5 | Human Genome U133A Array   | Homo sapiens


Now how can I bring in the foreign key 5 to my
previous table. All I have is U133A.

Could any one help me in this.

Thank you.





__________________________________
Do you Yahoo!?
Yahoo! Mail - now with 250MB free storage. Learn more.
http://info.mail.yahoo.com/mail_250

Re: Please help

From
Sean Davis
Date:
On Mar 1, 2005, at 12:32 PM, Kumar S wrote:

> Hello all,
>
>
> I have a table and it has is like this:
> exp_id
>  exp_name
>  exp_type
>  exp_desc
>  exp_pmid
>  exp_paper
>  exp_author
>  genechip_id
>  exp_rawdata_link
>  con_id
> Indexes:
>     "experiment_pkey" primary key, btree (exp_id)
> Foreign-key constraints:
>     "$1" FOREIGN KEY (genechip_id) REFERENCES
> genechip_array(genechip_id)
>     "$2" FOREIGN KEY (con_id) REFERENCES
> contacts(con_id)
>
>
> Based on the data I have information that should fill
> in this column U133A.
>
> What I have in the genechip_array table is
>
>  genechip_id |        fc_genechip_array  |gc_spec
> --+----------------------+------------------
>
> 5 | Human Genome U133A Array   | Homo sapiens
>
>
> Now how can I bring in the foreign key 5 to my
> previous table. All I have is U133A.
>
> Could any one help me in this.
>

Postgresql doesn't do this automatically.  A foreign key only functions
as a link between tables after it is formed.  You must supply the link
in the first place by a lookup in the foreign table either in the
client or as part of a function to load the data.  You aren't going to
have many genechip_ids, so you could easily just append a column full
of "5" to the incoming data for each of those tables if you want a
quick and easy solution.

Sean

Re: Please help

From
Kumar S
Date:
Hi Sean,
I tried a SELECT statement in INSERT Statement.



INSERT into xx(aa,aa,my_FK) values(bb,bb,

(SELECT my_fk
from my_chip_table
where chip_description ~'U133A'
)
);

The problem with this procedure is:

 genechip_id
-------------
           4
           5
(2 rows)


I am getting two chips:

4 | Human Genome U133A 2.0 Array |Homo sapiens
5 | Human Genome U133A Array  | Homo sapiens

U133A has also a Ver.2.

Any suggestions?

Thanks
Kumar




--- Sean Davis <sdavis2@mail.nih.gov> wrote:

>
> On Mar 1, 2005, at 12:32 PM, Kumar S wrote:
>
> > Hello all,
> >
> >
> > I have a table and it has is like this:
> > exp_id
> >  exp_name
> >  exp_type
> >  exp_desc
> >  exp_pmid
> >  exp_paper
> >  exp_author
> >  genechip_id
> >  exp_rawdata_link
> >  con_id
> > Indexes:
> >     "experiment_pkey" primary key, btree (exp_id)
> > Foreign-key constraints:
> >     "$1" FOREIGN KEY (genechip_id) REFERENCES
> > genechip_array(genechip_id)
> >     "$2" FOREIGN KEY (con_id) REFERENCES
> > contacts(con_id)
> >
> >
> > Based on the data I have information that should
> fill
> > in this column U133A.
> >
> > What I have in the genechip_array table is
> >
> >  genechip_id |        fc_genechip_array  |gc_spec
> > --+----------------------+------------------
> >
> > 5 | Human Genome U133A Array   | Homo sapiens
> >
> >
> > Now how can I bring in the foreign key 5 to my
> > previous table. All I have is U133A.
> >
> > Could any one help me in this.
> >
>
> Postgresql doesn't do this automatically.  A foreign
> key only functions
> as a link between tables after it is formed.  You
> must supply the link
> in the first place by a lookup in the foreign table
> either in the
> client or as part of a function to load the data.
> You aren't going to
> have many genechip_ids, so you could easily just
> append a column full
> of "5" to the incoming data for each of those tables
> if you want a
> quick and easy solution.
>
> Sean
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250

Re: Please help

From
Date:
kumar, if you know the key is always the highest id
(in this case 5) then you can sort in descending order
and limit your result to one entry - which, by
definition, will be the largest "id" (equal to 5 in
this case).

something like...

$id = "SELECT cust_id from customer  where
ustomer_name ='$cust' order by cust_id desc limit 1";

except applied to your case.  pay special attention
to...

"order by cust_id desc limit 1"

Vishal showed me this example to solve another problem
and it may well solve yours, too.

--- Kumar S <ps_postgres@yahoo.com> wrote:

> Hi Sean,
> I tried a SELECT statement in INSERT Statement.
>
>
>
> INSERT into xx(aa,aa,my_FK) values(bb,bb,
>
> (SELECT my_fk
> from my_chip_table
> where chip_description ~'U133A'
> )
> );
>
> The problem with this procedure is:
>
>  genechip_id
> -------------
>            4
>            5
> (2 rows)
>
>
> I am getting two chips:
>
> 4 | Human Genome U133A 2.0 Array |Homo sapiens
> 5 | Human Genome U133A Array  | Homo sapiens
>
> U133A has also a Ver.2.
>
> Any suggestions?
>
> Thanks
> Kumar
>
>
>
>
> --- Sean Davis <sdavis2@mail.nih.gov> wrote:
>
> >
> > On Mar 1, 2005, at 12:32 PM, Kumar S wrote:
> >
> > > Hello all,
> > >
> > >
> > > I have a table and it has is like this:
> > > exp_id
> > >  exp_name
> > >  exp_type
> > >  exp_desc
> > >  exp_pmid
> > >  exp_paper
> > >  exp_author
> > >  genechip_id
> > >  exp_rawdata_link
> > >  con_id
> > > Indexes:
> > >     "experiment_pkey" primary key, btree
> (exp_id)
> > > Foreign-key constraints:
> > >     "$1" FOREIGN KEY (genechip_id) REFERENCES
> > > genechip_array(genechip_id)
> > >     "$2" FOREIGN KEY (con_id) REFERENCES
> > > contacts(con_id)
> > >
> > >
> > > Based on the data I have information that should
> > fill
> > > in this column U133A.
> > >
> > > What I have in the genechip_array table is
> > >
> > >  genechip_id |        fc_genechip_array
> |gc_spec
> > > --+----------------------+------------------
> > >
> > > 5 | Human Genome U133A Array   | Homo sapiens
> > >
> > >
> > > Now how can I bring in the foreign key 5 to my
> > > previous table. All I have is U133A.
> > >
> > > Could any one help me in this.
> > >
> >
> > Postgresql doesn't do this automatically.  A
> foreign
> > key only functions
> > as a link between tables after it is formed.  You
> > must supply the link
> > in the first place by a lookup in the foreign
> table
> > either in the
> > client or as part of a function to load the data.
> > You aren't going to
> > have many genechip_ids, so you could easily just
> > append a column full
> > of "5" to the incoming data for each of those
> tables
> > if you want a
> > quick and easy solution.
> >
> > Sean
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> > unregister command
> >     (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
> >
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail - Find what you need with new enhanced
> search.
> http://info.mail.yahoo.com/mail_250
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - 250MB free storage. Do more. Manage less.
http://info.mail.yahoo.com/mail_250

Re: Please help

From
Sean Davis
Date:
On Mar 1, 2005, at 1:03 PM, Kumar S wrote:

> Hi Sean,
> I tried a SELECT statement in INSERT Statement.
>
>
>
> INSERT into xx(aa,aa,my_FK) values(bb,bb,
>
> (SELECT my_fk
> from my_chip_table
> where chip_description ~'U133A'
> )
> );
>
> The problem with this procedure is:
>
>  genechip_id
> -------------
>            4
>            5
> (2 rows)
>
>
> I am getting two chips:
>
> 4 | Human Genome U133A 2.0 Array |Homo sapiens
> 5 | Human Genome U133A Array  | Homo sapiens
>
> U133A has also a Ver.2.
>

So which one do you want?  If you know you want only #5, then you can
just do

~ 'U133A Array'

for hgu133A or

~ 'U133A 2'

for AV2.

If you want to lump all 133A arrays together, regardless of version, I
would suggest changing the my_chip_table to have only one U133 entry to
stave off future confusion.

Sean

Form Design Advice

From
Date:
i'm developing a number of data entry forms.  thanks
to many on this board, i'm getting pretty close.  i
*really* do appreciate everyone's knowledge and
insight and their unselfish spirit.

i want my data enterer to be able to verify what they
entered and still minimize the clicks required to
enter data.

i ahve a form process that looks like this...

1. display blank form
2. perform insert after data is submitted (assuming no
errors which i check for)
3. perform select to get data entered
4. save data in global session array
5. redisplay the empty form (ready for new input) with
the printed session array components (representing
what was entered) above the form followed by "
successfully entered."

now that i've moved beyond filling in one column in
one table, this seems to be an overhead hog -
especially when multiple columns are entered into
multiple tables.

it's design decision time... am i being to picky by
wanting to display the data without adding any extra
button clicks?

does anybody have a "cool green" way of getting this
done?

i'm thinking i can just do the data entry (and avoid
the extra selects and session work) and include a
button to include a user input variable number of most
recent entries.

tia...



__________________________________
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo

Re: Form Design Advice

From
Sean Davis
Date:
On Mar 1, 2005, at 3:39 PM, <operationsengineer1@yahoo.com> wrote:

> i'm developing a number of data entry forms.  thanks
> to many on this board, i'm getting pretty close.  i
> *really* do appreciate everyone's knowledge and
> insight and their unselfish spirit.
>
> i want my data enterer to be able to verify what they
> entered and still minimize the clicks required to
> enter data.
>
> i ahve a form process that looks like this...
>
> 1. display blank form
> 2. perform insert after data is submitted (assuming no
> errors which i check for)
> 3. perform select to get data entered
> 4. save data in global session array
> 5. redisplay the empty form (ready for new input) with
> the printed session array components (representing
> what was entered) above the form followed by "
> successfully entered."
>
> now that i've moved beyond filling in one column in
> one table, this seems to be an overhead hog -
> especially when multiple columns are entered into
> multiple tables.
>
> it's design decision time... am i being to picky by
> wanting to display the data without adding any extra
> button clicks?
>
> does anybody have a "cool green" way of getting this
> done?
>
> i'm thinking i can just do the data entry (and avoid
> the extra selects and session work) and include a
> button to include a user input variable number of most
> recent entries.

In what framework are you doing this--CGI?

Sean

Re: Form Design Advice

From
Date:
php on my dev box (winxp with apache and cygwin) is
installed as cgi, though the production db will be set
up as a module.

i'm using php 4.3, the latest version of adodb, and
postgresql 7.4.

--- Sean Davis <sdavis2@mail.nih.gov> wrote:

>
> On Mar 1, 2005, at 3:39 PM,
> <operationsengineer1@yahoo.com> wrote:
>
> > i'm developing a number of data entry forms.
> thanks
> > to many on this board, i'm getting pretty close.
> i
> > *really* do appreciate everyone's knowledge and
> > insight and their unselfish spirit.
> >
> > i want my data enterer to be able to verify what
> they
> > entered and still minimize the clicks required to
> > enter data.
> >
> > i ahve a form process that looks like this...
> >
> > 1. display blank form
> > 2. perform insert after data is submitted
> (assuming no
> > errors which i check for)
> > 3. perform select to get data entered
> > 4. save data in global session array
> > 5. redisplay the empty form (ready for new input)
> with
> > the printed session array components (representing
> > what was entered) above the form followed by "
> > successfully entered."
> >
> > now that i've moved beyond filling in one column
> in
> > one table, this seems to be an overhead hog -
> > especially when multiple columns are entered into
> > multiple tables.
> >
> > it's design decision time... am i being to picky
> by
> > wanting to display the data without adding any
> extra
> > button clicks?
> >
> > does anybody have a "cool green" way of getting
> this
> > done?
> >
> > i'm thinking i can just do the data entry (and
> avoid
> > the extra selects and session work) and include a
> > button to include a user input variable number of
> most
> > recent entries.
>
> In what framework are you doing this--CGI?
>
> Sean
>
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail

difference between varchar and varchar[] in pgadmin3 (and maybe postgres, too)

From
Date:
what is the difference between varchar and varchar[]
in pgadmin3?  i googled it and nothing turned up.

i'm thinking not much, but i'm twice shy when it comes
to little details that may end causing me much grief
in the future.


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: Form Design Advice

From
Frank Bax
Date:
At 03:39 PM 3/1/05, operationsengineer1@yahoo.com wrote:
>i ahve a form process that looks like this...
>
>1. display blank form
>2. perform insert after data is submitted (assuming no
>errors which i check for)
>3. perform select to get data entered
>4. save data in global session array
>5. redisplay the empty form (ready for new input) with
>the printed session array components (representing
>what was entered) above the form followed by "
>successfully entered."


You have it backwards.  This can all be done in a single script with this
structure:
1.  Check to see if there is any incoming data.  If there is incoming data,
validate it, and insert into your database.
2.  Display data from your database.
3.  Present an empty form.

In your form tag, use POST="" with null value, which causes results to go
back to same page.

Frank

Re: Form Design Advice

From
Keith Worthington
Date:
operationsengineer1@yahoo.com wrote:

>i'm developing a number of data entry forms.  thanks
>to many on this board, i'm getting pretty close.  i
>*really* do appreciate everyone's knowledge and
>insight and their unselfish spirit.
>
>i want my data enterer to be able to verify what they
>entered and still minimize the clicks required to
>enter data.
>
>i ahve a form process that looks like this...
>
>1. display blank form
>2. perform insert after data is submitted (assuming no
>errors which i check for)
>3. perform select to get data entered
>4. save data in global session array
>5. redisplay the empty form (ready for new input) with
>the printed session array components (representing
>what was entered) above the form followed by "
>successfully entered."
>
>now that i've moved beyond filling in one column in
>one table, this seems to be an overhead hog -
>especially when multiple columns are entered into
>multiple tables.
>
>it's design decision time... am i being to picky by
>wanting to display the data without adding any extra
>button clicks?
>
>does anybody have a "cool green" way of getting this
>done?
>
>i'm thinking i can just do the data entry (and avoid
>the extra selects and session work) and include a
>button to include a user input variable number of most
>recent entries.
>
>tia...
>
>
Keith's first rule of user interface design "Given the opportunity to
screw something up the user will."

IMHO If you display the data that the user just entered they will ignore
the message that the data submission was successful and cheerfully
resubmit the dataset believing that it was not accepted the first time.
One way to prevent this is to lock all the input fields and perhaps
change the color of the fields so that the dumb user notices and
disable/remove the submit button.  IF a combination of the fields is the
primary key then the database could help prevent resubmission as well.
Having said that; if the data that the user just submitted is displayed
then how do they clear the form to enter the next set?  Click a clear
button perhaps?  So your left with click to clear or click to view
last.  Your preference.  Personally I would choose the latter.  HTH

--
Kind Regards,
Keith

Re: Form Design Advice

From
Date:
--- Keith Worthington <KeithW@NarrowPathInc.com>
wrote:

> operationsengineer1@yahoo.com wrote:
>
> >i'm developing a number of data entry forms.
> thanks
> >to many on this board, i'm getting pretty close.  i
> >*really* do appreciate everyone's knowledge and
> >insight and their unselfish spirit.
> >
> >i want my data enterer to be able to verify what
> they
> >entered and still minimize the clicks required to
> >enter data.
> >
> >i ahve a form process that looks like this...
> >
> >1. display blank form
> >2. perform insert after data is submitted (assuming
> no
> >errors which i check for)
> >3. perform select to get data entered
> >4. save data in global session array
> >5. redisplay the empty form (ready for new input)
> with
> >the printed session array components (representing
> >what was entered) above the form followed by "
> >successfully entered."
> >
> >now that i've moved beyond filling in one column in
> >one table, this seems to be an overhead hog -
> >especially when multiple columns are entered into
> >multiple tables.
> >
> >it's design decision time... am i being to picky by
> >wanting to display the data without adding any
> extra
> >button clicks?
> >
> >does anybody have a "cool green" way of getting
> this
> >done?
> >
> >i'm thinking i can just do the data entry (and
> avoid
> >the extra selects and session work) and include a
> >button to include a user input variable number of
> most
> >recent entries.
> >
> >tia...
> >
> >
> Keith's first rule of user interface design "Given
> the opportunity to
> screw something up the user will."
>
> IMHO If you display the data that the user just
> entered they will ignore
> the message that the data submission was successful
> and cheerfully
> resubmit the dataset believing that it was not
> accepted the first time.
> One way to prevent this is to lock all the input
> fields and perhaps
> change the color of the fields so that the dumb user
> notices and
> disable/remove the submit button.  IF a combination
> of the fields is the
> primary key then the database could help prevent
> resubmission as well.
> Having said that; if the data that the user just
> submitted is displayed
> then how do they clear the form to enter the next
> set?  Click a clear
> button perhaps?  So your left with click to clear or
> click to view
> last.  Your preference.  Personally I would choose
> the latter.  HTH
>
> --
> Kind Regards,
> Keith

keith, forgive me for not being more clear.  i was
going to print out the data that was entered *above*
the blank form fields.

all the user has to do is visually review what was
entered and then type in their next entry in the blank
form fields.

this minimizes the clicks required to enter data.  it
is one click per data set entered.



__________________________________
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250

Re: Form Design Advice

From
Date:
> You have it backwards.  This can all be done in a
> single script with this
> structure:
> 1.  Check to see if there is any incoming data.  If
> there is incoming data,
> validate it, and insert into your database.
> 2.  Display data from your database.
> 3.  Present an empty form.

frank, i didn't mention it, but i do validate the data
using javascript.  i will eventually add in regular
expressions, too, but that's down the road a bit.

i then insert the data.

i could display the data, but then we are talking two
clicks for each data set entered - or at least some
period of time between form display.  admittedly, i'm
probably being hyper technical here, but one click is
more efficient from the user pov.

enter data -> click -> quickly review data if desired
-> enter more data -> click...  etc.

as opposed to

enter data -> click -> quickly review data -> click ->
enter more data -> click...  etc.

that's 100% increase in required clicks.

i can see the programming logic in the method you
outlined, though.  all those session variables get
rather difficult to manage when lots of data is involved.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: difference between varchar and varchar[] in pgadmin3 (and

From
George Weaver
Date:
Varchar[] represents an array of type varchar.

----- Original Message -----
From: <operationsengineer1@yahoo.com>
To: <pgsql-novice@postgresql.org>
Sent: Tuesday, March 01, 2005 3:32 PM
Subject: [NOVICE] difference between varchar and varchar[] in pgadmin3 (and
maybe postgres, too)


> what is the difference between varchar and varchar[]
> in pgadmin3?  i googled it and nothing turned up.
>
> i'm thinking not much, but i'm twice shy when it comes
> to little details that may end causing me much grief
> in the future.
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

Re: Form Design Advice

From
"Ross Gohlke"
Date:
Overhead is not really the issue.

The real question is how to reuse the same page/script.
Let's say your script is form.php, accessed at www.mydomain.org/form.php

Your POST has to point to itself
<form method="post" action="form.php">

Set up a check system with two different states: check, process; the
default is check
<input type="hidden" name="action" value="check">

LOGIC
If action = check (always the case when form is submitted)
  do error checking
  if errors
   display error messages
  else action = process
if action = process
  $x = 0
  foreach ($form as $key => $val)
   $field[$x] = $val[$x]
   $x++
  insert into table set $field[0] = $val[0], $field[1] = $val[0]...
foreach ($form as $key => $val)
   echo $key : $val // display results
display form
Use the form field names to construct form values as an array
<input  type="text" name="form[$fieldname]" value="">

The form is always displayed. Error messages and results both appear above
the form. Nothing is inserted until there are no errors.

Use PHP to validate the input.

> 1. display blank form
> 2. perform insert after data is submitted (assuming no
> errors which i check for)
> 3. perform select to get data entered
> 4. save data in global session array
> 5. redisplay the empty form (ready for new input) with
> the printed session array components (representing
> what was entered) above the form followed by "
> successfully entered."
>
> now that i've moved beyond filling in one column in
> one table, this seems to be an overhead hog -
> especially when multiple columns are entered into
> multiple tables.
>
> it's design decision time... am i being to picky by
> wanting to display the data without adding any extra
> button clicks?
>
> does anybody have a "cool green" way of getting this
> done?
>
> i'm thinking i can just do the data entry (and avoid
> the extra selects and session work) and include a
> button to include a user input variable number of most
> recent entries.
>
> tia...
>
>
>
> __________________________________
> Do you Yahoo!?
> Take Yahoo! Mail with you! Get it on your mobile phone.
> http://mobile.yahoo.com/maildemo
>
> ---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

--
Ross Gohlke <-> http://GRINZ.com
P> 901.276.9750 (Memphis) / 310.356.6906 (Los Angeles)
FAX/HOTLINE> 877.806.0861
99 S. 2nd St. #A-161
Memphis, TN 38103

| interactive zen |

Re: Form Design Advice

From
Date:
--- Ross Gohlke <ross@grinz.com> wrote:

> Overhead is not really the issue.
>
> The real question is how to reuse the same
> page/script.
> Let's say your script is form.php, accessed at
> www.mydomain.org/form.php
>
> Your POST has to point to itself
> <form method="post" action="form.php">
>
> Set up a check system with two different states:
> check, process; the
> default is check
> <input type="hidden" name="action" value="check">
>
> LOGIC
> If action = check (always the case when form is
> submitted)
>   do error checking
>   if errors
>    display error messages
>   else action = process
> if action = process
>   $x = 0
>   foreach ($form as $key => $val)
>    $field[$x] = $val[$x]
>    $x++
>   insert into table set $field[0] = $val[0],
> $field[1] = $val[0]...
> foreach ($form as $key => $val)
>    echo $key : $val // display results
> display form
> Use the form field names to construct form values as
> an array
> <input  type="text" name="form[$fieldname]"
> value="">
>
> The form is always displayed. Error messages and
> results both appear above
> the form. Nothing is inserted until there are no
> errors.
>
> Use PHP to validate the input.

ross, i have the logic and error checking (for both
form entry and db process) down.  i'm just wondering
if it is worth displaying the entry for a quick user
visual check - not in the form, but above the form.
for large forms, though, this takes a lot of overhead,
although, my sessions approach could be eliminated to
reduce lots of coding.

you struck on something that may be even more
important to me.  you seem to have a function that
iterates through the form elements.

this method seems to work nicely, although i don't
know enough to decide if it will work for me.  i'm
using manuel lemos' forms class from phpclasses.org.

http://www.phpclasses.org/browse/package/1.html

do you have a link to a tutorial that explains this
approach in more depth?  i'll do some web searches now
to see what i can come up with and i'll report back.




__________________________________
Celebrate Yahoo!'s 10th Birthday!
Yahoo! Netrospective: 100 Moments of the Web
http://birthday.yahoo.com/netrospective/

Re: Form Design Advice

From
Bruno Wolff III
Date:
On Tue, Mar 01, 2005 at 17:53:30 -0800,
  operationsengineer1@yahoo.com wrote:
>
> frank, i didn't mention it, but i do validate the data
> using javascript.  i will eventually add in regular
> expressions, too, but that's down the road a bit.

While that is OK in some uses, in general you shouldn't be trusting data
validated by the client. You can use that to save a round trip, but the
data should be validated by the server as well.

Re: Form Design Advice

From
"Ross Gohlke"
Date:
> ross, i have the logic and error checking (for both
> form entry and db process) down.  i'm just wondering
> if it is worth displaying the entry for a quick user
> visual check - not in the form, but above the form.
> for large forms, though, this takes a lot of overhead,
> although, my sessions approach could be eliminated to
> reduce lots of coding.

There's two kinds of overhead here. Processing overhead effects your
users. Coding overhead effects you. The following approach increases the
former to eliminate the latter. If performance is your main concern,
forget doing anything interesting. However, in my experience, users will
take a performance hit in stride if what they get at the end is valuable.
In your example, I would ask, how valuable to the user is the data you
want to display? Will it make her more productive or efficient? Will it
decrease her mental overhead, making her work easier, less stressful and
more enjoyable? If so, a few extra seconds is a small price to pay.

If you're worried about whether your hardware can handle the extra load, I
say get new hardware.

> you struck on something that may be even more
> important to me.  you seem to have a function that
> iterates through the form elements.

> do you have a link to a tutorial that explains this
> approach in more depth?  i'll do some web searches now
> to see what i can come up with and i'll report back.

If your form fields are named after your table fields, you have a closed
loop from which you can dynamically generate the bulk of your code.

First, call the database and build an array of field names/numbers/types.
http://us4.php.net/manual/en/function.pg-field-name.php
http://us4.php.net/manual/en/function.pg-field-num.php
http://us4.php.net/manual/en/function.pg-field-type.php

Iterate through this array to declare global form variables. I use
eval(), but I'm pretty sure there's a better way.

// EXAMPLE ELEMENT ARRAYS:
$arr[$fieldnum][0] = $fieldname;
$arr[$fieldnum][1] = $fieldtype;

foreach($arr as $a) {
  eval("\$" . $a[0] . " = \$_POST[" . $a[0] . "];";
  }

Code your form manually with the proper element names. Add a hidden
element for each visible element to pass the field's type for validation
purposes.

Use the same array to build your insert statement
when the form is submitted, corrected and validated.

$insert = "insert into table set (";
$x = 0;
foreach($arr as $a) {
  $insert .= $a[0];
  if($x != count($arr - 1)) $insert .= ", ";
  $x++;
  }
$insert .= ") VALUES (";
$x = 0;
foreach($arr as $a) {
  $insert .= "'" . $$fieldname . "'";
  if($x != count($arr - 1)) $insert .= ", ";
  $x++;
  }
$insert .= ");";

Re: Form Design Advice

From
Bruno Wolff III
Date:
On Fri, Mar 04, 2005 at 00:27:05 -0600,
  Ross Gohlke <ross@grinz.com> wrote:
>
> Code your form manually with the proper element names. Add a hidden
> element for each visible element to pass the field's type for validation
> purposes.

This should be in an additional table in the database, not on the form.
Otherwise the end users can send back incorrect types to check against
which could potentially be a security issue.

Re: Form Design Advice

From
"Ross Gohlke"
Date:
>>Code your form manually with the proper element names. Add a hidden
element for each visible element to pass the field's type for validation

>>purposes.
>
> This should be in an additional table in the database, not on the form.
Otherwise the end users can send back incorrect types to check against
which could potentially be a security issue.

I'm not sure I understand. How could a user send incorrect data types if
the types are included as hidden fields? Since the variables are
declared as coming from $_POST, they cannot send anything in the URL.

Re: Form Design Advice

From
Colin McGuigan
Date:
Ross Gohlke wrote:
 > I'm not sure I understand. How could a user send incorrect data types if
 > the types are included as hidden fields? Since the variables are
 > declared as coming from $_POST, they cannot send anything in the URL.

Nothing stops anyone from copying the "View Source" of a webpage to a
local file, modifying it as they wish, and then pointing their web
browser at the local file and submitting from that.

--Colin McGuigan

Re: Form Design Advice

From
"Ross Gohlke"
Date:
>  > I'm not sure I understand. How could a user send incorrect data types
if
>  > the types are included as hidden fields? Since the variables are
declared as coming from $_POST, they cannot send anything in the URL.
>
> Nothing stops anyone from copying the "View Source" of a webpage to a
local file, modifying it as they wish, and then pointing their web
browser at the local file and submitting from that.

OK, fair enough. In fact, the hidden fieldtypes in the form are
unnecessary in addition to being unsafe. But you still have the correct
fieldtypes in the array you initially derived from your call, so you can
still prevent any funny business such as mentioned above.

Ross

Re: Form Design Advice

From
Geoffrey
Date:
Colin McGuigan wrote:
> Ross Gohlke wrote:
>  > I'm not sure I understand. How could a user send incorrect data types if
>  > the types are included as hidden fields? Since the variables are
>  > declared as coming from $_POST, they cannot send anything in the URL.
>
> Nothing stops anyone from copying the "View Source" of a webpage to a
> local file, modifying it as they wish, and then pointing their web
> browser at the local file and submitting from that.

Correct and another point to understand is that 'hidden' fields are not
hidden.

--
Until later, Geoffrey

Re: Form Design Advice

From
Bruno Wolff III
Date:
On Fri, Mar 04, 2005 at 11:00:24 -0600,
  Ross Gohlke <ross@grinz.com> wrote:
> >  > I'm not sure I understand. How could a user send incorrect data types
> if
> >  > the types are included as hidden fields? Since the variables are
> declared as coming from $_POST, they cannot send anything in the URL.
> >
> > Nothing stops anyone from copying the "View Source" of a webpage to a
> local file, modifying it as they wish, and then pointing their web
> browser at the local file and submitting from that.

In fact I have actually done that a few times. Though usually to get
around javascript limitations rather than mess with hidden fields.

> OK, fair enough. In fact, the hidden fieldtypes in the form are
> unnecessary in addition to being unsafe. But you still have the correct
> fieldtypes in the array you initially derived from your call, so you can
> still prevent any funny business such as mentioned above.

That is pretty much my suggestion. You want to get the data from the server
side.

A key rule for security in client-server applications is to never trust
anything done by the client. (You can have the client do some calculations
on its end to save needlessly bothering the server. You just can't trust
those calculations when data is passed to the server.)

Re: Form Design Advice

From
Mike Ellsworth
Date:
Thanks for all that have contributed to this thread.

It has been very helpful for an 'about to happen' project.

Mike Ellsworth




Bruno Wolff III wrote:
On Fri, Mar 04, 2005 at 11:00:24 -0600, Ross Gohlke <ross@grinz.com> wrote: 
 > I'm not sure I understand. How could a user send incorrect data types     
if   
 > the types are included as hidden fields? Since the variables are     
declared as coming from $_POST, they cannot send anything in the URL.   
Nothing stops anyone from copying the "View Source" of a webpage to a      
local file, modifying it as they wish, and then pointing their web 
browser at the local file and submitting from that.   
In fact I have actually done that a few times. Though usually to get
around javascript limitations rather than mess with hidden fields.
 
OK, fair enough. In fact, the hidden fieldtypes in the form are
unnecessary in addition to being unsafe. But you still have the correct 
fieldtypes in the array you initially derived from your call, so you can 
still prevent any funny business such as mentioned above.   
That is pretty much my suggestion. You want to get the data from the server
side.

A key rule for security in client-server applications is to never trust
anything done by the client. (You can have the client do some calculations
on its end to save needlessly bothering the server. You just can't trust
those calculations when data is passed to the server.)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org