Re: Incorrect Query

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?