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

Search Discussions

  • John D. Burger at Jan 11, 2007 at 10:40 pm

    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
  • Scassidy at Jan 12, 2007 at 12:47 am
    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
    ----------------------------------------------------------------------------------------------
  • Ragnar at Jan 12, 2007 at 10:25 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJan 11, '07 at 1:38a
activeJan 12, '07 at 10:25p
posts4
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase