Обсуждение: Incorrect Query

Hi,

I'm working on an application, the user enters the customers id or their firstname or their lastname or their firstname
andlastname.  The problem is when they enter their firstname AND their lastname it returns as if you had entered either
theirfirstname OR their lastname.  I must be missing brackets somewhere, but I can't figure it out, I've tried
everythingI could think of, does anyone have any ideas?  

Query:
select person_id, initcap(firstname), initcap(lastname), dob, street, city
from person5
where person_id = ''
or ( ( firstname = initcap('sharon') )
or ( lastname = initcap('cowling') ) )
or ( ( firstname = initcap('sharon')
and lastname = initcap('cowling')  ) )
order by lastname;

Results:
 person_id | initcap | initcap  |    dob     | street  |    city
-----------+---------+----------+------------+---------+------------
      1018 | Katrina | Cowling  | 07/07/1976 | Long St | Wellington
       858 | Sharon  | Cowling  | 16/10/1979 | A Grove | Wellington
      1060 | Sharon  | O'neill  | 11/11/1977 | Fsddf   | Fdsfds
      1062 | Sharon  | O'ray    | 11/11/1977 | Dfs     | Sfsf
      1061 | Sharon  | O'reilly | 11/11/1977 | Sfds    | Sfdsfs
       880 | Sharon  | Smiley   | 16/11/1979 | The St  | The City
(6 rows)



sharon=> \d person5
                   Table "person5"
    Attribute     |         Type          | Modifier
------------------+-----------------------+----------
 person_id        | integer               | not null
 firstname        | character varying(25) | not null
 lastname         | character varying(25) | not null
 dob              | date                  | not null
 street           | character varying(50) | not null
 suburb           | character varying(50) |
 city             | character varying(50) | not null
 homephone        | character varying(15) |
 workphone        | character varying(15) |
 mobile           | character varying(15) |
 type             | character varying(30) | not null
 date_approved    | date                  | not null
 approved_by      | character varying(50) | not null
 vehicle_type     | character varying(50) |
 vehicle_rego     | character varying(6)  |
 drivers_licence  | character varying(10) |
 firearms_licence | character varying(20) |
 notes            | character varying(80) |
 status           | character varying(10) |
Indices: firstname_idx,
         fullname_idx,
         lastname_idx,
         person5_drivers_licence_key,
         person5_firearms_licence_key,
         person5_pkey


Regards,

Sharon Cowling

On Wed, 2002-05-08 at 17:06, Sharon Cowling wrote:
> Hi,
>
> I'm working on an application, the user enters the customers id or
> their firstname or their lastname or their firstname and lastname.
>  The problem is when they enter their firstname AND their lastname
> it returns as if you had entered either their firstname OR their
> lastname.  I must be missing brackets somewhere, but I can't figure
> it out, I've tried everything I could think of, does anyone have
> any ideas?
>
> Query:
> select person_id, initcap(firstname), initcap(lastname), dob, street, city
> from person5
> where person_id = ''
> or ( ( firstname = initcap('sharon') )
> or ( lastname = initcap('cowling') ) )
> or ( ( firstname = initcap('sharon')
> and lastname = initcap('cowling')  ) )
> order by lastname;

Hi Sharon,

Several points here:

Firstly, you are presumably building this SQL in a program.  You should
probably look at the values for firstname and lastname in your program,
and construct your SQL differently, as appropriate.

If that's not possible, and you have to have a static SQL string that
you replace values into, then you will want to do something like this:

SELECT ...
  WHERE (firstname = '$fname' AND '' = '$lname' )
     OR (lastname = '$lname' AND '' = '$fname' )
     OR (firstname = '$fname' AND lastname = '$lname' )

This means that you compare firstname with $fname _only_ if $lname is
empty, and vice versa.  I'll leave it to you to decide if the person_id
= '' was doing the correct thing...

Thirdly, if you don't trust your database to contain 'initcap()' values
(looking at your output specifiers), how can you compare against
initcap() values in the where clause!  In the where clause you should
really be something like:
  WHERE lower(firstname) = lower('$fname') ...

Cheers,
                        Andrew.

PS. Say "Hi" to Andrew McClure from me :-)
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?

Hi Andrew,

Sorry I should have said the values were just examples, its a java program so
the id, firstname, and lastname are variables taken from the user entry, I left
the id blank in the example as more often than not the user will only be
entering the persons name.

The names are stored with initcap() values in the database, but when the person
is doing a search to retrieve this data they may enter the name all in lower
case, so I probably don't need initcap() in the select clause, thanks for your
advice I will look at a different way of comparing the values in my java code.

