Question - Query based on WHERE OR

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

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

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