Re: [GENERAL] Question - Query based on WHERE OR

2007-01-12 Thread Ragnar 
On mið, 2007-01-10 at 17:38 -0800, Mike Poe wrote:
 I'm a rank newbie to Postgres  am having a hard time getting my arms
 around this.
 
 I'm trying to construct a query to be run in a PHP script.  I have an
 HTML form were someone can enter either a last name or a social
 security number  then query the database based on what they entered.
 
 My query looks like this:
 
 SELECT foo, baz, bar FROM public.table WHERE lastname ~* '$lastname' OR
 ssn='$ssn'

 I need to leave the last name a wildcard in case someone enters a
 partial name, lower case / upper case, etc.

note that you really should not be using values directly from
user input for $lastname and $ssn without doing some sanity
checks on them. consider for example the user submitting a
string containing a quote character.

most languages provide functions to make such input safe.

gnari



---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Question - Query based on WHERE OR

2007-01-11 Thread Mike Poe 
I'm a rank newbie to Postgres  am having a hard time getting my arms
around this.

I'm trying to construct a query to be run in a PHP script.  I have an
HTML form were someone can enter either a last name or a social
security number  then query the database based on what they entered.

My query looks like this:

SELECT foo, baz, bar FROM public.table WHERE lastname ~* '$lastname' OR
ssn='$ssn'

I need to leave the last name a wildcard in case someone enters a
partial name, lower case / upper case, etc.

I want the SSN to match exactly if they search by that.

The way it's written, if I enter nothing for the last name, it returns
all rows, regardless of what I enter for the ssn.

I understand why it happens, but what can I do to return the desired
results?

Thank you.
Mike


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Question - Query based on WHERE OR

2007-01-11 Thread John D. Burger 

Mike Poe wrote:

SELECT foo, baz, bar FROM public.table WHERE lastname ~*  
'$lastname' OR ssn='$ssn'


I need to leave the last name a wildcard in case someone enters a  
partial name, lower case / upper case, etc.

I want the SSN to match exactly if they search by that.
The way it's written, if I enter nothing for the last name, it  
returns all rows, regardless of what I enter for the ssn.
I understand why it happens, but what can I do to return the  
desired results?


How about:

SELECT foo, baz, bar FROM public.table
  WHERE ('$lastname' != '' and lastname ~* '$lastname') OR ssn='$ssn';

Or perhaps some more general condition for empty lastname parameters.

- John Burger
  MITRE

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Question - Query based on WHERE OR

2007-01-11 Thread SCassidy 
You could have your program check to see if the lastname form field was 
empty, and send different queries to the database depending on what they 
entered.

I'm a perl person, not php, so my php syntax might not be perfect, but 
you'll get the idea:

if ($lastname ==) {
  $query=SELECT foo, baz, bar FROM public.table WHERE ssn='$ssn';
} elsif (($lastname != ) and ($ssn != )) {
  $query=SELECT foo, baz, bar FROM public.table WHERE lastname ~* 
'$lastname' OR ssn='$ssn';
}

then execute the query 

or, get fancy, and build the where clause:

if ($lastname !=) {
  $where.= lastname ~* '$lastname';
}
if ($ssn != ) {
   if ($where != ) {
  $where.= OR ;
   } 
 $where.= ssn='$ssn';
}

$query=SELECT foo, baz, bar FROM public.table WHERE  $where;

Then, run the query.

Just a couple of ideas.

Susan Cassidy




Mike Poe [EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED]
01/10/2007 05:38 PM

To
pgsql-general@postgresql.org
cc

Subject
[GENERAL] Question - Query based on WHERE OR








I'm a rank newbie to Postgres  am having a hard time getting my arms
around this.

I'm trying to construct a query to be run in a PHP script.  I have an
HTML form were someone can enter either a last name or a social
security number  then query the database based on what they entered.

My query looks like this:

SELECT foo, baz, bar FROM public.table WHERE lastname ~* '$lastname' OR
ssn='$ssn'

I need to leave the last name a wildcard in case someone enters a
partial name, lower case / upper case, etc.

I want the SSN to match exactly if they search by that.

The way it's written, if I enter nothing for the last name, it returns
all rows, regardless of what I enter for the ssn.

I understand why it happens, but what can I do to return the desired
results?

Thank you.
Mike


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq



--
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at 
http://www.overlandstorage.com
--