I'll say hi to Andrew for you.

Regards,

Sharon Cowling



Quoting Andrew McMillan <andrew@catalyst.net.nz>:

> On Wed, 2002-05-08 at 17:06, Sharon Cowling wrote:
> > Hi,
> >
> > I'm working on an application, the user enters the customers id or
> > their firstname or their lastname or their firstname and lastname.
> >  The problem is when they enter their firstname AND their lastname
> > it returns as if you had entered either their firstname OR their
> > lastname.  I must be missing brackets somewhere, but I can't figure
> > it out, I've tried everything I could think of, does anyone have
> > any ideas?
> >
> > Query:
> > select person_id, initcap(firstname), initcap(lastname), dob, street, city
>
> > from person5
> > where person_id = ''
> > or ( ( firstname = initcap('sharon') )
> > or ( lastname = initcap('cowling') ) )
> > or ( ( firstname = initcap('sharon')
> > and lastname = initcap('cowling')  ) )
> > order by lastname;
>
> Hi Sharon,
>
> Several points here:
>
> Firstly, you are presumably building this SQL in a program.  You should
> probably look at the values for firstname and lastname in your program,
> and construct your SQL differently, as appropriate.
>
> If that's not possible, and you have to have a static SQL string that
> you replace values into, then you will want to do something like this:
>
> SELECT ...
>   WHERE (firstname = '$fname' AND '' = '$lname' )
>      OR (lastname = '$lname' AND '' = '$fname' )
>      OR (firstname = '$fname' AND lastname = '$lname' )
>
> This means that you compare firstname with $fname _only_ if $lname is
> empty, and vice versa.  I'll leave it to you to decide if the person_id
> = '' was doing the correct thing...
>
> Thirdly, if you don't trust your database to contain 'initcap()' values
> (looking at your output specifiers), how can you compare against
> initcap() values in the where clause!  In the where clause you should
> really be something like:
>   WHERE lower(firstname) = lower('$fname') ...
>
> Cheers,
>                         Andrew.
>
> PS. Say "Hi" to Andrew McClure from me :-)
> --
> --------------------------------------------------------------------
> Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
> WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
> DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
>        Are you enrolled at http://schoolreunions.co.nz/ yet?
>


-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/

Sharon,
You were abusing parentheses and confusing the issue. Don't do that, it
just makes the query less readable.

Here is what you actually wrote:

where
person_id = ''
or
(
   (
       firstname = initcap('sharon')
   )
   or
   (
       lastname = initcap('cowling')
   )
)
or
(
   (
       firstname = initcap('sharon')
       and
       lastname = initcap('cowling')
   )
)

Which is simplified to (removing parentheses where redundant but
retaining all the logic). Check out that first bit where you match on
fname or lname. That last bit doesn't even do anything since the other
name expressions covered it already.

where
person_id = ''
or
firstname = initcap('sharon')
or
lastname = initcap('cowling')
or
(
   firstname = initcap('sharon')
   and
   lastname = initcap('cowling')
)

Why not just use?

WHERE person_id = ''
OR (firstname = initcap('sharon')
    AND
    lastname = initcap('cowling')
   )

Hi,

> Why not just use?
>
> WHERE person_id = ''
> OR (firstname = initcap('sharon')
>     AND
>     lastname = initcap('cowling')
>    )
>

Because the user may enter either just the firstname or just the lastname, so the above won't work if I just enter
'cowling'and there are multiple entries of 'cowling' in the database, it returns 0 rows. 


select person_id, initcap(firstname), initcap(lastname), dob, street, city
from person5
where person_id = ''
OR (firstname = initcap('')
AND
lastname = initcap('cowling')
);

 person_id | initcap | initcap | dob | street | city
-----------+---------+---------+-----+--------+------
(0 rows)

select person_id, initcap(firstname), initcap(lastname), dob, street, city
from person5
where
person_id = ''
or
firstname = initcap('')
or
lastname = initcap('cowling')
or
(
   firstname = initcap('sharon')
   and
   lastname = initcap('')
)

 person_id | initcap | initcap |    dob     | street  |    city
-----------+---------+---------+------------+---------+------------
      1018 | Katrina | Cowling | 07/07/1976 | Long St | Wellington
       858 | Sharon  | Cowling | 16/10/1979 | A Grove | Wellington

Regards,

Sharon Cowling


> -----Original Message-----
> From: Joshua b. Jore [mailto:josh@greentechnologist.org]
> Sent: Thursday, 9 May 2002 01:39
> To: Sharon Cowling
> Cc: Pgsql-Novice (E-mail)
> Subject: Re: [NOVICE] Incorrect Query
>
>
> Sharon,
> You were abusing parentheses and confusing the issue. Don't
> do that, it
> just makes the query less readable.
>
> Here is what you actually wrote:
>
> where
> person_id = ''
> or
> (
>    (
>        firstname = initcap('sharon')
>    )
>    or
>    (
>        lastname = initcap('cowling')
>    )
> )
> or
> (
>    (
>        firstname = initcap('sharon')
>        and
>        lastname = initcap('cowling')
>    )
> )
>
> Which is simplified to (removing parentheses where redundant but
> retaining all the logic). Check out that first bit where you match on
> fname or lname. That last bit doesn't even do anything since the other
> name expressions covered it already.
>
> where
> person_id = ''
> or
> firstname = initcap('sharon')
> or
> lastname = initcap('cowling')
> or
> (
>    firstname = initcap('sharon')
>    and
>    lastname = initcap('cowling')
> )
>
> Why not just use?
>
> WHERE person_id = ''
> OR (firstname = initcap('sharon')
>     AND
>     lastname = initcap('cowling')
>    )
>

Hi,

Sorry for bothering the list, had a bad day yesterday, acheived the desired effect in Java code.

Regards,

Sharon Cowling



> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Sharon Cowling
> Sent: Thursday, 9 May 2002 08:01
> To: Joshua b. Jore
> Cc: Pgsql-Novice (E-mail)
> Subject: Re: [NOVICE] Incorrect Query
>
>
> Hi,
>
> > Why not just use?
> >
> > WHERE person_id = ''
> > OR (firstname = initcap('sharon')
> >     AND
> >     lastname = initcap('cowling')
> >    )
> >
>
> Because the user may enter either just the firstname or just
> the lastname, so the above won't work if I just enter
> 'cowling' and there are multiple entries of 'cowling' in the
> database, it returns 0 rows.
>
>
> select person_id, initcap(firstname), initcap(lastname), dob,
> street, city
> from person5
> where person_id = ''
> OR (firstname = initcap('')
> AND
> lastname = initcap('cowling')
> );
>
>  person_id | initcap | initcap | dob | street | city
> -----------+---------+---------+-----+--------+------
> (0 rows)
>
> select person_id, initcap(firstname), initcap(lastname), dob,
> street, city
> from person5
> where
> person_id = ''
> or
> firstname = initcap('')
> or
> lastname = initcap('cowling')
> or
> (
>    firstname = initcap('sharon')
>    and
>    lastname = initcap('')
> )
>
>  person_id | initcap | initcap |    dob     | street  |    city
> -----------+---------+---------+------------+---------+------------
>       1018 | Katrina | Cowling | 07/07/1976 | Long St | Wellington
>        858 | Sharon  | Cowling | 16/10/1979 | A Grove | Wellington
>
> Regards,
>
> Sharon Cowling
>
>
> > -----Original Message-----
> > From: Joshua b. Jore [mailto:josh@greentechnologist.org]
> > Sent: Thursday, 9 May 2002 01:39
> > To: Sharon Cowling
> > Cc: Pgsql-Novice (E-mail)
> > Subject: Re: [NOVICE] Incorrect Query
> >
> >
> > Sharon,
> > You were abusing parentheses and confusing the issue. Don't
> > do that, it
> > just makes the query less readable.
> >
> > Here is what you actually wrote:
> >
> > where
> > person_id = ''
> > or
> > (
> >    (
> >        firstname = initcap('sharon')
> >    )
> >    or
> >    (
> >        lastname = initcap('cowling')
> >    )
> > )
> > or
> > (
> >    (
> >        firstname = initcap('sharon')
> >        and
> >        lastname = initcap('cowling')
> >    )
> > )
> >
> > Which is simplified to (removing parentheses where redundant but
> > retaining all the logic). Check out that first bit where
> you match on
> > fname or lname. That last bit doesn't even do anything
> since the other
> > name expressions covered it already.
> >
> > where
> > person_id = ''
> > or
> > firstname = initcap('sharon')
> > or
> > lastname = initcap('cowling')
> > or
> > (
> >    firstname = initcap('sharon')
> >    and
> >    lastname = initcap('cowling')
> > )
> >
> > Why not just use?
> >
> > WHERE person_id = ''
> > OR (firstname = initcap('sharon')
> >     AND
> >     lastname = initcap('cowling')
> >    )
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>