Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question - Query based on WHERE OR

Question - Query based on WHERE OR

From
"Mike Poe"
Date:
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

Re: Question - Query based on WHERE OR

From
"John D. Burger"
Date:
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

Re: Question - Query based on WHERE OR

From
SCassidy@overlandstorage.com
Date:

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" <trolling4dollars@gmail.com>
Sent by: pgsql-general-owner@postgresql.org

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
----------------------------------------------------------------------------------------------

Re: Question - Query based on WHERE OR

From
Ragnar
Date:
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